How to create table with datetime in its name using postgres sql

Question

Is there a way to create a table with date time on it's name purely using postgres sql.

Consider the typical create table:

CREATE TABLE someNewTable AS SELECT .... FROM someSourceTable WHERE date >= somedate AND date <somedate;

What I need is to append the time stamp in a table name. The time stamp is dynamically created. I know this is not a common way but needed for archiving purpose. So is there a way to do this in pure postgres sql?

CREATE TABLE someNewTableYYYYMMDDHHMMSS AS SELECT .... FROM someSourceTable WHERE date >= somedate AND date <somedate;

Thanks in advance!

Answer 1

Looks like you want to execute a dynamic query: https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Then you need something like this: execute 'CREATE TABLE someNewTable' || to_char(current_timestamp, 'YYYYMMDDHHMMSS') || ' AS SELECT .... FROM someSourceTable WHERE date >= somedate AND date < somedate;'

Or if you want to run it in psql then you need to use also DO operator: DO $$ begin execute 'CREATE TABLE someNewTable' || to_char(current_timestamp, 'YYYYMMDDHHMMSS') || ' AS SELECT .... FROM someSourceTable WHERE date >= somedate AND date < somedate;' end $$ language plpgsql;