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

Possible duplicate of PostgreSQL - Rename database

Gabriel M
– Gabriel M

2018-12-14 14:57:43 +00:00
Commented Dec 14, 2018 at 14:57 — Gabriel M
– Gabriel M, Commented Dec 14, 2018 at 14:57

Kaushik Nayak · Accepted Answer · 2018-12-14 14:42:54Z

4

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)

answered Dec 14, 2018 at 14:42

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dnl_ Over a year ago

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?

Kaushik Nayak Over a year ago

@DanielCampeão : Remove the double quotes, you don't need it

Dnl_ Over a year ago

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

Collectives™ on Stack Overflow

Use date function to rename a database in postgres

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related