Adding New Records to a Table

The two previous sections showed you how to create some simple tables and how to view the table definitions. Now let's see how to insert data into these tables.

Using the INSERT Command

The most common method to get data into a table is by using the INSERT command. Like most SQL commands, there are a number of different formats for the INSERT command. Let's look at the simplest form first:

INSERT INTO table VALUES ( expression [,...] );

A Quick Introduction to Syntax Diagrams

In many books that describe a computer language (such as SQL), you will see syntax diagrams. A syntax diagram is a precise way to describe the syntax for a command. Here is an example of a simple syntax diagram:

INSERT INTO table VALUES ( expression [,...] );

 

In this book, I'll use the following conventions:

• Words that are presented in uppercase must be entered literally, as shown, except for the case. When you enter these words, it doesn't matter if you enter them in uppercase, lowercase, or mixed case, but the spelling must be the same. SQL keywords are traditionally typed in uppercase to improve readability, but the case does not really matter otherwise.
• A lowercase italic word is a placeholder for user-provided text. For example, the table placeholder shows where you would enter a table name, and expression shows where you would enter an expression.
• Optional text is shown inside a pair of square brackets ([]). If you include optional text, don't include the square brackets.
• Finally, ,... means that you can repeat the previous component one or more times, separating multiple occurrences with commas.

So, the following INSERT commands are (syntactically) correct:

INSERT INTO states VALUES ( 'WA', 'Washington' );
INSERT INTO states VALUES ( 'OR' );

 

This command would not be legal:

INSERT states VALUES ( 'WA' 'Washington' );

 

There are two problems with this command. First, I forgot to include the INTO keyword (following INSERT). Second, the two values that I provided are not separated by a comma.

When you use an INSERT statement, you have to provide the name of the table and the values that you want to include in the new row. The following command inserts a new row into the customers table:

INSERT INTO customers VALUES
(
 1,
 'William Rubin',
 '555-1212',
 '1970-12-31',
 0.00
);

This command creates a single row in the customers table. Notice that you did not have to tell PostgreSQL how to match up each value with a specific column: In this form of the INSERT command, PostgreSQL assumes that you listed the values in column order. In other words, the first value that you provide will be placed in the first column, the second value will be stored in the second column, and so forth. (The ordering of columns within a table is defined when you create the table.)

If you don't include one (or more) of the trailing values, PostgreSQL will insert default values for those columns. The default value is typically NULL.

Notice that I have included single quotes around some of the data values. Numeric data should not be quoted; most other data types must be. In Chapter 2, I'll cover the literal value syntax for each data type.

In the second form of the INSERT statement, you include a list of columns and a list of values:

INSERT INTO table ( column [,...] ) VALUES ( expression [,...] );

Using this form of INSERT, I can specify the order of the column values:

INSERT INTO customers
(
 customer_name, birth_date, phone, customer_id, balance
)
VALUES
(
 'William Rubin',
 '1970-12-31',
 '555-1212',
 1,
 0.00
);

As long as the column values match up with the order of the column names that you specified, everybody's happy.

The advantage to this second form is that you can omit the value for any column (at least any column that allows NULLs). If you use the first form (without column names), you can only omit values for trailing columns. You can't omit a value in the middle of the row because PostgreSQL can only match up column values in left to right order.

Here is an example that shows how to INSERT a customer who wasn't willing to give you his date of birth:

INSERT INTO customers
(
 customer_name, phone, customer_id, balance
)
VALUES
(
 'William Rubin',
 '555-1212',
 1,
 0.00
);

This is equivalent to either of the following statements:

INSERT INTO customers
(
 customer_name, birth_date, phone, customer_id, balance
)
VALUES
(
 'William Rubin',
 NULL,
 '555-1212',
 1,
 0.00
);

or

INSERT INTO customers VALUES
(
 1,
 'William Rubin',
 '555-1212',
 NULL,
 0.00
);

There are two other forms for the INSERT command. If you want to create a row that contains only default values, you can use the following form:

INSERT INTO table DEFAULT VALUES;

Of course, if any of the columns in your table are unique, you can only insert a single row with default values.

The final form for the INSERT statement allows you to insert one or more rows based on the results of a query:

INSERT INTO table ( column [,...] ) SELECT query;

I haven't really talked extensively about the SELECT statement yet (that's in the next section), but I'll show you a simple example here:

INSERT INTO customer_backup SELECT * from customers;

This INSERT command copies every row in the customers table into the customer_backup table. It's unusual to use INSERT...SELECT... to make an exact copy of a table (in fact, there are easier ways to do that). In most cases, you will use the INSERT...SELECT... command to make an altered version of a table; you might add or remove columns or change the data using expressions.

Using the COPY Command

If you need to load a lot of data into a table, you might want to use the COPY command. The COPY command comes in two forms. COPY ... TO writes the contents of a table into an external file. COPY ... FROM reads data from an external file into a table.

Let's start by exporting the customers table:

COPY customers TO '/tmp/customers.txt';

This command copies every row in the customers table into a file named '/tmp/customers.txt'. Take a look at the customers.txt file:

1 Jones, Henry 555-1212 1970-10-10 0.00
2 Rubin, William 555-2211 1972-07-10 15.00
3 Panky, Henry 555-1221 1968-01-21 0.00
4 Wonderland, Alison 555-1122 1980-03-05 3.00

If you compare the file contents with the definition of the customers table:

movies=# d customers
 Table "customers"
 Attribute | Type | Modifier
--------------+-----------------------+----------
 customer_id | integer |
 customer_name| character varying(50) |
 phone | character(8) |
 birth_date | date |
 balance | numeric(7,2) |
Index: customers_customer_id_key

You can see that the columns in the text form match (left to right) with the columns defined in the table: The leftmost column is the customer_id, followed by customer_name, phone, and so on. Each column is separated from the next by a tab character and each row ends with an invisible newline character. You can choose a different column separator (with the DELIMITERS 'delimiter' option), but you can't change the line terminator. That means that you have to be careful editing a COPY file using a DOS (or Windows) text editor because most of these editors terminate each line with a carriage-return/newline combination. That will confuse the COPY ... FROM command when you try to import the text file.

The inverse of COPY ... TO is COPY ... FROM. COPY ... FROM imports data from an external file into a PostgreSQL table. When you use COPY ... FROM, the format of the text file is very important. The easiest way to find the correct format is to export a few rows using COPY ... TO, and then examine the text file.

If you decide to create your own text file for use with the COPY ... FROM command, you'll have to worry about a lot of details like proper quoting, column delimiters, and such. Consult the PostgreSQL reference documentation for more details.