CODEWITHSUNDEEP
postgresql
Bart Friederichs
Bart Friederichs

Reputation: 33573

Getting CREATE TABLE definition in query

I am writing my own script to save a PostgreSQL database schema (including views, functions, etc) for version control. For example, I found this way to get the function names and definitions from the database:

SELECT P.proname, pg_get_functiondef(P.oid) 
FROM pg_proc P 
LEFT JOIN pg_language L ON P.prolang=L.oid 
WHERE P.pronamespace='public' AND (L.lanname='sql' OR L.lanname='plpgsql') 
ORDER BY proname, proargnames, proargtypes

However, trying to get table definitions as CREATE TABLE statements seems not so straightforward. I have searched for it, and all I could find was to use pg_dump, which isn't what I want because:

  1. It means I have to exec something from my (Python) script.
  2. It adds all kind of extra stuff I don't want.

Is there another way of just getting the CREATE TABLE statement? (Basically like DESCRIBE <table> in MySQL?)

Upvotes: 0

Views: 446

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325141

There are a bunch of functions to extract definitions of many things in Pg, but tables are surprisingly not one of them.

craig=> select distinct proname from pg_proc where proname like 'pg_get_%';
              proname               
------------------------------------
 pg_get_constraintdef
 pg_get_expr
 pg_get_function_arguments
 pg_get_function_identity_arguments
 pg_get_function_result
 pg_get_functiondef
 pg_get_indexdef
 pg_get_keywords
 pg_get_multixact_members
 pg_get_ruledef
 pg_get_serial_sequence
 pg_get_triggerdef
 pg_get_userbyid
 pg_get_viewdef
(14 rows)

pg_get_viewdef is particularly useful.

Your only real option is to use pg_dump --table ... and trim the extraneous stuff. This is crude and clumsy, but all you've got unless you want to write the code to build the DDL yourself.

I've long wanted to be able to use bits of pg_dump from client code as a library - or even better, dump objects from within Pg its self - but at present it's not available.

Upvotes: 1

Related Questions