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
1
vote
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
  • 133
  • 1
  • 5
1
vote
2 answers

ERROR: syntax error at or near "." in copy command using java?

I am using following code: query1=" SELECT distinct copy meta.amz_payment1(\"Date\", settlement_id, type, order_id, " + "sku, description, quantity, marketplace, fulfillment, order_city, order_state, " + "order_postal, product_sales,…
user3946530
1
vote
1 answer

Error at copy command in postgresql?

my command is \copy meta.amz_payment1("Date", settlement_id, type, order_id, sku, description, quantity, marketplace, fulfillment, order_city, order_state, order_postal, product_sales, shipping_credits, promotional_rebates,…
user3946530
1
vote
1 answer

Columns to rows and export (copy) to a text file

I have a following table CREATE TABLE usr_addr_part_15 ( company character varying(70), add1 character varying(50), add2 character varying(50), add3 character varying(50), phoneoff character varying(30) ) with sample data as…
gabriele
  • 109
  • 1
  • 12
1
vote
2 answers

Date/time formatting for table creation

I am creating a table that will be populated with a COPY. Here's the format of that data: 6/30/2014 2:33:00 PM MM-DD-YYYY HH:MM:SS ?? What would I use as the formatting for the CREATE TABLE statement? CREATE TABLE practice ( Data_Time ???? ) One…
Pat Stroh
  • 189
  • 1
  • 3
  • 10
1
vote
2 answers

How do I find out the number of rows copied?

I am running a Postgres DB and using the COPY command in ruby to load data into a temporary table. It looks something like this: @conn = PG.connect(dbname: 'load_test') res = @conn.async_exec <<-QUERY COPY tmp_inventory FROM '#{infile_location}'…
Drew
  • 15,158
  • 17
  • 68
  • 77
1
vote
2 answers

Check if records exists in a Postgres table

I have to read a CSV every 20 seconds. Each CSV contains min. of 500 to max. 60000 lines. I have to insert the data in a Postgres table, but before that I need to check if the items have already been inserted, because there is a high probability of…
Aman
  • 4,786
  • 1
  • 17
  • 17
1
vote
2 answers

Load matrix from file into PostgreSQL table

I have a file universities.txt which looks like this: Alabama Air University Alabama A&M University Alabama State University Concordia College-Selma Faulkner University Huntingdon College Jacksonville State University Judson College Miles…
chaze
  • 365
  • 1
  • 3
  • 14
0
votes
2 answers

PostgreSQL's COPY statement

Using COPY statement of PostgreSQL, we can load data from a text file into data base's table as below: COPY CME_ERROR_CODES FROM E'C:\\Program Files\\ERROR_CODES\\errcodes.txt' DELIMITER AS '~' The above statement is run from a machine which has…
vchitta
  • 2,043
  • 9
  • 28
  • 37
0
votes
2 answers

Writing the output of sql query to a file

I have an SQL query: SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables where tablename like 'r_395%'; whose output is this: ?column? -------------------- DROP TABLE r_395_0 DROP TABLE r_395_1 DROP TABLE r_395_2 DROP TABLE…
lamostreta
  • 2,359
  • 7
  • 44
  • 61
0
votes
0 answers

PostgreSQL COPY Syntax Failure (My issue - PostgreSQL is fine)

I'm sure it is obvious that I am a "NEWB" - gotta start somewhere, right? Having an issue understanding how to implement PostgreSQL COPY as depicted in the image below: PostgreSQL COPY Error Here's the query I attempted to run: COPY…
BEB_1963
  • 1
  • 1
0
votes
0 answers

I uploaded a csv file from local computer to user stage using PUT command . then I used COPY command but i am facing error

I created a file format: create or replace file format po_req type='csv' compression='none' FIELD_DELIMITER = ',' RECORD_DELIMITER = '\n' TRIM_SPACE = FALSE ESCAPE_UNENCLOSED_FIELD= '\\' NULL_IF = ('NULL', 'null', '') …
Shubham Joshi
  • 1
  • 1
0
votes
0 answers

ERROR: could not open file "C:\Users\kelly\Downloads\orders_data.csv" for reading: No such file or directory

It shows this when I try to import a csv file to my postgresql I did this but still shows similar error COPY public."orders_data" from 'C:\Users\kelly\Downloads\orders_data.csv' DELIMITERS ',' csv Header; I have created my table already, I just…
Kelly
  • 1
  • 1
0
votes
1 answer

What is the maximum query length accepted for a PostgreSQL multi-valued SELECT query?

The context is that I am writing a script to send load/insert query to a postgres server to insert rows into an existent table. I read many resources about the topic, about the approach to choose, between "COPY" and multi-valued SELECT query. I want…
mabkoz
  • 13
  • 3
0
votes
0 answers

It's possible copy many PostgreSQL tables into one file?

Is there possibility to copy (In loop) many tables into one csv file? Command copy overwrite file so I get only last set of data... in file: execute 'COPY (select * from '||source_table_name||' order by time) to…
Bear4
  • 17
  • 6
1 2 3
…
9 10