current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

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!

share|improve this question
    
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 23 hours ago
up vote 1 down vote accepted

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;

share|improve this answer
    
Thank you @stas.yaranov ! – RonPringadi 22 hours ago
    
Also to note, the EXECUTE command is not a standard sql therefore will always need the DO $$ BEGIN .... language plpgsql; – RonPringadi 22 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.