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
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
  • 566
  • 5
  • 14
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 Chiu
  • 17,838
  • 29
  • 110
  • 187
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
  • 249
  • 6
  • 19
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
  • 1,737
  • 4
  • 22
  • 31
0
votes
1 answer

SQL server Openquery equivalent to PostgresQL

Is there query equivalent to sql server's openquery or openrowset to use in postgresql to query from excel or csv ?
Harish U
  • 79
  • 5
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
  • 4
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,842
  • 6
  • 29
  • 35
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
  • 542
  • 4
  • 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
  • 757
  • 1
  • 9
  • 20
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,007
  • 2
  • 8
  • 15
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

Importing and maintaining multiple csv files into PostgreSQL

I am new to using SQL, so please bear with me. I need to import several hundred csv files into PostgreSQL. My web search has only indicated how to import many csv files into one table. However, most csv files have different column types (all have…
Sean
  • 123
  • 1
  • 5
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
  • 371
  • 1
  • 5
  • 18
1 2 3
…
8
9