Re: Query to return data types

On 7 December 2016 at 10:36, Killian Driscoll <killiandriscoll(at)gmail(dot)com>
wrote:

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

I see what I did: I just queried where I had already declared the precision
and scale.

What I actually want is to see is: which columns contain data with values
that contain decimal points, and the max precision and scale of these
values.

>
>
> Killian
>