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
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
  • 225
  • 2
  • 3
  • 6
13
votes
1 answer

COPY with dynamic file name

I am trying to write a function to load csv data into a table. I want the input argument to be the path to the file. CREATE OR REPLACE FUNCTION public.loaddata(filepathname varchar) RETURNS void AS $BODY$ BEGIN COPY climatedata( …
bebego
  • 233
  • 1
  • 2
  • 6
12
votes
2 answers

Correct way to use copy Postgres jdbc

Unable to use copy command with jdbc Postgres. Whats wrong with the below code snippet sample. public boolean loadReportToDB(String date) { // TODO Auto-generated method stub Connection connection =…
Mrinal Bhattacharjee
  • 1,326
  • 4
  • 10
  • 15
12
votes
3 answers

postgresql csv copy unquoted newline found in data

I have some csv data in excel, and I'm importing it into postgresql. I'm opening the excel csv file with a notepad editor (have tried notepad, wordpad and notepad++) and am then copying/pasting into a remote desktop connection to a linux machine. …
user1475191
  • 121
  • 1
  • 1
  • 5
11
votes
2 answers

COPY function in PostgreSQL

I would like to use the COPY function in PostgreSQL to import a CSV file into a PostgreSQL database. Where it says the filename in the documentation, does the CSV file have to be stored in a specific location or can it be stored in any location.…
Jeiman
  • 1,121
  • 9
  • 27
  • 50
10
votes
2 answers

COPY command: copy only specific columns from csv

I had a question surrounding the COPY command in PostgreSQL. I have a CSV file that I only want to copy some of the columns values into my PostgreSQL table. Is it possible to do this? I am familiar with using the COPY command to copy all of the data…
parchambeau
  • 1,141
  • 9
  • 34
  • 56
8
votes
1 answer

Saving psql output to csv file

I have a query written in a file located at /path/to/query. How can I save the output result to a csv file, without using COPY in the query? I tried the following command, but the output file's fields are separated by " | ". psql -U username -d…
Agrim Pathak
  • 3,047
  • 4
  • 27
  • 43
7
votes
2 answers

Delete rows of a table specified in a text file in Postgres

I have a text file containing the row numbers of the rows that should be deleted in my table like this: 3 32 40 55 [...] How can I get a PostgreSQL compatible SQL statement which deletes each of these rows from my table using the text file?
ptikobj
  • 2,690
  • 7
  • 39
  • 64
7
votes
1 answer

Importing CSV with commas in string values

I am trying to import a trivial CSV to Postgres 8.4 database: Here is a table: CREATE TABLE public.sample ( a VARCHAR, b VARCHAR ) WITHOUT OIDS; Here is a CSV file sample: "foo","bar, baz" The query: COPY sample FROM '/tmp/sample.csv' USING…
zerkms
  • 249,484
  • 69
  • 436
  • 539
6
votes
4 answers

insert additional column values(not available in file) in postgresql table using COPY

Table columns: col1, col2, col3, fname, lname CSV file contains values like: col1,col2,col3 val1,val2,val3 val1,val2,val3 I want to add data from csv along with additional data i.e. col1,col2,col3,fname,lname in table using COPY functionality of…
Ketu
  • 1,608
  • 2
  • 14
  • 30
5
votes
2 answers

How to use \copy in postgresql with pgadmin4

I want to use the \copy command to make csv file with relative path. I used a query to make from test table to csv file named test.csv \copy (SELECT * FROM test) to './test.csv' with csv But in postgresql pgadmin4, it shows that \copy command as a…
윤성필
  • 85
  • 1
  • 1
  • 7
5
votes
1 answer

Execute COPY command in remote database using local file

I'm trying to execute the Postgres COPY command. COPY warehouse_terminal.test_table FROM 'C:\file.csv' DELIMITERS E'\t' CSV HEADER QUOTE E'\"' ESCAPE E'\\' NULL AS ''; The problem is, the db where I'm going to use this is remote, and the file…
muffin
  • 2,034
  • 10
  • 43
  • 79
5
votes
1 answer

How to tell if record has changed in Postgres

I have a bit of an "upsert" type of question... but, I want to throw it out there because it's a little bit different than any that I've read on stackoverflow. Basic problem. I'm working on moving from mysql to PostgreSQL 9.1.5 (hosted on…
David S
  • 12,967
  • 12
  • 55
  • 93
4
votes
2 answers

COPY csv file with additionnal datas

I have the following table : persons(id,id_tech,name,nationality,id_list) And a CSV file containing the datas for the column id_tech, name, and nationality. Importing the data like so is working : \copy persons(id_tech,name,nationality) FROM…
grunk
  • 14,718
  • 15
  • 67
  • 108
4
votes
0 answers

Postgres \copy throws a syntax error at STDOUT when exporting as CSV (v8.0.2)

I'm trying to locally download the results of a query as csv on a postgres instance that I have read-only access to (v8.0.2). I've truly read 10 different ways of going about this and have tried implementing them all (here, here, here, here, and…
yungblud
  • 388
  • 4
  • 17
1
2
3
…
9 10