Обсуждение: Composite type operator not unique
Hi, I'm trying to make a custom composite type and use it as a PK and FK. When adding FK to table I've got trhis error: operator is not unique: "BigintRef" pg_catalog.= "BigintRef" (see below). Here is my type definition with operator: CREATE TYPE "BigintRef" AS ("Value" bigint, "Null" boolean); CREATE OR REPLACE FUNCTION bigintref_op_eq("BigintRef", "BigintRef") RETURNS boolean AS 'SELECT ($1."Null" = TRUE AND $2."Null" = TRUE) OR ($1."Null" = FALSe AND $2."Null" = FALSE AND $1."Value" = $2."Value")' LANGUAGE sql IMMUTABLE COST 100; CREATE OPERATOR =( PROCEDURE = bigintref_op_eq, LEFTARG = "BigintRef", RIGHTARG = "BigintRef"); Here are table definitions: CREATE TEMP TABLE t_a(id "BigintRef"); CREATE TEMP TABLE t_b(id "BigintRef"); ALTER TABLE t_a ADD PRIMARY KEY(id); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "t_a_pkey" for table "t_a" Query returned successfully with no result in 51 ms. ALTER TABLE t_b ADD FOREIGN KEY(id) REFERENCES t_a(id); ERROR: operator is not unique: "BigintRef" pg_catalog.= "BigintRef" LINE 1: ..._temp_6"."t_a" pk ON ( pk."id"::pg_catalog.record OPERATOR(p... ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. QUERY: SELECT fk."id" FROM ONLY "pg_temp_6"."t_b" fk LEFT OUTER JOIN ONLY "pg_temp_6"."t_a" pk ON ( pk."id"::pg_catalog.record OPERATOR(pg_catalog.=) fk."id"::pg_catalog.record) WHERE pk."id" IS NULL AND (fk."id" IS NOT NULL) Anyone know what is wrong? I'm using PostgreSQL 9.0 Thanks Trigve
On Wed, Oct 6, 2010 at 2:48 AM, Trigve <trigves@gmail.com> wrote: > Hi, > I'm trying to make a custom composite type and use it as a PK and FK. > When adding FK to table I've got trhis error: operator is not unique: > "BigintRef" pg_catalog.= "BigintRef" (see below). Here is my type > definition with operator: > > CREATE TYPE "BigintRef" AS > ("Value" bigint, > "Null" boolean); > > CREATE OR REPLACE FUNCTION bigintref_op_eq("BigintRef", "BigintRef") > RETURNS boolean AS > 'SELECT ($1."Null" = TRUE AND $2."Null" = TRUE) OR ($1."Null" = FALSe > AND $2."Null" = FALSE AND $1."Value" = $2."Value")' > LANGUAGE sql IMMUTABLE > COST 100; you shouldn't have to do this -- as of 8.4 record types can be directly used in comparisons w/o going custom operator route and I would highly advise against redefining the = operator to use non-standard behavior. postgres=# create table foo(a int, b bool); CREATE TABLE Time: 11.433 ms postgres=# select (1, true)::foo = (2,false)::foo; ?column? ---------- f (1 row) Time: 1.424 ms merlin
On 6. Okt, 08:48 h., Trigve <trig...@gmail.com> wrote: > Hi, > I'm trying to make a custom composite type and use it as a PK and FK. > When adding FK to table I've got trhis error: operator is not unique: > "BigintRef" pg_catalog.= "BigintRef" (see below). Here is my type > definition with operator: > > CREATE TYPE "BigintRef" AS > ("Value" bigint, > "Null" boolean); > > CREATE OR REPLACE FUNCTION bigintref_op_eq("BigintRef", "BigintRef") > RETURNS boolean AS > 'SELECT ($1."Null" = TRUE AND $2."Null" = TRUE) OR ($1."Null" = FALSe > AND $2."Null" = FALSE AND $1."Value" = $2."Value")' > LANGUAGE sql IMMUTABLE > COST 100; > > CREATE OPERATOR =( > PROCEDURE = bigintref_op_eq, > LEFTARG = "BigintRef", > RIGHTARG = "BigintRef"); > > Here are table definitions: > > CREATE TEMP TABLE t_a(id "BigintRef"); > CREATE TEMP TABLE t_b(id "BigintRef"); > ALTER TABLE t_a ADD PRIMARY KEY(id); > > NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index > "t_a_pkey" for table "t_a" > Query returned successfully with no result in 51 ms. > > ALTER TABLE t_b ADD FOREIGN KEY(id) REFERENCES t_a(id); > > ERROR: operator is not unique: "BigintRef" pg_catalog.= "BigintRef" > LINE 1: ..._temp_6"."t_a" pk ON ( pk."id"::pg_catalog.record > OPERATOR(p... > ^ > HINT: Could not choose a best candidate operator. You might need to > add explicit type casts. > QUERY: SELECT fk."id" FROM ONLY "pg_temp_6"."t_b" fk LEFT OUTER JOIN > ONLY "pg_temp_6"."t_a" pk ON ( pk."id"::pg_catalog.record > OPERATOR(pg_catalog.=) fk."id"::pg_catalog.record) WHERE pk."id" IS > NULL AND (fk."id" IS NOT NULL) > > Anyone know what is wrong? I'm using PostgreSQL 9.0 > > Thanks > > Trigve I forgot to mention that I've created implicit cast from BigintRef -> bigint. This cast caused the error. Trigve