Create a database and two tables using SQL

Question

This seems like a very easy thing to do but could you tell me where I am going wrong.

I want to execute some SQL files on Postgres db start up to create a database and tables.

I use docker-compose to create a Postgres version 16.2 database I copy 3 SQL files ( named so they execute sequentially) into docker-entrypoint-initdb.d where they are executed, this works! I want the tables to belong in the created database, but I cannot get that working

Here are the files.

aa.sql

CREATE USER user1 WITH PASSWORD 'password';
CREATE DATABASE products;
GRANT ALL PRIVILEGES ON DATABASE products TO user1;

bb.sql (runs after aa.sql):

CREATE TABLE brands (
    brand_id INTEGER PRIMARY KEY,
    brand_name VARCHAR(255) NOT NULL
);

cc.sql (runs after bb.sql)

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    brand_id INTEGER NOT NULL,
            FOREIGN KEY (brand_id)
            REFERENCES products.brands (brand_id)
            ON UPDATE CASCADE ON DELETE CASCADE
)

If I run my docker-compose I see the tables are created in the postgres database not products

If I use CREATE_TABLE databasename.tablename , no tables are created

If I use USE products; I get

psql:/docker-entrypoint-initdb.d/bb.sql:1: ERROR:  syntax error at or near "USE"

Can someone please tell me what I am doing wrong

Answer 1

Thanks for all the comments.

I found this resolves the problem (connect to the right db first using the code below)

\connect products

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    brand_id INTEGER NOT NULL,
            FOREIGN KEY (brand_id)
            REFERENCES brands (brand_id)
            ON UPDATE CASCADE ON DELETE CASCADE
)