ERROR: database "fix8_shop" already exists (42P04) and ERROR: role "shop_app" already exists (42710) mean a setup script ran a second time. PostgreSQL 18.4 refused IF NOT EXISTS on these two statements, so guard them: \gexec for the database, a DO block for the role.

Both guards below ran in the test after the objects existed: the database line created nothing for the existing name and created fix8_shop2 for a new one, and the role block skipped the existing role without an error.

The error

CREATE DATABASE fix8_shop;
CREATE DATABASE
CREATE DATABASE fix8_shop;
psql:/tmp/f42p04.sql:3: ERROR:  42P04: database "fix8_shop" already exists
LOCATION:  createdb, dbcommands.c:1393
CREATE ROLE shop_app LOGIN;
CREATE ROLE
CREATE ROLE shop_app LOGIN;
psql:/tmp/f42p04.sql:5: ERROR:  42710: role "shop_app" already exists
LOCATION:  CreateRole, user.c:375

Why it happens

Database and role names are unique on the server, so the second CREATE of the same name is refused. A setup script run twice against the same server hits both. psql did not stop at the error in the test: without ON_ERROR_STOP it went on to the next line, so a script can print the error and still carry on.

On PostgreSQL 18.4 CREATE SCHEMA IF NOT EXISTS and CREATE TABLE IF NOT EXISTS worked (the second table line printed NOTICE: 42P07: relation "orders" already exists, skipping), while CREATE DATABASE IF NOT EXISTS fix8_shop and CREATE ROLE IF NOT EXISTS shop_app both failed with 42601: syntax error at or near "NOT". For a database and a role, the script has to check the catalog itself.

The fix

For the database, build the statement only when the name is missing and run it with psql's \gexec:

SELECT 'CREATE DATABASE fix8_shop' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'fix8_shop')\gexec
SELECT 'CREATE DATABASE fix8_shop2' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'fix8_shop2')\gexec

The output: the first line produced no statement, the second created the new database.

SELECT 'CREATE DATABASE fix8_shop' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'fix8_shop')
SELECT 'CREATE DATABASE fix8_shop2' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'fix8_shop2')
CREATE DATABASE fix8_shop2
CREATE DATABASE

For the role, check pg_roles in a DO block, or catch the duplicate:

DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'shop_app') THEN
    CREATE ROLE shop_app LOGIN;
  END IF;
END $$;
DO
DO $$
BEGIN
  CREATE ROLE shop_app LOGIN;
EXCEPTION WHEN duplicate_object THEN
  RAISE NOTICE 'role shop_app already exists, skipped';
END $$;
psql:/tmp/f42p04.sql:19: NOTICE:  00000: role shop_app already exists, skipped
LOCATION:  exec_stmt_raise, pl_exec.c:3909
DO

The first block did nothing because the role existed; the second turned the 42710 error into a notice. The database needs \gexec rather than a DO block: the same IF NOT EXISTS check in a DO block failed with 25001: CREATE DATABASE cannot be executed from a function. \gexec is a psql command: from application code, run the SELECT and issue the CREATE DATABASE only when it returns a row.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian 18.4-1.pgdg13+1) on Docker Engine 29.9.2. The script f42p04.sql created fix8_shop and the role shop_app twice, then ran the guarded versions; it ran with docker exec fix8-pg3 sh -c "psql -U postgres -d lab -e -f /tmp/f42p04.sql 2>&1" and \set VERBOSITY verbose at the top for the SQLSTATE codes. The database, role and container names are the test's own.

Frequently asked

Does PostgreSQL support CREATE DATABASE IF NOT EXISTS?
Not on PostgreSQL 18.4: CREATE DATABASE IF NOT EXISTS fix8_shop failed with a 42601 syntax error at NOT. The tested guard is SELECT 'CREATE DATABASE fix8_shop' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'fix8_shop')\gexec, which created nothing for an existing name and created a missing one.
How do I create a PostgreSQL role only if it does not exist?
Use a DO block that checks pg_roles before CREATE ROLE, or one that catches duplicate_object. Both ran without an error in the test after shop_app already existed.
What do SQLSTATE 42P04 and 42710 mean?
In the test 42P04 came with database fix8_shop already exists and 42710 with role shop_app already exists, both from running the same CREATE statement twice.

More decoded errors in the Fixes category.