How to insert rows into a table in PostgreSQL

Inserting Rows into a PostgreSQL Table

The primary SQL command for inserting new rows is the INSERT statement. The simplest form of this statement requires specifying the target table and the values to insert. Here is a basic example:

INSERT INTO table_name (column1, column2, column3)

VALUES (value1, 'value2', value3);

In this syntax, table_name refers to the name of the table where data will be inserted. column1, column2, and column3 represent the columns within the table where the data is to be placed. value1, 'value2', and value3 are the data values for these columns, respectively. Text values are enclosed in single quotes. Make sure to use single quotes for text values, and do not use quotes for numeric values.

Bulk Insert

PostgreSQL supports inserting multiple rows with a single INSERT statement. This approach is efficient for adding large amounts of data in one operation:

INSERT INTO table_name (column1, column2, column3)

VALUES (value1_1, 'value1_2', value1_3),

   (value2_1, 'value2_2', value2_3),

   (value3_1, 'value3_2', value3_3);

This method reduces the number of commands sent to the database, helping to minimize network traffic and the overhead of processing multiple transactions.

Inserting Data from Another Table

PostgreSQL enables the insertion of data into one table directly from another using a SELECT statement. This feature is useful for copying or reorganizing data:

INSERT INTO target_table (column1, column2, column3)

SELECT source_column1, source_column2, source_column3

FROM source_table

WHERE condition;

In this structure, target_table is the destination for the data, and column1, column2, and column3 are the specific columns where the data will be inserted. The SELECT statement fetches the data to be inserted: source_column1, source_column2, and source_column3 indicate the source columns from source_table. The WHERE clause, which is optional, filters the rows that will be copied from source_table to target_table.

This method is efficient for transferring data between tables, especially when filtering or transforming the data as part of the insertion process.