Creating (Inserting) Data in PostgreSQL

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.

We use cookies and similar technologies to enhance the quality of services, maintain statistics and adjust marketing content. You will find more information in the Cookies Policy.

By clicking OK you grant consent to processing of your personal data by us and our Trusted Partners with the purpose of maintain statistics and adjustment of the marketing content pursuant to the Privacy Policy. If you wish to not grant that consent and/or limit its extent click Settings.