[Issuing DB Commands]

1、 Starting a DB Session:
PostgreSQL uses a client/server model of communication. A PostgreSQL server is continually running, waiting for client requests. The server processes the request and returns the result to the client.
- Choosing an Interface:
Because the PostgreSQL server runs as an independent process on the computer, a user can’t interact with it directly. Instead, client applications have been designed specifically for user interaction.
- Choosing a DB:
Each PostgreSQL server controls access to a number of DBs. DBs are storage areas used by the server to partition information.
- Starting a Session:
$ psql test
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

test=>

2、 Controlling a Session:
test=> SELECT CURRENT_USER;
getpgusername
---------------------
postgres
(1 row)

test=>

--> Typing in the Query Buffer:
Typing in the query buffer is similar to typing at an OS command prompt. However, at an OS command prompt, Enter completes each command. In psql, commands are completed only when you enter a semicolon ( ; ) or backslash-g ( \g ).
[Example]
test=> SELECT
test-> 1+3
test->;
?column?
--------------
4
(1 row)

test=>

--> Displaying the Query Buffer:
You can continue typing indefinitely, until you use a semicolon or \g. Everything you type will be buffered by psql until you’re ready to send the query. If you use \p, you’ll see everything accumulated in the query buffer.
--> Erasing the Query Buffer:
If you don’t like what you have typed, use \r to reset or erase the buffer.

3、 Getting Help:
You might ask, “Are these backslash commands documented anywhere?”
test=> SELECT
test-> 2*10+1
test-> \p
SELECT
2*10+1
test-> \g
?column?
--------------
21
(1 row)

test=>

4、 Exiting a Session:
Use \q to quit the session and exit psql.

[Importing and Exporting Data]

Copy allows rapid loading and unloading of user tables. This command can write contents of a table to an ASCII file or a table from an ASCII file. These files can be used for backup purposes or to transfer data between PostgreSQL and other applications.

1、 Using COPY:
test=> create table copytest (
test(> intcol integer,
test(> numcol numeric(16,2),
test(> textcol text,
text(> boolcol Boolean);
CREATE
text=> insert into copytest
test-> values(1, 23.99, ‘fresh spring water’, ‘t’);
INSERT 174656 1
test=> insert into copytest
test-> values(2, 55.23, ‘bottled soda’, ‘t’);
INSERT 174657 1
test=> select * from copytest;
……
(2 rows)

test=> copy copytest to ‘/tmp/copytest.out’;
COPY
Test=> delete from copytest;
DELETE 2
test=> copy copytest from ‘/tmp/copytest.out’;
COPY
test=> select * from copytest;
……
(2 rows)

2、 Copy File Format:
test=> \q
$ cat /tmp/copytest.out
1 23.99 fresh spring water t
2 55.23 bottled soda t

$ sed ‘s/ /<TAB>/g’ /tmp/copytest.out # the gap between / / is a TAB
1<TAB>23.99<TAB>fresh spring water<TAB>t
2<TAB>55.23<TAB>bottled soda<TAB>t

3、 Delimiters:
You can easily change the default tab column delimiter. Copy’s USING DELIMITERS option allows you to set the column delimiter.

test=> copy copytest to ‘/tmp/copytest.out’ using delimiters ‘|’;
COPY
test=> \q
$ cat /tmp/copytest.out
1|23.99|fresh spring water|t
2|55.23|bottled soda|t

If a copy file doesn’t use the default tab column delimiter, COPY … FROM must employ the proper USING DELIMITERS option.

test=> delete from copytest;
DELETE 2
test=>
test=> copy copytest from ‘/tmp/copytest.out’;
ERROR: copy: line 1, pg_atoi: error in “1|23.99|fresh spring water|t”: cannot parse “|23.99|fresh spring water|t”
test=>
test=> copy copytest from ‘/tmp/copytest.out’ USING DELIMITERS ‘|’;
COPY

4、 Copy Without Files:
Copy can also be used without files. The command can use the same input and output locations used by psql. The special name stdin represents the psql input, and stdout represents the psql output.

test=> copy copytest from stdin;
Enter data to be copied followed by a new line.
End with a backslash and a period on a line by itself.
test> \.
test=> copy copytest to stdout;
1 23.99 fresh spring water t
2 55.23 bottled soda t
3 77.43 coffee f
test=>

5、 Backslashes and NULL Values:
Copy avoids any confusion by specially marking delimiters appearing in user data. It precedes them with a \. If a piple is the delimiter, COPY … TO uses | for delimiters, and \| for pipes in user data.
Use of a \ causes any character that follows it to be treated specially. As a result, a \ in user data is output as \\.
Another special backslash \N represents NULL. It prevents NULL values from being confused with user values.
To change the default NULL representation, use WITH NULL AS.

\TAB – tab if using default delimiter tab
\| -- pipe if using pipe as the delimiter
\N -- NULL if using the default NULL output
\b – backspace
\f – form feed
\n – new line
\r – carriage return
\t – tab
\v – vertical tab
\### -- character represented by octal number ###
\\ -- backslash

6、 Copy Tips:
You must use full path names with the COPY command because the DB server runs in a different directory than the psql client. Files are read and written by the postgres user, so postgres must have permission to read the file for COPY … FROM and directory write permission for COPY … TO. Because COPY uses the local file system, users connecting over a network can’t use file names. They can use stdin and stdout, or psql’s \copy command.
By default, the system-generated OID column isn’t written out, and loaded rows receive nes OIDs. COPY … WITH OIDS allows OIDs to be written and read.
COPY writes only entire tables. To COYY only part of a table, use SELECT … INTO TEMPORARY TABLE with an appropriate WHERE clause and then COPY the temporary table to a file.