Skip to content
aviral gupta
Get in touch

// B1.1 · ~35 min · Beginner

Installing PostgreSQL and psql (or the in-browser editor)

After this lesson you can run SQL in PostgreSQL, create a small table, fill it, query it, and read the error PostgreSQL gives when a statement fails.

Lesson 1 of 4 in B1 Getting started

Start of the module

You will be able to

  • Install PostgreSQL 18 or use the editor here, connect with psql, and tell SQL from psql commands such as \q
  • Create a table, insert rows and query them, ending each statement with a semicolon
  • Read a PostgreSQL error: the ERROR line, the name it points to, and where the run stopped

  1. Warm-up · Activity 1 of 7

    Warm-up: what is psql?

  2. Predict · Activity 2 of 7

    Predict before you read on: what number does the last statement return?

    CREATE TABLE books (title text);
INSERT INTO books VALUES ('Dune');
INSERT INTO books VALUES ('Emma');
SELECT count(*) FROM books;

  3. Practice · Activity 3 of 7

    Fill in the key word so that the statement creates a table called books with one column, title.

    CREATE ____ books (title text);
    CREATE books (title text);

  4. Practice · Activity 4 of 7

    In psql you type SELECT 2 + 2 and press Enter, without a semicolon. What happens?

  5. Practice · Activity 5 of 7

    Match each command to what it does.

  6. Brain teaser · Activity 6 of 7

    Brain teaser. The table is created as Books, filled as BOOKS and read as books. What does the SELECT return?

    CREATE TABLE Books (Title text);
INSERT INTO BOOKS VALUES ('Dune');
select title from books;

  7. Apply · Activity 7 of 7

    Mini-task. In the editor, write a script for your own shelf: create a table shelf with the columns id, title and year, insert three books you know, and list them with SELECT * FROM shelf ORDER BY year;. Run it, then press Run again: the table is created anew, because every run starts from an empty database.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

A tiny library

This script creates a table of books, adds three rows and runs two queries. The third INSERT names its columns, the style many developers prefer. Run it, then add a fourth book and run it again. Every run starts from an empty database, so the table is created anew each time. On your computer, the same file runs with psql -X -q -v ON_ERROR_STOP=1 -f main.sql.

main.sql

-- A tiny library: one table, three books.
CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer
);

INSERT INTO books VALUES (1, 'Dune', 'Frank Herbert', 1965);
INSERT INTO books VALUES (2, 'Emma', 'Jane Austen', 1815);
INSERT INTO books (id, title, author, year)
VALUES (3, 'Kindred', 'Octavia E. Butler', 1979);

SELECT * FROM books ORDER BY id;

SELECT count(*) AS books FROM books;

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f main.sql

Output

 id |  title  |      author       | year
----+---------+-------------------+------
  1 | Dune    | Frank Herbert     | 1965
  2 | Emma    | Jane Austen       | 1815
  3 | Kindred | Octavia E. Butler | 1979
(3 rows)

 books
-------
     3
(1 row)
  • CREATE TABLE and INSERT print nothing; only the two SELECT statements produce output.
  • Each result is a table: the column names, a line of dashes, the rows and a count such as (3 rows).
  • Numbers are right-aligned and text is left-aligned.
  • Lines starting with -- are comments: PostgreSQL ignores them.
  • ORDER BY id fixes the order of the rows; without it, SQL promises no particular order.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

Your first table

Create a table members with two columns: id (integer) and name (text). Add two members: 1, Ada and 2, Grace. Then list them with SELECT * FROM members ORDER BY id;. End every statement with a semicolon.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    CREATE TABLE members (id integer, name text); creates the table.

  2. Hint 2

    One INSERT per member: INSERT INTO members VALUES (1, 'Ada');

  3. Hint 3

    Names are text, so they go in single quotes; the ids are numbers and need none.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

CREATE TABLE members (
  id integer,
  name text
);

INSERT INTO members VALUES (1, 'Ada');
INSERT INTO members VALUES (2, 'Grace');

SELECT * FROM members ORDER BY id;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- 1. Create the table members (id integer, name text).
-- 2. Add Ada (id 1) and Grace (id 2).
-- 3. List every member, ordered by id.

test.sql

-- test: The table members has two rows
SELECT count(*) = 2 FROM members;

-- test: Ada has id 1 and Grace has id 2
SELECT EXISTS (SELECT 1 FROM members WHERE id = 1 AND name = 'Ada')
   AND EXISTS (SELECT 1 FROM members WHERE id = 2 AND name = 'Grace');

-- test: The script lists both members, ordered by id
-- output:
--  id | name
-- ----+-------
--   1 | Ada
--   2 | Grace
-- (2 rows)

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Two more books

