// B1.2 · ~30 min · Beginner

Creating tables: data types, names and DROP TABLE

After this lesson you can choose a fitting type for each column, drop and re-create a table without errors, and tell from an ERROR line which value did not fit.

Lesson 2 of 4 in B1 Getting started

Previous Next

You will be able to

Choose a type for each column: integer, bigint, numeric(p,s), text, varchar(n), boolean or date
Name tables well, and create, drop and re-create them with IF EXISTS and IF NOT EXISTS
Read the errors a type raises: value too long, numeric field overflow, date out of range, integer out of range

Warm-up · Activity 1 of 7
Warm-up: a column holds the number of copies of a book: 1, 2, 3 … Which type fits best?
booleantextdateinteger
Predict · Activity 2 of 7
Predict before you read on: the column allows two decimal places. What does the SELECT return?
```
CREATE TABLE prices (amount numeric(5,2));
INSERT INTO prices VALUES (12.345);
SELECT amount FROM prices;
```
12.34512.3412.35An error: numeric field overflow
Practice · Activity 3 of 7
Fill in the type so that the column active holds only true or false.
```
CREATE TABLE members (id integer, active ____);
```
CREATE TABLE members (id integer, active Your answer);
Practice · Activity 4 of 7
Match each column to the type that fits it.
Number of copies: 3 — Match for “Number of copies: 3”
Price in euros: 12.99 — Match for “Price in euros: 12.99”
Title, any length — Match for “Title, any length”
Is the book on loan? — Match for “Is the book on loan?”
Day a member joined — Match for “Day a member joined”
Practice · Activity 5 of 7
Put the statements in the order of a script that runs without an error every time, even when books already exists.
1. 1.INSERT INTO books VALUES (1, 'Dune');
2. 2.SELECT * FROM books;
3. 3.DROP TABLE IF EXISTS books;
4. 4.CREATE TABLE books (id integer, title text);
Brain teaser · Activity 6 of 7
Brain teaser. books exists with two columns. Which message follows ERROR: when this runs, or is there none?
```
CREATE TABLE books (id integer, title text);
CREATE TABLE IF NOT EXISTS books (id integer, title text, year integer);
INSERT INTO books (id, title, year) VALUES (1, 'Dune', 1965);
```
no error: the row is addedcolumn "year" of relation "books" does not existrelation "books" already existsno error: the table is replaced
Apply · Activity 7 of 7
Mini-task. Design a table members for a library: an id, a name of at most 40 characters, an e-mail address, the day the member joined and whether the membership is active. Start the script with DROP TABLE IF EXISTS, insert two members with example.com addresses, and list them ordered by id. Then try to insert a date such as 2026-02-30 and read the error.
Check your work against this list
- Starts with DROP TABLE IF EXISTS members;
- Gives each column a fitting type: integer, varchar(40), text, date, boolean
- Writes dates as quoted text in the form 2026-09-30
- Runs without an error and lists both members

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 table with five types

The script drops books if it exists, creates it with five different types and adds three books. Look at how each value comes back: the prices get two decimal places, 7.999 is rounded, and the text 'no' is stored as false. Then change 12.5 to 123456.5 and run it again to meet numeric field overflow. On your computer, run it with psql -X -q -v ON_ERROR_STOP=1 -f main.sql; psql also prints a NOTICE the first time, because there is no books to drop.

main.sql

-- Start clean: remove books if an earlier run left it behind.
DROP TABLE IF EXISTS books;

CREATE TABLE books (
  id integer,
  title varchar(80),
  price numeric(6,2),
  added date,
  on_loan boolean
);

INSERT INTO books VALUES (1, 'Dune', 12.5, '2026-09-01', true);
INSERT INTO books VALUES (2, 'Emma', 7.999, '2026-09-15', false);
INSERT INTO books VALUES (3, 'Kindred', 9, '2026-09-30', 'no');

SELECT * FROM books ORDER BY id;

Run it with

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

Output

 id |  title  | price |   added    | on_loan
----+---------+-------+------------+---------
  1 | Dune    | 12.50 | 2026-09-01 | t
  2 | Emma    |  8.00 | 2026-09-15 | f
  3 | Kindred |  9.00 | 2026-09-30 | f
(3 rows)

