Thread: Reference users for postgresql

Reference users for postgresql

From
Frank Albert Ekern
Date:
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

Recreating unique index for primary key

From
Ryan Ho
Date:
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

Re: Recreating unique index for primary key

From
Tod McQuillin
Date:
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 :-)



Re: Recreating unique index for primary key

From
Tom Lane
Date:
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

Re: Recreating unique index for primary key

From
Tod McQuillin
Date:
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




Re: Recreating unique index for primary key

From
Tod McQuillin
Date:
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