liquibase.exception.DatabaseException: ERROR: syntax error at or near "\"

General Discussion
1

Hi Team,

I am trying to run the Liquibase update with below change and getting the error message. Can someone please help me oh this issue.

Liquibase validate, update-sql commands are working fine, only liquibase update command is failing.

Note: I am using postgres database to do the same in local machine. Manual steps execution is working fine, but when I run the same change using Liquibase getting below error.

dbchange.sql:
============
–liquibase formatted sql
–changeset srinivas:1.sql

\set tbl_prefix

\set tbl_passpoint_user :tbl_prefix’cloud_passpoint_user’
\set tbl_subscribers :tbl_prefix’cloud_subscribers’
\set tbl_myiq_service :tbl_prefix’cloud_mycommunityiq_service’

\echo :tbl_passpoint_user
\echo :tbl_subscribers
\echo :tbl_myiq_service

—Migration Steps—

—Step1

UPDATE :tbl_passpoint_user u
SET name = s.name, first_name = s.first_name, last_name = s.last_name
FROM :tbl_subscribers s
WHERE u.org_id = s.org_id AND u.subscriber_id = s.subscriber_id
;

—Step2

UPDATE :tbl_passpoint_user u
SET uuid = uuid(s.subscriber_id)
FROM :tbl_subscribers s
WHERE u.org_id = s.org_id AND u.subscriber_id = s.subscriber_id AND s.subscriber_location_id LIKE ‘srinivas:%’
;

—Step3

UPDATE :tbl_myiq_service myiq
SET user_id = u.uuid
FROM :tbl_passpoint_user u
WHERE u.org_id = myiq.org_id AND u.subscriber_id = myiq.subscriber_id
;

Error Message:
================

Running Changeset: dbchange.sql::dbchange::srinivas

Unexpected error running Liquibase: Migration failed for changeset dbchange.sql::dbchange::srinivas:

  • Reason: liquibase.exception.DatabaseException: ERROR: syntax error at or near "\"*
  • Position: 1 [Failed SQL: (0) \set tbl_prefix*

\set tbl_passpoint_user :tbl_prefix’cloud_passpoint_user’
\set tbl_subscribers :tbl_prefix’cloud_subscribers’
\set tbl_myiq_service :tbl_prefix’cloud_mycommunityiq_service’

\echo :tbl_passpoint_user
\echo :tbl_subscribers
\echo :tbl_myiq_service

UPDATE :tbl_passpoint_user u
SET name = s.name, first_name = s.first_name, last_name = s.last_name
FROM :tbl_subscribers s
WHERE u.org_id = s.org_id AND u.subscriber_id = s.subscriber_id]

2

Liquibase executes the SQL from the changelog through JDBC, so Postgres-specific directives like \set and \echo will most likely give you the syntax error you are seeing.

3

Hi @wwillard , Does liquibase handle these type errors? If yes may I know the work around to fix this issue please.

4

Liquibase just catches and reports the SQL exception thrown by the driver.

5

@wwillard Which means Liquibase doesn’t support the specifications like \set , \echo etc…

Please help us on this.

6

Liquibase Community version does not support database-specific directives. Liquibase Pro does support these directives with its native executor feature, which runs the SQL through the database’s command line tool, i.e. psql, sqlplus, etc.