numeric(6,2) always shows two decimal places: 12.5 becomes 12.50, 9 becomes 9.00.
7.999 has three decimal places, so it is rounded to 8.00 without an error.
Booleans print as t and f; the text 'no' was accepted as false.
Dates come back in the form 2026-09-01, the form you should also write them in.
DROP TABLE IF EXISTS prints no rows; on an empty database it only issues a notice.

Change it and run it

Your code (main.sql)

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

Give every column a type that fits

The starter creates books with every column as text, so PostgreSQL checks nothing. Change the types: id integer, title varchar(80), price numeric(6,2), added date, on_loan boolean. Leave the INSERT and SELECT statements as they are. When you run it, the prices come back as 12.50 and 8.00, and on_loan as t and f.

Your code (main.sql)

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

Hint 1
Only the CREATE TABLE statement changes: replace each text with the type from the task.
Hint 2
numeric takes two numbers in brackets: all digits, then the digits after the point.
Hint 3
CREATE TABLE books (id integer, title varchar(80), price numeric(6,2), added date, on_loan boolean);

Show a solution

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

CREATE TABLE books (
  id integer,
  title varchar(80),
  price numeric(6,2),
  added date,
  on_loan boolean
);

INSERT INTO books VALUES (1, 'Dune', 12.5, '2026-09-01', true);
INSERT INTO books VALUES (2, 'Emma', 8, '2026-09-15', false);

SELECT * FROM books 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

-- Every column is text. Give each one a type that fits.
CREATE TABLE books (
  id text,
  title text,
  price text,
  added text,
  on_loan text
);

INSERT INTO books VALUES (1, 'Dune', 12.5, '2026-09-01', true);
INSERT INTO books VALUES (2, 'Emma', 8, '2026-09-15', false);

SELECT * FROM books ORDER BY id;

test.sql

-- test: id is an integer column
SELECT format_type(atttypid, atttypmod) = 'integer' FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'id';

-- test: title is varchar(80)
SELECT format_type(atttypid, atttypmod) = 'character varying(80)' FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'title';

-- test: price is numeric(6,2)
SELECT format_type(atttypid, atttypmod) = 'numeric(6,2)' FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'price';

-- test: added is a date column
SELECT format_type(atttypid, atttypmod) = 'date' FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'added';

-- test: on_loan is a boolean column
SELECT format_type(atttypid, atttypmod) = 'boolean' FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'on_loan';

