Thread: About primary keys.
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
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.attrelidAND 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.
"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
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
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_attributewhere 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
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
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.indisprimaryas "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 nwhere 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
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
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.attnumfrom pg_attribute ta, pg_attribute ia, pg_class c, pg_index i, pg_namespace nwhere c.oid = i.indrelidAND n.oid = c.relnamespaceAND i.indisprimary = 't'AND ia.attrelid = i.indexrelidANDta.attrelid = i.indrelidAND 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
This might not be the cleanest solution, but it runs
fast and it retrieved the information I need.
I broke it down into pieces and created several views
to query from to simplify it for myself.
The first four statements are views and the last one
is the query I was originally trying to get. (note
that smmtsys is a schema I created, everything else is
dealing with system catalog tables)
Here's the SQL:
---------------
create view smmtsys.v_datatype as (
select oid, typname from pg_type)
;
create view smmtsys.v_primarykeys as(
select pg_tables.schemaname, pg_tables.tablename,
ta.attname, ta.attrelid, 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
AND (pg_tables.schemaname = 'summit' or
pg_tables.schemaname = 'uhelp' or
pg_tables.schemaname = 'smmtsys' or
pg_tables.schemaname = 'smmtsec' or
pg_tables.schemaname = 'smmtccon' )
and ta.attname > 0
)
;
create view smmtsys.v_allcolumns as (
select pg_tables.schemaname, pg_tables.tablename, pg_attribute.attname
from pg_tables, pg_class, pg_attribute, smmtsys.v_datatype
where (schemaname = 'smmtccon' or schemaname = 'smmtsec' or schemaname = 'smmtsys' or schemaname =
'summit'or schemaname = 'uhelp' ) and pg_class.relname = pg_tables.tablename and pg_type.typname =
pg_tables.tablenameand pg_attribute.attrelid = pg_class.relfilenode and
pg_attribute.attnum > 0 and pg_attribute.atttypid = smmtsys.v_datatype.oid
)
;
create view smmtsys.v_primarykeyind as (
select cols.schemaname , cols.tablename , cols.attname, case pks.indisprimary when true then 'Y'
else 'N' end as in_primary_key
from smmtsys.v_allcolumns cols left outer join
smmtsys.v_primarykeys pks
on (cols.schemaname = pks.schemaname and cols.tablename = pks.tablename and cols.attname= pks.attname)
);
select upper(tbls.schemaname) as "creator", upper(tbls.tablename) as "tname", upper(cols.attname) as
"cname", case smmtsys.v_datatype.typname when 'bpchar' then 'char' else smmtsys.v_datatype.typname
end as "coltype", case cols.attnotnull when true then 'N' when false then 'Y' end as
"nulls", length(cols.attrelid) as "length", cols.attndims as "syslength", vpk.in_primary_key,
cols.attnumas "colno"
from pg_tables tbls, pg_class, pg_attribute cols, pg_type, smmtsys.v_datatype,
smmtsys.v_primarykeyindvpk
where (tbls.schemaname = 'smmtccon' or tbls.schemaname = 'smmtsec' or tbls.schemaname = 'smmtsys' or
tbls.schemaname= 'summit' or tbls.schemaname = 'uhelp') and pg_class.relname = tbls.tablename and
pg_type.typname= tbls.tablename and cols.attrelid = pg_class.relfilenode and cols.attnum > 0 and
cols.atttypid= smmtsys.v_datatype.oid and vpk.schemaname = tbls.schemaname and vpk.tablename = tbls.tablename
and vpk.attname = cols.attname
;
This retrieves all of the columns and shows a primary
key indicator for each column. If someone could put
this logic all into one SQL query, I'd really like to
see it!
I still have a question about how to get the
information about length and precision of a column
from pg_attributes.atttypmod. are there built-in
functions for PostgreSQL to extract this information?
Additionally, I need to get the column default value
and the comments on the column, but I think I can
figure that out with a little more time.
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
Tim Andersen <timander37@yahoo.com> writes: > I still have a question about how to get the > information about length and precision of a column > from pg_attributes.atttypmod. are there built-in > functions for PostgreSQL to extract this information? Best is to rely on the format_type() function. Also, have you thought about using pg_get_indexdef() in place of all that hacking about in pg_index? http://www.postgresql.org/docs/7.3/static/functions-misc.html In general, your code is less likely to break if you can use the "catalog information functions" rather than poking around in the catalogs directly. regards, tom lane