Re: About primary keys.
Re: About primary keys.
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Tim Andersen 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
Re: About primary keys.
От:
Roberto Mello <rmello@cc.usu.edu>
Дата:
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
Re: About primary keys.
От:
darcy@druid.net (D'Arcy J.M. Cain)
Дата:
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 | 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.
Re: About primary keys.
От:
Tim Andersen <timander37@yahoo.com>
Дата:
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 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
Re: About primary keys.
От:
Tim Andersen <timander37@yahoo.com>
Дата:
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.indexrelidAND ta.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
Re: About primary keys -- made some progress
От:
Tim Andersen <timander37@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.tablename and 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.attnum as "colno"
from pg_tables tbls, pg_class, pg_attribute cols, pg_type, smmtsys.v_datatype, smmtsys.v_primarykeyind vpk
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
Re: About primary keys.
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
David BOURIAUD 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
Re: About primary keys -- made some progress
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Tim Andersen 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
About primary keys.
От:
David BOURIAUD <david.bouriaud@ac-rouen.fr>
Дата:
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
Re: About primary keys.
От:
David BOURIAUD <david.bouriaud@ac-rouen.fr>
Дата:
"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
Re: About primary keys.
От:
David BOURIAUD <david.bouriaud@ac-rouen.fr>
Дата:
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
