Обсуждение: [GENERAL]

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?

Since I'm using libpq + C++ I can do multiple queries, but I thought
getting it in one shot would be preferable - less db hit.

Thank you for the help.

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

Andreas,

On Sun, May 7, 2017 at 6:02 AM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:
> 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?

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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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.

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

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

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

On 8 May 2017 at 00:42, Igor Korot <ikorot01@gmail.com> wrote:
> 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.

information_schema.table_constraints is of no use to you then. There
are no details about which column(s) the constraint applies to.

Likely you'll want to look at pg_constraint for contype in('p','f')
and unnest(conkey) and join that to information_schema.columns. You
may also need to think about pg_constraint.confkey, depending on if
you want to know if the column is referencing or referenced in a
foreign key constraint.



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

David Rowley <david.rowley@2ndquadrant.com> writes:
> On 8 May 2017 at 00:42, Igor Korot <ikorot01@gmail.com> wrote:
>> 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.

> information_schema.table_constraints is of no use to you then. There
> are no details about which column(s) the constraint applies to.

information_schema.constraint_column_usage might help, though you'll
still need to join that to other views.

> Likely you'll want to look at pg_constraint for contype in('p','f')
> and unnest(conkey) and join that to information_schema.columns. You
> may also need to think about pg_constraint.confkey, depending on if
> you want to know if the column is referencing or referenced in a
> foreign key constraint.

If you don't mind a PG-specific solution, that's the way to go, as
it will surely be more efficient than going through the information_schema
views.  Also, there are things that act like constraints but aren't
SQL-standard, such as exclusion constraints; we don't reflect those
in information_schema, so if you want to include those then you
*must* look directly at the catalogs.

            regards, tom lane

David,

On Sun, May 7, 2017 at 8:57 AM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> On 8 May 2017 at 00:42, Igor Korot <ikorot01@gmail.com> wrote:
>> 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.
>
> information_schema.table_constraints is of no use to you then. There
> are no details about which column(s) the constraint applies to.
>
> Likely you'll want to look at pg_constraint for contype in('p','f')
> and unnest(conkey) and join that to information_schema.columns. You
> may also need to think about pg_constraint.confkey, depending on if
> you want to know if the column is referencing or referenced in a
> foreign key constraint.

I checked pg_constraint view, but I didn't see anything to join to.
I do see a table name, but no schema or column name.

Any idea on the query syntax?

Thank you.

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

Hi, guys,

On Sun, May 7, 2017 at 1:40 PM, Igor Korot <ikorot01@gmail.com> wrote:
> David,
>
> On Sun, May 7, 2017 at 8:57 AM, David Rowley
> <david.rowley@2ndquadrant.com> wrote:
>> On 8 May 2017 at 00:42, Igor Korot <ikorot01@gmail.com> wrote:
>>> 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.
>>
>> information_schema.table_constraints is of no use to you then. There
>> are no details about which column(s) the constraint applies to.
>>
>> Likely you'll want to look at pg_constraint for contype in('p','f')
>> and unnest(conkey) and join that to information_schema.columns. You
>> may also need to think about pg_constraint.confkey, depending on if
>> you want to know if the column is referencing or referenced in a
>> foreign key constraint.
>
> I checked pg_constraint view, but I didn't see anything to join to.
> I do see a table name, but no schema or column name.
>
> Any idea on the query syntax?

I found this: https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
but now I need
to connect this with information_schema.columns.

What is best way to do it?

Or maybe that query I referenced is completely wrong?

Please help.

Thank you.

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

On 5/10/2017 7:45 PM, Igor Korot wrote:
> I found this:https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
> but now I need
> to connect this with information_schema.columns.
>
> What is best way to do it?
>
> Or maybe that query I referenced is completely wrong?


if you're using pg_catalog stuff there's little point in using the
information_schema views, which exist for compatability with the SQL
standard.

information_schema.columns is a view, like...

