SQL ET POSTGRE 4.17 : Inserting data into a table

In this video we learn how to insert data into a table. We open the test database and create a new query script. The SQL statement starts with INSERT INTO, followed by the table name. In parentheses we list the target columns, then we add VALUES and another pair of parentheses containing the values to insert. Strings go between single (or double) quotes; integers do not need any. Each value matches the column at the same position.

A first insertion

We have an exemple table with the columns exemple_id, prenom, nom, email, nationalite, age. The exemple_id column is a SERIAL so we do not include it in the column list — it will be incremented automatically. We insert Vanessa Dupont, her email, the nationality code FR and the age 42. We run the query, it returns successfully, and a SELECT * FROM exemple confirms the row is in place with exemple_id = 1. 
INSERT INTO exemple (prenom, nom, email, nationalite, age)
VALUES
  ('Mike',   'Brown',   'mike@example.com',   'GB', 27),
  ('Cruz',   'Hernandez', 'cruz@gmail.com',   'ES', 34),
  ('Anke',   'Jurgen',  'anke@example.com',   'DE', 57),
  ('John',   'Douglas', 'john@example.com',   'US', 39);
  • List target columns (skip SERIAL ids).
  • Use VALUES followed by one or more parenthesised tuples.
  • Strings between quotes, integers without.
  • Separate multiple rows with commas, end with a semicolon.
We can also insert several rows in a single statement by separating each VALUES tuple with a comma. We add Mike from Great Britain, Cruz from Spain, Anke from Germany and John from the United States in one query. Running it and re-selecting the table shows the four new rows, each with their auto-generated exemple_id. To recap: INSERT INTO <table> (columns...) VALUES (values...) — strings get quotes, integers do not, the SERIAL id is filled in automatically.

Summary

This lesson teaches how to insert data into a SQL table using the INSERT INTO statement in PostgreSQL. The tutorial covers the syntax: specifying the table name, listing column names (excluding auto-increment SERIAL columns), and providing values in a VALUES clause. The lesson demonstrates both single-row and multi-row insertions with practical examples from a test database, showing how to verify successful insertions using SELECT queries.

Key points

  • INSERT INTO syntax requires the table name followed by column names in parentheses, excluding SERIAL auto-increment columns
  • String values must be enclosed in single or double quotes; numeric values (integers) are entered without quotation marks
  • Multiple rows can be inserted in a single query by separating each row's value set with commas
  • SERIAL columns automatically increment and should be excluded from the INSERT statement
  • Always verify insertions by running a SELECT query to confirm the data was added correctly

FAQ

Why do we exclude the auto-increment column (example_id) from the INSERT statement?

The example_id column is defined as SERIAL, meaning it automatically increments with each new row. We only need to specify columns for which we're providing explicit values. The auto-increment value is generated automatically by PostgreSQL.

What is the difference between how string values and integer values are formatted in an INSERT statement?

String values must be enclosed in either single or double quotes to indicate they are text. Integer values are entered without any quotation marks, just the numeric digits.

How can we insert multiple rows into a table in a single SQL query?

You can insert multiple rows by listing each set of values in parentheses and separating them with commas. For example: VALUES ('col1', 'col2'), ('col3', 'col4'), ('col5', 'col6'). All rows are inserted with one INSERT statement.