How to create database in PostgreSQL

In this PostgreSQL tutorial, we will discuss that, How to create a database in Postgresql in different ways and will cover the below topic:

• How to create database in postgresql
• How to create database in postgresql in linux
• How to create database in postgresql in windows
• How to create database in postgresql in mac
• How to create database in postgresql using psql
• How to create database in postgresql using CREATE DATABASE
• How to create database in postgresql using createdb
• How to create database in postgresql using pgadmin 4
• Postgresql create database
• Postgresql create database command line
• Postgresql create database and user
• Postgresql create database if not exists
• Postgresql create database command line ubuntu
• Postgresql create database with owner
• Postgresql create database not working
• Postgresql create database link
• Postgresql create database and table
• Postgresql create database and schema
• Postgresql create database and set owner
• Postgresql create database and role

How to create database in postgresql

There are mainly two ways to create a database in Postgresql:

• By using psql, which is a terminal-based interaction to PostgreSQL, that provide you a way to type queries, and interactively show you the query results.
• And by using pgadmin, which is a web-based, Open Source management tool for Postgres. And pgAdmin 4 provides a graphical interface that simplify the creation, maintenance and use of database objects.

How to create database in postgresql using psql

Below shown are the ways to create a database in Postgresql using psql in 3 major Operating Systems:

How to create database in postgresql in linux

First, locate the location of the psql on the operating system:

Open the terminal and try the following command:

[root@localhost data]# which psql

It will give you the path. And you don’t need to browse for it as the path is already known by the OS.

However, if it shows errors, you can locate the psql by using the find command to search the file by writing the following command on the terminal:   

[root@localhost /]# find / -name psql

Now, you have to browse the path to open the psql utility. Copy the path say, /usr/edb/as11/bin/psql

[root@localhost /]# cd /usr/edb/as11/bin

Connecting psql  

Now since you have located psql. Let’s understand the basic parameters required to connect to the database and launch psql:

Server [localhost] -h: It specifies the address for the server. You can use an IP address or the hostname of the machine on which the database server is running. By default it is localhost

Database [postgres] -d: It specifies the name of the database with which you want to connect. By default, it will be the name of the user.

Port [5432] -p: It specifies the port on which you have configured your instance while installing or initializing. By default, the port is 5432.

Username [postgres] -U: It specifies the username created while installing the PostgreSQL’s psql takes place. By default the username is Postgres.

The command shown below will start the psql in localhost initializing the Postgres database with the default parameters:

[root@localhost /]# bash-4.2$ psql -d postgres -U postgres 

If a connection has to be made to a remote server the syntax for that command will be as shown below:

[root@localhost /]# bash-4.2$ psql -h <hostname or ip address> -p <port number of remote machine> -d <database name to connect> -U <username of the remote database server>

Read PostgreSQL ADD COLUMN + 17 Examples

How to create database in postgresql in windows

First, locate the location of the psql on the operating system:

On Windows, psql will be in the Program Files, and you should be able to launch it in a command prompt simply by clicking it.

Read PostgreSQL vs SQL Server

How to create database in postgresql in mac

On a Mac psql can be locate under the Applications > PostgreSQL (version number) > SQL Shell (psql). And you should be able to launch it in a terminal simply by clicking it.

You can use either of the two commands given below to create a database in Postgresql using psql after opening the psql utility on any of the 3 major operating systems discussed above:

1. CREATE DATABASE
2. createdb

How to create database in postgresql using CREATE DATABASE, a SQL command/query

Syntax:

postgres-# CREATE DATABASE database_name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ];

The “CREATE DATABASE" command will create a new PostgreSQL database with the name database_name.

Parameters:

• database_name: You can specify the name of a database to be create.
• user_name: You can specify the role name of the user who will own the new database, or just write DEFAULT (it will set the owner to be the user executing the command). If you want to create a database owned by another role, then you must be a direct or indirect member of that role or be a superuser.
• template: You can specify the name of the template which will be cloned and from which the new database will be created, or write DEFAULT to use the default template (the standard system database template1).
• encoding: You can specify the character set encoding to use in the new database by specifying a string constant (like 'SQL_ASCII'), or an integer encoding number, or write DEFAULT to use the default encoding (that will set the encoding of the template database). You can specify any character set’s encoding supported by PostgreSQL server.
• locale: It is used to set LC_COLLATE and LC_CTYPE parameters at once. If you specify it, you cannot specify either of the two parameters.
• lc_collate: You can specify the collation order (LC_COLLATE) to use in the database created. This will affect the sort order applied to strings, e.g., in queries with ORDER BY, as well as the order used in indexes on text columns. The default collation order used is of the template database.
• lc_ctype: You can specify the character classification (LC_CTYPE) to use in the database created. This will affect the categorization of characters, e.g., lower case, upper case and digit. The default character classification used is of the template database.
• tablespace_name: You can specify the name of the tablespace that will be associated with the database created, or write DEFAULT for using the tablespace of template database. All the objects that will be created in this database will have this tablespace by default.
• allowconn: The default value for this is true allowing connections (except restricted ones). but if set as false then no one can connect to this database.
• connlimit: You can specify the number of concurrent connections that can be made to the database created. By default the value is -1, that means no limit.
• istemplate: This parameter can specify the CREATEDB privileges. that is if set as true, then this database can be cloned by any user, but if set as false (the default value), then only the owner of the database or the superusers can clone it.