View definition:
  SELECT current_database()::information_schema.sql_identifier AS
table_catalog,
     nc.nspname::information_schema.sql_identifier AS table_schema,
     c.relname::information_schema.sql_identifier AS table_name,
     a.attname::information_schema.sql_identifier AS column_name,
     a.attnum::information_schema.cardinal_number AS ordinal_position,
     pg_get_expr(ad.adbin,
ad.adrelid)::information_schema.character_data AS column_default,
         CASE
             WHEN a.attnotnull OR t.typtype = 'd'::"char" AND
t.typnotnull THEN 'NO'::text
             ELSE 'YES'::text
         END::information_schema.yes_or_no AS is_nullable,
         CASE
             WHEN t.typtype = 'd'::"char" THEN
             CASE
                 WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN
'ARRAY'::text
                 WHEN nbt.nspname = 'pg_catalog'::name THEN
format_type(t.typbasetype, NULL::integer)
                 ELSE 'USER-DEFINED'::text
             END
             ELSE
             CASE
                 WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN
'ARRAY'::text
                 WHEN nt.nspname = 'pg_catalog'::name THEN
format_type(a.atttypid, NULL::integer)
                 ELSE 'USER-DEFINED'::text
             END
         END::information_schema.character_data AS data_type,
information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_numb
er AS character_maximum_length,
information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_nu
mber AS character_octet_length,
information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_nu
mber AS numeric_precision,
information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardi
nal_number AS numeric_precision_radix,
information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_number
  AS numeric_scale,
information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_n
umber AS datetime_precision,
information_schema._pg_interval_type(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.character_data
AS interval_type,
     NULL::integer::information_schema.cardinal_number AS
interval_precision,
     NULL::character varying::information_schema.sql_identifier AS
character_set_catalog,
     NULL::character varying::information_schema.sql_identifier AS
character_set_schema,
     NULL::character varying::information_schema.sql_identifier AS
character_set_name,
         CASE
             WHEN nco.nspname IS NOT NULL THEN current_database()
             ELSE NULL::name
         END::information_schema.sql_identifier AS collation_catalog,
     nco.nspname::information_schema.sql_identifier AS collation_schema,
     co.collname::information_schema.sql_identifier AS collation_name,
         CASE
             WHEN t.typtype = 'd'::"char" THEN current_database()
             ELSE NULL::name
         END::information_schema.sql_identifier AS domain_catalog,
         CASE
             WHEN t.typtype = 'd'::"char" THEN nt.nspname
             ELSE NULL::name
         END::information_schema.sql_identifier AS domain_schema,
         CASE
             WHEN t.typtype = 'd'::"char" THEN t.typname
             ELSE NULL::name
         END::information_schema.sql_identifier AS domain_name,
     current_database()::information_schema.sql_identifier AS udt_catalog,
     COALESCE(nbt.nspname,
nt.nspname)::information_schema.sql_identifier AS udt_schema,
     COALESCE(bt.typname, t.typname)::information_schema.sql_identifier
AS udt_name,
     NULL::character varying::information_schema.sql_identifier AS
scope_catalog,
     NULL::character varying::information_schema.sql_identifier AS
scope_schema,
     NULL::character varying::information_schema.sql_identifier AS
scope_name,
     NULL::integer::information_schema.cardinal_number AS
maximum_cardinality,
     a.attnum::information_schema.sql_identifier AS dtd_identifier,
     'NO'::character varying::information_schema.yes_or_no AS
is_self_referencing,
     'NO'::character varying::information_schema.yes_or_no AS is_identity,
     NULL::character varying::information_schema.character_data AS
identity_generation,
     NULL::character varying::information_schema.character_data AS
identity_start,
     NULL::character varying::information_schema.character_data AS
identity_increment,
     NULL::character varying::information_schema.character_data AS
identity_maximum,
     NULL::character varying::information_schema.character_data AS
identity_minimum,
     NULL::character varying::information_schema.yes_or_no AS
identity_cycle,
     'NEVER'::character varying::information_schema.character_data AS
is_generated,
     NULL::character varying::information_schema.character_data AS
generation_expression,
         CASE
             WHEN c.relkind = 'r'::"char" OR (c.relkind = ANY
(ARRAY['v'::"char", 'f'::"char"])) AND
pg_column_is_updatable(c.oid::regclass, a.attnum, false) THEN 'YE
S'::text
             ELSE 'NO'::text
         END::information_schema.yes_or_no AS is_updatable
    FROM pg_attribute a
      LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum =
ad.adnum
      JOIN (pg_class c
      JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid
      JOIN (pg_type t
      JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid
      LEFT JOIN (pg_type bt
      JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype =
'd'::"char" AND t.typbasetype = bt.oid
      LEFT JOIN (pg_collation co
      JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON
a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog'::name OR
co.collname <> 'default'::name)
   WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT
a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char",
'f'::"char"])) AND (pg_has_
role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum,
'SELECT, INSERT, UPDATE, REFERENCES'::text));



