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!

You'll have to write the SQL statement dynamically through script like a Stored Procedure, or some outside scripting like bash, python, vbscript, etc. — JNevill
– JNevill, Commented Dec 7, 2016 at 14:20

stas.yaranov · Accepted Answer · 2016-12-07 14:34:37Z

4

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;

edited Dec 7, 2016 at 14:34

answered Dec 7, 2016 at 14:28

stas.yaranov

1,79711 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RonPringadi Over a year ago

Also to note, the EXECUTE command is not a standard sql therefore will always need the DO $$ BEGIN .... language plpgsql;

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related