Does PostgreSQL have an equivalent function for determining column metadata?
Reference: SQL_VARIANT_PROPERTY
pg_typeof() appears to be close, but it does not show the precision and scale. Looking up the type in pg_type only shows the generic type info, not the column specific, e.g. SQL Fiddle.
The fact that pg_typeof doesn't show the typmod is frustrating.
To get the fully qualified type you can query the system catalogs. Let's look at how psql does it using psql -E:
$ psql -E regress
psql (9.2.1)
Type "help" for help.
regress=> CREATE TABLE typmodtest ( a numeric(16,2), b varchar(32) );
CREATE TABLE
regress=> \d typmodtest
... tons of information printed, including this query which produces the information we want:
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
NULL AS indexdef,
NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '24641' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
This query can clearly be trimmed if you're not interested in the field collation, NOT NULL status, etc. We can also change the attrelid filter from an oid to a regclass filter:
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'typmodtest'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
producing:
attname | format_type
---------+-----------------------
a | numeric(16,2)
b | character varying(32)
This will only work on relations. I'm not aware of a reliable, generic way to get the typmod-qualified type for any result set, and it's a serious irritation. Among other things it makes it hard for the PostgreSQL JDBC driver to be compliant.
Since PostgreSQL 9.2, there is also collation for (any) to get the collation (if any) - which is one of the 6 properties you can retrieve with SQL_VARIANT_PROPERTY in SQL Server.
Works universally, not just for table columns.
You'll find more on format_type() at the same page of the manual.
@Craig demonstrated how to use it.
psqlI believe now you can just use \gdesc
SELECT
NULL AS zero,
1 AS one,
2.0 AS two,
'three' AS three,
$1 AS four,
sin($2) as five,
'foo'::varchar(4) as six,
CURRENT_DATE AS now
\gdesc
Column | Type
--------+----------------------
zero | text
one | integer
two | numeric
three | text
four | text
five | double precision
six | character varying(4) -- XXX: notice the (4)
now | date
(8 rows)
What about
select column_name, DATA_TYPE,NUMERIC_PRECISION, NUMERIC_SCALE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='YourTable'
?
