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
2 answers

How to COPY to multiple CSV files in PostgreSQL?

I've got a PostGIS database of points in Postgres, and I would like to extract the points in several geographically distinct areas to CSV files, one file per area. I have set up an area table with area polygons, and area titles and I would like to…
Gary T
  • 93
  • 2
  • 7
0
votes
1 answer

Single quotes stored in a Postgres database

I've been working on an Express app that has a form designed to hold lines and quotes. Some of the lines will have single quotes('), but overall it's able to store the info and I'm able to back it up and store it without any problems. Now, when I…
Andrew R
  • 151
  • 1
  • 1
  • 7
0
votes
1 answer

Copying postgres data base with links / ref ids

Trying to extract a csv of a db table Using... COPY sale_order TO '/tmp/salesorder.csv' DELIMITER ',' CSV HEADER; I get.. id - partner_id 1 - 45 2 - 55 "partner_id" is references another table "contacts" as id, where I want "name" Can I ref…
Floggedhorse
  • 694
  • 8
  • 15
0
votes
3 answers

Handling quotes in COPY to CSV from command shell

I am trying to run this COPY query from the command shell: COPY products_273 TO '/tmp/products_199.csv' DELIMITER ',' CSV HEADER; I know you can run queries from the command line using psql: psql -U username -d mydatabase -c 'SELECT * FROM…
Henley
  • 21,258
  • 32
  • 119
  • 207
0
votes
0 answers

Copy table from txt file with Chinese Characters - Postgresql on Windows

I am trying to load a table from a txt file which has Chinese characters in it using the \copy command in PostgreSQL. I have a test table with only one columns Names Varchar(25) in it. When I run an insert statement from PSQL or PgAdmin like insert…
P_Ar
  • 377
  • 2
  • 9
  • 25
0
votes
1 answer

population csv file into sql

COPY population FROM '/Users/chittampalliyashaswini/Documents/population.csv' DELIMITER ',' CSV HEADER; I tried running this same command. I moved the population csv to the my documents folder. This is the result I am getting. ERROR: invalid…
Yadu
  • 11
  • 3
0
votes
1 answer

error using copy from in postgresql when value is '2"%'

I have a table with 300,000 rows not under my control that I need to import. I export the data from mysql using: mysqldump -u root --password=secret --fields-enclosed-by='\"' -T/tmp apflora_beob Then I try to import each table using for…
Alex
  • 2,117
  • 5
  • 28
  • 36
0
votes
0 answers

The query can't be executed!ERROR: 22P02: invalid input syntax for type numeric: ""1"

I am trying to import csv to postgres through c# code the query is i am guess right but i get error : The query can't be executed!ERROR: 22P02: invalid input syntax for type numeric: ""1" My code to do this is: string identification_client =…
traps traps
  • 1
  • 6
0
votes
1 answer

The query can't be executed!ERROR: 22P04: extra data after last expected column

I am trying to import csv to postgres through c# code the query is i am guess right but i get error : The query can't be executed!ERROR: 22P04: extra data after last expected column My code to do this is: string codes_colisage = "COPY…
trap trap
  • 3
  • 2
0
votes
1 answer

Postgres copy data & evaluate expression

Is it possible a copy command to evaluate expressions upon insertion? For example consider the following table create table test1 ( a int, b int) and we have a file to import 5 , case when b = 1 then 100 else 101 25 , case when b = 1 then 100…
dimcookies
  • 1,930
  • 7
  • 31
  • 37
0
votes
1 answer

Copy Multiple table to postgres database using shell script

I have two databases one in mysql and other in postgres. I want to copy nearly ten table data to postgres database which already has tables with the same name. I want to do this using a shell script because i don't want do write 10 individual…
Suganya
  • 652
  • 7
  • 14
0
votes
2 answers

On conflict replace in Postgresql - while loading a external file

I'm considering to use 'COPY' command to load the text file into table A. COPY myTable FROM value.txt (DELIMITER('|')); A table has already 10 rows and the value.txt file also has the same 10 rows but only 1 column value(CheckTime) is different…
Sigularity
  • 917
  • 2
  • 12
  • 28
0
votes
1 answer

PostgreSQL COPY syntax to remote DB from local CSV

I'm creating a program in Java to read an Excel file, remove some bad rows, create a CSV file from it, and insert it into PostgreSQL database by using COPY syntax. Currently it works if the file and database are in the same machine. I want to make…
Aldibe
  • 1,277
  • 2
  • 11
  • 16
0
votes
0 answers

Postgres "ERROR: invalid byte sequence for encoding "UTF8": 0xa0" occurred while dumpimg csv

I got ERROR: invalid byte sequence for encoding "UTF8": 0xa0" while dumpimg csv to table, no record was inserted in the table but size of table and database got increased. How does that happen and is there any way to reduce it? Thanks.
Arpit Sharma
  • 21
  • 1
  • 2
0
votes
2 answers

Export an array into a CSV-file in PL/pgSQL

I have a function, which RETURNS SETOF text[]. Sample result of this function: {080213806381,"personal data1","question 1",answer1,"question 2",answer2,"question 3","answer 3"} {080213806382,"personal data1","question 1",answer1,"question…
kumade
  • 541
  • 2
  • 9
  • 18
1 2 3
…
9
10