From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: |
Date: | 2017-05-07 10:02:10 |
Message-ID: | 20170507100210.GA2317@tux |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Igor Korot <ikorot01(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Korot | 2017-05-07 11:08:39 | Re: |
Previous Message | Christoph Moench-Tegeder | 2017-05-07 06:20:29 | Re: Where is the error? |