How to Insert Data Into A PostgreSQL Table

Introduction

If you’re getting started with PostgreSQL, one of the first things you’ll want to do is insert some data into tables. Fortunately, this task is easy to accomplish using the INSERT command. In this article, we’ll show you how to connect to PostgreSQL using the psql command and insert data into a PostgreSQL table with the INSERT statement.

Prerequisites

There are only a couple of basic prerequisites that need to be in place in order to follow along with this tutorial. You’ll need to have PostgreSQL installed on your machine, and you’ll need to be able to use the psql command in a terminal or command prompt window. This command allows you to access a PostgreSQL database in order to insert data.

Once connected to the database, you’ll use the INSERT INTO command to insert data row(s) into a table.

Overview of PostgreSQL datatypes

We’ll begin our discussion with a brief overview of the various PostgreSQL datatypes:

BOOLEAN — This datatype must have a true or false value, but it also accepts t or f (or yes and no) when data is inserted.

VARCHAR(NUM), CHAR(NUM), and TEXT — These datatypes are used for strings and text, which encompasses a wide range of ASCII characters.

INT, INTEGER, BIGINT, SERIAL, DECIMAL(NUM), FLOAT(NUM), etc.. — These are just a few of the numeric datatypes used for floats and integers.

In addition to these commonly-used datatypes, there are other types such as UUID for unique identifiers of a specified length, and JSON for data that adheres to the JavaScript object-notation syntax.

NOTE: You’ll notice that some datatypes include (NUM)— this represents an integer for the precision character length that is passed while creating the table.

Exceeding the datatype length specified for the table

If the data you’re inserting exceeds the length specified, you’ll get the following error:

The columns specified must match the PostgreSQL table’s columns

If the data you’re inserting doesn’t match the datatype specified by the table, then you may receive an error like this one:

You’ll also receive an error if you try to insert data into a column that doesn’t exist. In this example, the error was returned after an attempt was made to insert a Boolean value into a table that did not have a column named bool_col.

All PostgreSQL command must end with a semi-colon

Be sure to use a semi-colon (;) to let PostgreSQL know that a command has ended, or it will assume that you are executing a multi-line command.

Connect to PostgreSQL using the ‘psql’ command in terminal or command prompt

Now that we’ve discussed the basic PostgreSQL datatypes and looked at some common errors that occur upon insertion, we’re ready to connect to the database. To do this, we’ll use the psql command in a terminal window. The -h option specifies the domain host (IP address), and the -d option specifies the PostgreSQL database name:

Create a table for the PostgreSQL data

If you haven’t yet created a table for your data, you can do so now with the following statement:

NOTE: Be sure to specify the datatype for each column, and specify the data length when necessary.

Using the ‘INSERT’ command to add data to a PostgreSQL table

Avoid inserting string data enclosed in double-quotation marks

When you insert string data into a PostgreSQL table, avoid using double quotes to enclose the data. The PostgreSQL interpreter recognizes double quotes (") as identifiers for a table or database, so if you use them for your string data, it will return an error like the following:

Instead, use single-quotes ('), as seen in the following example:

NOTE: You can use two single quotation marks (e.g. '') if you’d like to add an apostrophe to a string value. The first single quote serves to escape the second one, which acts as the apostrophe.

As you can see, quotes can be a bit tricky to handle when you’re inserting data in a PostgreSQL table. Let’s look at an example that shows the different ways quotes can be used. The following command will insert the string “Joe's crazy STRINGS!!” into the table:

Inserting multiple data rows into a PostgreSQL table

If you’d like to add more than one row of data while using the INSERT INTO command, you can simply use multiple sets of data enclosed in parentheses, with each row delimited by a comma:

Conclusion

Being able to insert data into a table is a key skill to have when you’re working with PostgreSQL. In this article, we reviewed some of the common datatypes used in PostgreSQL, and we looked at several examples of inserting rows into a table. With the instructions provided in this article, you’ll be able to connect to your database using psql and insert data into a PostgreSQL table of your own.