pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to determine PRIMARY KEYS
3+ messages / 3 participants
[nested] [flat]
* How to determine PRIMARY KEYS
@ 2001-06-11 16:46 Sandro Dentella <sandro.dentella@tin.it>
0 siblings, 2 replies; 3+ messages in thread
From: Sandro Dentella @ 2001-06-11 16:46 UTC (permalink / raw)
To: pgsql-interfaces
Hi all,
I'm the author of sdsq/tksql an interface to postgreSQL I released under
GPL some weeks ago.
Tksql tries as much as possible to use PRIMARY KEYS instead of oids in any
operation so that logs are much more readable and so that I can use those
logs to do a replica of the database to keep two databases in sync (in a
very simple situation thought...)
Now, I'd like suggestion and a comment on the algorithm I'm using to
find the primary key.
I really think that this is more than needed, becouse if you have a table
w/ a primary key and unique index NOT NULL on an other attribute both these
attributes would be choosen not just the minimum needed (the primary key
OR an attribute NOT NULL w/ unique index).
Here is what I do:
1. find all unique indexes on table ($tbl)
SELECT i.indexrelid FROM pg_class c, pg_index i \
WHERE c.relname = '$tbl' and c.oid = i.indrelid \
and i.indisunique = 't'
2. select in pg_attribute the attributes of these indexes...
3. loop over these attrs ($at) and select those that are NOT NULL.
SELECT attname,attnotnull from pg_attribute \
where attrelid = (SELECT oid from pg_class \
where relname = '$tbl') and attname = '$at' \
and attnotnull = 't'
May I as an alternative just relay on the pattern *_pkey and look at the
attribute of relation *_pkey to get the PRIMARY KEY?
Is there a document that explains system tables?
Thanks in advance
sandro
*:-)
PS. For those who whant to look at tksql or sdsql (the tcl/tk package):
http://pcco2.mi.infn.it/sd/soft. If you encounter problems installing or
running it I'd really appreciate to be informed (and/or if you like
it...;-) . Thanks
Note: sorry if this message shows up 2 times. I had problems w/ config of
sendmail
--
Sandro Dentella *:-)
e-mail: sandro@ermit.it sandro.dentella@mi.infn.it
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to determine PRIMARY KEYS
@ 2001-06-11 17:32 Alex Pilosov <alex@acecape.com>
parent: Sandro Dentella <sandro.dentella@tin.it>
1 sibling, 0 replies; 3+ messages in thread
From: Alex Pilosov @ 2001-06-11 17:32 UTC (permalink / raw)
To: Sandro Dentella <sandro.dentella@tin.it>; +Cc: pgsql-interfaces
On Mon, 11 Jun 2001, Sandro Dentella wrote:
> I really think that this is more than needed, becouse if you have a table
> w/ a primary key and unique index NOT NULL on an other attribute both these
> attributes would be choosen not just the minimum needed (the primary key
> OR an attribute NOT NULL w/ unique index).
There's an attribute indisprimary in pg_index which you can use to find
what's the primary key on the table.
I think what you should do is this:
a) if there's a primary key, use that.
b) if there isn't, find a unique/notnull index.
> 1. find all unique indexes on table ($tbl)
>
> SELECT i.indexrelid FROM pg_class c, pg_index i \
> WHERE c.relname = '$tbl' and c.oid = i.indrelid \
> and i.indisunique = 't'
>
>
> 2. select in pg_attribute the attributes of these indexes...
>
> 3. loop over these attrs ($at) and select those that are NOT NULL.
>
> SELECT attname,attnotnull from pg_attribute \
> where attrelid = (SELECT oid from pg_class \
> where relname = '$tbl') and attname = '$at' \
> and attnotnull = 't'
>
> May I as an alternative just relay on the pattern *_pkey and look at the
> attribute of relation *_pkey to get the PRIMARY KEY?
>
> Is there a document that explains system tables?
>
>
> Thanks in advance
>
> sandro
> *:-)
>
>
>
> PS. For those who whant to look at tksql or sdsql (the tcl/tk package):
> http://pcco2.mi.infn.it/sd/soft. If you encounter problems installing or
> running it I'd really appreciate to be informed (and/or if you like
> it...;-) . Thanks
>
> Note: sorry if this message shows up 2 times. I had problems w/ config of
> sendmail
>
>
>
>
--
--
Alex Pilosov | http://www.acecape.com/dsl
CTO - Acecape, Inc. | AceDSL:The best ADSL in Bell Atlantic area
325 W 38 St. Suite 1005 | (Stealth Marketing Works! :)
New York, NY 10018 |
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to determine PRIMARY KEYS
@ 2001-06-11 18:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sandro Dentella <sandro.dentella@tin.it>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2001-06-11 18:40 UTC (permalink / raw)
To: Sandro Dentella <sandro.dentella@tin.it>; +Cc: pgsql-interfaces
Sandro Dentella <sandro.dentella@tin.it> writes:
> Here is what I do:
Wouldn't it be easier just to look for the pg_index row (if any) that
has indisprimary = true?
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-06-11 18:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-11 16:46 How to determine PRIMARY KEYS Sandro Dentella <sandro.dentella@tin.it>
2001-06-11 17:32 ` Alex Pilosov <alex@acecape.com>
2001-06-11 18:40 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox