1. Basic Insert Syntax
The
INSERT INTO
statement is used to add new rows of data to a table. The general structure is:INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
table_name
: The name of the table where you are adding data.
column1, column2, column3
: The columns in which you want to insert values.
value1, value2, value3
: The values to be inserted into those columns.
2. Example: Inserting Data into a Table
Let’s take the
street
table as an example, which contains the following columns:name
(text)
city
(text)
description
(text)
postal_code
(text)
house_id
(integer, required)
The following query inserts a new record into the
street
table:INSERT INTO public.street (name, city, description, postal_code, house_id) VALUES ('Wall Street', 'New York', 'Known for being the financial district of New York City', '10005', 10);
This query will add a new row with the following values:
name
: Wall Street
city
: New York
description
: Known for being the financial district of New York City
postal_code
: 10005
house_id
: 10
3. Inserting Multiple Rows
Just like before, you can insert multiple rows in one statement:
INSERT INTO public.street (name, city, description, postal_code, house_id) VALUES ('Broadway', 'New York', 'Famous for its theaters', '10036', 101), ('Fifth Avenue', 'New York', 'Renowned for shopping', '10022', 102);
This will insert two rows into the
street
table at once.Summary
The
INSERT INTO
statement is a powerful way to add data to your PostgreSQL tables. In this example, we inserted information about "Wall Street" into the street
table, with flexibility for handling NULL
values. By understanding the syntax and structure, you can efficiently insert records into your database.