CODEWITHSUNDEEP
sqlpostgresql
aatuc210
aatuc210

Reputation: 1868

How to use sql commands with psql copy in the same file

I have a series to sql commands that I'm running, and one of them is a copy of the sql results to a file. I then want to drop the table. Here's an example:

psql - d dw -U postgres -f input.sql

contents of input.sql ---------

CREATE TABLE a_temp AS
SELECT a.*
    FROM a

COPY dw.a_temp to '/opt/data/a/a.csv (delimiter E'\x01');

DROP table a_temp;

Is this all possible to do in one file? Right now the script just stops after the CREATE TABLE, and the copy is never run. We're trying to avoid multiple invocation of the PSQL executable.

Upvotes: 0

Views: 437

Answers (2)

aatuc210
aatuc210

Reputation: 1868

What is your version of postgresql? In later versions you can COPY directly from a SELECT query. – Igor Romanchenko

This is the solution I ultimately went with. So the statement looks like

COPY (SELECT * FROM a)
TO '/opt/data/a/a.csv' 
WITH DELIMITER E'\x01';

No temporary table necessary!

Upvotes: 0

zero323
zero323

Reputation: 330203

Missing semicolon after CREATE TABLE and missing apostrophe after path.

CREATE TABLE a_temp AS
SELECT * FROM a;

COPY dw.a_temp TO '/opt/data/a/a.csv' DELIMITER E'\x01';

DROP table a_temp;

Depending on context there can be also problem with schema. If you set SEARCH_PATH to 'dw' it should work, otherwise you probably create table a_temp in public, and try to copy from dw.

Upvotes: 1

Related Questions