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
0
votes
0answers
22 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 ...
0
votes
0answers
20 views

PostgreSQL - pgAdmin 4 - COPY command not working with Schema

When trying to use the COPY command in pgAdmin 4 to copy a table from my database into a CSV file on disk copy Administrative.REGIONS TO 'd:\temp\regions.csv' with csv header I get the following ...
0
votes
1answer
42 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.
0
votes
1answer
45 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 ...
0
votes
1answer
25 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 ...
0
votes
1answer
31 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 ...
0
votes
1answer
36 views

Postgres randomly fails silently on huge CSV import

With Postgres 10.12 on Windows 10, I am importing at 67 GB CSV file into a database using a simple COPY query: COPY table_name FROM 'c:/temp/data.csv' WITH (FORMAT csv, HEADER); The import fails ...
0
votes
2answers
24 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-...
0
votes
3answers
94 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. ...
0
votes
0answers
32 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 ...
0
votes
2answers
62 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 ...
0
votes
2answers
37 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 ...
0
votes
2answers
459 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: ...
2
votes
1answer
104 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 ...
3
votes
1answer
69 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 ...
0
votes
1answer
180 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 ...
0
votes
0answers
23 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 ...
0
votes
2answers
59 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, ...
0
votes
1answer
211 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 ...
0
votes
1answer
223 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?
0
votes
1answer
61 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 ...
0
votes
2answers
71 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 ...
2
votes
4answers
841 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 ...
0
votes
0answers
45 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 ...
1
vote
2answers
535 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 ...
0
votes
1answer
43 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 ...
0
votes
0answers
19 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 ...
3
votes
1answer
1k views

Fastest way to extract full table in Postgres

I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This ...
3
votes
2answers
458 views

Log Shipping Copy Job Fails once in a while

I have configured log shipping at multiple database server with monitored server(separate server) and all of them are running fine without any issues. One particular server has 7 database and the ...
2
votes
1answer
116 views

Best way to copy data to other location without blocking source

I have a production database with some data I need to copy to other location, either another database on the same server or another server. The DB size is huge and I don't need all the data on the DB,...
0
votes
1answer
392 views

SELECT vs COPY (SELECT) TO STDOUT

I need to read more rows than fit in memory, resonably fast. I see two options: SELECT ... where my bindings use a cursor to stream the rows; COPY (SELECT ...) TO STDOUT (FORMAT binary) where my ...
0
votes
2answers
42 views

Copying all columns of a row from table into another?

I would like to keep the history of a table using SQL queries without triggers. The simplest way to do that is to fire a SELECT and INSERT into the history table like below: INSERT INTO history (...
1
vote
1answer
473 views

How to copy selected table data from sql server to mysql on a daily basis? [closed]

I am having a retail pos application with MSSQL database. Now I need to transfer selected table data from SQL server to MYSQL database on a daily basis like job. How to achive this?
-2
votes
1answer
3k views

Using Postgres \COPY function with TSV format

I am getting ERROR: COPY format "tsv" not recognized when trying to copy file contents with psql \copy function. For some reason Postgres does not know TSV format? Command that I am running is: psql -...
1
vote
1answer
672 views

Implications of using PostgreSQL COPY command to stdout and then immeadiately reading it from stdin

When transferring data between two databases, I would like to know if there is any disadvantages in the following approach: psql -h [HOST1] -U postgres -d [DB1] -c [SQL1] | psql -h [HOST2] -d [DB2] -...
0
votes
1answer
591 views

With PostgreSQL getting ERROR: COPY format “CSV” not recognized

Whever I try to copy from test=# \COPY table FROM 'file.csv' WITH ( FORMAT 'CSV', HEADER true ); ERROR: COPY format "CSV" not recognized What's the reason for this? I know I've used this before.
2
votes
0answers
129 views

Copy to file and transform arrays

I'm copying data from a Postgres DB on AWS RDS to a Postgres-like DB on AWS Redshift. Varchar arrays are being exported as {foo,bar} but Redshift wants them as ["foo","bar"]. Redshift doesn't ...
0
votes
1answer
2k views

MySQL copy all views from one database to another in stored procedure

I've got a stored procedure that copies all tables from one database to another but steps through each table individually (because of queries). I need to add to the stored procedure to copy all views ...
1
vote
0answers
437 views

Postgres pg_dump copy table to another database isn't copying columns

Goal: copy a single table including all columns from one database to another using psql on the command line, avoiding an issue with XML from QGIS layer styles. I am running the following on the ...
2
votes
1answer
792 views

postgresql - Importing CSV Data with Custom Type

I'm mostly a newcomer to databases and am building a postgres database of student achievement in a series of courses. My database uses a type called "class" that consists of the course, the academic ...
0
votes
1answer
32 views

Oracle 11g database copy

I got database oracle 11g on my localhost on laptop. I would like to copy it to run on other PC (windows) for some tests. How can I do that?
1
vote
1answer
793 views

How can I import CSV data stored in a bytea?

I have a CSV file that can be loaded into a table easily with COPY FROM, but the catch is that the file is stored in the database in a bytea field. Is the only way to use COPY on this data to write ...
0
votes
1answer
157 views

How can clone database in mariadb prompt character?

mysqlword="wwww" old="xxxx" new="yyyy" mysqldump $old -u root -p$mysqlword | mysql $new -u root -p$mysqlword Now new database was cloned as like old database. Can i use some command to clone ...
3
votes
2answers
96 views

SQL Server 2016 Primary and Backup

We have a new production box with two SQL servers,SQL Server 2016 Standard edition. The current plan is to have: One primary SQL server Backup SQL server:only SQL Report server,which points to ...
1
vote
1answer
378 views

How do I synchronize parallel imports with COPY? [closed]

I have ~20 tables that get built at the same time but using different psql processes (via bash script) at the first stage with something like: create table foo_1 as (select ....); -- via psql process ...
1
vote
0answers
278 views

Export json data into single cell

My problem is a relatively simple one; unfortunately, I haven't been able to find any solutions from searching around. I have a SQL table called alerts from which I'm trying to export data into CSV ...
0
votes
2answers
64 views

INSERT statement COPY equivalent [closed]

I would like to transform this: INSERT INTO users (username) VALUES ('JohnDoe'); Into a copy equivalent: COPY users (username) FROM ('JohnDoe'); What would be a correct syntax for the copy ...
2
votes
1answer
1k views

Can I trim values on COPY from csv?

I used csvsql to generate CREATE statements, and it looks like it assumed trimmed fields though the data has some spaces padding the field: ERROR: value too long for type character varying(11) ...
0
votes
1answer
708 views

How output a json file in postgresql functions?

I want to define a trigger in postgresql when updated a record in a db table, It output the record as a json file: CREATE TABLE mytable( id serial primary key, first_name varchar(40) NOT NULL, ...
1
vote
2answers
2k views

How do I use a dynamic command with copy from program?

I'm trying to read a JSON API with the Postgres copy from program command. When I call copy _temp from program 'curl -uri "https://url"'; it works fine but the API has paging and I need to loop ...