pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAbout primary keys.
9+ messages / 5 participants
[nested] [flat]
* About primary keys.
@ 2001-06-19 12:10 David BOURIAUD <david.bouriaud@ac-rouen.fr>
0 siblings, 2 replies; 9+ messages in thread
From: David BOURIAUD @ 2001-06-19 12:10 UTC (permalink / raw)
To: pgsql-sql
Hi the list !
Is there a way to get in system tables all the primary keys of a table ?
Thanks by advance.
P.S. I'm running postgreSQL v7.1.2 on a SuSE 6.4 Linux box.
--
David BOURIAUD
----------------------------------------------------------
In a world without walls or fences, what use do we have
for windows or gates ?
----------------------------------------------------------
ICQ#102562021
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: About primary keys.
@ 2001-06-19 12:36 D'Arcy J.M. Cain <darcy@druid.net>
parent: David BOURIAUD <david.bouriaud@ac-rouen.fr>
1 sibling, 1 reply; 9+ messages in thread
From: darcy@druid.net @ 2001-06-19 12:36 UTC (permalink / raw)
To: David BOURIAUD <david.bouriaud@ac-rouen.fr>; +Cc: pgsql-sql
Thus spake David BOURIAUD
> Is there a way to get in system tables all the primary keys of a table ?
> Thanks by advance.
SELECT pg_class.relname, pg_attribute.attname
FROM pg_class, pg_attribute, pg_index
WHERE pg_class.oid = pg_attribute.attrelid AND
pg_class.oid = pg_index.indrelid AND
pg_index.indkey[0] = pg_attribute.attnum AND
pg_index.indisprimary = 't'
Caveat: Does not work for complex primary keys.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: About primary keys.
@ 2001-06-19 12:50 David BOURIAUD <david.bouriaud@ac-rouen.fr>
parent: D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 1 reply; 9+ messages in thread
From: David BOURIAUD @ 2001-06-19 12:50 UTC (permalink / raw)
To: pgsql-sql
"D'Arcy J.M. Cain" wrote:
>
> Thus spake David BOURIAUD
> > Is there a way to get in system tables all the primary keys of a table ?
> > Thanks by advance.
>
> SELECT pg_class.relname, pg_attribute.attname
> FROM pg_class, pg_attribute, pg_index
> WHERE pg_class.oid = pg_attribute.attrelid AND
> pg_class.oid = pg_index.indrelid AND
> pg_index.indkey[0] = pg_attribute.attnum AND
> pg_index.indisprimary = 't'
>
> Caveat: Does not work for complex primary keys.
Thanks, but that is my problem actually !
I've got few tables that have complex primary keys. I know that this
shouldn't be, but I have to work with it (no choice, much pain !). Since
I've got to write a php program that will have to work for both type of
tables (many with simple keys, and few with complex ones), how can I
have a same querry work with both ?
--
David BOURIAUD
----------------------------------------------------------
In a world without walls or fences, what use do we have
for windows or gates ?
----------------------------------------------------------
ICQ#102562021
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: About primary keys.
@ 2001-06-19 13:49 Roberto Mello <rmello@cc.usu.edu>
parent: David BOURIAUD <david.bouriaud@ac-rouen.fr>
1 sibling, 1 reply; 9+ messages in thread
From: Roberto Mello @ 2001-06-19 13:49 UTC (permalink / raw)
To: David BOURIAUD <david.bouriaud@ac-rouen.fr>; +Cc: pgsql-sql
On Tue, Jun 19, 2001 at 02:10:16PM +0200, David BOURIAUD wrote:
> Hi the list !
> Is there a way to get in system tables all the primary keys of a table ?
There's a recipe that is related to that in my Postgres CookBook that
you could adapt to your needs:
http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=36
-Roberto
--
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net
http://www.sdl.usu.edu - Space Dynamics Lab, Developer
TAGLINE NO ESPELHO ยค OHLEPSE ON ENILGAT
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: About primary keys.
@ 2001-06-19 14:38 David BOURIAUD <david.bouriaud@ac-rouen.fr>
parent: Roberto Mello <rmello@cc.usu.edu>
0 siblings, 0 replies; 9+ messages in thread
From: David BOURIAUD @ 2001-06-19 14:38 UTC (permalink / raw)
To: pgsql-sql
Roberto Mello wrote:
>
> On Tue, Jun 19, 2001 at 02:10:16PM +0200, David BOURIAUD wrote:
> > Hi the list !
> > Is there a way to get in system tables all the primary keys of a table ?
>
> There's a recipe that is related to that in my Postgres CookBook that
> you could adapt to your needs:
>
> http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=36
>
> -Roberto
Hi !
Thankx ! Indeed, it could (and will be) adapted, but it uses many unions
for one unique querry. It's a shame that there is no operator to check
if a value belongs to an int2vector. Why not having an operator such as
this one :
select ...
from ....
where attribute in int2vector ?
This could be included in the wish list, couldn't it ?
Anyway, thanks, for I can go further in my programm.
--
David BOURIAUD
----------------------------------------------------------
In a world without walls or fences, what use do we have
for windows or gates ?
----------------------------------------------------------
ICQ#102562021
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: About primary keys.
@ 2001-06-19 14:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: David BOURIAUD <david.bouriaud@ac-rouen.fr>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2001-06-19 14:38 UTC (permalink / raw)
To: David BOURIAUD <david.bouriaud@ac-rouen.fr>; +Cc: pgsql-sql
David BOURIAUD <david.bouriaud@ac-rouen.fr> writes:
>> Caveat: Does not work for complex primary keys.
> Thanks, but that is my problem actually !
Here's a rather brute-force approach:
select relname,
(select attname from pg_attribute where
attrelid = c.oid and attnum = indkey[0]),
(select attname from pg_attribute where
attrelid = c.oid and attnum = indkey[1]),
(select attname from pg_attribute where
attrelid = c.oid and attnum = indkey[2]),
(select attname from pg_attribute where
attrelid = c.oid and attnum = indkey[3])
from pg_class c, pg_index i
where c.oid = indrelid and indisprimary;
You can carry this out to however many key columns you want to
deal with. The sub-selects will yield NULLs for the columns after
the last key column of a particular index, which is just what you
want here.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: About primary keys.
@ 2003-08-15 14:38 Tim Andersen <timander37@yahoo.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tim Andersen @ 2003-08-15 14:38 UTC (permalink / raw)
To: pgsql-sql
I'm new to PostgreSQL but I am familiar with DB2,
Oracle and Sybase. I must say, I am impressed with
PostgreSQL so far!
In order to compare databases across DBMS platforms,
we need to create a view that queries from the system
catalog tables. This view returns all of the columns
in the database listed by schema, table, and
columnname with some additional information about the
column (such as a primary key indicator).
These are the columns in the view:
creator (schema), tname (tablename), cname
(columnname), coltype (datatype), nulls (nullable),
length, syslength (precision), in_primary_key, colno
(columnumber), default_value, comments
I looked in the archives at postgresql.com, and I
found someone else with the same problem that I had
but no solution was posted.
I have made some good progress on creating a view that
selects from system catalog tables, but I am having
trouble with the in_primary_key and the
length/precision columns. Many of our tables have
complex primary keys.
The query I have so far only gets columns that are
part of a primary key. I need to return all of the
columns listed and a Y/N indicator for whether or not
the column is a part of the tables primary key.
Here's what I have:
/*-------------------------------//
// This view shows all rows that //
// are part of a primary key: //
//-------------------------------*/
select upper(pgt1.schemaname) as "creator",
upper(pgt1.tablename) as "tname",
upper(pga1.attname) as "cname",
case smmtsys.v_datatype.typname
when 'bpchar' then 'char'
else smmtsys.v_datatype.typname
end as "coltype",
case pga1.attnotnull
when true then 'N'
when false then 'Y'
end as "nulls",
i.indisprimary as "in_primary_key",
pga1.atttypmod as "length",
pga1.attndims as "syslength",
pga1.attnum as "colno"
from pg_tables pgt1,
pg_class pgc1,
pg_attribute pga1,
pg_attribute pga2,
pg_type,
smmtsys.v_datatype,
pg_index i,
pg_namespace n
where pgc1.relname = pgt1.tablename
and pg_type.typname = pgt1.tablename
and pga1.attrelid = pgc1.relfilenode
and pga1.attnum > 0
and pga1.atttypid = smmtsys.v_datatype.oid
and pgc1.oid = i.indrelid
and i.indisprimary = 't'
and n.oid = pgc1.relnamespace
and pgt1.tablename = pgc1.relname
and pga2.attrelid = i.indexrelid
and pga1.attrelid = i.indrelid
and pga1.attnum = i.indkey[pga2.attnum-1];
/*---------------------------//
// this is a quick and dirty //
// view to get the datatypes //
// used in the above query: //
//---------------------------*/
create view smmtsys.v_datatype as (
select oid, typname from pg_type)
;
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: About primary keys.
@ 2003-08-15 15:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tim Andersen <timander37@yahoo.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-08-15 15:57 UTC (permalink / raw)
To: Tim Andersen <timander37@yahoo.com>; +Cc: pgsql-sql
Tim Andersen <timander37@yahoo.com> writes:
> The query I have so far only gets columns that are
> part of a primary key.
> ...
> and pga1.attnum = i.indkey[pga2.attnum-1];
This is wrong because you are looking at only one indkey position, and
the attribute could be in any position of the primary key. I think
what you want is to drop pga2 from the query and instead use something
like
... and pga1.attnum in (i.indkey[0], i.indkey[1], i.indkey[2], ...)
(carrying it out to whatever you think is a reasonable upper bound on
the number of columns in a primary key --- the normal Postgres limit
is 32 keys but I can't believe anyone would use that many in practice).
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: About primary keys.
@ 2003-08-15 21:32 Tim Andersen <timander37@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tim Andersen @ 2003-08-15 21:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
I looked in the info.c on line 2891 of the
psqlodbc-7.2.5 to find this SQL logic (courtesy of Tom
Lane)
select ta.attname, ia.attnum
from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n
where c.oid = i.indrelid
AND n.oid = c.relnamespace
AND i.indisprimary = 't'
AND ia.attrelid = i.indexrelid
AND ta.attrelid = i.indrelid
AND ta.attnum = i.indkey[ia.attnum-1];
The above SQL retrieves each and every column in the
database that is a part of a complex primary key.
I need to join this to a list of all of the columns in
the database so I can have the primary key indicator.
Here's another variation of the above SQL that shows
schema, table, column, colum_num, and a primary key
indicator:
select pg_tables.schemaname, pg_tables.tablename,
ta.attname, ia.attnum, i.indisprimary
from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n , pg_tables
where c.oid = i.indrelid
AND n.oid = c.relnamespace
AND i.indisprimary = 't'
AND ia.attrelid = i.indexrelid
AND ta.attrelid = i.indrelid
AND ta.attnum = i.indkey[ia.attnum-1]
AND pg_tables.tablename = c.relname;
so, shouldn't there be an easy way to retrieve all of
the columns for all tables with a primary key
indicator using this strategy?
If creating another view will simplify syntax, that's
fine too.
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-08-15 21:32 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-19 12:10 About primary keys. David BOURIAUD <david.bouriaud@ac-rouen.fr>
2001-06-19 12:36 ` D'Arcy J.M. Cain <darcy@druid.net>
2001-06-19 12:50 ` David BOURIAUD <david.bouriaud@ac-rouen.fr>
2001-06-19 14:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-19 13:49 ` Roberto Mello <rmello@cc.usu.edu>
2001-06-19 14:38 ` David BOURIAUD <david.bouriaud@ac-rouen.fr>
2003-08-15 14:38 Re: About primary keys. Tim Andersen <timander37@yahoo.com>
2003-08-15 15:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-15 21:32 ` Tim Andersen <timander37@yahoo.com>
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