Create schema with current date in name

2
votes

I'm trying to create a new schema with todays date in the name. Named like this

select concat('Copy_', replace(now()::date::text,'-',''))
Copy_20181102

So I try:

CREATE SCHEMA concat('Copy_', replace(now()::date::text,'-',''))
    AUTHORIZATION postgres;

ERROR: syntax error at or near "(" LINE 1: CREATE SCHEMA concat('Copy_', replace(now()::date::text,'-',... ^ SQL state: 42601 Character: 21

How can I fix this?

2
sqlpostgresql
replace(now()::date::text,'-','') is better written as to_char(now(), 'yyyymmdd') or to_char(current_date, 'yyyymmdd') - a_horse_with_no_name

2 Answers

3
votes

You cannot do that with pure SQL. You need to use procedural language instead:

--Anonymous block
DO $$
BEGIN

    EXECUTE format('CREATE SCHEMA %I AUTHORIZATION postgres',
            concat('Copy_', replace(now()::date::text,'-','')));

END $$;

More info about dynamic commands here.

2
votes

You can use the \gexec functionality of psql, which will execute the output of the given query. Let's note that the query can return multiple rows, leading to multiple sql instructions.

SELECT format('CREATE SCHEMA %I AUTHORIZATION postgres',
       concat('Copy_', replace(now()::date::text,'-','')));\gexec