Use date function to rename a database in postgres

Question

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

thanks for your help

Answer 1

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)