Re: [GENERAL] - Mailing list pgsql-general

David,

On Sun, May 7, 2017 at 7:57 AM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> On 7 May 2017 at 16:43, Igor Korot <ikorot01@gmail.com> wrote:
>> 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've not really mentioned what you'd like to see.
>
> The reason you get each column multiple times is because there are
> multiple constraints for the table, and your join condition joins only
> by table and schema, so the information_schema.columns are shown once
> for each information_schema.table_constraints row matching the join
> condition.
>
> If you can state what you want to achieve then I'm sure someone will help.

Basically what I'd like to see is the definition of each column and
whether this column is
part of primary/foreign key or not.

Something like this:

id | integer | | | 10 | 2 | 0 | | NO | P |
name | varchar| 50 | 2 | | | | Fake Name| YES | |

Thank you.

>
> (Please, in the future, give your emails a suitable subject line)

P.S.: Yes, sorry. I think I just hit "Send" too early. And its too
late to do anything about this right now.
It will just screw up the thread.

>
> --
>  David Rowley                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services