Once you have created your table with the necessary specifications, the next logical step is to fill the
table with data. There are generally three methods in PostgreSQL with which you can fill a table with data:
Use the INSERT INTO command with a grouped set of data to insert new values.
Use the INSERT INTO command in conjunction with a SELECT statement to insert existing values from another table.
Use the COPY (or \copy) command to insert values from a system file.
The following is the syntax of the INSERT INTO command, when used to insert
new values, which is subsequently described in detail:
INSERT INTO table_name
[ ( column_name [, ...] ) ]
VALUES ( value [, ...] )
table_name
The INSERT SQL command initiates an insertion of data into the table called
table_name.
( column_name [, ...] )
An optional grouped expression which describes the targeted columns for the insertion.
VALUES
The SQL clause which instructs PostgreSQL to expect a grouped expression of values to follow.
( value [, ...] )
The required grouped expression that describes the values to be inserted. There should be one value
for each specified column, separated by commas. These values may be expressions themselves (e.g., an operation
between two values), or constants.
Each value following the VALUES clause must be of the same data type as the column
it is being inserted into. If the optional column-target expression is omitted, PostgreSQL will expect there to be
one value for each column in the literal order of the table's structure. If there are fewer values to be inserted than
columns, PostgreSQL will attempt to insert a default value (or the
NULL value, if there is no default) for each omitted value.
To demonstrate, Example 4-16 illustrates the insertion of a new book
into Book Town's books table.
Example 4-16. Inserting new values into the books table
The SQL statement in Example 4-16 inserts a new book with an id of 41472, a
title of Practical PostgreSQL, an author identifier of 1212, and a subject identifier of 4. Note the
feedback beginning with INSERT, which indicates that the insertion was successful. The first number
following INSERT is the OID (object identifier) of the freshly inserted row. The second number following
INSERT represents the number of rows inserted (in this case, 1).
Notice that the optional column target list is specified identically to the physical structure of the table, from
left to right. In this case, omitting the grouped expression would have no effect on the statement since the
INSERT statement assumes that you are inserting values in the natural order of the
table's columns. You can re-arrange the names of the columns in the grouped column target list if you wish to specify
the values in a different order following the VALUES clause, as demonstrated in Example 4-17.
Example 4-17. Changing the order of target columns
If you already have values within one table (or across several other tables) that you wish to insert into
a separate table, this can also be achieved with the INSERT INTO command. The
following syntax is used for this technique:
Similar to the syntax of INSERT INTO presented in the previous section,
you may optionally specify which columns you wish to insert into, and in what order the query returns
their values. However, with this form of INSERT INTO, you provide a complete SQL
SELECT statement in the place of the VALUES keyword.
For example, imagine that Book Town keeps a table called book_queue, which holds
books waiting to be approved for sale. When approved, those values need to be moved from the queue, into the normal
books table. This can be achieved with the syntax demonstrated in
Example 4-18.
Example 4-18. Inserting values from another table
booktown=# INSERT INTO books (id, title, author_id, subject_id)
booktown-# SELECT nextval('book_ids'), title, author_id, subject_id
booktown-# FROM book_queue WHERE approved;
INSERT 0 2
The preceding example demonstrates the insertion of two rows from the table
book_queue into the books table by way of a
SELECT statement that is passed to the INSERT INTO command. Any
valid SELECT statement may be used in this context. In this case, the query selects the result of a
function called nextval() from a sequence called book_ids, followed
by the title, author_id and subject_id
columns from the book_queue table.
Since more than one row is being inserted, the INSERT result indicating success returns
0 in place of the OID that would be returned if a single row had been inserted. The second number, as with
a normal INSERT INTO command, returns the number of rows inserted (in this case, 2).
A useful technique within PostgreSQL is to use the COPY command to insert values
directly into tables from external files. Files used for input by COPY must either be
in standard ASCII text format, whose fields are delimited by a uniform symbol, or in PostgreSQL's binary table format.
Common delimiters for ASCII files are tabs and commas. When using an ASCII formatted input file with
COPY, each line within the file will be treated as a row of data to be inserted and
each delimited field will be treated as a column value.
The COPY FROM command operates much faster than a normal
INSERT command because the data is read as a single transaction directly
to the target table. On the other hand, it is a very strict format, and the entire COPY
procedure will fail if just one line is malformed.
The following is the syntax for using the COPY FROM command, where
table_name is the table that you wish to insert values into and filename is
the absolute system path to the from which file to be read:
COPY [ BINARY ] table_name [ WITH OIDS ]
FROM { 'filename' | stdin }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null_string' ]
BINARY
Indicates that input will come from a binary
file previously created by the COPY TO command.
table_name
The name of the table you are copying.
WITH OIDS
Instructs PostgreSQL to retrieve all of the OIDs of the table represented by filename
from the first line of the file.
FROM { 'filename' | stdin }
Indicates that either the file specified with filename or standard input (stdin)
should be read by PostgreSQL.
[ USING ] DELIMITERS 'delimiter'
Indicates the character provided with delimiter should be used as a delimiter when parsing input.
This clause is not applicable to files that were output in PostgreSQL's binary format.
WITH NULL AS 'null_string'
Indicates that the character(s) provided with null_string should be interpreted as
NULL values. This clause is not applicable to files that were output in PostgreSQL's binary format.
When preparing to copy a file from the underlying operating system, remember that the file specified must be readable
by the postmaster process (i.e., the user which PostgreSQL is running as), since the
backend reads the file directly. Additionally, the filename must be provided with an absolute path; an attempt to use a
relative path will result in an error.
If you are using an ASCII formatted input file, a delimiter value may be passed to the
DELIMITERS clause, which defines the character which delimits columns on a single line in
the filename. If omitted, PostgreSQL will assume that the ASCII file is tab-delimited. The optional
WITH NULL clause allows you to specify in what form to expect
NULL values. If omitted, PostgreSQL interprets the \N
sequence as a NULL value to be inserted (e.g., blank fields in a source file will be
treated as blank string constants, rather than NULL, by default).
The stdin term may be supplied as the source for the FROM
clause if you wish to type values in manually or paste from another location directly into a terminal session. If you
choose to enter values from stdin, you must terminate the input stream with a \. sequence
(backslash-period) followed immediately by a newline.
Example 4-19 shows the contents of a file that was output in ASCII format by PostgreSQL.
The file in Example 4-19 is comma-delimited and uses \null to
represent NULL values. It contains row data from the Book Town
subjects table.
Example 4-19. An example ASCII copy file
1,Business,Productivity Ave
2,Children's Books,Kids Ct
3,Classics,Academic Rd
4,Computers,Productivity Ave
5,Cooking,Creativity St
12,Religion,\null
8,History,Academic Rd
9,Horror,Black Raven Dr
10,Mystery,Black Raven Dr
11,Poetry,Sunset Dr
13,Romance,Main St
14,Science,Productivity Ave
15,Science Fiction,Main St
0,Arts,Creativity St
6,Drama,Main St
7,Entertainment,Main St
The statement in Example 4-20 copies the file
(/tmp/subjects.sql) into a table within the booktown database's
subjects table.
Example 4-20. Copying an ASCII file
booktown=# COPY subjects FROM '/tmp/subjects.sql'
booktown-# USING DELIMITERS ',' WITH NULL AS '\null';
COPY
The COPY command can also input and output both binary formatted data. Specifying
to the COPY FROM command the BINARY keyword requires
that the input file specified was created with the COPY TO command in PostgreSQL's
binary format. Binary files can be read more quickly than ASCII files, but are not readable or modifiable with plain-text
editors as ASCII files are.
Example 4-21 uses the COPY command to insert the rows in
the binary output file from the subjects table within the
booktown database.
Example 4-21. Copying a binary file
booktown=# COPY BINARY subjects FROM '/tmp/subjects.sql';
COPY
The COPY command is not the same as the
psql\copy command. The \copy command
accepts the same syntax (though without a terminating semicolon), and therefore performs the operation via the
psql client, rather than the postmaster server. The result is that
\copy operates with the permissions of the user running psql rather
than of the user the postmaster is running as.
The syntax of COPY FROM may be used with nearly identical syntax to send a
table's data to a file. You need only replace the FROM keyword with the
TO keyword. Additionally, the stdin keyword may be
replaced with stdout if you wish to redirect to standard output rather than to a file
(e.g., to the screen, in psql ). Example 4-22 shows how you would
copy the books table to an ASCII formatted file.
Example 4-22. Copying the books table to an ASCII file
Files containing row data with object identifier values (created with the
COPY TO command, involving the WITH OIDS clause) can
be read by a COPY FROM command, if the WITH OIDS
clause is specified. Attempts to use the COPY FROM command with the
WITH OIDS clause on a file that wasn't given OIDs during its creation will
fail.
The ability to copy values into a table with object-identifiers is a special capability reserved for
COPY. This value cannot be modified by INSERT or
UPDATE, as it is a system value. If you are not careful, you may end up with two rows
which have the same OID, which potentially negates their usefulness.
