Skip to content

Database

Create Database Syntax

CREATE DATABASE database_name
WITH
   [OWNER =  role_name]
   [TEMPLATE = template]
   [ENCODING = encoding]
   [LC_COLLATE = collate]
   [LC_CTYPE = ctype]
   [TABLESPACE = tablespace_name]
   [ALLOW_CONNECTIONS = true | false]
   [CONNECTION LIMIT = max_concurrent_connection]
   [IS_TEMPLATE = true | false ]

Creating new database with default parameter

CREATE database mydatabase;

                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 mydatabase | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 mydb       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Creating database with some parameter

create database mydatbase1 with owner = prabin;

                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 mydatabase | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 mydatbase1 | prabin   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 mydb       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

List all the database

\l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

List all database with more information

\l+

                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 15 MB   | pg_default |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8577 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8417 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8569 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

Show current database

select current_database();

 current_database
------------------
 postgres
(1 row)

Switch to other database

postgres=# \c mydb;
You are now connected to database "mydb" as user "postgres".

mydb=# \connect postgres
You are now connected to database "postgres" as user "postgres".

Rename the database

Alter database mydatabase Rename to database;

                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 database   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 mydatbase1 | prabin   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 mydb       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Change the owner of the database

alter database mydb owner to postgres;

Drop Database

drop database mydatbase1;

Get the size of database

select pg_size_pretty(pg_database_size('mydb'));

 pg_size_pretty
----------------
 15 MB
(1 row)