Обсуждение: Query to return data types
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
On 7 December 2016 at 10:36, Killian Driscoll <killiandriscoll@gmail.com> wrote:
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_radixFROM information_schema.columnsWHERE table_name = 't1'ORDER BY ordinal_position;This returns"x";"numeric";10;20;10"y";"numeric";10;20;10"z";"numeric";4;10;10If I query a table on another schema using:SELECT column_name, data_type, numeric_scale, numeric_precision, numeric_precision_radixFROM information_schema.columnsWHERE table_name = 't1b'ORDER BY ordinal_position;This returns"area";"numeric";;;10"latitude";"numeric";;;10"longitude";"numeric";;;10even 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