Thread: Reference users for postgresql
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
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
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 :-)
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
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
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