Creating a Training Database and Loading SQL with psql

Hello! Last lesson, you used psql to confirm your connection and inspect databases, schemas, and tables. This time you’ll give those inspection commands something to find: you’ll create a dedicated training database, save a small supplied SQL script on your Windows computer, and load it with psql.

Allow about 40 minutes. Keep the PostgreSQL port, username, and password you used last lesson handy. The script for this lesson is included below, so you do not need to download a separate file.

---

Create a database for practice

A SQL script is a text file containing commands for PostgreSQL to execute. Before loading one, decide which database should receive those commands. We’ll use grasp_training rather than the postgres maintenance database.

Watch this brief psql demonstration from BekBrace’s PostgreSQL (1) Installation and Create Your First Database. Focus on the terminating semicolon and the database appearing in the database list; use our database name rather than the presenter’s.

Open PowerShell and connect to the existing postgres database. Replace 5432 with your server’s port and postgres after -U with your PostgreSQL username if yours differs:

psql -h localhost -p 5432 -U postgres -d postgres

If PowerShell cannot find psql, use the executable path you located in an earlier lesson, substituting your installed version for 18:

& 'C:\Program Files\PostgreSQL\18\bin\psql.exe' -h localhost -p 5432 -U postgres -d postgres

At the psql prompt, run these lines one at a time:

CREATE DATABASE grasp_training;

\l
\c grasp_training
\conninfo

CREATE DATABASE is SQL, so it needs a semicolon. The backslash commands are psql commands and do not. Look for grasp_training in \l, then use \conninfo to confirm that your current connection is to it. If the create command says the database already exists, do not load the script into it without checking its contents; choose a new name such as grasp_training_2 and use that name consistently in the steps below.

Leave psql so you can prepare the file in PowerShell:

\q

---

Save the supplied SQL script

The following script creates two related tables and inserts four customers and four orders. One customer has no order, which will be useful when you learn to query and combine tables. You do not need to master the table-definition syntax yet; for now, notice that the file contains several SQL statements, each ending in a semicolon.

First, make a folder for the file. At the PowerShell prompt, run:

$folder = Join-Path $HOME 'sql-training'
New-Item -ItemType Directory -Path $folder -Force
$script = Join-Path $folder 'setup.sql'
notepad $script

If Notepad asks whether to create the file, choose Yes. Paste the complete script below, then save it as setup.sql. If using Save As, select All files as the file type so it does not become setup.sql.txt.

CREATE TABLE public.customers (
    customer_id integer PRIMARY KEY,
    customer_name text NOT NULL,
    city text
);

CREATE TABLE public.orders (
    order_id integer PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES public.customers(customer_id),
    ordered_on date NOT NULL,
    total_amount numeric(10, 2) NOT NULL
);

INSERT INTO public.customers (customer_id, customer_name, city) VALUES
    (1, 'Maya Chen', 'Seattle'),
    (2, 'Luis Rivera', 'Boston'),
    (3, 'Aisha Khan', 'Seattle'),
    (4, 'Noah Patel', NULL);

INSERT INTO public.orders (order_id, customer_id, ordered_on, total_amount) VALUES
    (101, 1, '2025-01-12', 24.50),
    (102, 1, '2025-02-03', 18.00),
    (103, 2, '2025-02-10', 42.75),
    (104, 3, '2025-03-01', 31.20);

Back in PowerShell, check that the file exists at the path your next command will use:

Test-Path $script
Get-Content $script -TotalCount 8

Test-Path should print True, and Get-Content should show the beginning of your SQL. If either result is wrong, check the filename and where Notepad saved it. The script is stored in your Windows folder; it is not the PostgreSQL server’s data directory.

---

Load the file with psql

PostgreSQL’s psql documentation describes -f as reading commands from a file. It also documents \i, which does the same job from inside an interactive psql session. Read these short entries before running the load.

We’ll use -f from PowerShell. Replace the port and username as before. If psql is not on your PATH, replace psql at the start with & 'C:\Program Files\PostgreSQL\18\bin\psql.exe', using your installed version.

psql -h localhost -p 5432 -U postgres -d grasp_training -v ON_ERROR_STOP=1 -f $script
$LASTEXITCODE

The -d option names the destination database; -f names the local SQL file. PowerShell passes the path stored in $script to psql, including when your home-folder name contains spaces. Enter your password at the prompt, not in the command.

A successful run should print two CREATE TABLE messages and two INSERT 0 4 messages. The final $LASTEXITCODE should be 0. The ON_ERROR_STOP setting tells psql to stop if a statement fails, rather than quietly continuing through the rest of the file.

You may also encounter \i when working interactively. For example, after connecting to grasp_training in psql, a file at C:\Users\YourName\sql-training\setup.sql could be loaded with:

\set ON_ERROR_STOP on
\i 'C:/Users/YourName/sql-training/setup.sql'

For that form, replace YourName with your actual Windows path and use forward slashes in the quoted filename. Do not run \i now if the -f load succeeded: this particular script creates tables that already exist, so it is not meant to be loaded twice into the same database.

---

Verify the tables and data

A successful command message is useful, but inspect the destination database itself too. From PowerShell, reconnect:

psql -h localhost -p 5432 -U postgres -d grasp_training

Use your executable path instead if needed. At the psql prompt, check the connection and the tables:

\conninfo
\dt public.*
\d public.orders

You should be connected to grasp_training. The table list should contain customers and orders; the description of orders should show its columns and a reference to customers.

Finally, run these two small checks at the psql prompt:

SELECT * FROM public.customers;
SELECT * FROM public.orders;

Each should return 4 rows. For now, treat SELECT * as “show every column”; the next module will teach you how to choose, filter, and organize query results. Exit with \q when finished.

If a check fails, first confirm \conninfo: it is easy to inspect postgres while the tables are in grasp_training. If -f reported that the file could not be opened, return to PowerShell and check Test-Path $script. If a statement failed partway through loading, do not blindly rerun the script—earlier statements may already have succeeded. For a clean retry, create a new empty training database under a different name and load the corrected file into that database.

---

Wrap-up

You created a database, saved SQL as a local .sql file, loaded that file with psql’s -f option, and checked both its tables and rows. The distinction to retain is that the database name tells psql where to execute the commands, while the file path tells it which commands to read.

This completes the Windows and psql foundations module. Next, you’ll start querying the training data with SELECT, choosing the columns and results you actually want to see.