How to perform a SQL Insert

Introduction

This tutorial will cover the basics of performing SQL INSERT operations used to add rows of data to a database table. There are two ways of using the INSERT statement for inserting rows into a table, with the first method specifying only the value of data without the column names and the second way specifying the columns where data will be added and their corresponding values. Typically, it is not necessary to specify the name of the column, or columns, in a query if the values are being added for all columns in the table, provided the order of the values matches the order in the table’s columns. This tutorial will follow a slightly different format, and start off with some examples that will be the most helpful to developers.

The demodb=# Prompt

Note that the prompt used in this section will be demodb=# and the code will be typed in following that prompt.

In this example, begin with the following empty table named DOGS:

1
2
3
id | name | age | breed | gender
----+------+-----+-------+--------
(0 ROWS)

Now insert a new record, a dog named buffy, into the table with the following command:

1
demodb=# INSERT INTO DOGS (name) VALUES ('buffy');

A successful entry will return the following results:

1
INSERT 0 1

Now execute the following query to verify the new row for buffy was actually created:

1
demodb=# SELECT * FROM DOGS;

The output should resemble the following:

1
2
3
4
id | name | age | breed | gender
----+-------+-----+-------+--------
1 | buffy | | |
(1 ROW)

As shown in the above results, the new row for ‘buffy’ was successfully created. Now add another row, but this time the values will be inserted into multiple columns with the following command:

1
demodb=# INSERT INTO DOGS (name, age, breed, gender) VALUES ('spot', 5, 'dalmatian', 'female');

The following output again indicates successfully inserting one row:

1
INSERT 0 1

Now execute the following command to verify the new row was successfully inserted into the DOGS table:

1
demodb=# SELECT * FROM DOGS;

The results should resemble the following:

1
2
3
4
5
id | name | age | breed | gender
----+-------+-----+-----------+--------
1 | buffy | | |
2 | spot | 5 | dalmatian | female
(2 ROWS)

Note that the values don’t necessarily have to be entered in any specific order. To confirm this, perform the following query and note that in this example the values are entered in reverse order from the last example:

1
demodb=# INSERT INTO DOGS (gender, breed, age, name) VALUES ('female', 'dalmation', 5, 'spot');

The output should resemble the following:

1
INSERT 0 1

Now execute the following command to again verify the new row was successfully inserted:

1
demodb=# SELECT * FROM DOGS;

The results of the query should resemble the following:

1
2
3
4
5
6
id | name | age | breed | gender
----+-------+-----+-----------+--------
1 | buffy | | |
2 | spot | 5 | dalmatian | female
3 | spot | 5 | dalmation | female
(3 ROWS)

The row was inserted in the same way, showing that the column order doesn’t matter.

Syntax

While the syntax for the INSERT command was provided in the previous examples, for developers who may not be familiar with it, the basic INSERT command syntax follows:

1
2
INSERT INTO TABLE_NAME (column1, column2, column3, ... columnN)
VALUES (value1, value2, value3, ... valueN);
  • TABLE_NAME is the name of the table.
  • column1, column2, column3, … columnN are the names of the columns where the data is inserted.

  • value1, value2, value3, … valueN are the values inserted into the columns. For example, value1 will go into column1, value2 will go into column2 and so on.

Note These are just a few basic examples and there are a lot more options available for using the INSERT command. Consult the PostgreSQL documentation for more advanced SQL INSERT options.

Output

This section will review the INSET commands used in the previous sections of this tutorial, explaining the INSERT operations used and why.

Example one:

1
demodb=# INSERT INTO DOGS (name, age, breed, gender) VALUES ('spot', 5, 'dalmatian', 'female');

Returned the following results:

1
INSERT 0 1

The format for the return of this INSERT statement is:

1
INSERT oid COUNT
  • count is the number of rows inserted, which was just a single row in this case.
  • oid is a globally unique id for every row, however, these are often set to “off” by default. This is why there was a return of “0” for the oid.

Demo Details

  • PostgreSQL 11 was used in this example.
  • The database name was demodb, which is why the PostgreSQL command prompt was written as demodb=#.

Schema

Following is the SQL command used to create the DOGS table for this demo:

1
2
3
4
5
6
7
CREATE TABLE DOGS(
ID SERIAL PRIMARY KEY,
NAME TEXT,
AGE INT,
BREED TEXT,
GENDER TEXT
);

Conclusion

This tutorial covered the basics of performing SQL INSERT operations used to add rows of data to a database table. The tutorial provided several examples for using the SQL INSERT command, including the demodb=# prompt and demonstrated how to add new single and multiple rows to a table and verify the operations were successful. The tutorial then covered the basic syntax of the SQL INSERT commands used in the examples and a breakdown of the basic INSERT functions. Remember that the values do not have to be entered in a particular order when entering the new values into a table. Hopefully the examples proved in this tutorial will aid in performing new SQL INSERT operations.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.