Completely copying a postgres table with SQL

The closest "miracle command" is something like

pg_dump -t tablename | sed -r 's/\btablename\b/tablename_copy/' | psql -f -

In particular, this takes care of creating the indexes after loading the table data.

But that doesn't reset the sequences; you will have to script that yourself.

The create table as feature in PostgreSQL may now be the answer the OP was looking for.

https://www.postgresql.org/docs/9.5/static/sql-createtableas.html

create table my_table_copy as
  select * from my_table

This will create an identical table with the data.

Adding with no data will copy the schema without the data.

create table my_table_copy as
  select * from my_table
with no data

This will create the table with all the data, but without indexes and triggers etc.

---

create table my_table_copy (like my_table including all)

The create table like syntax will include all triggers, indexes, constraints, etc. But not include data.

To copy a table completely, including both table structure and data, you use the following statement:

CREATE TABLE new_table AS 
TABLE existing_table;

To copy a table structure without data, you add the WITH NO DATA clause to the CREATE TABLE statement as follows:

CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

To copy a table with partial data from an existing table, you use the following statement:

CREATE TABLE new_table AS 
SELECT
*
FROM
    existing_table
WHERE
    condition;

Well, you're gonna have to do some of this stuff by hand, unfortunately. But it can all be done from something like psql. The first command is simple enough:

select * into newtable from oldtable

This will create newtable with oldtable's data but not indexes. Then you've got to create the indexes and sequences etc on your own. You can get a list of all the indexes on a table with the command:

select indexdef from pg_indexes where tablename='oldtable';

Then run psql -E to access your db and use \d to look at the old table. You can then mangle these two queries to get the info on the sequences:

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 ~ '^(oldtable)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (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),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '74359' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

Replace that 74359 above with the oid you get from the previous query.