CODEWITHSUNDEEP
postgresqlinformation-schema
shafa.yeat
shafa.yeat

Reputation: 308

information_schema.columns returning different datatype.

in pagila sample database inside film table there is column called release_year with datatype year but when I query for this column datatype using information_schema I get Integer as it's data type what is going wrong? the query I have used 

select c.column_name,c.data_type
from information_schema.columns c
where c.table_name = 'film'
;

How do I get the actual datatype?

Upvotes: 0

Views: 513

Answers (1)

shafa.yeat
shafa.yeat

Reputation: 308

I have got the answer using this query:

SELECT
    a.attname as "Column",
    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
FROM
    pg_catalog.pg_attribute a
WHERE
    a.attnum > 0
    AND NOT a.attisdropped
    AND a.attrelid = (
        SELECT c.oid
        FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ '^(table_name)$'
            AND pg_catalog.pg_table_is_visible(c.oid)
    )
;

enter code here

Upvotes: 1

Related Questions