What is SQL? It’s the language you use to talk to a database. You type a statement, and PostgreSQL carries it out and shows you the result.
Every command and output on this page is real, from PostgreSQL 19 on macOS.
What Is SQL Used For?
SQL is the standard language for working with data in a relational database such as PostgreSQL. With it, you create tables, add data to them, read it back, change it and delete it.
A relational database keeps its data in tables. Each table has named columns, and each row holds one item, with a value in every column, as the PostgreSQL documentation explains. A table of pets, for example, can have a name, a kind and an age column, with one row per pet.
In SQL, you describe the result you want, not the steps to get it, and PostgreSQL’s planner works out how to find the rows. This page shows what SQL looks like, then runs the five statements you’ll use most on that table of pets.
Syntax
Most everyday work uses five SQL statements. In their simplest form, they look like this:
Syntax
CREATE TABLE table_name (column_name data_type, ...);
INSERT INTO table_name (column_name, ...) VALUES (value, ...);
SELECT column_name, ... FROM table_name WHERE condition;
UPDATE table_name SET column_name = value WHERE condition;
DELETE FROM table_name WHERE condition;
CREATE TABLE: makes a new table. Each column gets adata_type, such astextfor words orintegerfor whole numbers.INSERT: adds rows. Eachvaluegoes into the column in the same position.SELECT: reads rows. A*in place of the column names means every column.UPDATE: changes values in rows that already exist.DELETE: removes rows.WHERE condition: limits the statement to the rows that match, such askind = 'cat'.
Three rules apply to every statement:
- It ends with a semicolon (
;). - Key words such as
SELECTwork in any case. Writing them in capitals and names in lowercase is a common convention that keeps statements easy to read. - Text goes in single quotes, as in
'cat'. Double quotes are for names, not text.
The PostgreSQL documentation describes these rules in SQL Syntax, and every statement in SQL Commands.
Step 1: Create a Practice Database
This guide works in a small database named petshop, so your other data stays untouched. Connect with psql, as Part 2 of the PostgreSQL Tutorial shows, then run:
postgres=#
CREATE DATABASE petshop;
Output
CREATE DATABASE
Then switch to it with \c, psql’s command for switching databases:
postgres=#
\c petshop
Output
You are now connected to database "petshop" as user "postgres".
Commands that start with a backslash, such as \c, are psql’s own commands, not SQL. Everything else on this page is SQL.
Step 2: Create a Table with CREATE TABLE
petshop=#
CREATE TABLE pets (name text, kind text, age integer);
Output
CREATE TABLE
The pets table now exists, with three columns: name and kind hold text, and age holds whole numbers. It has no rows yet.
Step 3: Add Rows with INSERT
petshop=#
INSERT INTO pets (name, kind, age)
VALUES ('Rex', 'dog', 3), ('Tom', 'cat', 5), ('Goldie', 'fish', 1);
Output
INSERT 0 3
INSERT 0 3 means three rows were added. When you press Return after the first line, the prompt changes to petshop-#: psql is waiting for the rest of the statement, and runs it when it reaches the semicolon.
Step 4: Read Rows with SELECT
petshop=#
SELECT * FROM pets;
Output
name | kind | age
--------+------+-----
Rex | dog | 3
Tom | cat | 5
Goldie | fish | 1
(3 rows)
Each line of the result is one row, and (3 rows) counts them. To read only some columns and rows, name the columns and add WHERE:
petshop=#
SELECT name, age FROM pets WHERE kind = 'cat';
Output
name | age
------+-----
Tom | 5
(1 row)
Step 5: Change a Row with UPDATE
petshop=#
UPDATE pets SET age = 4 WHERE name = 'Rex';
Output
UPDATE 1
UPDATE 1 means one row changed. Read the table again to see it:
petshop=#
SELECT * FROM pets;
Output
name | kind | age
--------+------+-----
Tom | cat | 5
Goldie | fish | 1
Rex | dog | 4
(3 rows)
Rex is now 4. He also moved to the end: without ORDER BY, PostgreSQL returns rows in no fixed order.
Warning: Always add WHERE to UPDATE. Without it, every row in the table changes.
Step 6: Remove a Row with DELETE
petshop=#
DELETE FROM pets WHERE name = 'Goldie';
Output
DELETE 1
petshop=#
SELECT * FROM pets;
Output
name | kind | age
------+------+-----
Tom | cat | 5
Rex | dog | 4
(2 rows)
Goldie is gone, and two pets remain.
Warning: DELETE without WHERE deletes every row in the table, so check the condition before you press Return.
Clean Up
To remove the practice database, first switch back to the postgres database, because PostgreSQL can’t delete the database you’re connected to:
petshop=#
\c postgres
Output
You are now connected to database "postgres" as user "postgres".
postgres=#
DROP DATABASE petshop;
Output
DROP DATABASE
Warning: DROP DATABASE deletes the database and everything in it at once. There’s no undo, so check the name before you press Return.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
Text without quotes, as in VALUES (Rex, 'dog', 3) | ERROR: column "rex" does not exist | Put text in single quotes: 'Rex'. Without them, PostgreSQL reads Rex as a column name. |
Text in double quotes, as in name = "Tom" | ERROR: column "Tom" does not exist | Use single quotes for text. Double quotes mark a name, so PostgreSQL looks for a column called Tom. |
| Leaving out the semicolon | Nothing runs, and the prompt changes to petshop-# | Type ; and press Return to finish the statement. |
| Dropping the database you’re connected to | ERROR: cannot drop the currently open database | Switch to another database first with \c postgres. |
UPDATE or DELETE without WHERE | Every row in the table changes or is deleted | Add WHERE, and check it before you press Return. |
FAQ
Do SQL key words have to be in capitals?
No. SELECT, select and Select all work. Capitals are a convention that makes key words stand out from table and column names.
Is psql the same as SQL?
No. SQL is the language. psql is PostgreSQL’s command-line program for typing SQL and reading the results, and it adds its own backslash commands, such as \c.
Is SQL the same in every database?
The basics are the same, because SQL is an international standard, ISO/IEC 9075. PostgreSQL supports most of it: of the 177 features that SQL:2023 requires for full Core conformance, it supports at least 170, as its SQL Conformance page says. Where PostgreSQL differs from the standard or adds extras, each statement’s page in the documentation says so in its Compatibility section.
Conclusion
SQL is the language for working with data in a relational database. Five statements cover most everyday work: CREATE TABLE makes a table, INSERT adds rows, SELECT reads them, UPDATE changes them and DELETE removes them. End each statement with a semicolon, put text in single quotes, and always add WHERE to UPDATE and DELETE.
Additional Resources
- PostgreSQL 19 documentation: The SQL Language
- PostgreSQL 19 documentation: SQL Syntax
- PostgreSQL 19 documentation: SQL Commands
- PostgreSQL 19 documentation: SQL Conformance
Related
- PostgreSQL Tutorial — Part 1: What Is PostgreSQL? Databases, Tables and SQL Explained
- PostgreSQL Tutorial — Part 2: Connect with psql and Run Your First Commands
- How to Run a SQL File in psql
- How to Switch Databases in psql
Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.
