COPY - Exporting and Importing Data

Giriş

Açıklaması şöyle. Yani COPY, COPY TO ve COPY FROM komutları var

The COPY command can import data to Postgres if access to text, CSV, or binary format data.
...
The file mentioned in the COPY command must be accessible by the Postgres user and should be specified from the perspective of the Postgres server.

The command can also use a SELECT query to load data to a table. It also allows you to specify a list of columns to insert the data into instead of the whole table. On successful completion, the COPY command outputs result in the format COPY count, denoting the number of rows inserted by the command.

Both the text and the CSV file formats allow you to specify a delimiter. But if your input is CSV, it is better to use the CSV format with the DELIMITER option rather than the TEXT format since CSV format adheres to the commonly accepted CSV escaping mechanism. By contrast, the TEXT format follows Postgres-specific escaping rules.

Açıklaması şöyle

The COPY command has many optional parameters that you can use to customize its behavior. Some of the important ones are listed below:

• QUOTE: Specify the character used to quote the data values.
• NULL: Specifies the character used to represent the NULL value.
• ESCAPE: Specifies the character used to escape a character that is being used as the QUOTE character.
• ENCODING: Used to describe the encoding of the file. If nothing is mentioned, it defaults to client encoding.

EXPORT

Örnek - CSV

Şöyle yaparız

COPY table_name TO '/path/to/file.csv' DELIMITER ',' CSV HEADER;
COPY table_name FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;

IMPORT

Söz dizimi

COPY [tablename] FROM [filename] şeklindedir

Örnek - CSV

Şöyle yaparız

COPY customer FROM '/home/data/customer.csv' DELIMITER ',' CSV HEADER;

Örnek - CSV

CSV dosyasındaki bazı sütunları almak için şöyle yaparız

COPY customer(first_name,last_name,email) FROM '/home/data/customers1.csv' DELIMITER ',' CSV HEADER;

Örnek

Şöyle yaparız

-- Create temporary table
DROP TABLE IF EXISTS music_track;
CREATE TABLE music_track (
  id         varchar primary key,
  artist_id  varchar,
  title      varchar
);

-- first we copy from the csv into a temporary table
-- if the file is inside the server, you can use COPY
-- otherwise, use \copy to point to your machine
\copy music_track -- music_track is the table name
FROM '/home/username/data/music-track-external.csv' -- using absolute path on client side
WITH (format csv, header); -- file is csv and first line is the header

-- we use the temporary table to populate the correct values into the production table
INSERT INTO music_catalog (track_id, artist_id, title, url)
  SELECT music_track.id, music_track.artist_id, music_track.title, music_file.url 
  FROM music_track INNER JOIN music_file ON music_track.id = music_file.track_id
ON CONFLICT (music_catalog.track_id) DO UPDATE SET
  artist_id = music_track.artist_id
  title     = music_track.title,
  url       = music_files.url;

Açıklaması şöyle

In the example above, we needed to import a large amount of data from an external source. Using COPY or \copy (depending on if you have the file in the server or the client) is probably the fastest option.

You can also leverage the power of “INSERT INTO SELECT” to merge the data from a COPY with data from other tables, loading the initial data into a temporary table first.