[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] How to find primary keys by querying system tables
From:       Haller Christoph <ch () rodos ! fzk ! de>
Date:       2001-10-25 15:40:26
[Download RAW message or body]

> 
> 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 

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org


[prev in list] [next in list] [prev in thread] [next in thread]