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

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