pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: How to find primary keys by querying system tables
2+ messages / 2 participants
[nested] [flat]

* Re: How to find primary keys by querying system tables
@ 1970-01-01 00:00 Haller Christoph <ch@rodos.fzk.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Haller Christoph @ 1970-01-01 00:00 UTC (permalink / raw)
  To: josteinu@sylfest.hiof.no; +Cc: pgsql-sql

> 
> Is there a way of testing for membership in an int2vector-field? For
> example:
> select column, "prim.key" from ... where pg_attribute.attnum in
> pg_index.indkey
> ?? 
> 
I think so. Refer to the array section of the documentation. 
Extract:
To search for a value in an array, you must check each value of the array. This can be done by hand (if you know the size of the array): 

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

However, this quickly becomes tedious for large arrays, and is not helpful if the size of the array is unknown. Although it is not part of the primary
PostgreSQL distribution, in the contributions directory, there is an extension to PostgreSQL that defines new functions and operators for iterating
over array values. Using this, the above query could be: 

SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;

To search the entire array (not just specified columns), you could use: 

SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000;

In addition, you could find rows where the array had all values equal to 10 000 with: 

SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000;

To install this optional module, look in the contrib/array directory of the PostgreSQL source distribution. 

Regards, Christoph 



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* How to find primary keys by querying system tables
@ 2001-10-24 13:51 Jostein Ulseth <josteinu@sylfest.hiof.no>
  0 siblings, 0 replies; 2+ messages in thread

From: josteinu@sylfest.hiof.no @ 2001-10-24 13:51 UTC (permalink / raw)
  To: pgsql-sql

Hi. 

I'm trying to write a select statement that does the following; 
select all tables in my database; 
for each table, select all columns, and tell me if the column is a
primary key
in the table. 

I've tried this by querying pg_class, pg_index and pg_attribute, but I
have a problem when I this is supposed to be "general", ie I do not
know the maximum number of columns in a primary key (I can't use
subscription of "indkey").

Is there a way of testing for membership in an int2vector-field? For
example:
select column, "prim.key" from ... where pg_attribute.attnum in
pg_index.indkey
?? 

Thanks! 

-Jostein



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-10-24 13:51 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 Re: How to find primary keys by querying system tables Haller Christoph <ch@rodos.fzk.de>
2001-10-24 13:51 How to find primary keys by querying system tables Jostein Ulseth <josteinu@sylfest.hiof.no>

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