--
john r pierce, recycling bits in santa cruz

Hi, John et al,

On Wed, May 10, 2017 at 11:02 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 5/10/2017 7:45 PM, Igor Korot wrote:
>>
>> I found
>> this:https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
>> but now I need
>> to connect this with information_schema.columns.
>>
>> What is best way to do it?
>>
>> Or maybe that query I referenced is completely wrong?
>
>
>
> if you're using pg_catalog stuff there's little point in using the
> information_schema views, which exist for compatability with the SQL
> standard.
>
> information_schema.columns is a view, like...

Like I said, what I expect to see from the query is:

id | integer | | 5| 2 | 0 | P |
name | varchar | 50| 2 | | | | <NULL>

So I need the information about the field and whether the field is a
primary/foreign key or not.

And this is according to the schema.table.

Thank you.

>
> View definition:
>  SELECT current_database()::information_schema.sql_identifier AS
> table_catalog,
>     nc.nspname::information_schema.sql_identifier AS table_schema,
>     c.relname::information_schema.sql_identifier AS table_name,
>     a.attname::information_schema.sql_identifier AS column_name,
>     a.attnum::information_schema.cardinal_number AS ordinal_position,
>     pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data AS
> column_default,
>         CASE
>             WHEN a.attnotnull OR t.typtype = 'd'::"char" AND t.typnotnull
> THEN 'NO'::text
>             ELSE 'YES'::text
>         END::information_schema.yes_or_no AS is_nullable,
>         CASE
>             WHEN t.typtype = 'd'::"char" THEN
>             CASE
>                 WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN
> 'ARRAY'::text
>                 WHEN nbt.nspname = 'pg_catalog'::name THEN
> format_type(t.typbasetype, NULL::integer)
>                 ELSE 'USER-DEFINED'::text
>             END
>             ELSE
>             CASE
>                 WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN
> 'ARRAY'::text
>                 WHEN nt.nspname = 'pg_catalog'::name THEN
> format_type(a.atttypid, NULL::integer)
>                 ELSE 'USER-DEFINED'::text
>             END
>         END::information_schema.character_data AS data_type,
> information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_numb
> er AS character_maximum_length,
> information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_nu
> mber AS character_octet_length,
> information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_nu
> mber AS numeric_precision,
> information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardi
> nal_number AS numeric_precision_radix,
> information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_number
>  AS numeric_scale,
> information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_n
> umber AS datetime_precision,
> information_schema._pg_interval_type(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.character_data
> AS interval_type,
>     NULL::integer::information_schema.cardinal_number AS interval_precision,
>     NULL::character varying::information_schema.sql_identifier AS
> character_set_catalog,
>     NULL::character varying::information_schema.sql_identifier AS
> character_set_schema,
>     NULL::character varying::information_schema.sql_identifier AS
> character_set_name,
>         CASE
>             WHEN nco.nspname IS NOT NULL THEN current_database()
>             ELSE NULL::name
>         END::information_schema.sql_identifier AS collation_catalog,
>     nco.nspname::information_schema.sql_identifier AS collation_schema,
>     co.collname::information_schema.sql_identifier AS collation_name,
>         CASE
>             WHEN t.typtype = 'd'::"char" THEN current_database()
>             ELSE NULL::name
>         END::information_schema.sql_identifier AS domain_catalog,
>         CASE
>             WHEN t.typtype = 'd'::"char" THEN nt.nspname
>             ELSE NULL::name
>         END::information_schema.sql_identifier AS domain_schema,
>         CASE
>             WHEN t.typtype = 'd'::"char" THEN t.typname
>             ELSE NULL::name
>         END::information_schema.sql_identifier AS domain_name,
>     current_database()::information_schema.sql_identifier AS udt_catalog,
>     COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS
> udt_schema,
>     COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS
> udt_name,
>     NULL::character varying::information_schema.sql_identifier AS
> scope_catalog,
>     NULL::character varying::information_schema.sql_identifier AS
> scope_schema,
>     NULL::character varying::information_schema.sql_identifier AS
> scope_name,
>     NULL::integer::information_schema.cardinal_number AS
> maximum_cardinality,
>     a.attnum::information_schema.sql_identifier AS dtd_identifier,
>     'NO'::character varying::information_schema.yes_or_no AS
> is_self_referencing,
>     'NO'::character varying::information_schema.yes_or_no AS is_identity,
>     NULL::character varying::information_schema.character_data AS
> identity_generation,
>     NULL::character varying::information_schema.character_data AS
> identity_start,
>     NULL::character varying::information_schema.character_data AS
> identity_increment,
>     NULL::character varying::information_schema.character_data AS
> identity_maximum,
>     NULL::character varying::information_schema.character_data AS
> identity_minimum,
>     NULL::character varying::information_schema.yes_or_no AS identity_cycle,
>     'NEVER'::character varying::information_schema.character_data AS
> is_generated,
>     NULL::character varying::information_schema.character_data AS
> generation_expression,
>         CASE
>             WHEN c.relkind = 'r'::"char" OR (c.relkind = ANY
> (ARRAY['v'::"char", 'f'::"char"])) AND
> pg_column_is_updatable(c.oid::regclass, a.attnum, false) THEN 'YE
> S'::text
>             ELSE 'NO'::text
>         END::information_schema.yes_or_no AS is_updatable
>    FROM pg_attribute a
>      LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum =
> ad.adnum
>      JOIN (pg_class c
>      JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid
>      JOIN (pg_type t
>      JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid
>      LEFT JOIN (pg_type bt
>      JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype =
> 'd'::"char" AND t.typbasetype = bt.oid
>      LEFT JOIN (pg_collation co
>      JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation
> = co.oid AND (nco.nspname <> 'pg_catalog'::name OR co.collname <>
> 'default'::name)
>   WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT
> a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char",
> 'f'::"char"])) AND (pg_has_
> role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum,
> 'SELECT, INSERT, UPDATE, REFERENCES'::text));
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 05/11/2017 06:24 AM, Igor Korot wrote:
> Melvin et al,
>

>
> Now are you saying that the information about the fields in the table
> can be retrieved from
> system catalog? Or are you saying that retrieving everything in one shot

As Melvin and others have mentioned that is where the
information_schema.* get their data. If you want to see how it is done
as a way of possibly customizing for your own use see in the source code:

src/backend/catalog/information_schema.sql


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18be08fead5762970e7a9718422c6fd0835bd209;hb=ca9cfed883333d5801716eb01cf28b6b5be2b5cd

> is not possible?
>
> Thank you.



--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian et al,

On Thu, May 11, 2017 at 9:54 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 05/11/2017 06:24 AM, Igor Korot wrote:
>>
>> Melvin et al,
>>
>
>>
>> Now are you saying that the information about the fields in the table can
>> be retrieved from
>> system catalog? Or are you saying that retrieving everything in one shot
>
>
> As Melvin and others have mentioned that is where the information_schema.*
> get their data. If you want to see how it is done as a way of possibly
> customizing for your own use see in the source code:
>
> src/backend/catalog/information_schema.sql
>
>
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18be08fead5762970e7a9718422c6fd0835bd209;hb=ca9cfed883333d5801716eb01cf28b6b5be2b5cd

Thank you.
Will take a look and modify to use in my program.

I presume I'm allowed to do that, right?

>
>
>> is not possible?
>>
>> Thank you.
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

>
> Like I said, what I expect to see from the query is:
>
> id | integer | | 5| 2 | 0 | P |
> name | varchar | 50| 2 | | | | <NULL>
>
> So I need the information about the field and whether the field is a
> primary/foreign key or not.
>

I had a go at it using the catalog tables from v9.5 and an example
table 'films', maybe you can extend this further to get what you need
from the pg_attribute, pg_class, pg_type and pg_constraint tables?

SELECT columns.attname as name,
data_types.typname as type,
columns.attlen as length,
columns.attnotnull as not_null,
constraints.contype
FROM pg_attribute columns
INNER JOIN pg_class tables ON columns.attrelid = tables.oid
INNER JOIN pg_type data_types ON columns.atttypid = data_types.oid
LEFT JOIN pg_constraint constraints
ON constraints.conrelid = columns.attrelid AND columns.attnum = ANY
(constraints.conkey)
WHERE tables.relname = 'films' AND columns.attnum > 0;

Thanks,
Neil

--
Neil Anderson
neil@postgrescompare.com
https://www.postgrescompare.com

On 05/11/2017 07:26 AM, Igor Korot wrote:
> Adrian et al,
>
> On Thu, May 11, 2017 at 9:54 AM, Adrian Klaver
> <adrian.klaver@aklaver.com> wrote:
>> On 05/11/2017 06:24 AM, Igor Korot wrote:

> Thank you.
> Will take a look and modify to use in my program.
>
> I presume I'm allowed to do that, right?

Yes.




--
Adrian Klaver
adrian.klaver@aklaver.com

Hi, ALL

On Thu, May 11, 2017 at 6:47 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 05/11/2017 07:26 AM, Igor Korot wrote:
>>
>> Adrian et al,
>>
>> On Thu, May 11, 2017 at 9:54 AM, Adrian Klaver
>> <adrian.klaver@aklaver.com> wrote:
>>>
>>> On 05/11/2017 06:24 AM, Igor Korot wrote:
>
>
>> Thank you.
>> Will take a look and modify to use in my program.
>>
>> I presume I'm allowed to do that, right?
>
>
> Yes.

I'm posting this hoping that I will save some time to someone else.

Here is the query I came up with:

SELECT DISTINCT ordinal_position, column_name, data_type,
character_maximum_length, character_octet_length, numeric_precision,
numeric_precision_radix, numeric_scale, is_nullable, column_default,
CASE WHEN column_name IN (SELECT ccu.column_name FROM
information_schema.constraint_column_usage ccu,
information_schema.table_constraints tc WHERE ccu.constraint_name =
tc.constraint_name AND tc.constraint_type = 'PRIMARY KEY' AND
ccu.table_name = $2) THEN 'YES' ELSE 'NO' END AS is_pk  FROM
information_schema.columns col, information_schema.table_constraints
tc WHERE tc.table_schema = col.table_schema AND tc.table_name =
col.table_name AND col.table_schema = $1 AND col.table_name = $2 ORDER
BY ordinal_position;

Is there a better way to do that? I don't mind using Postgres-only solution.

Thank you.

BTW, is there a difference between query a real tables or query a view?

>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com