Crash course in PostgreSQL, part 2

Page 2 of 3

What if you have a lot of data to insert? A fast way is to enter them in a plain text file, one data set per line in column order, with each field separated by a tab. Make sure there are no trailing newlines following your last line, or psql will give you a "missing data" error that references the empty lines. This example text file, freakbros.txt, contains two rows of data:

Fabulous Furry Freak Brothers 2  Rip Off Press  1973-04-19
Fabulous Furry Freak Brothers 3  Rip Off Press  1973-12-03

Populate your table with data from this file like this:

testdb=# \copy comics FROM '/home/carla/Documents/postgres/freakbros.txt'

There are several important items to note here. There are two copy commands: a SQL COPY command, and a psql \copy command. These are close siblings, but not twins. Using COPY means the postgres user must have read permissions to the text file. Using \copy means the system user who is logged into psql must have read permissions on the file; for example, the "carla" user can import from a text file in her home directory.

The COPY command is faster because it runs on the server, where the \copy command runs from the psql client to the server. On success it returns a COPY [count] message telling how many rows it copied. It also saves a fair bit of hassle because you can run scripts without having to worry about who owns your source data file.

\copy does not require a terminating semi-colon, while COPY does. You may also elect to use a different delimiter, such as a comma or a pipe symbol instead of tabs. Make sure there are no spaces on either side of your delimiter, then specify your chosen delimiter like this COPY example:

testdb=# COPY comics FROM '/etc/postgresql/9.0/main/freakbros.txt'
  USING DELIMITERS ',';

Getting file permissions sorted out is a common source of confusion. SELinux may get in your way and need some adjusting, and plain old Unix file permissions can sometimes bite in weird ways. The simplest way I have found to make files accessible to the postgres user is to make a special directory owned by the postgres user and group, and then make all the files in it owned by postgres. Or use a directory already owned by postgres. For example, on my Debian testing system /etc/postgresql/9.0/ is owned by postgres, so I put my files in there. Use the find command to quickly find which files and directories are owned by postgres. This example searches the whole filesystem except for the /proc pseudo-directory and network shares:

# find / -xdev \( -name proc -prune \) -o -user postgres
| 1 2 3 Page 2
ITWorld DealPost: The best in tech deals and discounts.
Shop Tech Products at Amazon