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?
Regards, Andreas Kretschme?
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services