Andreas, On Sun, May 7, 2017 at 6:02 AM, Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx> wrote: > 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? 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
