Export to CSV file

Create table

CREATE TABLE airplanes (
  name text PRIMARY KEY,
  manufacturer ascii,
  year int,
  mach float
);

Inserting into table

INSERT INTO airplanes(name, manufacturer, year, mach) VALUES ('P38-Lightning', 'Lockheed', 1937, 0.7);

Export to temp.csv file

COPY airplanes (name, manufacturer, year, mach) TO 'temp.csv';

Using 3 child processes

Starting copy of admatic.airplanes with columns [name, manufacturer, year, mach].
Processed: 1 rows; Rate:       1 rows/s; Avg. rate:       1 rows/s
1 rows exported to 1 files in 0.758 seconds.

cat temp.csv

P38-Lightning,Lockheed,1937,0.7

Truncate airplanes to remove all data

TRUNCATE airplanes;

Import from temp.csv file

Syntax

COPY <table-name> (col1,col2,col3,...) FROM '<csv-file-path-in-local>';

Example

COPY airplanes (name, manufacturer, year, mach) FROM 'temp.csv';

Using 3 child processes

Starting copy of admatic.airplanes with columns [name, manufacturer, year, mach].
Processed: 1 rows; Rate:       1 rows/s; Avg. rate:       2 rows/s
1 rows imported from 1 files in 0.445 seconds (0 skipped).

Verify the import

select * from airplanes;

 name          | mach | manufacturer | year
---------------+------+--------------+------
 P38-Lightning |  0.7 |     Lockheed | 1937

(1 rows)

Copy data from standard input to a table

COPY airplanes (name, manufacturer, year, mach) FROM STDIN;

Using 3 child processes

Starting copy of admatic.airplanes with columns [name, manufacturer, year, mach].
[Use . on a line by itself to end input]
[copy]

'F-14D Super Tomcat', Grumman, 1987, 2.34
'MiG-23 Flogger', Russian-made, 1964, 2.35
'Su-27 Flanker', U.S.S.R., 1981, 2.35
.

Verify

SELECT * FROM airplanes;

name               | manufacturer | year | mach
--------------------+------+--------------+-------------
 F-14D Super Tomcat |      Grumman | 1987 | 2.35
      P38-Lightning |     Lockheed | 1937 | 0.7
      Su-27 Flanker |     U.S.S.R. | 1981 | 2.35