Thread

1. Reference users for postgresql

   Frank Albert Ekern <frank@ekern.no> — 2001-09-22T17:20:19Z

   Hi.
   
   I would like to get a list of companies using postgresql, preferably
   well known. I have found a list at http://www.pgsql.com/user_gallery/
   , but to me it seems to have a extremely poor signal to noise ratio.
   
   This list will give us an good argument in the discussion of whether
   we should choose postgresql or a "well known commercial DB".
   (Internally my group has met a lot of resistance in the choice of a
   Linux system with postgresql over a win2k with sqlserver. We have now
   finally managed to convince the management that Linux is the right os for this
   application, but now they have started to argue that postgresql is a
   crappy DB with no "real" users.)
   
   Thanks.
   
   -- 
   Frank
   
   

2. Recreating unique index for primary key

   Ryan Ho <ryanho@pacific.net.sg> — 2001-09-28T05:37:47Z

   Hi,
   
   I've dropped an primary key index in order to re-create it. but i realized 
   that i can't recreate a primary key index. Will a unique index be an adequate 
   replacement? will the database integrity be at risk as a result?
   
   Thanks!
   -- 
   Ho Siaw Ping, Ryan
   
   

3. Re: Recreating unique index for primary key

   Tod McQuillin <devin@spamcop.net> — 2001-09-29T08:07:49Z

   On Fri, 28 Sep 2001, Ryan Ho wrote:
   
   > I've dropped an primary key index in order to re-create it. but i realized
   > that i can't recreate a primary key index. Will a unique index be an adequate
   > replacement? will the database integrity be at risk as a result?
   
   I think that 'PRIMARY KEY' in a table definition is just shorthand for
   'UNIQUE NOT NULL', in addition to whatever magic is required to mark the
   column as primary so that 'REFERENCES' constraints in other tables know
   which column to default to.
   
   If all you did was delete the index and recreate it, you should be fine --
   as far as I know there is nothing in the index itself marking it as
   "primary".
   -- 
   Tod McQuillin
   
   P.S.  If I am wrong or incomplete in my speculations above, I am sure
   someone will jump in and correct me, which is exactly what I'm hoping for.
   The best way to get correct answers on a mailing list is to post wrong
   answers :-)
   
   
   
   

4. Re: Recreating unique index for primary key

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-09-29T14:41:44Z

   Ryan Ho <ryanho@pacific.net.sg> writes:
   > I've dropped an primary key index in order to re-create it. but i realized 
   > that i can't recreate a primary key index. Will a unique index be an adequate
   > replacement? will the database integrity be at risk as a result?
   
   CREATE UNIQUE INDEX is fine as far as the database goes.  Offhand it
   looks like the only extra thing a primary-key marker does is to define
   the default reference column for subsequent foreign-key references
   pointing at your table.
   
   If you want, you can reach into pg_index and set the indisprimary field
   after creating the index:
   
   update pg_index set indisprimary = true where indexrelid =
   (select oid from pg_class where relname = 'yourindexname')
   
   There's been some discussion of adding a PRIMARY option to CREATE INDEX
   to allow doing this in a cleaner way, but it's not been done yet.
   
   			regards, tom lane
   
   

5. Re: Recreating unique index for primary key

   Tod McQuillin <devin@spamcop.net> — 2001-09-29T15:26:07Z

   On Sat, 29 Sep 2001, Tom Lane wrote:
   
   > CREATE UNIQUE INDEX is fine as far as the database goes.  Offhand it
   > looks like the only extra thing a primary-key marker does is to define
   > the default reference column for subsequent foreign-key references
   > pointing at your table.
   >
   > If you want, you can reach into pg_index and set the indisprimary field
   > after creating the index:
   
   I stand corrected; there *is* something in the index itself which marks it
   primary.
   
   I'd guess that since the only time 'REFERENCES' is seen is when creating a
   new table (and translated into hard-coded triggers after that), you should
   be fine until you create a new table referencing the table whose primary
   index you removed.
   
   It's an inconsistency I would not feel comfortable with, so I'm glad I
   learned about indisprimary.  Is this documented anywhere?
   -- 
   Tod McQuillin
   
   
   
   
   

6. Re: Recreating unique index for primary key

   Tod McQuillin <devin@spamcop.net> — 2001-09-29T15:29:02Z

   Following up to my own post,
   
   On Sun, 30 Sep 2001, Tod McQuillin wrote:
   
   > It's an inconsistency I would not feel comfortable with, so I'm glad I
   > learned about indisprimary.  Is this documented anywhere?
   
   That's a lazy question and I apologise for asking it.
   
   I looked it up myself and it's right there at
   http://www.postgresql.org/idocs/index.php?catalog-pg-index.html
   -- 
   Tod McQUillin