How to create database, schema, and then table inside Postgresql using init.sql

Question

I am trying to create an init.sql file to automatically create an user, database, schema, and table. Currently, the user and database are created, but I am having troubles with my schema and table. Please see below for my code:

docker-compose:

postgres_db:
image: postgres:14.1
restart: unless-stopped
environment:
  POSTGRES_USER: ${SERVER_DB_USER}
  POSTGRES_PASSWORD: ${SERVER_DB_PASS}
volumes:
  - ./data/postgres:/var/lib/postgresql/data
  - ./admin/config:/docker-entrypoint-initdb.d
ports:
  - "5432:5432"
networks:
  - bypass

init.sql file inside /admin/config:

CREATE USER bypass;
CREATE DATABASE bypass;
GRANT ALL PRIVILEGES ON DATABASE bypass TO bypass;

\c bypass;
CREATE SCHEMA bypass_project;

CREATE TABLE bypass_project.Bypass_Data (
    id serial PRIMARY KEY,
    Date_Time TIMESTAMP,
    Module VARCHAR(25),
    Area VARCHAR(25),
                         );

After running docker-compose up, I am not able to see the schema and the table. I'm pretty sure \c bypass is used incorrectly. How do I connect to the bypass database and create everything there?

Answer 1

Your definitely have errors in your SQL syntax in the CREATE TABLE statement. You should remove trailing comma:

CREATE TABLE bypass_project.Bypass_Data (
    id serial PRIMARY KEY,
    Date_Time TIMESTAMP,
    Module VARCHAR(25),
    Area VARCHAR(25)
);

Also you can try (second bypass is your DB user):

\c bypass bypass;

instead of

\c bypass;

Also \c is a command specific to psql. If you try to execute an SQL script containing such command in another client, it will not work.

In this case you can try to use init.sh script instead of init.sql and use psql in your bash-script. You can see full example here.

If it does not help you, could you provide more information? I think docker-compose log would be helpful.