Create database, Alter database examples

PostreSQL tutorial – Create/Alter/Drop Database

Create postgresql database

Create database syntax, alter database examples, rename and drop.

Syntax

CREATE DATABASE db_name
[ [ WITH ] [ OWNER [=] username ]
	   [ TEMPLATE [=] template ]
	   [ ENCODING [=] encoding ]
	   [ LC_COLLATE [=] lc_collate ]
	   [ LC_CTYPE [=] lc_ctype ]
	   [ TABLESPACE [=] tablespace ]
	   [ CONNECTION LIMIT [=] connlimit ] ]

Example

CREATE DATABASE customers_db;

Create a database customers owned by user user_1 with a default tablespace of customers_space:

CREATE DATABASE customers_db 
OWNER user_1 TABLESPACE customers_space;

Alter postgresql database

Syntax

ALTER DATABASE old_db_name RENAME TO new_db_name;
ALTER DATABASE db_name OWNER TO new_owner;
ALTER DATABASE db_name SET TABLESPACE new_tablespace;

Example

ALTER DATABASE customers_db 
	RENAME TO customers_db2;

Drop postgresql database

Syntax

Drop DATABASE [ IF EXISTS ] db_name;

Example

DROP DATABASE customers_db;