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...

have you tried to read the documents? postgresql.org/docs/current/sql-createdatabase.html - createdb iirc is the shell command to do it — djdomi
– djdomi, Commented Jul 14, 2024 at 17:49

ElToro1966 · Accepted Answer · 2024-07-16 09:26:10Z

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            |

Phill W. · Accepted Answer · 2024-07-15 06:31:32Z

3

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 ;

answered Jul 15, 2024 at 6:31

Phill W.

1,6429 silver badges7 bronze badges

createdb doesn't work at the postgres promt postgres=# createdb prismatest2 ; ERROR: syntax error at or near "createdb" LINE 1: createdb prismatest2 ; ^ postgres=# createdb "prismatest2"; ERROR: syntax error at or near "createdb" LINE 1: createdb "prismatest2"; ^ postgres=#

HubbaBubba2000
– HubbaBubba2000

2024-07-16 08:53:41 +00:00
Commented Jul 16, 2024 at 8:53

Add a comment |

Stack Exchange Network

Cannot create database in PostgreSQL

2 Answers 2

You must log in to answer this question.

Hot Network Questions

Cannot create database in PostgreSQL

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions