Thread: [GENERAL]
Hi, I'm trying to retrieve an information about the table. Query is below: SELECT cols.column_name, cols.data_type, cols.character_maximum_length, cols.character_octet_length, cols.numeric_precision, cols.numeric_precision_radix, cols.numeric_scale, cols,column_default, cols.is_nullable, table_cons.constraint_type, cols.ordinal_position FROM information_schema.columns AS cols, information_schema.table_constraints AS table_cons WHERE table_cons.constraint_schema = cols.table_schema AND table_cons.table_name = cols.table_name AND cols.table_schema = 'public' AND cols.table_name = 'abcatcol' ORDER BY cols.ordinal_position ASC; For some reason it returns me every column multiplied instead of giving me the column information only once and whether the field is part of the constraint (PRIMARY KEY, FOREIGN KEY or CHECK). It's been some time since I tried to write a big query but I think I did it right. And still got wrong results. Even adding DISTINCT doesn't help. What am I doing wrong? Since I'm using libpq + C++ I can do multiple queries, but I thought getting it in one shot would be preferable - less db hit. Thank you for the help.
Igor Korot <ikorot01@gmail.com> wrote: > Hi, > I'm trying to retrieve an information about the table. Query is below: > > SELECT cols.column_name, cols.data_type, > cols.character_maximum_length, cols.character_octet_length, > cols.numeric_precision, cols.numeric_precision_radix, > cols.numeric_scale, cols,column_default, cols.is_nullable, > table_cons.constraint_type, cols.ordinal_position FROM > information_schema.columns AS cols, > information_schema.table_constraints AS table_cons WHERE > table_cons.constraint_schema = cols.table_schema AND > table_cons.table_name = cols.table_name AND cols.table_schema = > 'public' AND cols.table_name = 'abcatcol' ORDER BY > cols.ordinal_position ASC; > > For some reason it returns me every column multiplied instead of > giving me the column information only once and whether the field is > part of the constraint (PRIMARY KEY, FOREIGN KEY or CHECK). > > It's been some time since I tried to write a big query but I think I > did it right. > And still got wrong results. > > Even adding DISTINCT doesn't help. > > What am I doing wrong? you are mixing columns and tables, the JOIN is wrong. SELECT cols.column_name, cols.data_type, cols.character_maximum_length, cols.character_octet_length, cols.numeric_precision, cols.numeric_precision_radix, cols.numeric_scale, column_default, cols.is_nullable, cols.ordinal_position FROM information_schema.columns AS cols where cols.table_schema = 'public' AND cols.table_name = 'abcatcol' ORDER BY cols.ordinal_position ASC; is this better? Regards, Andreas Kretschme? -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Andreas, On Sun, May 7, 2017 at 6:02 AM, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > Igor Korot <ikorot01@gmail.com> wrote: > >> Hi, >> I'm trying to retrieve an information about the table. Query is below: >> >> SELECT cols.column_name, cols.data_type, >> cols.character_maximum_length, cols.character_octet_length, >> cols.numeric_precision, cols.numeric_precision_radix, >> cols.numeric_scale, cols,column_default, cols.is_nullable, >> table_cons.constraint_type, cols.ordinal_position FROM >> information_schema.columns AS cols, >> information_schema.table_constraints AS table_cons WHERE >> table_cons.constraint_schema = cols.table_schema AND >> table_cons.table_name = cols.table_name AND cols.table_schema = >> 'public' AND cols.table_name = 'abcatcol' ORDER BY >> cols.ordinal_position ASC; >> >> For some reason it returns me every column multiplied instead of >> giving me the column information only once and whether the field is >> part of the constraint (PRIMARY KEY, FOREIGN KEY or CHECK). >> >> It's been some time since I tried to write a big query but I think I >> did it right. >> And still got wrong results. >> >> Even adding DISTINCT doesn't help. >> >> What am I doing wrong? > > you are mixing columns and tables, the JOIN is wrong. > > SELECT cols.column_name, cols.data_type, > cols.character_maximum_length, cols.character_octet_length, > cols.numeric_precision, cols.numeric_precision_radix, > cols.numeric_scale, column_default, cols.is_nullable, > cols.ordinal_position FROM > information_schema.columns AS cols > where cols.table_schema = > 'public' AND cols.table_name = 'abcatcol' ORDER BY > cols.ordinal_position ASC; > > > is this better? But that means I will need a second query to get the column key information. Is it possible to get this in 1 query instead of 2? Thank you. > > > Regards, Andreas Kretschme? > -- > Andreas Kretschmer > http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On 7 May 2017 at 16:43, Igor Korot <ikorot01@gmail.com> wrote: > I'm trying to retrieve an information about the table. Query is below: > > SELECT cols.column_name, cols.data_type, > cols.character_maximum_length, cols.character_octet_length, > cols.numeric_precision, cols.numeric_precision_radix, > cols.numeric_scale, cols,column_default, cols.is_nullable, > table_cons.constraint_type, cols.ordinal_position FROM > information_schema.columns AS cols, > information_schema.table_constraints AS table_cons WHERE > table_cons.constraint_schema = cols.table_schema AND > table_cons.table_name = cols.table_name AND cols.table_schema = > 'public' AND cols.table_name = 'abcatcol' ORDER BY > cols.ordinal_position ASC; > > For some reason it returns me every column multiplied instead of > giving me the column information only once and whether the field is > part of the constraint (PRIMARY KEY, FOREIGN KEY or CHECK). > > It's been some time since I tried to write a big query but I think I > did it right. > And still got wrong results. > > Even adding DISTINCT doesn't help. > > What am I doing wrong? You've not really mentioned what you'd like to see. The reason you get each column multiple times is because there are multiple constraints for the table, and your join condition joins only by table and schema, so the information_schema.columns are shown once for each information_schema.table_constraints row matching the join condition. If you can state what you want to achieve then I'm sure someone will help. (Please, in the future, give your emails a suitable subject line) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
David, On Sun, May 7, 2017 at 7:57 AM, David Rowley <david.rowley@2ndquadrant.com> wrote: > On 7 May 2017 at 16:43, Igor Korot <ikorot01@gmail.com> wrote: >> I'm trying to retrieve an information about the table. Query is below: >> >> SELECT cols.column_name, cols.data_type, >> cols.character_maximum_length, cols.character_octet_length, >> cols.numeric_precision, cols.numeric_precision_radix, >> cols.numeric_scale, cols,column_default, cols.is_nullable, >> table_cons.constraint_type, cols.ordinal_position FROM >> information_schema.columns AS cols, >> information_schema.table_constraints AS table_cons WHERE >> table_cons.constraint_schema = cols.table_schema AND >> table_cons.table_name = cols.table_name AND cols.table_schema = >> 'public' AND cols.table_name = 'abcatcol' ORDER BY >> cols.ordinal_position ASC; >> >> For some reason it returns me every column multiplied instead of >> giving me the column information only once and whether the field is >> part of the constraint (PRIMARY KEY, FOREIGN KEY or CHECK). >> >> It's been some time since I tried to write a big query but I think I >> did it right. >> And still got wrong results. >> >> Even adding DISTINCT doesn't help. >> >> What am I doing wrong? > > You've not really mentioned what you'd like to see. > > The reason you get each column multiple times is because there are > multiple constraints for the table, and your join condition joins only > by table and schema, so the information_schema.columns are shown once > for each information_schema.table_constraints row matching the join > condition. > > If you can state what you want to achieve then I'm sure someone will help. Basically what I'd like to see is the definition of each column and whether this column is part of primary/foreign key or not. Something like this: id | integer | | | 10 | 2 | 0 | | NO | P | name | varchar| 50 | 2 | | | | Fake Name| YES | | Thank you. > > (Please, in the future, give your emails a suitable subject line) P.S.: Yes, sorry. I think I just hit "Send" too early. And its too late to do anything about this right now. It will just screw up the thread. > > -- > David Rowley http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
On 8 May 2017 at 00:42, Igor Korot <ikorot01@gmail.com> wrote: > Basically what I'd like to see is the definition of each column and > whether this column is > part of primary/foreign key or not. information_schema.table_constraints is of no use to you then. There are no details about which column(s) the constraint applies to. Likely you'll want to look at pg_constraint for contype in('p','f') and unnest(conkey) and join that to information_schema.columns. You may also need to think about pg_constraint.confkey, depending on if you want to know if the column is referencing or referenced in a foreign key constraint. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
David Rowley <david.rowley@2ndquadrant.com> writes: > On 8 May 2017 at 00:42, Igor Korot <ikorot01@gmail.com> wrote: >> Basically what I'd like to see is the definition of each column and >> whether this column is part of primary/foreign key or not. > information_schema.table_constraints is of no use to you then. There > are no details about which column(s) the constraint applies to. information_schema.constraint_column_usage might help, though you'll still need to join that to other views. > Likely you'll want to look at pg_constraint for contype in('p','f') > and unnest(conkey) and join that to information_schema.columns. You > may also need to think about pg_constraint.confkey, depending on if > you want to know if the column is referencing or referenced in a > foreign key constraint. If you don't mind a PG-specific solution, that's the way to go, as it will surely be more efficient than going through the information_schema views. Also, there are things that act like constraints but aren't SQL-standard, such as exclusion constraints; we don't reflect those in information_schema, so if you want to include those then you *must* look directly at the catalogs. regards, tom lane
David, On Sun, May 7, 2017 at 8:57 AM, David Rowley <david.rowley@2ndquadrant.com> wrote: > On 8 May 2017 at 00:42, Igor Korot <ikorot01@gmail.com> wrote: >> Basically what I'd like to see is the definition of each column and >> whether this column is >> part of primary/foreign key or not. > > information_schema.table_constraints is of no use to you then. There > are no details about which column(s) the constraint applies to. > > Likely you'll want to look at pg_constraint for contype in('p','f') > and unnest(conkey) and join that to information_schema.columns. You > may also need to think about pg_constraint.confkey, depending on if > you want to know if the column is referencing or referenced in a > foreign key constraint. I checked pg_constraint view, but I didn't see anything to join to. I do see a table name, but no schema or column name. Any idea on the query syntax? Thank you. > > > > -- > David Rowley http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
Hi, guys, On Sun, May 7, 2017 at 1:40 PM, Igor Korot <ikorot01@gmail.com> wrote: > David, > > On Sun, May 7, 2017 at 8:57 AM, David Rowley > <david.rowley@2ndquadrant.com> wrote: >> On 8 May 2017 at 00:42, Igor Korot <ikorot01@gmail.com> wrote: >>> Basically what I'd like to see is the definition of each column and >>> whether this column is >>> part of primary/foreign key or not. >> >> information_schema.table_constraints is of no use to you then. There >> are no details about which column(s) the constraint applies to. >> >> Likely you'll want to look at pg_constraint for contype in('p','f') >> and unnest(conkey) and join that to information_schema.columns. You >> may also need to think about pg_constraint.confkey, depending on if >> you want to know if the column is referencing or referenced in a >> foreign key constraint. > > I checked pg_constraint view, but I didn't see anything to join to. > I do see a table name, but no schema or column name. > > Any idea on the query syntax? 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? Please help. Thank you. > > Thank you. > >> >> >> >> -- >> David Rowley http://www.2ndQuadrant.com/ >> PostgreSQL Development, 24x7 Support, Training & Services
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... 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
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
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)
Melvin et al,
On Thu, May 11, 2017 at 8:50 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
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_c olumns,
>> 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.card i
> 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 Perhaps it will help you modify to your needs.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.
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.
On Thu, May 11, 2017 at 9:24 AM, Igor Korot <ikorot01@gmail.com> wrote:
Melvin et al,On Thu, May 11, 2017 at 8:50 AM, Melvin Davidson <melvin6925@gmail.com> wrote: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_c olumns,
>> 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.card i
> 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 Perhaps it will help you modify to your needs.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.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 fromsystem 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
--
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)
On 05/11/2017 06:24 AM, Igor Korot wrote: > Melvin et al, > > > 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 As Melvin and others have mentioned that is where the information_schema.* get their data. If you want to see how it is done as a way of possibly customizing for your own use see in the source code: src/backend/catalog/information_schema.sql https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18be08fead5762970e7a9718422c6fd0835bd209;hb=ca9cfed883333d5801716eb01cf28b6b5be2b5cd > is not possible? > > Thank you. -- Adrian Klaver adrian.klaver@aklaver.com
Adrian et al, On Thu, May 11, 2017 at 9:54 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 05/11/2017 06:24 AM, Igor Korot wrote: >> >> Melvin et al, >> > >> >> 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 > > > As Melvin and others have mentioned that is where the information_schema.* > get their data. If you want to see how it is done as a way of possibly > customizing for your own use see in the source code: > > src/backend/catalog/information_schema.sql > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18be08fead5762970e7a9718422c6fd0835bd209;hb=ca9cfed883333d5801716eb01cf28b6b5be2b5cd Thank you. Will take a look and modify to use in my program. I presume I'm allowed to do that, right? > > >> is not possible? >> >> Thank you. > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
> > 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. > I had a go at it using the catalog tables from v9.5 and an example table 'films', maybe you can extend this further to get what you need from the pg_attribute, pg_class, pg_type and pg_constraint tables? SELECT columns.attname as name, data_types.typname as type, columns.attlen as length, columns.attnotnull as not_null, constraints.contype FROM pg_attribute columns INNER JOIN pg_class tables ON columns.attrelid = tables.oid INNER JOIN pg_type data_types ON columns.atttypid = data_types.oid LEFT JOIN pg_constraint constraints ON constraints.conrelid = columns.attrelid AND columns.attnum = ANY (constraints.conkey) WHERE tables.relname = 'films' AND columns.attnum > 0; Thanks, Neil -- Neil Anderson neil@postgrescompare.com https://www.postgrescompare.com
On 05/11/2017 07:26 AM, Igor Korot wrote: > Adrian et al, > > On Thu, May 11, 2017 at 9:54 AM, Adrian Klaver > <adrian.klaver@aklaver.com> wrote: >> On 05/11/2017 06:24 AM, Igor Korot wrote: > Thank you. > Will take a look and modify to use in my program. > > I presume I'm allowed to do that, right? Yes. -- Adrian Klaver adrian.klaver@aklaver.com
Hi, ALL On Thu, May 11, 2017 at 6:47 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 05/11/2017 07:26 AM, Igor Korot wrote: >> >> Adrian et al, >> >> On Thu, May 11, 2017 at 9:54 AM, Adrian Klaver >> <adrian.klaver@aklaver.com> wrote: >>> >>> On 05/11/2017 06:24 AM, Igor Korot wrote: > > >> Thank you. >> Will take a look and modify to use in my program. >> >> I presume I'm allowed to do that, right? > > > Yes. I'm posting this hoping that I will save some time to someone else. Here is the query I came up with: SELECT DISTINCT ordinal_position, column_name, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_precision_radix, numeric_scale, is_nullable, column_default, CASE WHEN column_name IN (SELECT ccu.column_name FROM information_schema.constraint_column_usage ccu, information_schema.table_constraints tc WHERE ccu.constraint_name = tc.constraint_name AND tc.constraint_type = 'PRIMARY KEY' AND ccu.table_name = $2) THEN 'YES' ELSE 'NO' END AS is_pk FROM information_schema.columns col, information_schema.table_constraints tc WHERE tc.table_schema = col.table_schema AND tc.table_name = col.table_name AND col.table_schema = $1 AND col.table_name = $2 ORDER BY ordinal_position; Is there a better way to do that? I don't mind using Postgres-only solution. Thank you. BTW, is there a difference between query a real tables or query a view? > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com