Query to return data types

From: Killian Driscoll <killiandriscoll(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Query to return data types
Date: 2016-12-07 09:36:55
Message-ID: CAL64pZMPcFR_GYTSaV+WAABat9L-oWrRZfmG-2BT+g7A_dka9w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I want to query the precision and scale of all columns of numeric types. I
see here
<http://dba.stackexchange.com/questions/75015/query-to-return-output-column-names-and-data-types-of-a-query-table-or-view>
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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Killian Driscoll 2016-12-07 09:50:55 Re: Query to return data types
Previous Message Bee.Lists 2016-12-06 11:10:21 Fresh Start