postgres generate ddl - ghdrako/doc_snipets GitHub Wiki

Method

1. pgdump “-s” (schema only) and “-t” (tables) options you get the DDL for the complete table.

pg_dump -s -t t1 ddl | egrep -v "^--|^$"

2. \d+

# \d+ city_high  -- ddl view   
# \x on
# \df+ dup       -- ddl function

3. Build-in function
4. Using SQLs on metadata views https://alberton.info/postgresql_meta_info.html

Build-in function

There are many inbuilt functions to extract the DDL for various types of the objects. But, there is no method to get the DDL of a table.

select pg_get_viewdef('city_high'::regclass, true);    -- ddl view

SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'multipg'; -- ddl function

SELECT proname, pg_get_functiondef(f.oid)              
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public'
AND f.proname = 'multipg';

 SELECT pg_get_indexdef('city_pkey'::regclass);    -- ddl index

 SELECT pg_get_indexdef(indexrelid) AS index_query -- ddl all idex in table

SELECT conname, pg_get_constraintdef(r.oid, true) as definition  -- ddl constraint 
  FROM pg_constraint r
 WHERE r.conrelid = 't1'::regclass;

Using SQLs on metadata views

generate ddl table

# \pset linestyle old-ascii
 
# SELECT                                          
  'CREATE TABLE ' || relname || E'\n(\n' ||
  array_to_string(
    array_agg(
      '    ' || column_name || ' ' ||  type || ' '|| not_null
    )
    , E',\n'
  ) || E'\n);\n'
from
(
  SELECT 
    c.relname, a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
    case 
      when a.attnotnull
    then 'NOT NULL'
    else 'NULL'
    END as not_null 
  FROM pg_class c,
   pg_attribute a,
   pg_type t
   WHERE c.relname = 'city'
   AND a.attnum > 0
   AND a.attrelid = c.oid
   AND a.atttypid = t.oid
 ORDER BY a.attnum
) as tabledefinition
group by relname
;