Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Copying Values from External Files with COPY

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

Binary format

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 difference between COPY and \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.

COPY TO

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

booktown=# 
COPY books TO 'filename';

COPY

Copying WITH OIDS

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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire