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);
147 questions
0
votes
0 answers

Linux, postgres, copy command result in error with file permissions

I'm on a GCE instance with a postgresql DB and I'm running the following command: COPY (Select * from mytable) TO '/home/myuser/myfile.txt' DELIMITER '#' CSV HEADER; and getting the following error: could not open file "/home/myuser/myfile.txt"…
liv a
  • 3,232
  • 6
  • 35
  • 76
0
votes
0 answers

Absolute path is treated as a relative path

I'm trying to export table's data into a file with csv format. COPY (SELECT * FROM mailing.value) TO 'C:\\Users\\asd\\Desktop\\value.csv' (format csv); But I got with ERROR: relative path not allowed for COPY to file How can I fix that?
St.Antario
  • 26,175
  • 41
  • 130
  • 318
0
votes
1 answer

Django CSV Import Slows Down

I'm importing a csv list of cities into my Django app. I'm pretty new to Django and Python, and the import runs reasonably quickly, the first 25,000 rows take about 5 minutes, the next 25,000 take 2 hours. I stopped the import and started again…
kurtfoster
  • 353
  • 3
  • 14
0
votes
1 answer

Error on Dynamic csv file export using plpgsql copy to csv in a function

I am trying to filter a postgresql table for rows that have a product id as a foreign key. For each product id, I need to export 1 csv each to a folder eg , prod1.csv, prod2.csv etc. I have tried to create the function below to automate this but the…
Avagut
  • 924
  • 3
  • 18
  • 34
0
votes
1 answer

Data correction exporting CSV file to Postgres

I am importing a csv file into postgres, and would like to know how to import the correct data type while using the COPY command. For instance, I have a column column_1 integer; and want to insert the value 6 into it from my csv file. I run the…
Luminusss
  • 571
  • 1
  • 6
  • 27
0
votes
1 answer

COPY command issue

I'm using this to read data into the table I created in Postgres 9.2: COPY tagdata FROM 'C:/Filter112595/QF112595_3.csv' WITH DELIMITER ',' CSV HEADER FORCE_NOT_NULL; Data types are real, integer and date. I get this error: ERROR: invalid…
user2736311
  • 1
0
votes
1 answer

Can COPY be used with a function?

I've been tasked with profiling a postgresql database. The first requirement is to see how fast records can be added, with all possible external bottlenecks removed, in order to find our theoretical limit. At first I created a csv file with sample…
Dean
  • 8,632
  • 6
  • 45
  • 61
-1
votes
2 answers

syntax for COPY in postgresql

INSERT INTO contacts_lists (contact_id, list_id) SELECT contact_id, 67544 FROM plain_contacts Here I want to use Copy command instead of Insert command in sql to reduce the time to insert values. I fetched the data using select…
Rafiu
  • 4,700
  • 6
  • 25
  • 27
-1
votes
1 answer

How do I replace (select current_timestamp) with a filename that houses this same select statement?

I am using PSQL. My command line is: $\copy (select current_timestamp) to '/home/myname/outputfile.txt' I would like to know, How do I replace "(select current_Timestamp)" with a filename that houses that same select statement? ex: $\copy…
David Clarke
  • 55
-1
votes
1 answer

Importing a large number of rows into Postgres with duplicate key violations

Is it possible in Postgres 9.6 to get on duplicate key UPSERT functionality using the COPY command? I have a CSV file that I'm importing into Postgres, but it may contain some duplicate key violations, so the COPY command gives an error and…
user779159
  • 9,034
  • 14
  • 59
  • 89
-1
votes
1 answer

In ruby on rails application how can i upload 3 GB csv file to the pg database on server

I want to upload 3 GB csv file to the pg database on server. I need to finish this operation in 4 to 5 hours. Please show me the way to upload data to server from csv file.
urjit on rails
  • 1,763
  • 4
  • 19
  • 36
-2
votes
2 answers

syntax for COPY in postgresql

Possible Duplicate: syntax for COPY in postgresql INSERT INTO contacts_lists (contact_id, list_id) SELECT contact_id, 67544 FROM plain_contacts WHERE TRUE AND is_print = TRUE AND TRUE AND (NOT…
Rafiu
  • 4,700
  • 6
  • 25
  • 27
1 2 3
…
9
10