Re: How to determine the type of a column

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