On Wed, May 10, 2017 at 11:26 PM, Igor Korot <ikoro...@gmail.com> wrote:
> Hi, John et al, > > On Wed, May 10, 2017 at 11:02 PM, John R Pierce <pie...@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 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.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.
