Create database from command line in PostgreSQL

Try:

sudo -u postgres psql -c 'create database test;'

createdb is a command line utility which you can run from bash and not from psql. To create a database from psql, use the create database statement like so:

create database [databasename];

Note: be sure to always end your SQL statements with ;

As some of the answers point out, createdb is a command line utility that could be used to create database.

Assuming you have a user named dbuser, the following command could be used to create a database and provide access to dbuser:

createdb -h localhost -p 5432 -U dbuser testdb

Replace localhost with your correct DB host name, 5432 with correct DB port, and testdb with the database name you want to create.

Now psql could be used to connect to this newly created database:

psql -h localhost -p 5432 -U dbuser -d testdb

Tested with createdb and psql versions 9.4.15.

As the default configuration of Postgres, a user called postgres is made and the user postgres has full super admin access to entire PostgreSQL instance running on your OS.

sudo -u postgres psql

The above command gets you the psql command line interface in admin mode.

Creating user

sudo -u postgres createuser <username>

Creating Database

 sudo -u postgres createdb <dbname>

NOTE: < > are not to be used while writing command, they are used just to signify the variables

PostgreSQL Create Database - Steps to create database in Postgres.

1. Login to server using postgres user.

   su - postgres

2. Connect to postgresql database.

bash-4.1$ psql
psql (12.1)
Type "help" for help.
postgres=#

3. Execute below command to create database.

CREATE DATABASE database_name;

Check for detailed information below: https://orahow.com/postgresql-create-database/

For example, first, you can create the user(role) john with the password banana as shown below. *By default, PostgreSQL needs a password so you should set PASSWORD and you can omit WITH which is optional and you must use '' for PASSWORD instead of "" otherwise there is error:

CREATE ROLE john WITH LOGIN PASSWORD 'banana';

Then, you can create apple database with the owner john as shown below. *If you omit OWNER, the current user(role) is the database owner:

CREATE DATABASE apple OWNER john;

In addition, if you want to drop the user(role) john, you need to remove the owner john from apple database by replacing it with other (e.g., david), then drop the user(role) john as shown below otherwise there is error. *My answer explains how to drop a user(role) more properly:

ALTER DATABASE apple OWNER TO david;
DROP ROLE john;

Or:

REASSIGN OWNED BY john TO david;  
DROP ROLE john;

In addition again, you can drop the database as shown below:

DROP DATABASE apple;

With a single command line:

su -c "createuser dbuser;createdb -h localhost -p 5432 -E UTF8 -O dbuser dbname;" - postgres

It is pretty simple but sometimes I find the answers tricky.
[For windows users]
Open Windows cmd

• psql -U <username>

• Once connected to psql, enter the following command to create a new database:
  CREATE DATABASE <database_name>;

• To verify that the database has been created, you can run the \l command to list all available databases. Your new database should be listed in the output.
  ------[Additional]------

• You can now connect to the new database using the \c command followed by the database name, like this:
  \c <database_name>

• You can now run SQL commands on the new database to create tables, insert data, and so on.
  Note: Make sure to replace <username>, <database_name> with your actual Postgres username and database name.

PGPORT=5432
PGHOST="my.database.domain.com"
PGUSER="postgres"
PGDB="mydb"
createdb -h $PGHOST -p $PGPORT -U $PGUSER $PGDB

If you are using pgAdmin:

In query editor you can try like this :

CREATE DATABASE <databasename>
    WITH 
    OWNER = <dbowner>
    ENCODING = <encoding>
    CONNECTION LIMIT = <numberofsimulaneousconnections>;

an example snippet :

CREATE DATABASE twitterdb
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;