-- test: The script lists both books with typed values
-- output:
--  id | title | price |   added    | on_loan
-- ----+-------+-------+------------+---------
--   1 | Dune  | 12.50 | 2026-09-01 | t
--   2 | Emma  |  8.00 | 2026-09-15 | f
-- (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

Rebuild a table

setup.sql has created an old members table with only id and name. Replace it: drop it with DROP TABLE IF EXISTS, so the script also runs where no members exists, and create members (id integer, name varchar(40), city text, joined date). Add 1, Ada, Berlin, 2025-01-15; 2, Grace, Munich, 2025-03-02; 3, Linus, Berlin, 2026-02-10. The SELECT then lists all three.

Your code (main.sql)

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

Hint 1
CREATE TABLE members … on its own fails: relation "members" already exists. Drop the old table first.
Hint 2
DROP TABLE IF EXISTS members; removes the old table and its row, Ada included.
Hint 3
Then create the new table and add Ada again: INSERT INTO members VALUES (1, 'Ada', 'Berlin', '2025-01-15');

Show a solution

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

DROP TABLE IF EXISTS members;

CREATE TABLE members (
  id integer,
  name varchar(40),
  city text,
  joined date
);

INSERT INTO members VALUES (1, 'Ada', 'Berlin', '2025-01-15');
INSERT INTO members VALUES (2, 'Grace', 'Munich', '2025-03-02');
INSERT INTO members VALUES (3, 'Linus', 'Berlin', '2026-02-10');

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

-- setup.sql has created an old members table (id, name) with Ada in it.
-- Replace it with the new table, then add the three members.

SELECT * FROM members ORDER BY id;

test.sql

-- test: members has the four columns id, name, city and joined
SELECT count(*) = 4 FROM pg_attribute WHERE attrelid = 'members'::regclass AND attnum > 0 AND NOT attisdropped;

-- test: joined is a date column
SELECT format_type(atttypid, atttypmod) = 'date' FROM pg_attribute WHERE attrelid = 'members'::regclass AND attname = 'joined';

-- test: The table holds exactly three members
SELECT count(*) = 3 FROM members;

-- test: Ada is in the table once, now with her city
SELECT (SELECT count(*) FROM members WHERE name = 'Ada') = 1
   AND EXISTS (SELECT 1 FROM members WHERE id = 1 AND city = 'Berlin');

-- test: The script lists the three members
-- output:
--  id | name  |  city  |   joined
-- ----+-------+--------+------------
--   1 | Ada   | Berlin | 2025-01-15
--   2 | Grace | Munich | 2025-03-02
--   3 | Linus | Berlin | 2026-02-10
-- (3 rows)

setup.sql

CREATE TABLE members (
  id integer,
  name text
);

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

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

Creating a table that already exists

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

What psql prints

ERROR:  relation "books" already exists

Why, and the fix

setup.sql (or an earlier run on your own database) has already created books, and CREATE TABLE never replaces a table. To start over, put DROP TABLE IF EXISTS books; before it; that also deletes the rows. CREATE TABLE IF NOT EXISTS would hide the error, but it keeps the old table with its old columns.

A string longer than varchar(n)

CREATE TABLE members (id integer, country varchar(2));
INSERT INTO members VALUES (1, 'DEU');

What psql prints

ERROR:  value too long for type character varying(2)

Why, and the fix

varchar(2) holds at most two characters, and 'DEU' has three. PostgreSQL does not cut the value short; it refuses the row. Store the value the column was designed for ('DE'), or make the limit fit the data: varchar(3), or text when there is no natural limit.

A number too big for numeric(p,s)

CREATE TABLE books (title text, price numeric(4,2));
INSERT INTO books VALUES ('Atlas', 120.00);

What psql prints

ERROR:  numeric field overflow

Why, and the fix

numeric(4,2) has four digits in all, two of them after the point, so only two remain before it: the largest value is 99.99. 120.00 needs three. Choose a precision that leaves room for the largest value you expect, such as numeric(6,2) for prices up to 9999.99.

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.

Key ideas

A type for every column

Each column has one data type, and PostgreSQL checks every value against it. integer holds whole numbers from -2147483648 to +2147483647 and is the common choice; bigint is for larger ones, such as 8100000000. numeric(p,s) is exact: p digits in all, s of them after the point, so numeric(6,2) holds prices up to 9999.99 and stores 7.999 as 8.00. text holds strings of any length; varchar(n) holds at most n characters, and is no faster than text. boolean holds true or false and prints t or f. date holds a calendar day, best written as '2026-09-30'. A column that gets no value is NULL: empty, unknown.

Names, and the life of a table

A name starts with a letter or an underscore, followed by letters, digits or underscores. Lower case with underscores, such as book_loans, is the usual style. Key words such as order or user cannot be table names unless you double-quote them, which is best avoided. CREATE TABLE fails when the table exists: relation "books" already exists. DROP TABLE books removes the table and all its rows. DROP TABLE IF EXISTS books does the same, or only prints a notice when there is no such table, so a script that drops and then creates runs again and again. CREATE TABLE IF NOT EXISTS skips the statement when the name exists, even if that table has other columns.

The type is a guard

A type refuses a value that does not fit, and the ERROR line names the problem. 'DEU' in a varchar(2) column gives value too long for type character varying(2): PostgreSQL does not cut it short. 120.00 in numeric(4,2) gives numeric field overflow, because only two digits fit before the point. '2026-02-30' gives date/time field value out of range: February has no 30th. 3000000000 in an integer column gives integer out of range. Extra decimal places are the exception: numeric(p,s) rounds them to s places, and an integer column stores 2.5 as 3, both without an error. So choose each type for the values you expect.

Sources

Last reviewed September 30, 2026

Creating tables: data types, names and DROP TABLE

Warm-up: a column holds the number of copies of a book: 1, 2, 3 … Which type fits best?

Predict before you read on: the column allows two decimal places. What does the SELECT return?

Fill in the type so that the column active holds only true or false.

Match each column to the type that fits it.

Put the statements in the order of a script that runs without an error every time, even when books already exists.

Brain teaser. books exists with two columns. Which message follows ERROR: when this runs, or is there none?

Build it yourself

A table with five types

Exercises

Give every column a type that fits

Rebuild a table

Common mistakes

Creating a table that already exists

A string longer than varchar(n)

A number too big for numeric(p,s)

Exit ticket

Key ideas

A type for every column

Names, and the life of a table

The type is a guard

Sources