Site icon JD Bots

What Is SQL?

Featured card for the guide What Is SQL?, showing a database above a small table with name, kind and age columns.

What Is SQL?

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;

Three rules apply to every statement:

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

MistakeWhat happensDo this instead
Text without quotes, as in VALUES (Rex, 'dog', 3)ERROR: column "rex" does not existPut 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 existUse single quotes for text. Double quotes mark a name, so PostgreSQL looks for a column called Tom.
Leaving out the semicolonNothing runs, and the prompt changes to petshop-#Type ; and press Return to finish the statement.
Dropping the database you’re connected toERROR: cannot drop the currently open databaseSwitch to another database first with \c postgres.
UPDATE or DELETE without WHEREEvery row in the table changes or is deletedAdd 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

Related

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

Exit mobile version