Re: [GENERAL] - Mailing list pgsql-general
From | Melvin Davidson |
---|---|
Subject | Re: [GENERAL] |
Date | |
Msg-id | CANu8FizfMPJTom_hde6d0oJcS3T5DUc5xLzhU+K_KKb-y6BYBQ@mail.gmail.com Whole thread Raw |
In response to | Re: [GENERAL] (Igor Korot <ikorot01@gmail.com>) |
Responses |
Re: [GENERAL]
|
List | pgsql-general |
On Wed, May 10, 2017 at 11:26 PM, Igor Korot <ikorot01@gmail.com> wrote:
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
--
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.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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
pgsql-general by date: