Search code examples
sqlpostgresqlbackupdynamic-sql

how to include current time in table name - postgresql

Is there a way I can change this sql statement so that the resulting table has a timestamp baked in as a part of the table name: 

CREATE TABLE public.mytesttable (LIKE  public.tabletobackup INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);

so instead of just

 mytesttable

i want something like 

 mytesttable_20170131151515

I want to back up a table within the same database... and I've been asked to include the current date/time in the table name.

Any tips would be appreciated. Thanks.

Solution

  • use dynamic sql like here:

    vao=# do $$ begin execute format('create table "date_%s" (i int)',now()::date); end; $$;
DO
vao=# \dt+ "date_2017-01-31"
                        List of relations
 Schema |      Name       | Type  | Owner |  Size   | Description
--------+-----------------+-------+-------+---------+-------------
 public | date_2017-01-31 | table | vao   | 0 bytes |
(1 row)