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
0
votes
0 answers

How to generate table from csv in postgres sql

I am new to database management and we are using psql. All I need to do is to migrate csv (around 200 tables) to our database. Manually creating tables for every csv file is bit tiresome so please help me out, Is there any way to generate table from…
saniojoseph
  • 1
  • 1
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
  • 2,854
  • 6
  • 29
  • 69
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
  • 23,179
  • 26
  • 96
  • 243
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
  • 303
  • 3
  • 11
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
  • 784
  • 3
  • 14
  • 31
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
  • 551
  • 1
  • 5
  • 26
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
  • 7,444
  • 4
  • 40
  • 56
-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
  • 3,940
  • 4
  • 22
  • 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
  • 7,076
  • 9
  • 41
  • 69
-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,263
  • 4
  • 15
  • 34
-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
  • 3,940
  • 4
  • 22
  • 27
1 2 3
…
8
9