1

I would like to know how to rename a database with the current date

thanks for your help

Dnl_
  • 55
  • 9
  • 1
    Possible duplicate of [PostgreSQL - Rename database](https://stackoverflow.com/questions/143756/postgresql-rename-database) – Gabriel M Dec 14 '18 at 14:57

1 Answers1

3

You may use dynamic SQL in aDO block. Here I use a date suffix in YYYYMMDD format for the database name.

knayak=# CREATE DATABASE mydatabase;
CREATE DATABASE

DO $$
BEGIN

 EXECUTE format('ALTER DATABASE %I RENAME TO %I_%s', 'mydatabase','mydatabase',
                               to_char(current_date,'YYYYMMDD')::TEXT);
END
$$;

knayak=#
knayak=# \l mydatabase*
                                    List of databases
        Name         | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
---------------------+--------+----------+-------------+-------------+-------------------
 mydatabase_20181214 | knayak | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • Thanks for your help, I got this Error message: ERROR: syntax error at or near "_20181214" LINE 1: ALTER DATABASE "mydb" RENAME TO "mydb"_20181214 ^ QUERY: ALTER DATABASE "mydb" RENAME TO "mydb"_20181214 CONTEXT: PL/pgSQL function inline_code_block line 4 at EXECUTE SQL state: 42601 Am I missing something? – Dnl_ Dec 14 '18 at 15:22
  • @DanielCampeão : Remove the double quotes, you don't need it – Kaushik Nayak Dec 14 '18 at 15:40
  • First I created a database and named it "mydb" This is how it looks my Query [DO $$ BEGIN EXECUTE format('ALTER DATABASE %I RENAME TO %I_%s', 'mydb','mydb', to_char(current_date,'YYYYMMDD')::TEXT); END $$;] Thanks for your help – Dnl_ Dec 14 '18 at 15:48