Cannot create database in PostgreSQL

Question

I am a complete beginner on PostgreSQL, and I am following an introduction to databases to set up a local PostgreSQL database and connect to it. I am running Debian 15, so I do the following:

# apt update # apt install postgresql 

The installation is succesful. I then do:

sudo -u postgres psql psql (15.7 (Debian 15.7-0+deb12u1)) Type "help" for help. postgres=# \l postgres=# createdb prismatest postgres-# \l postgres-# \c prismatest connection to server on socket "/var/run/postgresql/.s.PGSQL.5433" failed: FATAL: database "prismatest" does not exist Previous connection kept 

So the list of databases shows nothing and it is not possible to connect to the - supposedly - newly created database. My user has the rights:

postgres-# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} 

Can anybody help me figure out what is going on? Thanks...

Answer 1

There are two different ways of creating a database in PostgreSQL. One is from the command line. The other is from the PostgreSQL console.

Command Line

In order to be able to create a database from the command line, you must first change to a user with rights to create a database. As you have shown with the \du command above, the user with those rights is postgres.

root@eric-desktop:/home/eric# su postgres postgres@eric-desktop:/home/eric$ createdb prismatest2 postgres@eric-desktop:/home/eric$ 

As you can see, the creatdb command does not give any feedback. You need to confirm the creation by listing the current databases:

psql -U postgres -l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges -------------+----------+----------+-------------+-------------+------------+-----------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | prismatest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | prismatest2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | 

PostgreSQL Console

Another possibility is to use the PostgreSQL console. To enter the console:

root@eric-desktop:/home/eric# sudo -u postgres psql psql (15.7 (Debian 15.7-0+deb12u1)) Type "help" for help.

postgres=# postgres=# CREATE DATABASE "prismatest3"; CREATE DATABASE postgres=# 

To confirm the creation of the database, list the databases:

postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges -------------+----------+----------+-------------+-------------+------------+-----------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | prismatest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | prismatest2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | prismatest3 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | 

Answer 2

Notice how the prompt has changed from the first line to the second:

 v postgres=# createdb prismatest postgres-# \l ^ 

psql is hinting that you are entering more lines of the same command.

Enter the semicolon (";") that terminates the create database statement.

postgres=# createdb prismatest ;