On 7 May 2017 at 16:43, Igor Korot <ikorot01@gmail.com> wrote:
> 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've not really mentioned what you'd like to see.
The reason you get each column multiple times is because there are
multiple constraints for the table, and your join condition joins only
by table and schema, so the information_schema.columns are shown once
for each information_schema.table_constraints row matching the join
condition.
If you can state what you want to achieve then I'm sure someone will help.
(Please, in the future, give your emails a suitable subject line)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services