How to specify the current working database for the initialization script of a postgres docker container?

Question

It is known that one can copy an init.sql file to be executed on the creation of the container with a docker command similar to this one: COPY init.sql /docker-entrypoint-initdb.d/

Let's consider that one uses a very simple create table postgresql statement:

CREATE TABLE films (
  code        char(5) CONSTRAINT firstkey PRIMARY KEY,
  title       varchar(40) NOT NULL,
  did         integer NOT NULL,
  date_prod   date,
  kind        varchar(10),
  len         interval hour to minute
);

The question is where (in which database) is this table created?

But more importantly is how to set the current working database to specify exactly on which database we are working with??
Not only for this statement but also for all subsequent ones, and they are many!

Answer 1

This explains in detail the initialization of the database. How to create User/Database in script for Docker Postgres.

To put it briefly, the name of the database created during the initialization and where your tables get created by default, is given by the environment variable POSTGRES_DB. If the variable is not set the default value postgres is used instead.

The scripts in the docker-entrypoint-initdb.d folder are executed one by one with the following command:

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -f <your-file>

therefore you are connected to the POSTGRES_DB database (have a look at the docker-entrypoint.sh script).

In your script files you can nevertheless connect to a different database using the meta-command

\connect DBNAME

Answer 2

Easy: set an environment variable POSTGRES_DB.

According to the docs:

This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used.