Practical PostgreSQL

Adding Data with INSERT and COPY

Once you have created your table with the necessary specifications, the next logical step is to fill the table with data. There are generally three methods in PostgreSQL with which you can fill a table with data:

• Use the INSERT INTO command with a grouped set of data to insert new values.

• Use the INSERT INTO command in conjunction with a SELECT statement to insert existing values from another table.

• Use the COPY (or \copy) command to insert values from a system file.

Inserting New Values

The following is the syntax of the INSERT INTO command, when used to insert new values, which is subsequently described in detail:

INSERT INTO table_name
       [ ( column_name [, ...] ) ]
       VALUES ( value [, ...] )

table_name

The INSERT SQL command initiates an insertion of data into the table called table_name.

( column_name [, ...] )

An optional grouped expression which describes the targeted columns for the insertion.

VALUES

The SQL clause which instructs PostgreSQL to expect a grouped expression of values to follow.

( value [, ...] )

The required grouped expression that describes the values to be inserted. There should be one value for each specified column, separated by commas. These values may be expressions themselves (e.g., an operation between two values), or constants.

Each value following the VALUES clause must be of the same data type as the column it is being inserted into. If the optional column-target expression is omitted, PostgreSQL will expect there to be one value for ...