Re: How to determine the type of a column

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(at)catalyst(dot)net(dot)nz>
>To: Ville Jungman <ville_jungman(at)hotmail(dot)com>
>CC: pgsql-novice(at)postgresql(dot)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(at)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