Search Postgresql Archives

Re:

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Igor Korot <ikorot01@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux