Andreas,
On Sun, May 7, 2017 at 6:02 AM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:
> Igor Korot <ikorot01@gmail.com> wrote:
>
>> Hi,
>> I'm trying to retrieve an information about the table. Query is below:
>>
>> SELECT cols.column_name, cols.data_type,
>> cols.character_maximum_length, cols.character_octet_length,
>> cols.numeric_precision, cols.numeric_precision_radix,
>> cols.numeric_scale, cols,column_default, cols.is_nullable,
>> table_cons.constraint_type, cols.ordinal_position FROM
>> information_schema.columns AS cols,
>> information_schema.table_constraints AS table_cons WHERE
>> table_cons.constraint_schema = cols.table_schema AND
>> table_cons.table_name = cols.table_name AND cols.table_schema =
>> 'public' AND cols.table_name = 'abcatcol' ORDER BY
>> cols.ordinal_position ASC;
>>
>> For some reason it returns me every column multiplied instead of
>> giving me the column information only once and whether the field is
>> part of the constraint (PRIMARY KEY, FOREIGN KEY or CHECK).
>>
>> It's been some time since I tried to write a big query but I think I
>> did it right.
>> And still got wrong results.
>>
>> Even adding DISTINCT doesn't help.
>>
>> What am I doing wrong?
>
> you are mixing columns and tables, the JOIN is wrong.
>
> SELECT cols.column_name, cols.data_type,
> cols.character_maximum_length, cols.character_octet_length,
> cols.numeric_precision, cols.numeric_precision_radix,
> cols.numeric_scale, column_default, cols.is_nullable,
> cols.ordinal_position FROM
> information_schema.columns AS cols
> where cols.table_schema =
> 'public' AND cols.table_name = 'abcatcol' ORDER BY
> cols.ordinal_position ASC;
>
>
> is this better?
But that means I will need a second query to get the column key information.
Is it possible to get this in 1 query instead of 2?
Thank you.
>
>
> Regards, Andreas Kretschme?
> --
> Andreas Kretschmer
> http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general