Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

Questions tagged [copy]

COPY is a PostgreSQL SQL statement for bulk loading tables and fetching table data or query results

Filter by
Sorted by
Tagged with
2 votes
1 answer
57 views

Should I delete index before bulk-loading data with COPY?

My pet project has one table which is totally rewritten once a day. This project uses a PostgreSQL database. I decided to use COPY to insert data from 4 CSV files (about 3 million rows). At the same ...
Rash-77's user avatar
  • 21
0 votes
1 answer
28 views

AWS RDS DB Migration

I have a PostgresFB on an AWS RDS instance that we want to migrate to an on-site server. The problem we having is the migrating of data and the time it is taking to migrate the data. I have tried AWS ...
Elene Roos's user avatar
0 votes
0 answers
25 views

Use COPY in PostgreSQL to insert huge amount of data to one table and populate another table at the same time using IDs

I have the following 2 tables in my database: CREATE TABLE IF NOT EXISTS segments ( id SERIAL, segment TEXT NOT NULL, lang_id INTEGER NOT NULL REFERENCES languages(id), ...
Panos's user avatar
  • 1
3 votes
1 answer
257 views

Creating a Trigger in MySQL that has a JOIN

PROBLEM: I'm trying to create a TRIGGER that will copy records from 2 different tables (users, addresses) in database A to a single table (users) in database B. Basically, I'm trying to consolidate ...
Tony Stephens's user avatar
0 votes
1 answer
35 views

Proper way of copying larger data from another Azure database?

Being the 3rd party that is allowed to read from the Azure SQL (in cloud), we need to simulate access to the data as in the older version of the ERP that used plain old MS-SQL server. Only the subset ...
pepr's user avatar
  • 143
0 votes
1 answer
482 views

Move timescaledb hypertable from a postgresql server to another one

I want to move a timescaledb hypertable that's on the terabytes order. I wanted to do something like the following: pg_dump -t "schema1"."table1" -h host1_ip -U username1 dbname1 | ...
Héctor's user avatar
  • 297
0 votes
2 answers
80 views

Determine new/changed data(rows) on a table during a table clone operation

I'm trying to alter some heavy data tables tables, this operation may cause some impact on our production service, so to minimize outages the plan is: create a copy table that inherits table ...
Retrosec6's user avatar
2 votes
1 answer
268 views

Not able to wrap values in quotes when exporting CQL table to CSV

I am not able to copy data into a CSV file and have them wrapped with single quotes: e.g. Table: myks.mytable column1 column2 value1 value2 command used: copy myks.mytable (column1,column2) to '...
Tarik Haddad's user avatar
0 votes
0 answers
586 views

How do I use COPY FROM on a backup file with multiple tables?

I used this command to backup my "supplies" table: COPY supplies (id, price, ...) FROM 'C:\Users\MyCompany\Desktop\database03-01-2022.sql'; But now, I would like to write three more tables' ...
water_enjoyer's user avatar
0 votes
1 answer
294 views

attribute 51 of type users has wrong type

I'm trying to import data to a pg table from a csv file. The error I get is: $ psql -U postgres -c "COPY users (first_name) FROM '/users.csv' (FORMAT csv)" ERROR: attribute 51 of type users ...
x-yuri's user avatar
  • 367
1 vote
1 answer
5k views

unterminated CSV quoted field when to import csv data file into postgresql

Create a table : CREATE TABLE num ( id serial NOT NULL , adsh VARCHAR(20) NOT NULL, tag VARCHAR(256) NOT NULL, version VARCHAR(20) NOT NULL, coreg VARCHAR(256), ddate date NOT ...
showkey's user avatar
  • 334
0 votes
2 answers
481 views

Export colums containing (geo)JSON elements to CSV while keeping the JSON double quotes properly

I want to export using copy 5 columns of a table, 3 of them contain ID, text and timestamp values but 2 of them contain JSON and GeoJSON elements, e.g.: // Please, note the non-quoted string Value1 /...
s.k's user avatar
  • 372
0 votes
1 answer
1k views

Insert from select without losing progress in the middle by Postgresql? "INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)"

Is it possible to move data to the new table sourcing other tables without losing progress in the middle? I think of plpgsql that read data by chunks and commit after every N inserts... If the process ...
gavenkoa's user avatar
  • 509
-1 votes
1 answer
3k views

Why can't copy data into csv file with double quotes?

The data can be exported with single quote in copy command: COPY mytest TO '/tmp/test.csv' DELIMITER ',' CSV HEADER; COPY 5 With double quotes: COPY mytest TO "/tmp/test.csv" DELIMITER ',' ...
showkey's user avatar
  • 334
0 votes
1 answer
226 views

Error with non-standard identifier in \copy command [closed]

Would like to be able to add characters like '-' in the schema name when running COPY command in PostgreSQL. Any way to get around this? psql -d postgres -c "\COPY (SELECT * FROM test-schema....
amateur's user avatar
  • 103
0 votes
2 answers
2k views

How to quote arrays in postgres copy statement to insert into JSON field

I have some data that looks like this: id,bo,c,url 1,"some text here", 22, "[{'id': 'NfKA3', 'u': 'https://somewebsite.com'}]" 2, "more text", 83, "[{'id': 'W3nAl', '...
Matt's user avatar
  • 271
0 votes
1 answer
3k views

Most efficient method to import bulk JSON data from different sources in postgresql?

I need to import data from thousands of URLs, here is an example of the data: [{"date":"20201006T120000Z","uri":"secret","val":"1765.756"},{...
Lautaro Aguilera's user avatar
1 vote
2 answers
4k views

postgresql copy comand with date in filename

I have a query and want the output in a .csv file with today's date and time in the name (I am using PostgreSQL v12). COPY (SELECT * FROM mytable) TO 'C:\Users\me\mylocation\filename_date_time.csv' ...
we need a Mat. Stat.'s user avatar
11 votes
1 answer
8k views

Postgres COPY with on conflict ignore - possible?

I want to add on conflict ignore to the Postgres copy command. I know I can copy the data to a table without unique index /primary key and then use insert with the on conflict syntax. But I wanted to ...
Nir's user avatar
  • 533
2 votes
3 answers
5k views

Postgres COPY with multiple small chunks

In postgres, we can do COPY to export the data into file. But all the data will be saved into a file. But I want to chunk that file into small files. We can do this after the export. But is there a ...
TheDataGuy's user avatar
  • 1,940
0 votes
2 answers
443 views

Using COPY in Postgres

I know COPY is the best utility to export the data from a table or a query to output files. But if we are doing an export like \COPY (select * from table) to '/tmp/file.csv' WITH CSV; How its ...
TheDataGuy's user avatar
  • 1,940
2 votes
1 answer
664 views

Load text column to postgres

I use the following to load data into postgres \copy tab FROM /tmp/file DELIMITER E'\t' QUOTE E'\b' NULL AS '\N' CSV Usually I select the data from a source db echo "select * from tab" | ...
Nir's user avatar
  • 533
0 votes
1 answer
662 views

Why doesn't the $1 syntax work for all queries?

I tried to do this: COPY postgres_log FROM $1 WITH csv; And with a separately sent file path, like I make all normal queries (SELECT/UPDATE/INSERT/DELETE). However, it gives: ERROR: syntax error at ...
user avatar
3 votes
1 answer
3k views

How to call from a shell program a postgresql COPY FROM FILE command, passing the path of the FILE as an argument?

I am trying to run, via a linux shell script, a Postgresql command that reads a CSV and updates a table with it. The difficulty I am facing, is that this shell script has to pass an argument to the ...
olivier2101's user avatar
0 votes
0 answers
187 views

Tuning postgres so it's optimized for bulk COPY imports [duplicate]

I am using the COPY command of Postgres to bulk insert data once a minute from an in-memory CSV file. I written the COPY code so that it performs the COPY's in parallel on separate db connections. My ...
Fachtna Simi's user avatar
1 vote
0 answers
1k views

PostgreSQL COPY - Generated columns cannot be used in COPY

Im trying to import a SQL dump file for a table (A COPY command dump file). The table has a generated column. While restoring the dump Im getting this error. ERROR: column "call_status" is ...
TheDataGuy's user avatar
  • 1,940
0 votes
1 answer
2k views

How to copy user rights and roles from one user to another

I require a PL/SQL script that copies all of a user's rights, roles and mandates from one user to another. I am using Oracle 12c.
Sangathamilan Ravichadnran's user avatar
1 vote
3 answers
6k views

How to import multiple CSV files to a postgres table using pgadmin or other method?

I'm looking to import a folder of over 100+ csv files into one table on PostgreSQL using pgadmin. I have the following script but is there a way to change the file path to all csvs? COPY schema.table ...
Chris Jenner's user avatar
0 votes
1 answer
3k views

Copy MySQL User but change HostName

I am migrating to a docker MariaDB setup and all my users were created w/ "Localhost". From what I read and tested ironically '%' excludes localhost. I want to copy my existing users adding a second ...
FreeSoftwareServers's user avatar
0 votes
1 answer
234 views

vertica/postgres copy text to binary column

Until now I used Oracle to store xml file in blob data type column. Is there any option in Vertica to copy xml file into binary column? I saw there is varbinary data type but, I haven't succeeded to ...
NoamiA's user avatar
  • 103
0 votes
2 answers
356 views

COPY Command spawning multiple processes

I have a lengthy data pipeline that imports a CSV, creates a sequence of dependent views and then exports the results using COPY as a CSV. This process had been working fine, completing in around 30-...
Matt's user avatar
  • 271
1 vote
3 answers
258 views

MS SQL 2008 R2 - How to use a production database through dev/test environments or how to copy a SQL schema and all its data to another

We currently have 3 Windows Server 2008 R2 VM identically build for Sage x3 v6.5, one for production/formation/budget database, a second one for QA (qualification) and a third one for dev & tests. ...
tigerblue77's user avatar
0 votes
0 answers
218 views

Firebird begin backup and forced writes off, timestamp update delayed

We have a Firebird database that's almost 200 Gbyte in sice, and for performance we have forced writes off. Please don't debate the risks of forced writes, we are aware of them. This is Firebird 3.0.4 ...
Kjell Rilbe's user avatar
1 vote
2 answers
865 views

Large database backup files integrity check after or before copy

I was trying to copy large database backup files(appx. 2TB each) to different region using robocopy. But I have noticed the speeds are drastically slow when using the switch /Z. So I have removed this ...
S.D.'s user avatar
  • 746
0 votes
2 answers
156 views

PostgreSQL dump distinct column without memory consumption

I'm trying to dump csv of distinct varchar(43) column to file, table has around billion rows, select distinct command either runs out of memory and command is aborted or server just closes all ...
Phalelashvili's user avatar
2 votes
2 answers
6k views

Importing plain (raw) files into a postgres table (bytea and large objects)

What is a good way to insert/copy plain files into a postgres table, preferrably using the psql command-line? In my case the files are a bunch of eMails from Maildir archives, so I tried to use COPY: ...
nyov's user avatar
  • 135
2 votes
1 answer
1k views

PostgreSQL Copy command internals

I need to know some internals about the copy command in PostgreSQL 10. In particular I need to know if the copy from into a remote database makes batch insertions. If so, what is the default ...
nprog89's user avatar
  • 23
3 votes
1 answer
227 views

Is it possible to load data into Postgres using COPY, where the data includes a SELECT statement?

(I think the answer is probably "no", but I haven't managed to find anything where anyone tried to do this. Which maybe is a sign in itself). So, I have a parent and child table than I'm generating ...
khafka's user avatar
  • 79
0 votes
1 answer
697 views

Can we copy data from 1 text file to 2 postgresql tables?

I have a text file with some data and I have two similar tables in my postgresql database. Is it possible to copy a part of the data present in the file, say first 10 lines, to the first table and the ...
suvrat's user avatar
  • 57
0 votes
0 answers
43 views

Can copy command in postgresql append data to a file? [duplicate]

I want to add some data from a postgresql table to a text file. But already some data is present in the file. I do not want to delete that data. Is it possible? The questions asked earlier were the ...
suvrat's user avatar
  • 57
0 votes
2 answers
254 views

Can't see process progress, machine getting slow trying to import 500 million records

Working environment: PostgreSQL 12.0, Ubuntu 18.10, 4GB memory, i5-3230M CPU @ 2.60GHz × 4, Os 64 bits I'm trying to import a .csv file with more than 500 million records into a PostgreSQL db table, ...
dwenzel's user avatar
  • 63
2 votes
1 answer
2k views

Parse a unix/epoch value as a `TIMESTAMP` in PostgreSQL

PostgreSQL's COPY command is useful to quickly import lots of data, and the data must be in the textual representation for the datatype. I am importing lots of data, includes a timestamp column, but ...
Amandasaurus's user avatar
2 votes
1 answer
5k views

Improve speed of COPY COMMAND in postgres

I would like to understand if increasing work_mem does help improving the speed of the COPY command. Does COPY use work_mem or maint_work_mem extensively?
Ramya's user avatar
  • 445
0 votes
1 answer
135 views

Copy Data from Two different databases with FK relation

So the situation is as follow: I have production DB called DB1 which has huge data, around 250 GB. I have Test DB called DB2 on my local machine which has the same schema as the production DB but ...
Nano's user avatar
  • 139
0 votes
2 answers
111 views

Get value from one field, and paste it in to a new column

I'm relatively new to Microsoft Access, and just a beginner with SQL programming. I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or ...
Neymar0001's user avatar
2 votes
4 answers
5k views

Copy table from one server to another including keys and indexes

I have been working on a small project for about two weeks. We want to consolidate a few tables from several remote server databases in order to run queries and reports on separate from the live ...
DaleP's user avatar
  • 21
0 votes
0 answers
305 views

How to copy/export part of my SQL database

So I have this huge live SQL database. I want to do some testing so I thought to export it, however, it is around 200 GB which is impossible to export. I thought maybe I export the schema of it and ...
Nano's user avatar
  • 139
3 votes
3 answers
3k views

Postgres \copy command into single column without delimiter

I am trying to import big fixed length files into Postgres and I am using the copy command and a staging table following this guide. The problem is that this files has a lot of different characters ...
Giacomo Ronconi's user avatar
1 vote
1 answer
8k views

The simplest way to copy sql tables from one database to another?

So I have been logged into two different database. I want to copy one table from the first database to the other one with its content. I want to overwrite the content of the destination one (Because I ...
Nano's user avatar
  • 139
0 votes
0 answers
30 views

regularly calculating data and then loading to production database

I have a Postgres database running a small website. Most of the tables in the database are calculated values derived from various input data. This input data is updated approximately monthly. The ...
susie derkins's user avatar