BUG #1162: lots of values that have "unique" property

From: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1162: lots of values that have "unique" property
Date: 2004-06-13 17:22:09
Message-ID: 20040613172209.52D47CF4B08@www.postgresql.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 1162
Logged by: Fehmi Noyan ISI

Email address: fnoyan(at)linuxmail(dot)org

PostgreSQL version: 7.4

Operating system: FreeBSD 5.1 - x86 platform

Description: lots of values that have "unique" property

Details:

Hello
First of all I am not a database expert! I just run MySQL and PostgreSQL
on my FreeBSD 5.1 system and develop small web applications.
I am new to PostgreSQL and don't know this is a bug (or something like
it) or not. But while I was reading the constraints section of user's
manual I noticed something strange!
Please consider the procedure below...

noyan=> CREATE TABLE table_1 (
noyan(> id integer UNIQUE NOT NULL,
noyan(> dsc text NOT NULL,
noyan(> passwd text NOT NULL);

The table is created successfully.Ok, go on...

noyan=> INSERT INTO table_1 VALUES (1,'System Admin','something');
noyan=> INSERT INTO table_1 VALUES (1,'Normal User','something');
ERROR: Cannot insert a duplicate key into unique index table_1_id_key
noyan=> SELECT * FROM table_1;
id | dsc | passwd
----+--------------+-----------
1 | System Admin | something
(1 row)

I got the error message as expected. Everything is ok.Now, let's create
another table called "table_2" with an inheritence from "table_1".

noyan=> CREATE TABLE table_2 (ext_column integer) INHERITS (table_1);
CREATE TABLE
noyan=> \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-------
public | table_1 | table | noyan
public | table_2 | table | noyan
(2 rows)

But, when I insert a new value into "table_2" with an "id" value same
as with one of the values in "table_1"...

noyan=> INSERT INTO table_2 VALUES (1,'Any User','AnyPasswd',123);
INSERT 17114 1
noyan=> SELECT * FROM table_2;
id | dsc | passwd | ext_column
----+----------+-----------+------------
1 | Any User | AnyPasswd | 123
(1 row)

noyan=> SELECT * FROM table_1;
id | dsc | passwd
----+--------------+-----------
1 | System Admin | something
1 | Any User | AnyPasswd
(2 rows)

I think, I must get an error message like the message above (when I try
to insert a new value into "table_1" directly with a same "id" value).
There are two same "id"s with the value "1" although the "id" column is
defined as UNIQUE.
If I use "PRIMARY KEY" instead of "UNIQUE NOT NULL", the same thing
happens.Nothing changes!

System Information :
PostgreSQL 7.3.4 on i386-unknown-freebsd5.1, compiled by GCC gcc (GCC)
3.2.2 [FreeBSD] 20030205 (release)

As I sad at the begining, I am not an SQL expert so please let me know
this is a strange behaviour or not!
And finally, thanks to all of PostgreSQL team for giving us this
excellent and powerfull free database software.


Thanks....
Fehmi Noyan ISI

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephan Szabo 2004-06-14 01:30:43 Re: BUG #1162: lots of values that have "unique" property
Previous Message Fehmi Noyan ISI 2004-06-13 17:07:09 inserting lots of values into a column that has "unique" property