Note:

• To create any database, you must be a superuser/or have the special CREATEDB privilege in postgresql.
• CREATE DATABASE cannot be executed inside a transaction block.
• There is no restriction in the order of the parameters as described in the syntax above.

Examples:

postgres-# \l

postgres=# CREATE DATABASE testdb;

Next, to confirm the database creation, you can use the following command given below

postgres=# \l

How to create database in postgresql using createdb, a command-line executable

Syntax:

postgres-# createdb [option...] [dbname [description]]

Parameters:

• dbname: Specifies the name of a database to create.
• description: Specifies a comment to be added with the database created.
• options: You can specify command-line arguments accepted by createdb.

Options:

• -D tablespace: It specifies the default tablespace for the database.
• -e: It echo the commands that are generated and sent to the server by createdb command.
• -E encoding: It specifies the character set encoding scheme to be used in the database created.
• -l locale: It specifies the locale to be used in this database.
• -T template: It specifies the template database from which the new database will be created.
• -h host: It specifies the host name of the machine where the server is running.
• -p port: It is used to specify the TCP port on which the server is listening for connections.
• -U username: It is used to set the user name to connect as.
• -w: It enables to never issue a password prompt.
• -W: It forces createdb to prompt for a password before connecting to a database.
• –help: It shows help/documentation about createdb command line arguments.

Examples:

postgres-# \l

postgres=# createdb -h localhost -p 5432 -U postgres mydb3
postgres-# \l

How to create database in postgresql using pgadmin 4

Launch pgAdmin: Launching pgAdmin is easy. It is available in the respective application or programs folder for the operating system that you may be using. 

On Linux systems, pgAdmin is available under Programming in the Applications menu:

On Windows, pgAdmin is available under Program Files:

On a Mac, pgAdmin is available in the Applications folder as an application:

• Connect to database server
• Navigate through: Databases > Create > Database…

• In the pop-up window,
  • Enter Database Name
  • Enter Comment – It is optional to specify
  • Click Save
• Done, Now you can see the newly created database in the Object Tree.

Postgresql create database

To create the database in Postgresql, we must be a superuser, follow the below syntax.

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

The following is the parameter meaning of the above syntax.

• name: It is a name of a database to create.
• user_name: It is the role name of the user who is going to own the new database.
• template: It is the name of the template from which to create the new database.
• encoding: It is character set encoding to use in the new database. Specify a string constant (like ‘SQL_ASCII’), or an integer encoding number.
• lc_collate: It is a collation order to be used in the new database.
• lc_ctype: It is a character classification to be used in the new database. It affects the categorization of characters, like lower, upper, and digit.
• tablespace: It is the name of the tablespace that is going to be associated with the new database.
• connlimit: It’s about how many concurrent connections can be made to this database. By default is -1 which means no limit.

Postgresql create database command line

In Postgresql, we can create a database using the command line, follow the below instructions.

• Open command prompt by Windows+R and then, type “cmd”, hit Enter.
• Type the below command to enter into the Postgresql command prompt.

psql -U postgres 
--In the above code -U represents user,so we are goint to login as postgres user.

• Create a database using the below statement.

CREATE DATABASE database_name;

• Check the database using the \l command that shows all the available databases.

\l -- To list all the databases

From the above output, we see a newly created database named database_name.

Read: How to create a table in PostgreSQL

Postgresql create database and user

In Postgresql, first, we will create a new user then create a new database under that user.

Syntax:

CREATE USER user_name  WITH option

Whether we are on Windows (command-line) or Linux (terminal), the same command is used to create a user in Postgresql given below.

psql -U postgres -- To login as superuser or postgres that is superuser

Create a new user named sam with the password ‘12345’.

CREATE USER sam with encrypted password '12345';

then create a new database named sam_database.

CREATE DATABASE sam_database;

Grant access to a new user of a newly created database named sam_database, run the following statement.

GRANT ALL PRIVILEGES ON DATABASE sam_database TO sam;

verify the newly created user using the below command.

\du  -- It is used to list all user

Read: How to connect to PostgreSQL database

Postgresql create database if not exists

In Postgresql, there is no command like if not exist but we have an alternative way to check the new database before creating it.

Postgresql has a system catalog named pg_database that contains information about all the databases. It is accessible from any database in the same database cluster.