setup.sql has created the table books (id, title, author, year) with Dune and Emma. Above the SELECT, add two books: 3, Kindred, Octavia E. Butler, 1979 and 4, Beloved, Toni Morrison, 1987. The SELECT then lists all four titles, oldest first.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    The table already exists: do not create it again, only add rows.

  2. Hint 2

    The values go in the order of the columns: id, title, author, year.

  3. Hint 3

    INSERT INTO books VALUES (3, 'Kindred', 'Octavia E. Butler', 1979); adds the first one.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

INSERT INTO books VALUES (3, 'Kindred', 'Octavia E. Butler', 1979);
INSERT INTO books (id, title, author, year)
VALUES (4, 'Beloved', 'Toni Morrison', 1987);

SELECT title, year FROM books ORDER BY year;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has created books and added Dune and Emma.
-- Add Kindred and Beloved here.

SELECT title, year FROM books ORDER BY year;

test.sql

-- test: The table holds four books
SELECT count(*) = 4 FROM books;

-- test: Kindred is book 3, by Octavia E. Butler, from 1979
SELECT EXISTS (SELECT 1 FROM books WHERE id = 3 AND title = 'Kindred' AND author = 'Octavia E. Butler' AND year = 1979);

-- test: Beloved is book 4, by Toni Morrison, from 1987
SELECT EXISTS (SELECT 1 FROM books WHERE id = 4 AND title = 'Beloved' AND author = 'Toni Morrison' AND year = 1987);

-- test: The script lists all four titles, oldest first
-- output:
--   title  | year
-- ---------+------
--  Emma    | 1815
--  Dune    | 1965
--  Kindred | 1979
--  Beloved | 1987
-- (4 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer
);

INSERT INTO books VALUES (1, 'Dune', 'Frank Herbert', 1965);
INSERT INTO books VALUES (2, 'Emma', 'Jane Austen', 1815);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

A missing semicolon

CREATE TABLE books (id integer, title text);
INSERT INTO books VALUES (1, 'Dune')
INSERT INTO books VALUES (2, 'Emma');

What psql prints

ERROR:  syntax error at or near "INSERT"

Why, and the fix

The first INSERT has no semicolon, so PostgreSQL reads both lines as one statement, and the second INSERT makes no sense in the middle of it. The error names the word where reading failed, which is often just after the missing semicolon. End every statement with ;.

Text in double quotes

CREATE TABLE books (id integer, title text);
INSERT INTO books VALUES (1, "Dune");

What psql prints

ERROR:  column "Dune" does not exist

Why, and the fix

In SQL, single quotes mark text and double quotes mark names, such as a column or a table. "Dune" is read as the name of a column, and there is no such column. Write text in single quotes: 'Dune'.

Values in the wrong order

CREATE TABLE books (id integer, title text);
INSERT INTO books VALUES ('Dune', 1);

What psql prints

ERROR:  invalid input syntax for type integer: "Dune"

Why, and the fix

Without a column list, INSERT fills the columns in the order CREATE TABLE gave them: id first, then title. 'Dune' lands in id, which takes whole numbers only. Put the values in column order, or name the columns: INSERT INTO books (title, id) VALUES ('Dune', 1);.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

A server, and psql to talk to it

PostgreSQL uses a client/server model. The server, a program called postgres, stores the data and runs SQL. psql is its interactive terminal: you type SQL, psql sends it to the server and shows the rows. To install PostgreSQL 18, open postgresql.org/download and pick your operating system: for Windows it links an interactive installer by EDB, for Linux the packages of your distribution. Then createdb mydb creates a database and psql mydb connects to it. The prompt mydb=> (mydb=# for a superuser) means psql is listening. Lines that begin with a backslash, such as \q to quit, are psql commands, not SQL. The editor on this page runs PostgreSQL 18.3 in your browser, with a fresh, empty database on every run.

CREATE TABLE, INSERT, SELECT, and a semicolon

CREATE TABLE books (id integer, title text) names a table and gives each column a name and a type. INSERT INTO books VALUES (1, 'Dune') adds one row, with the values in column order; INSERT INTO books (id, title) VALUES … names the columns, which many developers find clearer. Text goes in single quotes; double quotes mark names. SELECT * FROM books shows every column of every row, SELECT title FROM books only the titles. A statement ends at its semicolon, not at the end of a line, so one statement may span several lines. Key words and names are case-insensitive: SELECT and select are the same, and Books is the table books.

Read the ERROR line first

When a statement fails, PostgreSQL answers with a line such as ERROR: relation "book" does not exist. It says what went wrong and names the thing: here a table called book, which does not exist because the table is books. psql adds where it happened (psql:main.sql:3:) and a LINE with a caret under the spot. The editor on this page runs your file as psql -X -q -v ON_ERROR_STOP=1 -f main.sql does: statement by statement, stopping at the first error. Statements before it have run; statements after it never do. So fix the first error, then run again: a missing semicolon, for example, is reported as a syntax error at the next word.

Sources

Last reviewed September 30, 2026