How to PostgreSQL- Create & Delete Databases
In this tutorial, we will learn how to create & delete databases in PostgreSQL with various options. You can also create using pgAdmin UI.
Create PostgreSQL Database using SQL Shell
To create a new PostgreSQL database, use CREATE DATABASE statement as shown below. To create a database, you must be a superuser or have the special CREATEDB privilege.
CREATE DATABASE db_name;
To create our database, open SQL shell (psql) from the start menu. You can check the guide to install PostgreSQL on Windows. Select default Server, database, Port, Username and enter superuser password that created while installation of PostgreSQL.
Following is a simple example, which will create “lauyou” in your PostgreSQL schema.
CREATE DATABASE lauyou;
The CREATE DATABASE statement provides you with various options when creating a new database.
CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace ] [ CONNECTION LIMIT [=] connlimit ] ]
|user_name||The role name of the user who will own the new database, or DEFAULT to use the default. To create a database owned by another role, you must be a direct or indirect member of that role, or be a superuser.|
|template||The name of the template from which to create the new database, or DEFAULT to use the default template (template1).|
|encoding||Character set encoding to use in the new database.|
|lc_collate||Collation order (LC_COLLATE) to use in the new database. This affects the sort order applied to strings,|
|lc_ctype||Character classification (LC_CTYPE) to use in the new database. This affects the categorization of characters.|
|tablespace||The name of the tablespace that will be associated with the new database, or DEFAULT to use the template database's tablespace.|
|connlimit||How many concurrent connections can be made to this database. -1 (the default) means no limit.|
Select PostgreSQL Database
You can check available database list using \l (slash el).
To select our database, use the following syntax.
postgres-# \c db_name;
postgres-# \c lauyou;
The drops a database syntax will remove the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner.
DROP DATABASE [ IF EXISTS ] db_name
IF EXISTS: Do not throw an error if the database does not exist. A notice is issued in this case.
DROP DATABASE lauyou;
DROP DATABASE cannot be executed inside a transaction block. This command cannot be executed while connected to the target database.