CODEWITHSUNDEEP
pythonpostgresql
nish
nish

Reputation: 7280

How to get the existing postgres databases in a list

I am trying to write a python script to automate the task of creating database using the most recent production dump. I am using psychopg2 for the purpose.

But after the creation of the new database I want to delete the previously used database. My idea is that if I could get the names of databases in a list and sort them, I can easily delete the unwanted database.

So, my question is : How can I get the names of the DBs in a list.

Thanks

Upvotes: 1

Views: 2597

Answers (2)

Parthi P
Parthi P

Reputation: 65

psql -E -U postgres -c "\l"

The output of the above command is like 

********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                              List of databases
     Name     |  Owner   | Encoding | Collate | Ctype |   Access privileges   
--------------+----------+----------+---------+-------+-----------------------
 mickey       | postgres | UTF8     | C       | C     | 
 mickeylite   | postgres | UTF8     | C       | C     | 
 postgres     | postgres | UTF8     | C       | C     | 
 template0    | postgres | UTF8     | C       | C     | =c/postgres          +
              |          |          |         |       | postgres=CTc/postgres
 template1    | postgres | UTF8     | C       | C     | =c/postgres          +
              |          |          |         |       | postgres=CTc/postgres
(5 rows)

Upvotes: 1

DrColossos
DrColossos

Reputation: 12998

You can list all of your DBs with

SELECT d.datname as "Name",
FROM pg_catalog.pg_database d
ORDER BY 1;

You can filter or order it whatever way you like.

Upvotes: 1

Related Questions