CODEWITHSUNDEEP
postgresql
geomiles
geomiles

Reputation: 305

Postgres - create table but name is current month

I want to be able to create a new table which contains the current month.

Basically I've going to create a python script that each month will automatically load a lots of crime data, this python script will create a new table called crime_data (this is the easy bit). I then want to run a second script that will create a new table with a name which is date-stamped with the system-date month and filter out elements of the original crime_data.

Is this possible?

Upvotes: 0

Views: 1643

Answers (1)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28571

It is possible. Try creating PL/pgSQL function like that:

BEGIN

EXECUTE 'CREATE TABLE crime_data_'||to_char(CURRENT_DATE, 'Month')||' AS
SELECT *
FROM crime_data cd
WHERE date_part(''month'', cd.date) = date_part(''month'', CURRENT_DATE)';

END;

You need to adapt it for your needs, but idea is to form the query as a string and EXECUTE it.

Details here: CREATE TABLE AS to_char() date_part

UPD It may be better to create a VIEW with the data you need instead of a table:

EXECUTE 'CREATE VIEW crime_data_'||to_char(CURRENT_DATE, 'Month')||' AS
SELECT *
FROM crime_data cd
WHERE date_part(''month'', cd.date) = date_part(''month'', CURRENT_DATE)';

You will be able to SELECT from this VIEW as it was table and you wont need to keep duplicated data. Details here: CREATE VIEW.

UPD2 To test this thing:

1) do: 

SELECT 'CREATE TABLE crime_data_'||to_char(CURRENT_DATE, 'Month')||' AS
        SELECT *
        FROM crime_data cd
        WHERE date_part(''month'', cd.date) = date_part(''month'', CURRENT_DATE)'

2) Take the result of this SELECT and run it as a regular statement.

PL/pgSQL EXECUTE statement does exactly this two things. 1 - forms the string. 2 - executes it as statement.

Upvotes: 1

Related Questions