PostgreSQL INSERT Statement

PostgreSQL INSERT Statement

The PostgreSQL INSERT statement is used to add new rows of data into a table. This statement is essential for inserting new records and populating tables with initial data.

Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

The INSERT statement has the following components:

  • table_name: The name of the table where the data will be inserted.
  • column1, column2, column3, ...: The columns in the table where the data will be inserted.
  • value1, value2, value3, ...: The values to be inserted into the specified columns.

Example PostgreSQL INSERT Statement Queries

Let's look at some examples of PostgreSQL INSERT statement queries:

1. Basic INSERT Example

INSERT INTO employees (id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

This query inserts a new row into the employees table. The result will be that the employees table now contains the new row with the specified values.

2. INSERT Multiple Rows

INSERT INTO employees (id, first_name, last_name, email)
VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com'),
       (3, 'Jim', 'Brown', 'jim.brown@example.com');

This query inserts multiple rows into the employees table. The result will be that the employees table now contains the new rows with the specified values.

3. INSERT with Default Values

INSERT INTO employees (first_name, last_name, email)
VALUES ('Alice', 'Johnson', 'alice.johnson@example.com');

This query inserts a new row into the employees table, omitting the id column, which may be set to auto-increment or a default value. The result will be that the employees table now contains the new row with the specified values.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and querying the table.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

In this example, we create a table named employees with columns for id, first_name, last_name, and email.

PostgreSQL INSERT into Table

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');

INSERT INTO employees (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane.smith@example.com');

INSERT INTO employees (first_name, last_name, email)
VALUES ('Jim', 'Brown', 'jim.brown@example.com');

Here, we insert data into the employees table.

PostgreSQL INSERT into Table

Step 3: Querying the Table

This step involves selecting the data from the employees table to view the inserted records.

SELECT * FROM employees;

This query retrieves all the rows from the employees table. The result will be:

PostgreSQL INSERT into Table

Conclusion

The PostgreSQL INSERT statement is a fundamental tool for adding new rows of data into a table. Understanding how to use the INSERT statement and its syntax is essential for effective data management and manipulation in PostgreSQL databases.