From: | Trigve <trigves(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Composite type operator not unique |
Date: | 2010-10-06 06:48:09 |
Message-ID: | 81144875-c6b4-459a-87f6-e404c907d455@a36g2000yqc.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jutta Buschbom | 2010-10-06 07:24:27 | Compilation of optional packages |
Previous Message | Vishnu S. | 2010-10-06 06:24:25 | Slony-I Doubt |