Properties

Name: columns 
Owner: postgres 
Has rules:  
Has Triggers:  
Comment:  

Columns

Name Type Length
  table_catalog  sql_identifier  -1 
  table_schema  sql_identifier  -1 
  table_name  sql_identifier  -1 
  column_name  sql_identifier  -1 
  ordinal_position  cardinal_number  -1 
  column_default  character_data  -1 
  is_nullable  character_data  -1 
  data_type  character_data  -1 
  character_maximum_length  cardinal_number  -1 
  character_octet_length  cardinal_number  -1 
  numeric_precision  cardinal_number  -1 
  numeric_precision_radix  cardinal_number  -1 
  numeric_scale  cardinal_number  -1 
  datetime_precision  cardinal_number  -1 
  interval_type  character_data  -1 
  interval_precision  character_data  -1 
  character_set_catalog  sql_identifier  -1 
  character_set_schema  sql_identifier  -1 
  character_set_name  sql_identifier  -1 
  collation_catalog  sql_identifier  -1 
  collation_schema  sql_identifier  -1 
  collation_name  sql_identifier  -1 
  domain_catalog  sql_identifier  -1 
  domain_schema  sql_identifier  -1 
  domain_name  sql_identifier  -1 
  udt_catalog  sql_identifier  -1 
  udt_schema  sql_identifier  -1 
  udt_name  sql_identifier  -1 
  scope_catalog  sql_identifier  -1 
  scope_schema  sql_identifier  -1 
  scope_name  sql_identifier  -1 
  maximum_cardinality  cardinal_number  -1 
  dtd_identifier  sql_identifier  -1 
  is_self_referencing  character_data  -1 
  is_identity  character_data  -1 
  identity_generation  character_data  -1 
  identity_start  character_data  -1 
  identity_increment  character_data  -1 
  identity_maximum  character_data  -1 
  identity_minimum  character_data  -1 
  identity_cycle  character_data  -1 
  is_generated  character_data  -1 
  generation_expression  character_data  -1 
  is_updatable  character_data  -1 
Total: 44 column(s)

SQL


CREATE VIEW columns AS
    SELECT (current_database())::sql_identifier AS table_catalog, (nc.nspname)::sql_identifier AS table_schema, (c.relname)::sql_identifier AS table_name, (a.attname)::sql_identifier AS column_name, (a.attnum)::cardinal_number AS ordinal_position, (pg_get_expr(ad.adbin, ad.adrelid))::character_data AS column_default, (CASE WHEN (a.attnotnull OR ((t.typtype = 'd'::"char") AND t.typnotnull)) THEN 'NO'::text ELSE 'YES'::text END)::character_data 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)::character_data AS data_type, (_pg_char_max_length(_pg_truetypid(a.*, t.*), _pg_truetypmod(a.*, t.*)))::cardinal_number AS character_maximum_length, (_pg_char_octet_length(_pg_truetypid(a.*, t.*), _pg_truetypmod(a.*, t.*)))::cardinal_number AS character_octet_length, (_pg_numeric_precision(_pg_truetypid(a.*, t.*), _pg_truetypmod(a.*, t.*)))::cardinal_number AS numeric_precision, (_pg_numeric_precision_radix(_pg_truetypid(a.*, t.*), _pg_truetypmod(a.*, t.*)))::cardinal_number AS numeric_precision_radix, (_pg_numeric_scale(_pg_truetypid(a.*, t.*), _pg_truetypmod(a.*, t.*)))::cardinal_number AS numeric_scale, (_pg_datetime_precision(_pg_truetypid(a.*, t.*), _pg_truetypmod(a.*, t.*)))::cardinal_number AS datetime_precision, (NULL::character varying)::character_data AS interval_type, (NULL::character varying)::character_data AS interval_precision, (NULL::character varying)::sql_identifier AS character_set_catalog, (NULL::character varying)::sql_identifier AS character_set_schema, (NULL::character varying)::sql_identifier AS character_set_name, (NULL::character varying)::sql_identifier AS collation_catalog, (NULL::character varying)::sql_identifier AS collation_schema, (NULL::character varying)::sql_identifier AS collation_name, (CASE WHEN (t.typtype = 'd'::"char") THEN current_database() ELSE NULL::name END)::sql_identifier AS domain_catalog, (CASE WHEN (t.typtype = 'd'::"char") THEN nt.nspname ELSE NULL::name END)::sql_identifier AS domain_schema, (CASE WHEN (t.typtype = 'd'::"char") THEN t.typname ELSE NULL::name END)::sql_identifier AS domain_name, (current_database())::sql_identifier AS udt_catalog, (COALESCE(nbt.nspname, nt.nspname))::sql_identifier AS udt_schema, (COALESCE(bt.typname, t.typname))::sql_identifier AS udt_name, (NULL::character varying)::sql_identifier AS scope_catalog, (NULL::character varying)::sql_identifier AS scope_schema, (NULL::character varying)::sql_identifier AS scope_name, (NULL::integer)::cardinal_number AS maximum_cardinality, (a.attnum)::sql_identifier AS dtd_identifier, ('NO'::character varying)::character_data AS is_self_referencing, ('NO'::character varying)::character_data AS is_identity, (NULL::character varying)::character_data AS identity_generation, (NULL::character varying)::character_data AS identity_start, (NULL::character varying)::character_data AS identity_increment, (NULL::character varying)::character_data AS identity_maximum, (NULL::character varying)::character_data AS identity_minimum, (NULL::character varying)::character_data AS identity_cycle, ('NEVER'::character varying)::character_data AS is_generated, (NULL::character varying)::character_data AS generation_expression, (CASE WHEN (c.relkind = 'r'::"char") THEN 'YES'::text ELSE 'NO'::text END)::character_data AS is_updatable FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON (((a.attrelid = ad.adrelid) AND (a.attnum = ad.adnum)))), pg_class c, pg_namespace nc, ((pg_type t JOIN pg_namespace nt ON ((t.typnamespace = nt.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)))) WHERE ((((((((a.attrelid = c.oid) AND (a.atttypid = t.oid)) AND (nc.oid = c.relnamespace)) AND (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"]))) AND ((((pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text)) OR has_table_privilege(c.oid, 'INSERT'::text)) OR has_table_privilege(c.oid, 'UPDATE'::text)) OR has_table_privilege(c.oid, 'REFERENCES'::text)));


ALTER TABLE information_schema.columns OWNER TO postgres;

See Also

List of views