Re:

On Wed, May 10, 2017 at 11:26 PM, Igor Korot <ikorot01(at)gmail(dot)com> wrote:

> Hi, John et al,
>
> On Wed, May 10, 2017 at 11:02 PM, John R Pierce <pierce(at)hogranch(dot)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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> 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.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.