In pgAdmin 3 (and pgAdmin 4 if you're prepared to wait longer) the DDL for the currently selected database object is shown in the main panel, along with corresponding triggers, constraints, etc, all nicely formatted. It looks quite different to the DDL exported from pgdump. Is there a way to get the nicer version, without manually copy-and-pasting from pgAdmin?

  • 1
    AFAIK, the answer is no, there's not such a tool. – joanolo Jul 4 '17 at 20:09
  • 1
    The functions like pg_get_functiondef and pg_get_viewdef don't do the same pretty-printing PgAdmin does, so no, I don't think so. – Craig Ringer Jul 5 '17 at 1:37
  • 1
    Maybe you could fo your pg_dump for all DDL and then pipe it to a formatting tool like github.com/darold/pgFormatter ? – KookieMonster Jul 6 '17 at 5:02
  • @KookieMonster Thanks for the pgFormatter link - very nice. But what I like about the pgAdmin approach is that it groups related DDL statements together as a conceptual unit. I've located the code that does these steps now (as an answer to my own question) so maybe I'll get around to scripting up as you suggest, next time I find some Copious Free Time... – beldaz Jul 6 '17 at 23:58
  • One idea could be pg_dump --schema-only mybase | pgformatter > mydumpfile and after pg_dump --data-only mybase >> mydumpfile – KookieMonster Jul 7 '17 at 8:58

Digging around in the pgAdmin source code it appears that the routines to generate the DDL are embedded within the application code. For instance, I the code to generate the table DDL can be found in pgTable::GetSql.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.