Inserting records into a PostgreSQL database using SQL

SQL commands can be broken into roughly three categories: DDL (Data Definition Language), DML (Data Manipulation Language), and DCL (Data Control Language).

DDL is a set of commands used to define the overall structure or schema of how your data is going to be stored. In other words, you would use DDL to create a table, update the fields in an existing table, or any other operation where you are changing the structure of how you store data, but not actually changing the data itself.

DML is a set of commands used to actually manipulate data stored in your database. This includes looking up records in your table, inserting new data into a table, deleting records, etc. This is where you will spend a large portion of your time when dealing with SQL, and can be further broken into roughly four main categories:

1. Retrieving data
2. Creating data
3. Updating data
4. Deleting data

DCL is a set of commands used to dictate which users have permissions to do different things inside of your database. For example you might set up a user so that it has permission to read data in your database but it doesn’t have permission to create new records.

In this post we will be focusing on the DML; Specifically, we will be looking at how to create records using the INSERT commands in SQL, and we will briefly use the SELECT command to look at all of the data in our table after we create a record.

We are going to stick with just inserting data in this post because all of the other operations generally require some knowledge of using the WHERE clause to specify which specific rows you want to interact with. For example, you likely don’t want to update every record in your database, but instead need a way to specify which records to update.

In the next article in this series we will then start to cover using the SELECT statement along with WHERE clauses to find specific data, and finally we will start exploring how to update and delete records using conditional clauses.

SQL insert statement

The SQL INSERT statement, sometimes referred to as INSERT INTO, is how we go about inserting new records into a table in our database. There are basically two ways to use the command:

1. By providing the columns that you want to set values for, and then values for each of those columns.
2. By providing values for every column in the table in the same order as the columns of the table.

In a previous article, Creating PostgreSQL databases and tables with raw SQL, we created the following table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  age INT,
  first_name TEXT,
  last_name TEXT,
  email TEXT UNIQUE NOT NULL
);

We will continue using this table for our examples, and we will start off with the first use case where we are providing a list of columns that we have data for and values for each of those fields. Open up psql if you haven’t already and connect to your database. I am going to assume that you are using the role postgres with a database named calhounio_demo like we created earlier in .

# In your terminal
psql -U postgres -d calhounio_demo

After psql opens, run the following.

INSERT INTO users (age, email, first_name, last_name)
VALUES (30, 'jon@calhoun.io', 'Jonathan', 'Calhoun');

You should see the output INSERT 0 1 after inserting this row. If you happen to see something like ERROR: duplicate key value violates unique constraint "users_email_key" this means you likely already inserted a record with the same email address you provided.

If you would like to see the data you just inserted into your table, as well as the auto-incrementing id, you can do so by running the following SQL.

SELECT * FROM users;

You should see an output that looks something like mine below. Don’t worry if your id column doesn’t match mine. The important part is that you should have an id value automatically set by the database. If that isn’t the case, something isn’t setup correctly with your table.

 id | age | first_name | last_name |     email
----+-----+------------+-----------+----------------
  1 |  30 | Jonathan   | Calhoun   | jon@calhoun.io
(1 row)

We have successfully created a new record! Now let’s look at how we would create a new record without specifying which fields we intend to provide values for.

Remember, when we create a record without specifying the columns we are providing values for we need to provide values for every column, and we need to do it in the same order the are on the table. In this case, that order would be id, age, first_name, last_name, email. With that in mind, we are going to create a second record for a new user. Type the following SQL into your psql session.

INSERT INTO users
VALUES (2, 22, 'John', 'Smith', 'john@smith.com');

So there are two things worth noting before we move on. The first is that the newlines don’t matter. We could have just as easily written this on one line and it would have worked.

The second, and more important tidbit, is that by inserting without specifying which columns we want to provide values with, we also need to provide a value for the id column. That means we can’t just let the Postgres table handle that for us, which is what we wanted in the first place.

Because of this, we will be pretty much exclusively be specifying which columns we want to insert values for throughout the rest of this series.

In summary…

You should now have a pretty solid understanding for how to insert data into an SQL table using the INSERT command. While this is a pretty simple operation, it is crucial to understand before moving on to querying and updating data in your database, as many of those commands will be very similar to what you are seeing here, but with more complexity added to them. For example, we will continue using the VALUES keyword while writing SQL to update various rows of our database.

Ready to move on to querying? Check out the next article in the series - Querying for records stored in a PostgreSQL table using SQL. You can also check out an overview of the entire series, along with links to each article in the series, by going here - .