Skip site navigation (1) Skip section navigation (2)

inserting lots of values into a column that has "unique" property

From: "Fehmi Noyan ISI" <fnoyan(at)linuxmail(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: inserting lots of values into a column that has "unique" property
Date: 2004-06-13 17:07:09
Message-ID: (view raw or whole thread)
Lists: pgsql-bugs
 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);
 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 directly into "table_1" 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.

 Fehmi Noyan ISI

Check out the latest SMS services @ 
This allows you to send and receive SMS through your mailbox.

Powered by Outblaze


pgsql-bugs by date

Next:From: PostgreSQL Bugs ListDate: 2004-06-13 17:22:09
Subject: BUG #1162: lots of values that have "unique" property
Previous:From: Tom LaneDate: 2004-06-13 02:28:22
Subject: Re: Problem with inet = operator in 7.4.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group