Thread

1. designating a column as primary key after creation

   Fran Fabrizio <fran@primary.net> — 1998-08-13T02:57:55Z

   Why does postgres choke on the following:
   
   alter table mytable add constraint mycolumn_pk primary key(mycolumn);
   
   is this possible in a postgres database?  if not, what's an easy
   workaround, i really need to have this column as primary key. 
   
   Thanks!
   
   -Fran
   
   
   

2. Re: [GENERAL] designating a column as primary key after creation

   Aleksey Dashevsky <postgres@luckynet.co.il> — 1998-08-13T07:53:55Z

   
   On Wed, 12 Aug 1998, Fran Fabrizio wrote:
   
   > 
   > Why does postgres choke on the following:
   > 
   > alter table mytable add constraint mycolumn_pk primary key(mycolumn);
   > 
   > is this possible in a postgres database?  if not, what's an easy
   > workaround, i really need to have this column as primary key. 
   
   Unfortunately the syntax you wanted to use is not allowed in PostgresSQL
   as well as 
   1. alter table <tablename> drop column <colname>
   and 
   2. alter table <tablename> modify column ....
   
   Nevertheless, there is at least one workaround for your case: you can 
   create (at any time!) unique index on your table using any column or
   column combination from already existing table. The only weakness of this
   method is that NULL values are not forbidded in unique index , so you can
   lost uniquness if there will be some rows with NULLs in key column(s). 
   (note, that each NULL is treated as new  value, I mean one NULL is 
   note equal to another one!)
   
   for details refer to create_index(l) and drop_index(l) man pages.
   
   Al.
   
   
   

3. Nulls (was Re: designating a column as primary key after creation)

   Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-08-16T11:52:09Z

   At 10:53 +0300 on 13/8/98, Aleksey Dashevsky wrote:
   
   
   > (note, that each NULL is treated as new  value, I mean one NULL is
   > note equal to another one!)
   
   Hey, wait a second. Wasn't this supposed to be fixed in 6.3?
   
   I really hate the way nulls are treated in 6.2.1, and I'm pushing my
   sysadmin hard to update the version, because I want sorts on two columns
   not to be confused when there are nulls in the first column.
   
   (That is, if I "ORDER BY heb_term, eng_term" - and there are possible NULLs
   in heb_term, I expect all the nulls to be considered the same value, so
   that all the rows with NULL in their heb_term will be sorted by eng_term.
   Otherwise I have to make two separate queries).
   
   I also want to be able to compare fields, and get a correct result if the
   two fields are null. For example, I create two tables. The snapshot table
   shows the rows in the original table, as they were at a given time, and I
   want to be able to compare the row from the snapshot table with the
   corresponding row in the original table, and see if anything has changed.
   But if one of the fields is null, I would be comparing NULL to NULL, and in
   6.2.1, it would look as if the two rows are different!
   
   Anyway, I was sure this problem was fixed in 6.3.x... Can anyone confirm or
   deny?
   
   I do like the fact that I can have as many nulls as I need in a column with
   a unique index. This should stay like that (I think we discussed it a long
   time ago).
   
   Herouth
   
   --
   Herouth Maoz, Internet developer.
   Open University of Israel - Telem project
   http://telem.openu.ac.il/~herutma