Complete Guide to Generating CREATE TABLE Statements for Existing Tables in PostgreSQL

Introduction

In database development and maintenance, there is frequent need to view or recreate SQL definition statements for existing tables. PostgreSQL offers multiple approaches to fulfill this requirement, and this article systematically explains their core principles and practical applications.

Using pg_dump Tool for Table Structure Export

The pg_dump utility, PostgreSQL's official backup tool, can export only table structures without data using the --schema-only option. The basic syntax is:

pg_dump -t 'schema_name.table_name' --schema-only database_name

This command outputs complete CREATE TABLE statements, including all column definitions, constraints, indexes, and other metadata. For example, to export the users table in the public schema:

pg_dump -t 'public.users' --schema-only mydatabase

This method is suitable for production environments as it doesn't depend on database connection state and ensures standardized output format.

psql Meta-command Approach

For PostgreSQL versions below 12, psql meta-commands can be used to inspect table structure. First connect to the database with the -E option:

psql -E -U username -d database

Then execute within psql:

\dt public.*
\d+ public.tablename

Note that this approach is no longer applicable in PostgreSQL v12+, and updated alternatives are recommended.

Custom PL/pgSQL Function

For scenarios requiring programmatic generation of CREATE statements, a custom function can be created:

CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
  RETURNS text AS
$BODY$
DECLARE
    v_table_ddl   text;
    column_record record;
BEGIN
    FOR column_record IN 
        SELECT 
            b.nspname as schema_name,
            b.relname as table_name,
            a.attname as column_name,
            pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
            CASE WHEN 
                (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                 FROM pg_catalog.pg_attrdef d
                 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
                'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                              FROM pg_catalog.pg_attrdef d
                              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
            ELSE
                ''
            END as column_default_value,
            CASE WHEN a.attnotnull = true THEN 
                'NOT NULL'
            ELSE
                'NULL'
            END as column_not_null,
            a.attnum as attnum,
            e.max_attnum as max_attnum
        FROM 
            pg_catalog.pg_attribute a
            INNER JOIN 
             (SELECT c.oid,
                n.nspname,
                c.relname
              FROM pg_catalog.pg_class c
                   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relname ~ ('^('||p_table_name||')$')
                AND pg_catalog.pg_table_is_visible(c.oid)
              ORDER BY 2, 3) b
            ON a.attrelid = b.oid
            INNER JOIN 
             (SELECT 
                  a.attrelid,
                  max(a.attnum) as max_attnum
              FROM pg_catalog.pg_attribute a
              WHERE a.attnum > 0 
                AND NOT a.attisdropped
              GROUP BY a.attrelid) e
            ON a.attrelid=e.attrelid
        WHERE a.attnum > 0 
          AND NOT a.attisdropped
        ORDER BY a.attnum
    LOOP
        IF column_record.attnum = 1 THEN
            v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
        ELSE
            v_table_ddl:=v_table_ddl||',';
        END IF;

        IF column_record.attnum <= column_record.max_attnum THEN
            v_table_ddl:=v_table_ddl||chr(10)||
                     '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
        END IF;
    END LOOP;

    v_table_ddl:=v_table_ddl||');';
    RETURN v_table_ddl;
END;
$BODY$
  LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;

Query using the function:

SELECT generate_create_table_statement('tablename');

Drop the function after use if not needed permanently:

DROP FUNCTION generate_create_table_statement(p_table_name varchar);

GUI Tool Method

Besides command-line tools, graphical interfaces like TablePlus can be used. The procedure involves selecting the target table, switching to the structure tab, and clicking the definition button to view the complete CREATE TABLE statement. This approach is suitable for users unfamiliar with command-line operations.

Method Comparison and Selection Recommendations

Comparing various methods: pg_dump is the most reliable and standardized, suitable for automated scripts and production environments; custom functions offer maximum flexibility but require additional maintenance; GUI tools provide user-friendly interfaces. Appropriate methods should be selected based on specific scenarios.

Conclusion

Mastering PostgreSQL table structure export techniques is crucial for database development and maintenance. The methods introduced in this article cover complete solutions from command-line to graphical interfaces, enabling readers to choose the most suitable tools and approaches according to their needs.