I saw a few similar issues, but I already tried their solutions and it didn’t work for me.
I also discussed my problem in-depth with ChatGPT4 and it eventually ran out of ideas too 😅

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.

Environment:

PostgreSQL Version: latest Docker image
Host OS: macOS Sonoma
Docker Version: Docker version 27.1.1, build 6312585
Steps to Reproduce:

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.

Question:

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?

Additional Information:

The problem persists across different Docker container restarts.

Any insights or suggestions to troubleshoot this issue further would be greatly appreciated!