How to Insert Data Into A PostgreSQL Table
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
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
false value, but it also accepts
no) when data is inserted.
TEXT — These datatypes are used for strings and text, which encompasses a wide range of ASCII characters.
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
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
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:
VALUES ('string with single quotes', 42);
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:
VALUES ('Joe''s crazy STRINGS!!', false);
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:
VALUES ('strings', 123456),
('mo'' strings!!', 42),
('str string STRING', 6543),
('LAST STRING!', 345);
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started