Skip site navigation (1) Skip section navigation (2)

How to determine PRIMARY KEYS

From: Sandro Dentella <sandro(dot)dentella(at)tin(dot)it>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: How to determine PRIMARY KEYS
Date: 2001-06-11 16:46:17
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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


PS. For those who whant to look at tksql or sdsql (the tcl/tk package): 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

Sandro Dentella  *:-)
e-mail: sandro(at)ermit(dot)it sandro(dot)dentella(at)mi(dot)infn(dot)it 


pgsql-interfaces by date

Next:From: Alex PilosovDate: 2001-06-11 17:32:20
Subject: Re: How to determine PRIMARY KEYS
Previous:From: Andrea AimeDate: 2001-06-11 06:52:34
Subject: Re: [INTERFACES] The future of pgAdmin...

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group