Postgres Pro
Downloads
Home   >   mailing lists

Query to return data types - Mailing list pgsql-novice

From Killian Driscoll
Subject Query to return data types
Date
Msg-id CAL64pZMPcFR_GYTSaV+WAABat9L-oWrRZfmG-2BT+g7A_dka9w@mail.gmail.com
Whole thread Raw
Responses Re: Query to return data types  (Killian Driscoll <killiandriscoll@gmail.com>)
List pgsql-novice
Tree view
I want to query the precision and scale of all columns of numeric types. I see here to use information_schema.columns; I have done this, and for one schema (my db has three schemas) it works using:

SELECT column_name, data_type, numeric_scale, numeric_precision, numeric_precision_radix
FROM   information_schema.columns
WHERE  table_name = 't1'
ORDER  BY ordinal_position;

This returns

"x";"numeric";10;20;10
"y";"numeric";10;20;10
"z";"numeric";4;10;10

If I query a table on another schema using:

SELECT column_name, data_type, numeric_scale, numeric_precision, numeric_precision_radix
FROM   information_schema.columns
WHERE  table_name = 't1b'
ORDER  BY ordinal_position;

This returns

"area";"numeric";;;10
"latitude";"numeric";;;10
"longitude";"numeric";;;10

even though there should be an output for scale and precision.

How can I correctly query this?


Killian

pgsql-novice by date:

Previous
From: "Bee.Lists"
Date:
Subject: Fresh Start
Next
From: Killian Driscoll
Date:
Subject: Re: Query to return data types