Mastering PostgreSQL Databases: From Basics to Advanced/

...

/

Data Manipulation Commands

Data Manipulation Commands

Learn how to insert, update, and delete data in PostgreSQL.

We'll cover the following...

The INSERT statement

The most basic form of the INSERT statement is as follows:

INSERT INTO
  <table_name> (<column_1>, <column_2>, ... <column_n>)
VALUES
  (<value_1>, <value_2>, <value_3>, ... <value_n>)
Syntax of INSERT statement

This statement will insert a new row into the <table_name> table. The columns listed in the INSERT INTO portion of the statement are populated with the corresponding values listed in the VALUES portion of the statement. It’s important to note that the column order in the INSERT INTO clause has to match the order of the columns listed in the VALUES clause.

Inserting multiple rows

It’s also possible to insert multiple rows into a table at once using the following syntax:

INSERT INTO <table_name> (<column_1>, <column_2>, .... <column_n>)
VALUES 
    (<value1_1>, <value1_2>, <value1_3>, ... <value1_n>),
    (<value2_1>, <value2_2>, <value2_3>, ... <value2_n>),
    ...
    (<value3_1>, <value3_2>, <value3_3>, ... <value3_n>);
Syntax for inserting multiple rows with single INSERT statement

This statement inserts three rows into the <table_name> table. Each row has values corresponding to the columns listed in the INSERT INTO clause. As before, the order of the columns in the INSERT INTO clause has to match the order of the values in each row of the VALUES clause.

Inserting default values

It’s also possible to insert a row into a table without specifying any values, like so:

INSERT INTO 
  <table_name>
DEFAULT VALUES;
Inserting default values

This statement inserts a new row into the table_name table with default values for each column. This is only useful if all of the columns in the table have a default value specified.

Practice inserting data into a table

Now that we’ve gone over the basics of the INSERT statement, let’s take a look at how to use it to insert data into a table. We’ll be using the following table for our examples:

Employee Data

The id Value

The first_name Value

The last_name Value

The hire_date Value

The salary Value

1

John

Doe

2010-01-01

50000

If we want to insert a new row into this table with the data above, we use the following INSERT statement:

Press + to interact
INSERT INTO
  Employee (id, first_name, last_name, hire_date, salary)
VALUES
  (1, 'John', 'Doe', '2010-01-01', 50000.00);

If we want to insert multiple rows into the table at once, we can use the following INSERT statement:

Press + to interact
INSERT INTO
  Employee (id, first_name, last_name, hire_date, salary)
VALUES
  (2, 'Jane', 'Smith', '2012-04-15', 55000.00),
  (3, 'Jack', 'Jones', '2011-12-03', 40000.00);

Inserting data from another table

It’s also possible to insert data from another table into a table. It can be done using the following syntax:

Press + to interact
INSERT INTO
  Alumni
SELECT
  *
FROM
  Employee;

This INSERT statement will insert all of the rows from the “Employee” table into the “Alumni” table.

Inserting data from a query

It’s also possible to insert data into a table by using the results of a query. It can be done using the following ...