Creating (Inserting) Data

Tomasz Bogusz

What is Creating (Inserting) Data in PostgreSQL?

Creating (inserting) data in Postgres means adding a new row or rows to a database table providing literal (constant) or scalar expression values for cells of the row or rows and/or instructing Postgres to supplement the unprovided values with the default column values.

In turn, adding a new column to a table is not denoted as creating (inserting) data but as altering that table.

The Insert Command

To create a new row (to insert a new row) use the SQL INSERT INTO command appended with the:

  • required table name,

  • optional column order (if no such order is provided values are inserted in the table column order),

  • required values prepended with the VALUES key word.

INSERT INTO users VALUES ('test@example.net', 'John', 'Doe');

INSERT INTO users (email, first_name, last_name) VALUES ('test@example.net', 'John', 'Doe');

INSERT INTO users (last_name, email) VALUES 'test@example.net', 'Doe';

Default Values

It is possible to instruct Postgres to use default values for unprovided row cell values:

  • implicitly without using the DEFAULT key word and simply not provided a value for a given cell,

  • explicitly with using the DEFAULT key word.

For example, for a table users with the first_name, last_name columns and a column role with the default 'client' value we can create a new row with the said default in the following manners.

INSERT INTO users (first_name, last_name, role) VALUES ('John', 'Doe');
INSERT INTO users (first_name, last_name, role) VALUES ('John', 'Doe', DEFAULT);
INSERT INTO users DEFAULT VALUES;

Inserting Multiple Rows

It is possible to insert multiple rows simultaneously.

INSERT INTO users (first_name, last_name)
VALUES ('John', 'Doe'), ('Geralt', 'of Rivia'), ('Jean-Luc', 'Picard');

Inserting Subquery Result

As a SELECT query result consists of literal values it can be used as values for the INSERT INTO command.