On Thu, May 11, 2017 at 9:24 AM, Igor Korot <ikoro...@gmail.com> wrote:
> Melvin et al, > > On Thu, May 11, 2017 at 8:50 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> >> 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._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@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.* >> > > 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.
