Re:

On Thu, May 11, 2017 at 9:24 AM, Igor Korot <ikorot01(at)gmail(dot)com> wrote:

> Melvin et al,
>
> On Thu, May 11, 2017 at 8:50 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
>>
>> 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._p
>>> g_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_s
>>> chema._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.*
>>
>
> 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
<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.