Re: Getting list of Indexes & contrains

You might also want to look at
http://pgfoundry.org/projects/newsysviews/ as some of the queries there
are more efficient than what's in psql (some by a couple orders of
magnitude IIRC).

On Tue, Sep 20, 2005 at 09:09:03AM +1000, Marc McIntyre wrote:
> If you use -E option when doing issuing a "\di" command in psql it will
> show you the query that it performs to list the indexes.
>
> For example:
>
> mmcintyre@beta matrix $ psql -E -U clients marc_dev
> Welcome to psql 7.4.7, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>       \h for help with SQL commands
>       \? for help on internal slash commands
>       \g or terminate with semicolon to execute query
>       \q to quit
>
> marc_dev=> \di
> ********* QUERY **********
> SELECT n.nspname as "Schema",
>  c.relname as "Name",
>  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
> THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
>  u.usename as "Owner",
> c2.relname as "Table"
> FROM pg_catalog.pg_class c
>     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
>     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
>     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
>     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('i','')
>      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
>      AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1,2;
> **************************
>                                  List of relations
> Schema |              Name              | Type  |  Owner  |          Table
> --------+--------------------------------+-------+---------+--------------------------
> public | sq_ast_attr_name               | index | clients | sq_ast_attr
> public | sq_ast_attr_pkey               | index | clients | sq_ast_attr
> public | sq_ast_attr_type_code          | index | clients | sq_ast_attr
> public | sq_ast_attr_type_code_key      | index | clients | sq_ast_attr
> public | sq_ast_attr_uniq_val_pkey      | index | clients |
> sq_ast_attr_uniq_val
> public | sq_ast_attr_val_assetid        | index | clients | sq_ast_attr_val
> public | sq_ast_attr_val_attrid         | index | clients | sq_ast_attr_val
> public | sq_ast_attr_val_concat         | index | clients | sq_ast_attr_val
> public | sq_ast_attr_val_pkey           | index | clients | sq_ast_attr_val
> public | sq_ast_created                 | index | clients | sq_ast
> public | sq_ast_edit_access_pkey        | index | clients |
> sq_ast_edit_access
> :
>
> You can then modify and use this query in your application.
>
>
> Andrei Verovski (aka MacGuru) wrote:
>
> >Hi,
> >
> >I am using PostgreSQL 8 and adodb. adodb has a built-in function which
> >scans and retrieves db structure as assotiative array. Unfortunately,
> >it do not list indexes and constrains. What would be a SQL statement
> >to retrieve them?
> >
> >Thanks in advance for any suggestion(s).
> >
> >
> >************************************************
> >***   with best regards
> >***   Andrei Verovski (aka MacGuru)
> >***   Mac, Linux, DTP, Programming Web Site
> >***
> >***   http://snow.prohosting.com/guru4mac/
> >************************************************
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: explain analyze is your friend
> >
> >
> >
>
>
> --
> Marc McIntyre
> MySource Matrix Lead Developer
>
> ..>> Sydney ...>
> 92 Jarrett St          T: +61 (0) 2 9568 6866
> Leichhardt,            F: +61 (0) 2 9568 6733
> NSW, 2040              W: http://www.squiz.net/
>
> ..>> Canberra ...>
> Walter Turnbull Bldg   T: +61 (0) 2 6233 0607
> 44 Sydney Ave,         F: +61 (0) 2 6233 0696
> Forrest,               W: http://www.squiz.net/
> ACT 2603
>
> ..>> London ...>
> The Old Fire Station,  T: +44 (0) 20 7300 7321
> 140 Tabernacle St,     F: +44 (0) 870 112 3394
> London EC2A 4SD        W: http://www.squiz.co.uk/
>
> .....>> Open Source  - Own it  -  Squiz.net ...../>
>
> IMPORTANT: This email (and any attachments) is commercial-in-confidence and
> or may be legally privileged and must not be forwarded, copied or shared
> without express permission from Squiz. If you are not the intended
> recipient, you may not legally copy, disclose or use the contents in any
> way and you should contact squiz@squiz.net immediately and destroy this
> message and any attachments. Thank you.
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461