I know that I could do CREATE TABLE tbl_2 AS (select * from tbl_1)
But is there a better/faster/stronger way to do this? I am talking about performance more than anything else. The tables are all denormalised and I do not have any foreign key constraints to worry about.
EDIT
May be there isn't any better way? Ref: https://dba.stackexchange.com/questions/55661/how-to-duplicate-huge-postgres-table
A better way really depends on what exactly you're hoping to accomplish.
If you want to keep all the constraints and indexes from the original table you can use the LIKE clause in your CREATE TABLE statement like so:
CREATE TABLE tbl_2 (LIKE tbl_1 INCLUDING INDEXES INCLUDING CONSTRAINTS);
But that just creates an empty table. You would still have to copy in the data.
Alternatively you can use something like the following:
$ pg_dump -t tbl_1 | sed -e 's/^SET search_path = .*$/SET search_path = tmpschema, pg_catalog;' > table.sql
$ psql -d test -c 'CREATE SCHEMA tmpschema'
$ psql -1 -d test -f table.sql
$ psql -d test -c 'ALTER TABLE tmpschema.tbl_1 RENAME TO tbl_2; ALTER TABLE tmpschema.tbl_2 SET SCHEMA public; DROP SCHEMA tmpschema'
Perhaps it is not faster than CREATE TABLE ... AS (SELECT ...), but it will copy all indexes and constraints as well.
CREATE TABLE ... LIKE ... and then INSERT INTO ... (SELECT ...), but that would certainly be slow, because the indexes and constraints are already defined. pg_dump dumps the indexes and constraints after the table data.
create table as ... select ...is as fast as it gets. Btw: the parentheses around the select are totally useless.