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:
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.
This post is part of the longer series, Using PostgreSQL with Go, where we walk through all of the basics required to setup and start using PostgreSQL with Go.
If you want to stay up to date when new articles are released, or if you are interested in other articles about web development and Go you should sign up for my mailing list. Not only will I let you know when I release a new post, but I'll also send you two free chapters from my upcoming book, Web Development with Go. Oh, and I promise I don't spam :)
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.
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:
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, 'firstname.lastname@example.org', '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;
While we won’t go into the details of the
SELECT statement in this article, I did at least want to briefly demonstrate how to view the data we are creating, updating, and deleting. In the next article in this series we will dig into different ways to query using
SELECT and various search criteria.
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 | email@example.com (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
INSERT INTO users VALUES (2, 22, 'John', 'Smith', 'firstname.lastname@example.org');
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.
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 - .
Sign up for my mailing list and I'll send you a FREE sample from my course - Web Development with Go. The sample includes three chapters from the book, and over 2.5 hours of screencasts.
You will also receive notifications when I release new articles, along with other freebies that I only share with my mailing list.
Jon Calhoun is a full stack web developer who also teaches about Go, web development, algorithms, and anything programming related. He also consults for other companies who have development needs. (If you need some development work done, get in touch!)
Jon is a co-founder of EasyPost, a shipping API that many fortune 500 companies use to power their shipping infrastructure, and prior to founding EasyPost he worked at google as a software engineer.
More in this series
This post is part of the series, Using PostgreSQL with Go.
Spread the word
Did you find this page helpful? Let others know about it!
Sharing helps me continue to create both free and premium Go resources.
Want to discuss the article?
©2018 Jonathan Calhoun. All rights reserved.