Thread: A Table's Primary Key Listing
Hi to all,
Is there any means to get a list of the Primary Keys (or simply the
Primary Key if there's only one :) ) for a given table using an SQL query
?
Regards,
Roger Tannous.
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
On Thu, Aug 18, 2005 at 07:36:22AM -0700, Roger Tannous wrote: > Is there any means to get a list of the Primary Keys (or simply the > Primary Key if there's only one :) ) for a given table using an SQL query? Are you looking for the primary key definition or do you want the primary key values themselves? It's not clear what problem you're trying to solve if "SELECT columnname FROM tablename" isn't the answer. -- Michael Fuhr
RT> Hi to all, RT> Is there any means to get a list of the Primary Keys (or simply the RT> Primary Key if there's only one :) ) for a given table using an SQL query RT> ? RT> Regards, RT> Roger Tannous. Something like this? select (select attname from pg_attribute where attrelid=pg_index.indrelid and pg_attribute.attnum=pg_index.indkey[0]) frompg_indexwhere indisprimary and indrelid=(select oid from pg_class where relname='yourtable'); DAQ
On Thu, 18 Aug 2005 07:36:22 -0700 (PDT) Roger Tannous <roger77_lb@yahoo.com> wrote: > Is there any means to get a list of the Primary Keys (or simply the > Primary Key if there's only one :) ) for a given table using an SQL query Here is what I do in PyGreSQL: SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname FROM pg_class JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND pg_namespace.nspname NOT LIKE 'pg_%' JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND pg_attribute.attisdropped='f' JOIN pg_index ON pg_index.indrelid=pg_class.oid AND pg_index.indisprimary='t' AND pg_index.indkey[0]=pg_attribute.attnum -- D'Arcy J.M. Cain <darcy@druid.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.
Thanks for your query :) But it only shows the first of the primary keys of tables having multiple primary keys :) This is apparently because of the pg_index.indkey[0] thing, so how can we manage this query in order to get all of the keys :) Thanks in advance, Roger Tannous. --- "D'Arcy J.M. Cain" <darcy@druid.net> wrote: > On Thu, 18 Aug 2005 07:36:22 -0700 (PDT) > Roger Tannous <roger77_lb@yahoo.com> wrote: > > Is there any means to get a list of the Primary Keys (or simply the > > Primary Key if there's only one :) ) for a given table using an SQL > query > > Here is what I do in PyGreSQL: > > SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname > FROM pg_class > JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND > pg_namespace.nspname NOT LIKE 'pg_%' > JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND > pg_attribute.attisdropped='f' > JOIN pg_index ON pg_index.indrelid=pg_class.oid AND > pg_index.indisprimary='t' AND > pg_index.indkey[0]=pg_attribute.attnum > > -- > D'Arcy J.M. Cain <darcy@druid.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. > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Yes, I want only field names, not values. Thanks, Roger Tannous. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Thu, 18 Aug 2005 09:40:57 -0700 (PDT) Roger Tannous <roger77_lb@yahoo.com> wrote: > Thanks for your query :) > > But it only shows the first of the primary keys of tables having multiple > primary keys :) > > This is apparently because of the pg_index.indkey[0] thing, so how can we > manage this query in order to get all of the keys :) That's a good question. The following query does this in a very unsatisfactory way. Anyone know what the general solution would be? SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname FROM pg_class JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND pg_namespace.nspname NOT LIKE 'pg_%' JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND pg_attribute.attisdropped='f' JOIN pg_index ON pg_index.indrelid=pg_class.oid AND pg_index.indisprimary='t' AND ( pg_index.indkey[0]=pg_attribute.attnumOR pg_index.indkey[1]=pg_attribute.attnum OR pg_index.indkey[2]=pg_attribute.attnumOR pg_index.indkey[3]=pg_attribute.attnum OR pg_index.indkey[4]=pg_attribute.attnumOR pg_index.indkey[5]=pg_attribute.attnum OR pg_index.indkey[6]=pg_attribute.attnumOR pg_index.indkey[7]=pg_attribute.attnum OR pg_index.indkey[8]=pg_attribute.attnumOR pg_index.indkey[9]=pg_attribute.attnum ) ORDER BY pg_namespace.nspname, pg_class.relname,pg_attribute.attname; -- D'Arcy J.M. Cain <darcy@druid.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" <darcy@druid.net> writes:
> That's a good question. The following query does this in a very
> unsatisfactory way. Anyone know what the general solution would be?
> ...
> (
> pg_index.indkey[0]=pg_attribute.attnum OR
> pg_index.indkey[1]=pg_attribute.attnum OR
> pg_index.indkey[2]=pg_attribute.attnum OR
> pg_index.indkey[3]=pg_attribute.attnum OR
> pg_index.indkey[4]=pg_attribute.attnum OR
> pg_index.indkey[5]=pg_attribute.attnum OR
> pg_index.indkey[6]=pg_attribute.attnum OR
> pg_index.indkey[7]=pg_attribute.attnum OR
> pg_index.indkey[8]=pg_attribute.attnum OR
> pg_index.indkey[9]=pg_attribute.attnum
> )
In CVS tip you could replace this with "attnum = ANY (indkey)".
Unfortunately, most array support doesn't work on int2vector in
pre-8.1 releases, so I think you're kinda stuck with the above
for now.
regards, tom lane
Hi,
If you put pg_index.indkey in the select statement, you'd notice that it's
sometimes 1 ( it's when we have one PK field) and sometimes 1 2 ( for two
PK fields), etc.
So I tried to use a replace command like the following:
(just to add parentheses, replace the space by a comma to use the
resulting string in an IN statement)
select '(' || replace('1 2', " ", ",") || ')';
which yields: (1,2)
But the following query fails to execute!!
select replace(indkey, " ", ",") from pg_index;
[
sub question: Did I miss quotes around elements? I mean should I enclose
every element originating from the indkey array with single quotes ? if
yes, so easy, no need to matter about it: so I should have tried the
following (which I didn't have time to do yet):
select '(\'' || replace(indkey, " ", "','") || '\')' from pg_index;
Another issue here too: Could double quotes here be the source of a
problem ? So I should have tested also this query:
select '(\'' || replace(indkey, ' ', '\',\'') || '\')' from pg_index;
I expect this query to work :) Let's hope so!!
]
So we can use the following WHERE statement:
WHERE pg_attribute.attnum IN '(' || replace('1 2', " ", ",") || ')'
which should translate into: WHERE pg_attribute.attnum IN (1,2)
Finally, this WHERE statement:
WHERE pg_attribute.attnum IN '(\'' || replace(pg_index.indkey, " ", "','") || '\')'
[
Again, I should test:
WHERE pg_attribute.attnum IN '(\'' || replace(pg_index.indkey, ' ', '\',\'') || '\')'
]
I wish I had database access in the internet cafe I'm sending this message
from :) instead of just loading you with this bunch of questions.
Best Regards,
Roger Tannous.
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "D'Arcy J.M. Cain" <darcy@druid.net> writes:
> > That's a good question. The following query does this in a very
> > unsatisfactory way. Anyone know what the general solution would be?
>
> > ...
> > (
> > pg_index.indkey[0]=pg_attribute.attnum OR
> > pg_index.indkey[1]=pg_attribute.attnum OR
> > pg_index.indkey[2]=pg_attribute.attnum OR
> > pg_index.indkey[3]=pg_attribute.attnum OR
> > pg_index.indkey[4]=pg_attribute.attnum OR
> > pg_index.indkey[5]=pg_attribute.attnum OR
> > pg_index.indkey[6]=pg_attribute.attnum OR
> > pg_index.indkey[7]=pg_attribute.attnum OR
> > pg_index.indkey[8]=pg_attribute.attnum OR
> > pg_index.indkey[9]=pg_attribute.attnum
> > )
>
> In CVS tip you could replace this with "attnum = ANY (indkey)".
> Unfortunately, most array support doesn't work on int2vector in
> pre-8.1 releases, so I think you're kinda stuck with the above
> for now.
>
> regards, tom lane
>
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
Hi to all, there was a BIG MISTAKE in my proposition regarding my last
post:
In fact, after examining the online documentation (Note that I don't have
enough experience in postgreSQL !!) I found that
select '(' || replace('1 2', " ", ",") || ')';
could not, in any way, be equivalent to:
select '(\'' || replace(indkey, " ", "','") || '\')' from pg_index;
in that the first example '1 2' is a string, while indkey is an array and
the later usage of the concatenation operator with the array just appends
strings to the array, which yields an array, not what I expected to be, a
string!! So it's apparently irrelevant to directly use the replace command
with an array !!
In fact, I've also tried:
select replace('(\'' || indkey || '\')', " ", "','") from pg_index;
but forgot to mention it in the previous post.
So concatenating any string to an array yields an array... and this query
is irrelevant.
The possible solution would be to convert this array to a string, with the
insertion of the proper quotes and commas; but since the command to be
used already inserts a delimiter, we can get rid of the replace command.
Let's see this query now:
select '(\'' || array_to_string(indkey, '\',\'') || '\')' from pg_index;
I'm sure this should work :)
Now we have the final WHERE statement like this:
WHERE pg_attribute.attnum IN '(\'' || array_to_string(pg_index.indkey,
'\',\'') || '\')'
or ?
WHERE pg_attribute.attnum IN ('\'' || array_to_string(pg_index.indkey,
'\',\'') || '\'')
Anyway, I got to test those queries, and I'm optimistic about it.
Hope they'll work fine :)
Best Regards,
Roger Tannous.
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--- Roger Tannous <roger77_lb@yahoo.com> wrote:
> Hi,
>
> If you put pg_index.indkey in the select statement, you'd notice that
> it's
> sometimes 1 ( it's when we have one PK field) and sometimes 1 2 ( for
> two
> PK fields), etc.
>
> So I tried to use a replace command like the following:
>
> (just to add parentheses, replace the space by a comma to use the
> resulting string in an IN statement)
>
> select '(' || replace('1 2', " ", ",") || ')';
>
> which yields: (1,2)
>
> But the following query fails to execute!!
> select replace(indkey, " ", ",") from pg_index;
>
> [
> sub question: Did I miss quotes around elements? I mean should I enclose
> every element originating from the indkey array with single quotes ? if
> yes, so easy, no need to matter about it: so I should have tried the
> following (which I didn't have time to do yet):
>
> select '(\'' || replace(indkey, " ", "','") || '\')' from pg_index;
>
> Another issue here too: Could double quotes here be the source of a
> problem ? So I should have tested also this query:
>
> select '(\'' || replace(indkey, ' ', '\',\'') || '\')' from pg_index;
>
> I expect this query to work :) Let's hope so!!
> ]
>
>
>
> So we can use the following WHERE statement:
> WHERE pg_attribute.attnum IN '(' || replace('1 2', " ", ",") || ')'
>
> which should translate into: WHERE pg_attribute.attnum IN (1,2)
>
>
> Finally, this WHERE statement:
>
> WHERE pg_attribute.attnum IN
> '(\'' || replace(pg_index.indkey, " ", "','") || '\')'
>
>
> [
> Again, I should test:
>
> WHERE pg_attribute.attnum IN
> '(\'' || replace(pg_index.indkey, ' ', '\',\'') || '\')'
>
> ]
>
>
> I wish I had database access in the internet cafe I'm sending this
> message
> from :) instead of just loading you with this bunch of questions.
>
>
> Best Regards,
> Roger Tannous.
>
>
> --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > "D'Arcy J.M. Cain" <darcy@druid.net> writes:
> > > That's a good question. The following query does this in a very
> > > unsatisfactory way. Anyone know what the general solution would be?
> >
> > > ...
> > > (
> > > pg_index.indkey[0]=pg_attribute.attnum OR
> > > pg_index.indkey[1]=pg_attribute.attnum OR
> > > pg_index.indkey[2]=pg_attribute.attnum OR
> > > pg_index.indkey[3]=pg_attribute.attnum OR
> > > pg_index.indkey[4]=pg_attribute.attnum OR
> > > pg_index.indkey[5]=pg_attribute.attnum OR
> > > pg_index.indkey[6]=pg_attribute.attnum OR
> > > pg_index.indkey[7]=pg_attribute.attnum OR
> > > pg_index.indkey[8]=pg_attribute.attnum OR
> > > pg_index.indkey[9]=pg_attribute.attnum
> > > )
> >
> > In CVS tip you could replace this with "attnum = ANY (indkey)".
> > Unfortunately, most array support doesn't work on int2vector in
> > pre-8.1 releases, so I think you're kinda stuck with the above
> > for now.
> >
> > regards, tom lane
> >
>
>
>
>
> ____________________________________________________
> Start your day with Yahoo! - make it your home page
> http://www.yahoo.com/r/hs
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
OUPS !!
Things seem to be stuck now, since the DB version is 7.3.2, so no
array_to_string method is available.
Does anyone have any idea how to solve that ?
Regards,
Roger Tannous.
--- Roger Tannous <roger77_lb@yahoo.com> wrote:
> Hi to all, there was a BIG MISTAKE in my proposition regarding my last
> post:
>
> In fact, after examining the online documentation (Note that I don't
> have
> enough experience in postgreSQL !!) I found that
>
> select '(' || replace('1 2', " ", ",") || ')';
>
> could not, in any way, be equivalent to:
>
> select '(\'' || replace(indkey, " ", "','") || '\')' from pg_index;
>
> in that the first example '1 2' is a string, while indkey is an array
> and
> the later usage of the concatenation operator with the array just
> appends
> strings to the array, which yields an array, not what I expected to be,
> a
> string!! So it's apparently irrelevant to directly use the replace
> command
> with an array !!
>
> In fact, I've also tried:
>
>
> select replace('(\'' || indkey || '\')', " ", "','") from pg_index;
>
> but forgot to mention it in the previous post.
>
> So concatenating any string to an array yields an array... and this
> query
> is irrelevant.
>
> The possible solution would be to convert this array to a string, with
> the
> insertion of the proper quotes and commas; but since the command to be
> used already inserts a delimiter, we can get rid of the replace command.
> Let's see this query now:
>
>
> select '(\'' || array_to_string(indkey, '\',\'') || '\')' from
> pg_index;
>
> I'm sure this should work :)
>
> Now we have the final WHERE statement like this:
>
> WHERE pg_attribute.attnum IN '(\'' || array_to_string(pg_index.indkey,
> '\',\'') || '\')'
>
>
> or ?
>
> WHERE pg_attribute.attnum IN ('\'' || array_to_string(pg_index.indkey,
> '\',\'') || '\'')
>
>
>
> Anyway, I got to test those queries, and I'm optimistic about it.
> Hope they'll work fine :)
>
> Best Regards,
> Roger Tannous.
>
>
>
>
>
>
--------------------------------------------------------------------------
>
--------------------------------------------------------------------------
>
--------------------------------------------------------------------------
>
--------------------------------------------------------------------------
>
> --- Roger Tannous <roger77_lb@yahoo.com> wrote:
>
> > Hi,
> >
> > If you put pg_index.indkey in the select statement, you'd notice that
> > it's
> > sometimes 1 ( it's when we have one PK field) and sometimes 1 2 ( for
> > two
> > PK fields), etc.
> >
> > So I tried to use a replace command like the following:
> >
> > (just to add parentheses, replace the space by a comma to use the
> > resulting string in an IN statement)
> >
> > select '(' || replace('1 2', " ", ",") || ')';
> >
> > which yields: (1,2)
> >
> > But the following query fails to execute!!
> > select replace(indkey, " ", ",") from pg_index;
> >
> > [
> > sub question: Did I miss quotes around elements? I mean should I
> enclose
> > every element originating from the indkey array with single quotes ?
> if
> > yes, so easy, no need to matter about it: so I should have tried the
> > following (which I didn't have time to do yet):
> >
> > select '(\'' || replace(indkey, " ", "','") || '\')' from pg_index;
> >
> > Another issue here too: Could double quotes here be the source of a
> > problem ? So I should have tested also this query:
> >
> > select '(\'' || replace(indkey, ' ', '\',\'') || '\')' from pg_index;
> >
> > I expect this query to work :) Let's hope so!!
> > ]
> >
> >
> >
> > So we can use the following WHERE statement:
> > WHERE pg_attribute.attnum IN '(' || replace('1 2', " ", ",") || ')'
> >
> > which should translate into: WHERE pg_attribute.attnum IN (1,2)
> >
> >
> > Finally, this WHERE statement:
> >
> > WHERE pg_attribute.attnum IN
> > '(\'' || replace(pg_index.indkey, " ", "','") || '\')'
> >
> >
> > [
> > Again, I should test:
> >
> > WHERE pg_attribute.attnum IN
> > '(\'' || replace(pg_index.indkey, ' ', '\',\'') || '\')'
> >
> > ]
> >
> >
> > I wish I had database access in the internet cafe I'm sending this
> > message
> > from :) instead of just loading you with this bunch of questions.
> >
> >
> > Best Regards,
> > Roger Tannous.
> >
> >
> > --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > > "D'Arcy J.M. Cain" <darcy@druid.net> writes:
> > > > That's a good question. The following query does this in a very
> > > > unsatisfactory way. Anyone know what the general solution would
> be?
> > >
> > > > ...
> > > > (
> > > > pg_index.indkey[0]=pg_attribute.attnum OR
> > > > pg_index.indkey[1]=pg_attribute.attnum OR
> > > > pg_index.indkey[2]=pg_attribute.attnum OR
> > > > pg_index.indkey[3]=pg_attribute.attnum OR
> > > > pg_index.indkey[4]=pg_attribute.attnum OR
> > > > pg_index.indkey[5]=pg_attribute.attnum OR
> > > > pg_index.indkey[6]=pg_attribute.attnum OR
> > > > pg_index.indkey[7]=pg_attribute.attnum OR
> > > > pg_index.indkey[8]=pg_attribute.attnum OR
> > > > pg_index.indkey[9]=pg_attribute.attnum
> > > > )
> > >
> > > In CVS tip you could replace this with "attnum = ANY (indkey)".
> > > Unfortunately, most array support doesn't work on int2vector in
> > > pre-8.1 releases, so I think you're kinda stuck with the above
> > > for now.
> > >
> > > regards, tom lane
> > >
> >
> >
> >
> >
> > ____________________________________________________
> > Start your day with Yahoo! - make it your home page
> > http://www.yahoo.com/r/hs
> >
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
So, D'Arcy's solution, although described as 'unsatisfactory' (ref.: D'Arcy's message), seem to be the only solution. So I noticed I was trying to play the wise man, trying to do things in a better way, but nothing was found than D'Arcy's query: SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname FROM pg_class JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND pg_namespace.nspname NOT LIKE 'pg_%' AND pg_class.relnamelike 'sip_%' JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND pg_attribute.attisdropped='f' JOIN pg_index ON pg_index.indrelid=pg_class.oid AND pg_index.indisprimary='t' AND ( pg_index.indkey[0]=pg_attribute.attnumOR pg_index.indkey[1]=pg_attribute.attnum OR pg_index.indkey[2]=pg_attribute.attnumOR pg_index.indkey[3]=pg_attribute.attnum OR pg_index.indkey[4]=pg_attribute.attnumOR pg_index.indkey[5]=pg_attribute.attnum OR pg_index.indkey[6]=pg_attribute.attnumOR pg_index.indkey[7]=pg_attribute.attnum OR pg_index.indkey[8]=pg_attribute.attnumOR pg_index.indkey[9]=pg_attribute.attnum ) ORDER BY pg_namespace.nspname, pg_class.relname,pg_attribute.attname; Regards, Roger Tannous. --- "D'Arcy J.M. Cain" <darcy@druid.net> wrote: > On Thu, 18 Aug 2005 09:40:57 -0700 (PDT) > Roger Tannous <roger77_lb@yahoo.com> wrote: > > Thanks for your query :) > > > > But it only shows the first of the primary keys of tables having > multiple > > primary keys :) > > > > This is apparently because of the pg_index.indkey[0] thing, so how can > we > > manage this query in order to get all of the keys :) > > That's a good question. The following query does this in a very > unsatisfactory way. Anyone know what the general solution would be? > > SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname > FROM pg_class > JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND > pg_namespace.nspname NOT LIKE 'pg_%' > JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND > pg_attribute.attisdropped='f' > JOIN pg_index ON pg_index.indrelid=pg_class.oid AND > pg_index.indisprimary='t' AND > ( > pg_index.indkey[0]=pg_attribute.attnum OR > pg_index.indkey[1]=pg_attribute.attnum OR > pg_index.indkey[2]=pg_attribute.attnum OR > pg_index.indkey[3]=pg_attribute.attnum OR > pg_index.indkey[4]=pg_attribute.attnum OR > pg_index.indkey[5]=pg_attribute.attnum OR > pg_index.indkey[6]=pg_attribute.attnum OR > pg_index.indkey[7]=pg_attribute.attnum OR > pg_index.indkey[8]=pg_attribute.attnum OR > pg_index.indkey[9]=pg_attribute.attnum > ) > ORDER BY pg_namespace.nspname, pg_class.relname,pg_attribute.attname; > > -- > D'Arcy J.M. Cain <darcy@druid.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. > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Mon, Aug 22, 2005 at 03:23:29AM -0700, Roger Tannous wrote: > So, D'Arcy's solution, although described as 'unsatisfactory' (ref.: > D'Arcy's message), seem to be the only solution. > > So I noticed I was trying to play the wise man, trying to do things in a > better way, but nothing was found than D'Arcy's query: There's a PL/pgSQL function, which was posted to the spanish list: http://archives.postgresql.org/pgsql-es-ayuda/2005-08/msg00644.php Not sure if it qualifies as "better" or "worse" for you. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "El que vive para el futuro es un iluso, y el que vive para el pasado, un imbécil" (Luis Adler, "Los tripulantes de la noche")