Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
[Introspect Table Column DataTypes in PostgreSQL] View listing all tables joined with columns and data types for PostgreSQL
CREATE OR REPLACE VIEW table_columns AS
WITH table_oids AS (
SELECT c.relname, c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
pg_catalog.pg_table_is_visible(c.oid)),
column_types AS (
SELECT
toids.relname AS "tablename",
a.attname as "column",
pg_catalog.format_type(a.atttypid, a.atttypmod) as "datatype"
FROM
pg_catalog.pg_attribute a, table_oids toids
WHERE
a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = toids.oid)
SELECT * FROM column_types;
-- Example
SELECT * FROM table_columns WHERE tablename ~ 'dic_'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment