Re: How to determine the type of a column - Mailing list pgsql-novice

Running psql with -E seems to reveal (too?) many useful things for a
psql-novice like me. Thank You very much!

ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland
tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak
usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31)




>From: Andrew McMillan <andrew@catalyst.net.nz>
>To: Ville Jungman <ville_jungman@hotmail.com>
>CC: pgsql-novice@postgresql.org
>Subject: Re: [NOVICE] How to determine the type of a column
>Date: 03 Jan 2003 23:36:37 +1300
>
>On Fri, 2003-01-03 at 19:24, Ville Jungman wrote:
> > Hi!
> >
> > 1. create table t (i varchar(255))
> > 2. select ______(i) from t; #(Should return "varchar(255)")
> >
> > So what should I write to ______ to return "varchar(255)"
>
>wrms=# create table tess ( abcd varchar(77) );
>CREATE TABLE
>wrms=# select relname, attname, typname, typname || '(' || (atttypmod -
>4)::text || ')', pg_catalog.format_type(atttypid, atttypmod) from
>pg_class, pg_attribute, pg_type where attrelid = pg_class.oid AND
>relname = 'tess' AND pg_type.oid = atttypid AND attname = 'abcd';
>  relname | attname | typname |  ?column?   |      format_type
>---------+---------+---------+-------------+-----------------------
>  tess    | abcd    | varchar | varchar(77) | character varying(77)
>(1 row)
>
>
>Just FYI, I worked that out in response to your question by looking up
>what psql does, using the -E option.  This can be very useful for
>understanding the data dictionary relationships.
>
>Of course the DD stuff is somewhat subject to change from version to
>version.
>
>Regards,
>                    Andrew.
>
>
>andrew@kant ~/wrms $ psql -E wrms
>********* QUERY **********
>BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'andrew';
>COMMIT
>**************************
>
>Welcome to psql 7.3, 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
>
>wrms=# \d tess
>********* QUERY **********
>SELECT c.oid,
>   n.nspname,
>   c.relname
>FROM pg_catalog.pg_class c
>      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
>WHERE pg_catalog.pg_table_is_visible(c.oid)
>       AND c.relname ~ '^tess$'
>ORDER BY 2, 3;
>**************************
>
>********* QUERY **********
>SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
>FROM pg_catalog.pg_class WHERE oid = '320015'
>**************************
>
>********* QUERY **********
>SELECT a.attname,
>   pg_catalog.format_type(a.atttypid, a.atttypmod),
>   a.attnotnull, a.atthasdef, a.attnum
>FROM pg_catalog.pg_attribute a
>WHERE a.attrelid = '320015' AND a.attnum > 0 AND NOT a.attisdropped
>ORDER BY a.attnum
>**************************
>
>             Table "public.tess"
>  Column |         Type          | Modifiers
>--------+-----------------------+-----------
>  abcd   | character varying(77) |
>
>
>
>--
>---------------------------------------------------------------------
>Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
>WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
>DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
>            Survey for nothing with http://survey.net.nz/
>---------------------------------------------------------------------


_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*
http://join.msn.com/?page=features/virus