unique index considering nulls as equal

#1

Hi,

i have a table with the columns

id, pid, nodetext

which represent a tree. Root elements are signed by providing NULL for
pid, i.e.

1 NULL Root1
2 1 ChildOfRoot1

Now i want to force the nodetext to be unique within a certain pid. So i
created an UNIQUE CONSTRAINT covering the mentioned fields.

CONSTRAINT tbl_T_uc UNIQUE (pid, nodetext)

This works for all nodes having a pid != null. But it is still possible
to insert duplicate root nodes having pid == null. I've read the pg docs
and found that this behavior of unique constraints is wantet.

So, is it possible to force the desired behavior by still using unique
constraints or do i have to use triggers or something else?

Here the table-ddl

CREATE TABLE tbl_T
(
id serial NOT NULL,
pid integer,
nodetext character varying(16) NOT NULL,
CONSTRAINT tbl_T_pk PRIMARY KEY (id),
CONSTRAINT fk_tbl_T_pid FOREIGN KEY (pid)
REFERENCES tbl_T (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT tbl_T_fk FOREIGN KEY (pid)
REFERENCES tbl_T (id) MATCH FULL
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT tbl_T_uc UNIQUE (pid, nodetext)
)

THX

Thomas

#2

begin Thomas Winkler schrieb:

Quote:

Hi,

i have a table with the columns

id, pid, nodetext

which represent a tree. Root elements are signed by providing NULL for
pid, i.e.

1 NULL Root1
2 1 ChildOfRoot1

Now i want to force the nodetext to be unique within a certain pid. So i
created an UNIQUE CONSTRAINT covering the mentioned fields.

CONSTRAINT tbl_T_uc UNIQUE (pid, nodetext)

This works for all nodes having a pid != null. But it is still possible
to insert duplicate root nodes having pid == null. I've read the pg docs
and found that this behavior of unique constraints is wantet.

So, is it possible to force the desired behavior by still using unique
constraints or do i have to use triggers or something else?

Are the value -1 valid for pid? If no, you can use something link:

(functional index)

test=# create table node (id serial primary key, pid int, t text);
NOTICE: CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
CREATE TABLE
test=*# create unique index idx_node on node(coalesce(pid,-1),t);
CREATE INDEX
test=*# insert into node values (default,null, '1');
INSERT 0 1
test=*# insert into node values (default,null, '2');
INSERT 0 1
test=*# insert into node values (default,null, '2');
ERROR: duplicate key violates unique constraint "idx_node"
test=!#

As you can see, you can insert distinct values for the text-field and
NULL in the pid, but no with NULL in the pid and the same text. Helps
that?

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#3

begin Thomas Winkler schrieb:

Quote:

Hi,

i have a table with the columns

id, pid, nodetext

which represent a tree. Root elements are signed by providing NULL for
pid, i.e.

1 NULL Root1
2 1 ChildOfRoot1

Now i want to force the nodetext to be unique within a certain pid. So i
created an UNIQUE CONSTRAINT covering the mentioned fields.

CONSTRAINT tbl_T_uc UNIQUE (pid, nodetext)

This works for all nodes having a pid != null. But it is still possible
to insert duplicate root nodes having pid == null. I've read the pg docs
and found that this behavior of unique constraints is wantet.

So, is it possible to force the desired behavior by still using unique
constraints or do i have to use triggers or something else?

Are the value -1 valid for pid? If no, you can use something link:

(functional index)

test=# create table node (id serial primary key, pid int, t text);
NOTICE: CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
CREATE TABLE
test=*# create unique index idx_node on node(coalesce(pid,-1),t);
CREATE INDEX
test=*# insert into node values (default,null, '1');
INSERT 0 1
test=*# insert into node values (default,null, '2');
INSERT 0 1
test=*# insert into node values (default,null, '2');
ERROR: duplicate key violates unique constraint "idx_node"
test=!#

As you can see, you can insert distinct values for the text-field and
NULL in the pid, but no with NULL in the pid and the same text. Helps
that?

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#4

begin Thomas Winkler schrieb:

Quote:

Hi,

i have a table with the columns

id, pid, nodetext

which represent a tree. Root elements are signed by providing NULL for
pid, i.e.

1 NULL Root1
2 1 ChildOfRoot1

Now i want to force the nodetext to be unique within a certain pid. So i
created an UNIQUE CONSTRAINT covering the mentioned fields.

CONSTRAINT tbl_T_uc UNIQUE (pid, nodetext)

This works for all nodes having a pid != null. But it is still possible
to insert duplicate root nodes having pid == null. I've read the pg docs
and found that this behavior of unique constraints is wantet.

So, is it possible to force the desired behavior by still using unique
constraints or do i have to use triggers or something else?

Are the value -1 valid for pid? If no, you can use something link:

(functional index)

test=# create table node (id serial primary key, pid int, t text);
NOTICE: CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
CREATE TABLE
test=*# create unique index idx_node on node(coalesce(pid,-1),t);
CREATE INDEX
test=*# insert into node values (default,null, '1');
INSERT 0 1
test=*# insert into node values (default,null, '2');
INSERT 0 1
test=*# insert into node values (default,null, '2');
ERROR: duplicate key violates unique constraint "idx_node"
test=!#

As you can see, you can insert distinct values for the text-field and
NULL in the pid, but no with NULL in the pid and the same text. Helps
that?

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#5

begin Thomas Winkler schrieb:

Quote:

Hi,

i have a table with the columns

id, pid, nodetext

which represent a tree. Root elements are signed by providing NULL for
pid, i.e.

1 NULL Root1
2 1 ChildOfRoot1

Now i want to force the nodetext to be unique within a certain pid. So i
created an UNIQUE CONSTRAINT covering the mentioned fields.

CONSTRAINT tbl_T_uc UNIQUE (pid, nodetext)

This works for all nodes having a pid != null. But it is still possible
to insert duplicate root nodes having pid == null. I've read the pg docs
and found that this behavior of unique constraints is wantet.

So, is it possible to force the desired behavior by still using unique
constraints or do i have to use triggers or something else?

Are the value -1 valid for pid? If no, you can use something link:

(functional index)

test=# create table node (id serial primary key, pid int, t text);
NOTICE: CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
CREATE TABLE
test=*# create unique index idx_node on node(coalesce(pid,-1),t);
CREATE INDEX
test=*# insert into node values (default,null, '1');
INSERT 0 1
test=*# insert into node values (default,null, '2');
INSERT 0 1
test=*# insert into node values (default,null, '2');
ERROR: duplicate key violates unique constraint "idx_node"
test=!#

As you can see, you can insert distinct values for the text-field and
NULL in the pid, but no with NULL in the pid and the same text. Helps
that?

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#6

begin Thomas Winkler schrieb:

Quote:

Hi,

i have a table with the columns

id, pid, nodetext

which represent a tree. Root elements are signed by providing NULL for
pid, i.e.

1 NULL Root1
2 1 ChildOfRoot1

Now i want to force the nodetext to be unique within a certain pid. So i
created an UNIQUE CONSTRAINT covering the mentioned fields.

CONSTRAINT tbl_T_uc UNIQUE (pid, nodetext)

This works for all nodes having a pid != null. But it is still possible
to insert duplicate root nodes having pid == null. I've read the pg docs
and found that this behavior of unique constraints is wantet.

So, is it possible to force the desired behavior by still using unique
constraints or do i have to use triggers or something else?

Are the value -1 valid for pid? If no, you can use something link:

(functional index)

test=# create table node (id serial primary key, pid int, t text);
NOTICE: CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
CREATE TABLE
test=*# create unique index idx_node on node(coalesce(pid,-1),t);
CREATE INDEX
test=*# insert into node values (default,null, '1');
INSERT 0 1
test=*# insert into node values (default,null, '2');
INSERT 0 1
test=*# insert into node values (default,null, '2');
ERROR: duplicate key violates unique constraint "idx_node"
test=!#

As you can see, you can insert distinct values for the text-field and
NULL in the pid, but no with NULL in the pid and the same text. Helps
that?

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#7

begin Thomas Winkler schrieb:

Quote:

Hi,

i have a table with the columns

id, pid, nodetext

which represent a tree. Root elements are signed by providing NULL for
pid, i.e.

1 NULL Root1
2 1 ChildOfRoot1

Now i want to force the nodetext to be unique within a certain pid. So i
created an UNIQUE CONSTRAINT covering the mentioned fields.

CONSTRAINT tbl_T_uc UNIQUE (pid, nodetext)

This works for all nodes having a pid != null. But it is still possible
to insert duplicate root nodes having pid == null. I've read the pg docs
and found that this behavior of unique constraints is wantet.

So, is it possible to force the desired behavior by still using unique
constraints or do i have to use triggers or something else?

Are the value -1 valid for pid? If no, you can use something link:

(functional index)

test=# create table node (id serial primary key, pid int, t text);
NOTICE: CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
CREATE TABLE
test=*# create unique index idx_node on node(coalesce(pid,-1),t);
CREATE INDEX
test=*# insert into node values (default,null, '1');
INSERT 0 1
test=*# insert into node values (default,null, '2');
INSERT 0 1
test=*# insert into node values (default,null, '2');
ERROR: duplicate key violates unique constraint "idx_node"
test=!#

As you can see, you can insert distinct values for the text-field and
NULL in the pid, but no with NULL in the pid and the same text. Helps
that?

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#8

begin Thomas Winkler schrieb:

Quote:

Hi,

i have a table with the columns

id, pid, nodetext

which represent a tree. Root elements are signed by providing NULL for
pid, i.e.

1 NULL Root1
2 1 ChildOfRoot1

Now i want to force the nodetext to be unique within a certain pid. So i
created an UNIQUE CONSTRAINT covering the mentioned fields.

CONSTRAINT tbl_T_uc UNIQUE (pid, nodetext)

This works for all nodes having a pid != null. But it is still possible
to insert duplicate root nodes having pid == null. I've read the pg docs
and found that this behavior of unique constraints is wantet.

So, is it possible to force the desired behavior by still using unique
constraints or do i have to use triggers or something else?

Are the value -1 valid for pid? If no, you can use something link:

(functional index)

test=# create table node (id serial primary key, pid int, t text);
NOTICE: CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
CREATE TABLE
test=*# create unique index idx_node on node(coalesce(pid,-1),t);
CREATE INDEX
test=*# insert into node values (default,null, '1');
INSERT 0 1
test=*# insert into node values (default,null, '2');
INSERT 0 1
test=*# insert into node values (default,null, '2');
ERROR: duplicate key violates unique constraint "idx_node"
test=!#

As you can see, you can insert distinct values for the text-field and
NULL in the pid, but no with NULL in the pid and the same text. Helps
that?

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#9

begin Thomas Winkler schrieb:

Quote:

Hi,

i have a table with the columns

id, pid, nodetext

which represent a tree. Root elements are signed by providing NULL for
pid, i.e.

1 NULL Root1
2 1 ChildOfRoot1

Now i want to force the nodetext to be unique within a certain pid. So i
created an UNIQUE CONSTRAINT covering the mentioned fields.

CONSTRAINT tbl_T_uc UNIQUE (pid, nodetext)

This works for all nodes having a pid != null. But it is still possible
to insert duplicate root nodes having pid == null. I've read the pg docs
and found that this behavior of unique constraints is wantet.

So, is it possible to force the desired behavior by still using unique
constraints or do i have to use triggers or something else?

Are the value -1 valid for pid? If no, you can use something link:

(functional index)

test=# create table node (id serial primary key, pid int, t text);
NOTICE: CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
CREATE TABLE
test=*# create unique index idx_node on node(coalesce(pid,-1),t);
CREATE INDEX
test=*# insert into node values (default,null, '1');
INSERT 0 1
test=*# insert into node values (default,null, '2');
INSERT 0 1
test=*# insert into node values (default,null, '2');
ERROR: duplicate key violates unique constraint "idx_node"
test=!#

As you can see, you can insert distinct values for the text-field and
NULL in the pid, but no with NULL in the pid and the same text. Helps
that?

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#10

Hi,

Quote:

test=*# create unique index idx_node on node(coalesce(pid,-1),t);

As you can see, you can insert distinct values for the text-field and
NULL in the pid, but no with NULL in the pid and the same text. Helps
that?

THX for your tip. It works great. I just didn't know the way of using
functions in indexes.

Thomas