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 ;