CODEWITHSUNDEEP
sqlpostgresqlsqldatatypes
Phill Pafford
Phill Pafford

Reputation: 85298

Can I select a field from data type?

Can I select a field/column from data type?

Postgres 7.4 (yep we are upgrading)

SELECT *
FROM tbl_name
WHERE tbl_name.column = 'timestamp with time zone'

Upvotes: 2

Views: 565

Answers (2)

leonbloy
leonbloy

Reputation: 75896

I had to do that recently. To ease things I defined two views :

CREATE  VIEW view_table_columns AS 
 SELECT n.nspname AS schema, cl.relname AS table_name, 
  a.attname AS column_name, ty.typname AS data_type, 
  a.attnotnull AS nnull, 
  a.atthasdef AS hasdef, 
  descr.description AS descr, cl.oid AS tableoid, a.attnum AS colnum
   FROM pg_attribute a
   JOIN pg_class cl ON a.attrelid = cl.oid AND cl.relkind = 'r'::"char"
   JOIN pg_namespace n ON n.oid = cl.relnamespace
   JOIN pg_type ty ON ty.oid = a.atttypid
   LEFT JOIN pg_description descr 
       ON descr.objoid = cl.oid AND descr.objsubid = a.attnum
   WHERE a.attnum > 0 AND NOT a.attisdropped 
       AND n.nspname !~~ 'pg_%'::text 
       AND n.nspname <> 'information_schema'::name;

COMMENT ON VIEW view_table_columns IS 'Lista all fields of all tables';


CREATE VIEW view_table_columns2 AS 
 SELECT view_table_columns.*, 
 ( SELECT count(*) AS count
      FROM pg_index
      WHERE pg_index.indrelid = pg_index.tableoid AND 
  (view_table_columns.colnum = ANY   (pg_index.indkey::smallint[]))) AS indexes
   FROM view_table_columns;

COMMENT ON VIEW view_table_columns2 IS 'Adds to view_table_columns info about indexed fields';

That includes, for every field in your tables the following info:

  • schema name
  • table name
  • column name
  • data_type name
  • is nullable?
  • has default value?
  • description (comment)
  • tableoid (handy if you need to get more data from catalog)
  • column number (idem)
  • indexes (in second view - number of indexes that refer to this column)

Upvotes: 1

user554546
user554546

Reputation:

That requires metadata.

select column_name
from information_schema.columns
where table_schema='your_schema'
and table_name='tbl_name'
and data_type='timestamp without time zone'
order by ordinal_position;

ETA: If you want the actual data from the table with column names matching the list above, you could probably set up a user-defined function that would grab those column names, put them in a comma-delimited list, and parse the query from tbl_name appropriately (of course, this is a lot easier if you're working in a scripting language slightly outside of the database).

Upvotes: 3

Related Questions