Stack Overflow
Stack Overflow

Questions tagged [postgresql-copy]

COPY is an SQL command in PostgreSQL to move data between files and tables. There is also the meta-command \copy in the psql interface.

Besides the shell utilities pg_dump and pg_restore for backup and restore, there is also the SQL command COPY in PostgreSQL to move data between files and database tables quickly. Several file formats are supported: text, csv and binary.

The meta-command \copy in the psql interface is a wrapper for the SQL command that reads and writes files local to the client (while COPY is for files on the server).

Examples

Write all rows from a table employees to a CSV file on the DB server with SQL COPY:

COPY employees TO '/path/to/employees.csv' (FORMAT csv);

There are more examples for COPY in the manual .

Read data from a CSV file on the client machine into a table with matching structure with \copy in psql (appending to existing data):

\copy employees FROM '/path/to/employees.csv' (FORMAT csv);
133 questions
984
votes
17 answers

Save PL/pgSQL output from PostgreSQL to a CSV file

What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file? I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.
Hoff
  • 34,679
  • 17
  • 65
  • 89
651
votes
20 answers

How to import CSV file data into a PostgreSQL table?

How can I write a stored procedure that imports data from a CSV file and populates the table?
vardhan
  • 6,519
  • 3
  • 13
  • 3
438
votes
13 answers

How to export table as CSV with headings on Postgresql?

I'm trying to export a PostgreSQL table with headings to a CSV file via command line, however I get it to export to CSV file, but without headings. My code looks as follows: COPY products_273 to '/tmp/products_199.csv' delimiters',';
Elitmiar
  • 30,202
  • 72
  • 172
  • 224
221
votes
10 answers

Export specific rows from a PostgreSQL table as INSERT SQL script

I have a database schema named: nyummy and a table named cimory: create table nyummy.cimory ( id numeric(10,0) not null, name character varying(60) not null, city character varying(50) not null, CONSTRAINT cimory_pkey PRIMARY KEY (id) ); I…
null
  • 7,598
  • 14
  • 55
  • 94
98
votes
5 answers

How to copy from CSV file to PostgreSQL table with headers in CSV file?

I want to copy a CSV file to a Postgres table. There are about 100 columns in this table, so I do not want to rewrite them if I don't have to. I am using the \copy table from 'table.csv' delimiter ',' csv; command but without a table created I get…
Stanley Cup Phil
  • 9,502
  • 27
  • 86
  • 144
40
votes
2 answers

In PostgreSQL, how to insert data with COPY command?

I have problem when run 1 project NodeJs with PostgreSQL database. I have error when trying to insert data in pgAdmin using the COPY command. COPY beer (name, tags, alcohol, brewery, id, brewery_id, image) FROM stdin; Bons Voeux blonde 9.5…
user4646310
35
votes
3 answers

How does COPY work and why is it so much faster than INSERT?

Today I spent my day improving the performance of my Python script which pushes data into my Postgres database. I was previously inserting records as such: query = "INSERT INTO my_table (a,b,c ... ) VALUES (%s, %s, %s ...)"; for d in data: …
turnip
  • 1,787
  • 3
  • 24
  • 48
27
votes
2 answers

How to generate a schema from a CSV for a PostgreSQL Copy

Given a CSV with several dozen or more columns, how can a 'schema' be created that can be used in a CREATE TABLE SQL expression in PostgreSQL for use with the COPY tool? I see plenty of examples for the COPY tool, and basic CREATE TABLE expressions,…
DPSSpatial
  • 665
  • 1
  • 10
  • 28
24
votes
6 answers

PostgreSQL: export resulting data from SQL query to Excel/CSV

I need to export the resulting data from a query in PostgreSQL to Excel/CSV. I use PostgreSQL 8.2.11. SQL error: ERROR: relative path not allowed for COPY to file In statement: COPY (select distinct(m_price) from m_product)TO…
Ghostman
  • 5,718
  • 8
  • 31
  • 51
23
votes
8 answers

copy data from csv to postgresql using python

I am on windows 7 64 bit. I have a csv file 'data.csv'. I want to import data to a postgresql table 'temp_unicommerce_status' via a python script. My Script is: import psycopg2 conn = psycopg2.connect("host='localhost' port='5432' dbname='Ekodev'…
Manish Gupta
  • 3,771
  • 9
  • 48
  • 87
19
votes
6 answers

ERROR: could not stat file "XX.csv": Unknown error

I run this command: COPY XXX FROM 'D:/XXX.csv' WITH (FORMAT CSV, HEADER TRUE, NULL 'NULL') In Windows 7, it successfully imports CSV files of less than 1GB. If the file is more then 1GB big, I get an “unknown error”. [Code: 0, SQL State: XX000] …
亚军吴
  • 311
  • 1
  • 2
  • 7
19
votes
1 answer

ERROR: missing data for column when using \copy in psql

I'm trying to import a .txt file into PostgreSQL. The txt file has 6 columns: Laboratory_Name Laboratory_ID Facility ZIP_Code City State And 213 rows. I'm trying to use \copy to put the contents of this file into a table called doe2 in…
nathanmgroom
  • 197
  • 1
  • 1
  • 6
16
votes
3 answers

Importing zipped CSV file into PostgreSQL

I have a big compressed csv file (25gb) and I want to import it into PostgreSQL 9.5 version. Is there any fast way to import zip or qzip file into postgres without extracting the file?
Arezoo
  • 312
  • 3
  • 14
14
votes
5 answers

ERROR: COPY delimiter must be a single one-byte character

I want to load the data from a flat file with delimiter "~,~" into a PostgreSQL table. I have tried it as below but looks like there is a restriction for the delimiter. If COPY statement doesn't allow multiple chars for delimiter, is there any…
vchitta
  • 1,943
  • 8
  • 25
  • 34
13
votes
3 answers

How to convert date strings to timestamp without knowing the date format

I am trying to write a query to insert a value into a timestamp with no timezone data type field. The value is coming from CSV file. The version I am working with is PostgreSQL 8.1.21. The CSV file upload is done by the client and it has a date…
Shiver
  • 175
  • 2
  • 2
  • 6
1
2 3
…
8 9