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(at)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(at)squiz(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461