How to generate the "create table" sql statement for an existing table in postgreSQL

pg_dump -t 'schema-name.table-name' --schema-only database-name

More info - in the manual.

My solution is to log in to the postgres db using psql with the -E option as follows:

psql -E -U username -d database   

In psql, run the following commands to see the sql that postgres uses to generate
the describe table statement:

-- List all tables in the schema (my example schema name is public)
\dt public.*
-- Choose a table name from above
-- For create table of one public.tablename
\d+ public.tablename  

Based on the sql echoed out after running these describe commands, I was able to put together
the following plpgsql function:

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;

Here is the function usage:

SELECT generate_create_table_statement('tablename');

And here is the drop statement if you don't want this function to persist permanently:

DROP FUNCTION generate_create_table_statement(p_table_name varchar);

Generate the create table statement for a table in postgresql from linux commandline:

Create a table for a demo:

CREATE TABLE your_table(
    thekey   integer NOT NULL,  ticker   character varying(10) NOT NULL,
    date_val date,              open_val numeric(10,4) NOT NULL
); 

pg_dump can output the table-create psql:

pg_dump -U your_user your_database -t your_table --schema-only

Which prints:

-- pre-requisite database and table configuration omitted
CREATE TABLE your_table (                                                                                                      
    thekey integer NOT NULL,                                                                                                   
    ticker character varying(10) NOT NULL,                                                                                     
    date_val date,                                                                                                             
    open_val numeric(10,4) NOT NULL                                                                                            
);                                                                                                                   
-- post-requisite database and table configuration omitted
  

Explanation:

pg_dump helps us get information about the database itself. -U stands for username. My pgadmin user has no password set, so I don't have to put in a password. The -t option means specify for one table. --schema-only means print only data about the table, and not the data in the table. Here is the exact command I use:

Get the table name and datatype information from postgresql with SQL:

CREATE TABLE your_table(  thekey integer NOT NULL,
                          ticker character varying(10) NOT NULL,
                          date_val date,
                          open_val numeric(10,4) NOT NULL
); 

SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'your_table'; 

Which prints:

┌────────────┬─────────────┬───────────────────┐ 
│ table_name │ column_name │     data_type     │ 
├────────────┼─────────────┼───────────────────┤ 
│ your_table │ thekey      │ integer           │ 
│ your_table │ ticker      │ character varying │ 
│ your_table │ date_val    │ date              │ 
│ your_table │ open_val    │ numeric           │ 
└────────────┴─────────────┴───────────────────┘