July 11, 2019
How do you insert multiple values? I always find myself looking this up. I decided I’d jot it down here for future reference. Hopefully it helps you.
In their book Practical PostgreSQL, John C. Worsley and Joshua D. Drake write the following pertaining inserting values in Postgres:¹
The following is the syntax of the
INSERT INTOcommand, when used to insert new values, which is subsequently described in detail:
INSERT INTO *table_name*
[ ( *column_name* [, …] ) ]
VALUES ( *value* [, …] )
INSERT SQLcommand initiates an insertion of data into the table called
(column_name[, …] )An optional grouped expression which describes the targeted columns for the insertion.
VALUESThe 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 onevaluefor each specified column, separated by commas. These values may be expressions themselves (e.g., an operation between two values), or constants.
VALUESclause 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 each column in the literal order of the table’s structure. If there are fewer values to be inserted than columns, PostgreSQL will attempt to insert a default value (or the
NULLvalue, if there is no default) for each omitted value.
The description alludes to the fact that the values can have multiple, but the demonstration doesn’t show how and the examples left me wanting. This is not a knock on the authors. The book’s excellent. It’s more my continuing struggles reading documentation and parsing out what’s not explicit.
So, as I tend to do, I came up with the following example to make it painfully obvious:
CREATE TABLE sample_table ( id SERIAL PRIMARY KEY, name TEXT, favorite_color TEXT, age INT ); INSERT INTO sample_table (name, favorite_color, age) VALUES ('stephen', 'blue', 29), ('john', 'red', 18), ('bernard', 'yellow', 40);
Punch line: Just as you would to insert one value, you do with multiple, except that they’re comma separated as part of the
VALUES clause. I put together SQL Fiddle demonstrating INSERT with multiple values.
Thanks for reading! My name's Stephen Weiss. I live in Chicago with my wife, Kate, and dog, Finn.
Click here to see the archives of my weeks in review and sign up yourself!