CODEWITHSUNDEEP
postgresqlliquibase
Marcos Echagüe
Marcos Echagüe

Reputation: 597

COPY FROM STDIN does not work in liquibase

I'm trying to upload a lot of data from a .sql file using the COPY command for Postgresql. I have those data in a file.sql in the following format :

COPY my_table(id, name, status)  FROM stdin; 
1   peter  active
1   steve  active
1   maria  active
\.

And my changeset like this:

<changeSet id="sqlFile-example" author="me" >
        <sqlFile encoding="UTF-8"
                 path="file.sql"
                 relativeToChangelogFile="true"
                 endDelimiter=";"
                 splitStatements="false"
        />
 </changeSet>

And get this error:

[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.6.3:update (default-cli) on project lincoln-soft: Error setting up or running Liquibase: Migration failed for change set src/main/resources/db/liquibase/db-changelog.xml::sqlFile-example::me

[ERROR] Reason: liquibase.exception.DatabaseException: ERROR: unexpected message type 0x50 during COPY from stdin

[ERROR] Where: COPY my_table, line 1 [Failed SQL: COPY my_table(id, name, status) FROM stdin;

[ERROR] 1 peter active

[ERROR] 1 steve active

[ERROR] 1 maria active

[ERROR] \.]

I there a way to upload those data by liquibase?

Upvotes: 7

Views: 19933

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 246848

Mixing the COPY statement and the data in the same file only works in psql scripts.

There is support for COPY in the JDBC driver, but you'd have to use the CopyManager explicitly.

You should use INSERT statements in your script. If you want to load a pg_dump with the JDBC driver, use pg_dump --inserts (but expect slower performance).

Upvotes: 3

Marcos Echag&#252;e
Marcos Echag&#252;e

Reputation: 597

Finally got a solution, as @a_horse_with_no_name and @Laurenz Albe mentioned, can't use COPY FROM STDIN directly in JDBC, so I used pg_dump to generate insert statements like this:

pg_dump --table=public.my_table --data-only --column-inserts my_databse > /tmp/my_table_data.sql

It gives me a file my_table_data.sql with the inserts statements like this:

INSERT INTO public.my_table (id, name, status) VALUES (1,peter,active);
INSERT INTO public.my_table (id, name, status) VALUES (1,peter,active);
INSERT INTO public.my_table (id, name, status) VALUES (1,peter,active);

And then I use this liquibase Chageset to upload the sql file:

<changeSet id="sqlFile-example" author="me" >
     <sqlFile encoding="UTF-8"
           path="my_table_data.sql"
           relativeToChangelogFile="true"
           splitStatements="true"
           stripComments="true"
     />
</changeSet>

It works for me

Upvotes: 7

user330315
user330315

Reputation:

As Laurenz already mentioned: you can't use COPY FROM STDIN directly in JDBC (you can use the CopyManager API to implement that manually, but Liquibase doesn't support that and I also don't know of any plugin that would do that)

I would suggest you use Liquibase's built-in ability to load CSV (text) files. Put your input data in CSV file, e.g. my_table_data.txt with a header line for the columns:

id,name,status
1,peter,active
1,steve,active
1,maria,active

Then use <loadData> instead of running a SQL script:

<changeSet id="sqlFile-example" author="me" >
        <loadData tableName="my_table"
                  file="my_table_data.txt" 
                  separator=","
                  encoding="UTF-8">
</changeSet>

Upvotes: 3

Related Questions