Re:

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Thu, May 11, 2017 at 9:24 AM, Igor Korot <ikorot01@xxxxxxxxx> wrote:

Melvin et al,

On Thu, May 11, 2017 at 8:50 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Wed, May 10, 2017 at 11:26 PM, Igor Korot <ikorot01@xxxxxxxxx> wrote:

Hi, John et al,

On Wed, May 10, 2017 at 11:02 PM, John R Pierce <pierce@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Igor,

as others have suggested, you would be better off querying the system catalogs to get constraint information. The query below is what I use.

Perhaps it will help you modify to your needs.

I understand that. 

Trouble is that at the same time I need the complete information about all columns in the table.

And as far as I can see tis info is available in information_schema.columns table/view.

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 is not possible?

Thank you.

SELECT cn.conname,
       CASE WHEN cn.contype = 'c' THEN 'check'
            WHEN cn.contype = 'f' THEN 'foreign key'
            WHEN cn.contype = 'p' THEN 'primary key'
            WHEN cn.contype = 'u' THEN 'unique'
            WHEN cn.contype = 't' THEN 'trigger'
            WHEN cn.contype = 'x' THEN 'exclusion'
       END as type,
       cn.condeferrable,
       CASE WHEN cn.conrelid > 0
            THEN (SELECT nspname || '.' || relname
                   FROM pg_class c
                   JOIN pg_namespace n ON n.oid = c.relnamespace
                  WHERE c.oid = cn.conrelid)
            ELSE ''
       END as table,
       confkey,
       consrc 
   FROM pg_constraint cn
 ORDER BY 1;


--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

>Now are you saying that the information about the fields in the table can be retrieved from system catalog?

Absolutely, Yes. Information_schema is nothing more than views of the system catalogs!

The information about columns is in pg_attribute. Please focus your attention on the documentation for

system catalogs.  https://www.postgresql.org/docs/9.6/static/catalogs.html

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.