PostgreSQL 9.3 metadata tabulky a sloupce tabulek

Motivace

Někdy je potřeba zjistit definice tabulek i jinak než ze systemového výpisu, který né vždy musí být správny. Např:

Když definujete tabulku

CREATE TABLE sal_emp ( name            text, pay_by_quarter  integer[], schedule        text[][] );

 

Pak pgAdnmin III  vypíše :

CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[] ) WITH ( OIDS=FALSE );

Chybí další dimenze pro sloupec schedule.

pSQL vypisuje tohle :

pgHist=# \d sal_emp Tabulka "public.sal_emp" Sloupec         |    Typ    | Modifikßtory ----------------+-----------+-------------- name            | text      | pay_by_quarter  | integer[] | schedule        | text[]    |

Tedy opět špatně.

 

Seznam všech tabulek :

SELECT n.nspname AS schemaname, c.relname AS tablename FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace and c.relkind = 'r';

Seznam sloupců tabulky :

SELECT ns.nspname AS schemaname, cl.relname AS tablename, atr.attname as columnname, typ.typname::varchar as "data_type", atr.atttypmod as "length", atr.attnum as "position", CASE atttypid WHEN 21 /*int2*/ THEN 16 WHEN 23 /*int4*/ THEN 32 WHEN 20 /*int8*/ THEN 64 WHEN 1700 /*numeric*/ THEN CASE WHEN atttypmod = -1 THEN null ELSE ((atttypmod - 4) >> 16) & 65535     -- vypocet presnosti END WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/ WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/ ELSE null END   AS numeric_precision, CASE WHEN atttypid IN (21, 23, 20) THEN 0 WHEN atttypid IN (1700) THEN CASE WHEN atttypmod = -1 THEN null ELSE (atttypmod - 4) & 65535            -- calculate the scale END ELSE null END AS numeric_scale, atr.attndims as dimIfArray FROM pg_class     cl, pg_namespace ns, pg_attribute atr, pg_type      typ WHERE ns.oid       = cl.relnamespace and cl.relkind   = 'r' and atr.attrelid = cl.oid and ns.nspname    = 'public' and atr.attnum   > 0 and atr.atttypid = typ.oid;

Vyhledaní klíčů:

(Prozatím neověřeno)

(Zdroj: http://stackoverflow.com/qu­estions/109325/postgresql-describe-table)

SELECT f.attnum AS number, f.attname AS name, f.attnum, f.attnotnull AS notnull, pg_catalog.format_type(f.atttypid,f.atttypmod) AS type, CASE WHEN p.contype = 'p' THEN 't' ELSE 'f' END AS primarykey, CASE WHEN p.contype = 'u' THEN 't' ELSE 'f' END AS uniquekey, CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey, CASE WHEN p.contype = 'f' THEN p.confkey END AS foreignkey_fieldnum, CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey, CASE WHEN p.contype = 'f' THEN p.conkey END AS foreignkey_connnum, CASE WHEN f.atthasdef = 't' THEN d.adsrc END AS default FROM pg_attribute f JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey) LEFT JOIN pg_class AS g ON p.confrelid = g.oid WHERE c.relkind = 'r'::char AND n.nspname = '%s' -- Replace with Schema name AND c.relname = '%s' -- Replace with table name AND f.attnum > 0 ORDER BY number ;