Use the below command to a new create database.

SELECT 'CREATE DATABASE new_db'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'new_db')\gexec

Here first, we are checking whether the database already exists in pg_database, if it does not exist, then it will create a new database named new_db.

\gexec: It sends the current query buffer to the server, then considers each column of each row of the query’s output as a SQL statement to be executed.

In the above output, we have created a new database named new_db, before the creation of this database, we checked it into pg_database whether it already exists or not.

Read: How to Uninstall PostgreSQL

Postgresql create database command line ubuntu

In this sub-section, we are going to create a new Postgresql database on the Ubuntu operating system using the terminal (command-line).

• Open a terminal by CTRL + ALT + T on Ubuntu and log into the Postgres prompt using the below command.

sudo -u postgres psql

• To create a new database named ubuntu_database use the below statement.

CREATE DATABASE ubuntu_database;

• Check the newly created database using the \l command and execute the below statement.

\l -- To list all the databases

From the above output, we have created a database named ubuntu_database on the command line ( terminal ).

Read: PostgreSQL WHERE

Postgresql create database with owner

In Postgresql, we can create a new database with a different owner, the owner is like a role that executed the creation statement. For almost all kinds of objects, the initial state is that only the owner can do anything with the object.

The following statement creates a new database named role_database with an owner named postgres.

CREATE DATABASE role_database WITH OWNER = postgres_user;

View the database with the owner using the below statement.

\l --To list all daatabase with owner and other details

From the above output, we have created a new database named role_database and assigned an owner named postgres_user.

Read: PostgreSQL CASE

Postgresql create database not working

In Postgresql, if we get an error like create database not working, then check your syntax of creating database or what kind of permission user has, whether the user can create new database or not.

The correct syntax is given below to create a new database.

CREATE DATABASE db_name;

Where db_name is the name of the database that we want to create.

If the above solution doesn’t resolve your problem then, check the permission of the current user.

First, check the name of the current user.

SELECT current_user;

Then use the below statement to know the role attribute of the current user or permissions.

\du

In the above output, first, we have found the current user, that is postgres, then we list all available users with their roles attributes. we found that postgres user has permission like Superuser, Create role, Create DB, etc.

if the current user has all the above roles attributes, then this user can create the database, if not then the user can’t create the database.

Read: PostgreSQL DATE Format

Postgresql create database link

In Postgresql, we can create a database remotely with the help of “dblink” that executes the query in a remote database.

Syntax:

dblink(text connname, text sql [, bool fail_on_error]) returns setof record

dblink(text connstr, text sql [, bool fail_on_error]) returns setof record

dblink(text sql [, bool fail_on_error]) returns setof record

The following is the meaning of the above argument with the dblink function.

• connname: It is the name of the connection to use.
• connstr: It is a connection info string.
• SQL: It is the SQL query that we wish to execute in the remote database.
• fail_on_error: It is the error, that is if true then an error thrown on the remote side of the connection causes an error to also be thrown locally. If it is false, then remote error is locally reported as a NOTICE, and the function returns no rows.

Let’s understand with an example, for this example, we will connect to a local database that also applies to the remote databases.

Create two separate databases in the Postgresql server running locally using statement.

CREATE DATABASE person_database_one;

CREATE DATABASE person_database_two;

Create a table and insert the following records in person_database_one.

\c person_database_one -- connect to database

CREATE TABLE person(id int,name varchar(20),gender varchar(10));


INSERT INTO person(id,name,gender)VALUES(1,'Decker','Male'),
(2,'Lucifer','Male'),(3,'Dan','Male');

We are going to SELECT person_database_one data from person_database_two, so we have to execute all below scripts on person_database_two to configure the DbLink extension for cross-database queries.

Make a connection with person_database_two using the below command.

\c person_database_two

Now install the dblink extension.

CREATE EXTENSION dblink;

Let’s verify the installed dblink system tables.

SELECT pg_namespace.nspname, pg_proc.proname 
FROM pg_proc, pg_namespace 
WHERE pg_proc.pronamespace=pg_namespace.oid 
   AND pg_proc.proname LIKE '%dblink%';

Check the connection with person_database_one.

SELECT dblink_connect('host=localhost user=postgres password=12345 dbname=person_database_one');

Make foreign data wrapper and the server for the global authentication, after that we can use this server object for cross-database queries.

CREATE FOREIGN DATA WRAPPER postgre VALIDATOR postgresql_fdw_validator;

CREATE SERVER demod_postgre FOREIGN DATA WRAPPER postgre OPTIONS (hostaddr '127.0.0.1', dbname 'person_database_one');

Mapping of user with server.

CREATE USER MAPPING FOR postgres SERVER demod_postgre OPTIONS (user 'postgres', password '12345');

Check the created server is ready or not by connecting to it.

