0

I am running a script that creates a database, some tables with foreign keys and insert some data, but somehow creating the tables is not working, although it doesn't throw any error: I go to pgAdmin, look for the tables created and there's no one...

When I copy the text of my script and execute it into the Query Tool, it works fine and creates the tables.

Can you please explain me what I am doing wrong?

Script:

DROP DATABASE IF EXISTS test01 WITH (FORCE); --drops even if in use

CREATE DATABASE test01
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'German_Germany.1252'
    LC_CTYPE = 'German_Germany.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False
    ;

CREATE TABLE customers
(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts
(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
    CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
      ON DELETE CASCADE 
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');     
   
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev');

I am calling the script from a Windows console like this:

"C:\Program Files\PostgreSQL\15\bin\psql.exe" -U postgres -f "C:\Users\my user name\Desktop\db_create.sql" postgres

My script is edited in Notepad++ and saved with Encoding set to UTF-8 without BOM, as per a suggestion found here

Paul Efford
  • 261
  • 4
  • 12

1 Answers1

0

I see you are using -U postgres command line parameter, and also using database name as last parameter (postgres).

So all your SQL commands was executed while you are connected to postgres database. Of course, CREATE DATABASE command did creation of test01 database, but CREATE TABLE and INSERT INTO did executed not for test01 database, but for postgres database, and all your tables are in postgres database, but not in test01.

You need to split your SQL script into 2 scripts (files): first for 'CREATE DATABASE', second for the rest of.

You need to execute first script as before, like

psql.exe -U postgres -f "db_create_1.sql" postgres

And for second one need to choose the database which was created at 1st step, like

psql.exe -U postgres -f "db_create_2.sql" test01
Rabban Keyak
  • 220
  • 2
  • 6
  • thanks! that solved my problem, but.... isnt possible to run it in one step? I tried creating the database with an alias like `CREATE DATABASE test01 AS db` and then trying to refer to this alias when refering to the tables, but didn't work... I guess i could create a `perl` script to implement calling the two files... – Paul Efford Feb 10 '23 at 12:30
  • @PaulEfford You can add a `\connect test01;` after your `CREATE DATABASE test01...` statement. It's `psql` interpreting the script so it'll recognise the [meta-command](https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS) and switch to the newly created db. – Zegarek Feb 10 '23 at 12:47
  • @Zegarek that definitively helped me to achive creating a db and its tables from within a single script, thanks! – Paul Efford Feb 10 '23 at 13:11