psql - PostgreSQL: Up and Running - page 52

Indexes:
"pg_ts_dict_dictname_index" UNIQUE, btree (dictname, dictnamespace)
"pg_ts_dict_oid_index" UNIQUE, btree (oid)
Has OIDs: yes
Importing and Exporting Data
psql has a \copy command that lets you import data from and export data to a text file.
Tab is the default delimiter, but you can specify others. New line breaks must separate
the rows. For our first example, we downloaded data from US Census Fact Finder
covering racial demographics of housing in Massachusetts. You can download the file
we use in this example, DEC_10_SF1_QTH1_with_ann.csv , from the PostgreSQL Book
Data .
psql Import
Our usual practice in loading denormalized or unfamiliar data is to create a separate
staging schema to accept the incoming data. We then write a series of explorative queries
to get a good sense of what we have on our hands. Finally, we distribute the data into
various normalized production tables and delete the staging schema.
Before bringing the data into PostgreSQL, you must first create a table to hold the
incoming data. The data must match the file both in the number of columns and data
types. This could be an annoying extra step for a well-formed file, but it does obviate
the need for psql to guess at data types. psql processes the entire import as a single
transaction; if it encounters any errors in the data, the entire import will fail. If you're
unsure about the data contained in the file, we recommend setting up the table with the
most accommodating data types and then recasting them later if necessary. For example,
if you can't be sure that a column will just have numeric values, make it character
varying to get the data in for inspection and then recast it later.
Launch psql from the command line and run the commands in Example 3-7 in the psql
console.
Example 3-7. Importing data with psql
\connect postgresql_book
\cd /postgresql_book/ch03
\copy staging.factfinder_import FROM DEC_10_SF1_QTH1_with_ann.csv CSV
In Example 3-7 , we launch interactive psql, connect to our database, use \cd to change
the current directory to the folder containing our file, and import our data using the
\copy command. Because the default delimiter is a tab, we augment our statement with
CSV to tell psql that our data is comma-separated instead.
If your file has nonstandard delimiters such as pipes, indicate the delimiter: