Igor Korot <ikoro...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