SELECT dblink_connect('demod_postgre');

From the above output, we have successfully connected to a server named demod_postgre.

Now, we can SELECT the data of person_database_one from person_database_two.

SELECT * FROM dblink ('demod_postgre','SELECT id,name,gender FROM person') 
AS DATA(id INT,name VARCHAR(20),gender VARCHAR(10));

From the above output, we have created two databases and used the SELECT statement from person_database_two to data of person_database_one.

Read: PostgreSQL ADD COLUMN

Postgresql create database and table

In Postgresql, Let’s create a database and table under that database.

Use the below statement.

CREATE DATABASE employee; -- To create database

\c employee  -- Connect to newly created database named employee

CREATE TABLE emp(id INT,emp_name VARCHAR(20),emp_role VARCHAR(30));

From the above output, we have created a database employee and table named emp.

Read: PostgreSQL vs SQL Server

Postgresql create database and schema

We are going to create a database and schema in Postgresql but before that, we need to know “What is schema?.”.

A schema is a namespace that contains named objects like tables, data types, functions, etc.

Syntax:

CREATE SCHEMA schema_name AUTHORIZATION user_name schema_element 

CREATE SCHEMA AUTHORIZATION user_name schema_element

CREATE SCHEMA IF NOT EXISTS schema_name AUTHORIZATION user_name 

CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name

The following is the meaning of the above schema parameters.

1. schema_name: It is the name of a schema to be created. If this is omitted, then the user_name is used as the schema name. The schema name cannot begin with pg_, because names are reserved for system schemas.
2. user_name: It is the role name of the user who is going to own the new schema. If omitted, By defaults to the user executing the command.
3. schema_element: It is an SQL statement which is used defining an object to be created within the schema. Currently, only CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER, and GRANT is accepted as clauses within CREATE SCHEMA.
4. IF NOT EXISTS: It does nothing if a schema with the same name already exists.

Remember: To create a new schema, the invoking user should have the CREATE privilege for the current database.

The following statement creates a new schema in the current database.

CREATE DATABASE schema_database; -- Create new database named schema_database

\c schema_database; -- Connect to schema_database

CREATE SCHEMA new_schema; -- To create new schema in the current database

\dn -- To view schemas

From the above output, we have created a new database schema_database and schema named new_schema under that database.

Read: PostgreSQL ALTER TABLE

Postgresql create database and set owner

In Postgresql, we can change the ownership of any database using the below statement.

Let’s create a new database named owner_database and check the owner of the database.

CREATE DATABASE owner_database;

\l -- to list all databases with ownership and other details

From the above output, we have created a new database owner_database with the owner name postgres. There is also another database named jhony_database with the owner name jhony.

So our Postgresql database contains two kinds of owners named postgres and jhony.

Let’s change the owner of a newly created database named owner_database to the owner named jhony.

ALTER DATABASE owner_database OWNER TO jhony; -- to change the owner of database

In the above output, we have changed the owner of owner_database to an owner named jhony.

Read: PostgreSQL DATE Functions

Postgresql create database and role

In Postgresql, we are going to create a new role, and under that role, we will create a new database.

Enter into psql prompt using the below command.

psql -U postgres

Check current user or role using the below statement.

SELECT current_user; -- To show current user

Create a new role named jhony.

CREATE ROLE jhony WITH createdb; -- To create new role

Here createdb option allows the jhony role to create a new database;

Change the current role to a new role named jhony.

SET ROLE jhony;

Create a new database under this role.

CREATE DATABASE jhony_database;

Check the created database.

\l -- To list all the databases

From the above output, we have created the database jhony_database under the role named jhony.

Here, we saw, Postgresql create database under different roles, owners, the schema of the Postgresql database.

You may like the following PostgreSQL tutorials:

• PostgreSQL DATE Format + Examples
• PostgreSQL WHERE IN with examples
• How to Uninstall PostgreSQL (Linux, Mac, and Windows)
• PostgreSQL WHERE with examples
• Postgresql set user password
• PostgreSQL CASE with Examples
• How to Restart PostgreSQL (Linux, Windows, Mac)

In this PostgreSQL tutorial, we have learned How to create a database in Postgresql and have covered the below topic:

• How to create database in postgresql
• How to create database in postgresql in linux
• How to create database in postgresql in windows
• How to create database in postgresql in mac
• How to create database in postgresql using psql
• How to create database in postgresql using CREATE DATABASE
• How to create database in postgresql using createdb
• How to create database in postgresql using pgadmin 4
• Postgresql create database command line
• Postgresql create database and user
• Postgresql create database if not exists
• Postgresql create database command line ubuntu
• Postgresql create database with owner
• Postgresql create database not working
• Postgresql create database link
• Postgresql create database and table
• Postgresql create database and schema
• Postgresql create database and set owner
• Postgresql create database and role