pg_get_tabledef

pg_get_tabledef is a PostgreSQL PL/PGSQL function that generates table DDL for the given schema/table.

(c) 2021-2024 SQLEXEC LLC
GNU V3 and MIT licenses are conveyed accordingly.
Bugs can be reported by creating an issue here.
Please provide PG version and example code along with issues reported if possible.

History

pg_get_tabledef was considered in the early days (PostgreSQL 8.2), but was ultimately cast aside due to supposed complexities involved when compared to pg_dump and different PG versions. So since that time, everybody has been writing their own take on what getting table DDL should look like. This is just one more attempt at it, which is one of the best as far as I can tell.

I must give credits to some of the folks that provided code snippets that I used when I started to work on this project. Since that time I have added a lot more areas that most closely mimics the output of pg_dump. Here is that original reference: https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr

This function is also used in another github repo for cloning schemas: https://github.com/denishpatel/pg-clone-schema

Limitations

• No ACL information is returned.
• No support for PG Versions 9.5 and older.

Overview

This function handles these types of objects:

• column defaults
• user-defined data types
• arrays
• SET and WITH clause storage parameters
• check constraints
• primary and foreign keys
• table and column comments
• indexes
• tablespaces for tables and indexes
• triggers (not trigger functions)
• Partitioned tables including their partitions (declarative and inheritance-based)
• Temporary and unlogged tables

There are multiple ways to call this function where the differences are mostly related to whether Foreign Keys and/or Triggers are included and what format. Here is a description of each parameter:

in_schema      Required: schema name

in_table       Required: table name

verbose        Required: boolean - default=false, useful for debugging when set to True

FKEY ENUM      Optional: Default=FKEYS_INTERNAL  Enumeration: 'FKEYS_INTERNAL', 'FKEYS_EXTERNAL', 'FKEYS_COMMENTED', 'FKEYS_NONE'

TRIG ENUM      Optional: Default=NO_TRIGGERS     Enumeration: 'INCLUDE_TRIGGERS', 'NO_TRIGGERS'

PKEY ENUM      Optional: Default=internal def    Enumeration: 'PKEY_EXTERNAL'

COMMENTS ENUM  Optional: Default=no comments     Enumeration: 'COMMENTS'

With respect to the Foreign Key enumerations:
INTERNAL - part of table create statement
EXTERNAL - ALTER TABLE ADD FOREIGN KEY statement

Examples

select * from public.pg_get_tabledef('myschema','mytable', false);

select * from public.pg_get_tabledef('myschema','mytable', false, 'PKEY_EXTERNAL');

select * from public.pg_get_tabledef('myschema','mytable', false, 'FKEYS_EXTERNAL');

select * from public.pg_get_tabledef('myschema','mytable', false, 'FKEYS_EXTERNAL', 'INCLUDE_TRIGGERS');

select * from public.pg_get_tabledef('myschema','mytable', false, 'PKEY_EXTERNAL', 'FKEYS_EXTERNAL', 'COMMENTS', 'INCLUDE_TRIGGERS');

psql clone_testing -c "select * from pg_get_tabledef('sample','emp',false,'COMMENTS','INCLUDE_TRIGGERS')"

Compare to pg_dump

pg_dump -t 'myschema.mytable' --schema-only mydb | grep -v '--' | grep -v -e '^[[:space:]]*$'

psql formatting

You can avoid column headers and plus signs at the end of each line by specifying the -At parameters:

psql mydatabase -At

psql mydatabase -At -c "select pg_get_tabledef('myschema','mytable', false, 'FKEYS_EXTERNAL')"

or within a psql sesssion: \pset format unaligned