Why does init.sql not create any table?

Question

I am encountering a very odd issue with PostgreSQL running inside a Docker container on macOS. When attempting to create a table with using an initialization script, PostgreSQL erroneously reports that the relation already exists (even after a full pruning and cleanup of all docker containers, volumes, images, etc). However, querying the table immediately after shows no such relation. This issue does not occur when adding columns one at a time manually via the terminal.

I use the following docker-compose.yml and Dockerfile to set up PostgreSQL:

• docker-compose.yml:

services:
  db:
    build: ./
    restart: always
    container_name: SQLxTest
    ports:
      - '5432:5432'
    environment:
      - POSTGRES_USER=johndoe
      - POSTGRES_PASSWORD=1234
      - POSTGRES_DB=tests

• Dockerfile:

FROM postgres:latest
ADD ./init.sql /docker-entrypoint-initdb.d
RUN chmod a+r /docker-entrypoint-initdb.d/*
EXPOSE 5432

• My init.sql script:

DROP TABLE IF EXISTS spacetravellog;
CREATE TABLE IF NOT EXISTS spacetravellog (
    id TEXT NOT NULL,
    title VARCHAR(255) NOT NULL,
    traveldate TIMESTAMP(3) NOT NULL,
    logdate TIMESTAMP(3) NOT NULL,
    description TEXT NOT NULL,
    CONSTRAINT spacetravellog PRIMARY KEY (id)
);

Upon running docker-compose up --build -d, the logs show:

ERROR: relation "spacetravellog" already exists

But when I connect to the database and run \dt, it shows no relations.

What I've Tried:

• Separating the table creation from the primary key constraint addition.
• Wrapping the commands in a transaction.
• Manually creating the table column by column via psql, which works without issue.

Why does PostgreSQL report that the table already exists when the CREATE TABLE statement is included in the initialization script, but not when executed manually? Is there a known issue with Docker's handling of PostgreSQL initialization scripts or could this be a PostgreSQL bug?

The problem persists across different Docker container restarts.

Edit

I get the error logs from the docker logs SQLxTest command, which yields

...
2024-08-02 13:46:21.175 UTC [48] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE


/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
psql:/docker-entrypoint-initdb.d/init.sql:9: ERROR:  relation "space_travel_log" already exists
2024-08-02 13:46:21.311 UTC [61] ERROR:  relation "space_travel_log" already exists
2024-08-02 13:46:21.311 UTC [61] STATEMENT:  CREATE TABLE IF NOT EXISTS space_travel_log (
            id TEXT NOT NULL,
            title VARCHAR(255) NOT NULL,
            travel_date TIMESTAMP(3) NOT NULL,
            log_date TIMESTAMP(3) NOT NULL,
            description TEXT NOT NULL,
            CONSTRAINT space_travel_log PRIMARY KEY (id)
        );

PostgreSQL Database directory appears to contain a database; Skipping initialization

2024-08-02 13:46:21.549 UTC [1] LOG:  starting PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bi
...

Answer 1

Your table contains relation named the same as the table. Just rename it:

DROP TABLE IF EXISTS spacetravellog;
CREATE TABLE IF NOT EXISTS spacetravellog (
    id TEXT NOT NULL,
    title VARCHAR(255) NOT NULL,
    traveldate TIMESTAMP(3) NOT NULL,
    logdate TIMESTAMP(3) NOT NULL,
    description TEXT NOT NULL,
    CONSTRAINT pk PRIMARY KEY (id)
);

This way it works ;-)