Re: [GENERAL]
От | Andreas Kretschmer |
---|---|
Тема | Re: [GENERAL] |
Дата | |
Msg-id | 20170507100210.GA2317@tux обсуждение исходный текст |
Ответ на | [GENERAL] (Igor Korot <ikorot01@gmail.com>) |
Ответы |
Re: [GENERAL]
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: