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: 20040613170709.802734160BD@ws5-2.us4.outblaze.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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 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.

  
    Thanks....
 Fehmi Noyan ISI


-- 
______________________________________________
Check out the latest SMS services @ http://www.linuxmail.org 
This allows you to send and receive SMS through your mailbox.


Powered by Outblaze

Responses

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-2014 The PostgreSQL Global Development Group