What steps are required to setup PostgreSQL and create a db in Windows?

Question

I have installed PostgreSQL 12 from https://www.2ndquadrant.com/en/blog/pginstaller-install-postgresql/. and put \C:\...\PostgreSQL\12\bin in the PATH.

In an admin powershell I execute psql.exe -U postgres and enter the password I chose in the installation wizard. Now I'm in a shell. I want to create a database so I run createdb but it seems the database is not created:

postgres-# \l
                                          List of databases
   Name    |  Owner   | Encoding |       Collate       |        Ctype        |   Access privileges
-----------+----------+----------+---------------------+---------------------+-----------------------
 postgres  | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 |
 template0 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
 template1 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
(3 rows)
postgres-# createdb demo
postgres-# absolute nonsense
postgres-# \l
                                          List of databases
   Name    |  Owner   | Encoding |       Collate       |        Ctype        |   Access privileges
-----------+----------+----------+---------------------+---------------------+-----------------------
 postgres  | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 |
 template0 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
 template1 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
(3 rows)

I assume that my command createdb demo fails without error response as the intentionally wrong next command absolute nonsense fails as well. But I don't know how else to create a db, and moreover I'd like to know why this doesn't work and how to debug this.

score 2 · Accepted Answer · answered Sep 07 '20 at 09:05

In the psql client, you can only run SQL statements and psql commands (which start with a backslash).

So, to create a database, you would have to run

CREATE DATABASE demo;

The semicolon in the end is required to tell psql that the statement is complete.

The prompt you see:

postgres-#

shows that you didn't do that and psql is waiting for a continuation line. You see that from the -.

To delete the input buffer and get back to the prompt

postgres=#

simply press Ctrl+C. Then you can enter the CREATE DATABASE statement.

createdb is not an SQL statement, but an executable (program) that you would call from the shell (or from cmd.exe on Windows).

So the alternative is not to start psql, but type

createdb -U postgres demo

What steps are required to setup PostgreSQL and create a db in Windows?

1 Answers1