dropPrimaryKey doesnt work with PGSQL

I tried drop a primary key without declare the constraint name and I received this error:

Caused by: liquibase.exception.DatabaseException: ERROR: constraint “num_liquidation_pkey” of relation “num_liquidation” does not exist
Where: SQL statement “alter table public.num_liquidation drop constraint NUM_LIQUIDATION_pkey”
PL/pgSQL function inline_code_block line 7 at EXECUTE [Failed SQL: (0) DO $$ DECLARE constraint_name varchar;
BEGIN
SELECT tc.CONSTRAINT_NAME into strict constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’
AND TABLE_NAME = ‘num_liquidation’ AND TABLE_SCHEMA = ‘public’;
EXECUTE 'alter table public.num_liquidation drop constraint ’ || constraint_name;

I noticed that an error in SQL statment, the constraint name is not in quotes. Is this a bug or I configurate wrong?

Yaml:
- dropPrimaryKey:
dropIndex: true
schemaName: public
tableName: NUM_LIQUIDATION

Liquibase Version: 4.4.0
Liquibase Community 4.4.0 by Datical

Hi @vagner.montenegro ,

Would you be able to help us with the steps to reproduce this issue at our end? What exactly should be done to reproduce it?

Thanks!
Rakhi Agrawal

My project is Java Spring Boot.

my pom:

	<dependency>
		<groupId>org.liquibase</groupId>
		<artifactId>liquibase-core</artifactId>
		<version>4.4.0</version>
	</dependency>
	<dependency>
		<groupId>org.postgresql</groupId>
		<artifactId>postgresql</artifactId>
		<version>42.2.22</version>
	</dependency>

My changelog:

Yaml:

  • dropPrimaryKey:
    dropIndex: true
    schemaName: public
    tableName: NUM_LIQUIDATION

I run the follow command: mvn spring-boot:run

And on startup the app, the erro was showed. As show in the error the follow SQL is run:

SELECT tc.CONSTRAINT_NAME into strict constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’
AND TABLE_NAME = ‘num_liquidation’ AND TABLE_SCHEMA = ‘public’;
EXECUTE 'alter table public.num_liquidation drop constraint ’ || constraint_name;

I executed this SQL in my PgAdmin and I have the same error, but when I replace the constraint_name for name in quotes, the SQL run normally

Hi @vagner.montenegro
You need to provide the name of the primary key that you want to drop. Just like you said when you replace the constraint_name with the name it will run normally.
dropPrimaryKey:
dropIndex:true
schemaName:public
tableName:NUM_LIQUIDATION
constraintName: theprimarykeyconstraintname

And you need to add the right amount of tabs for the yaml section

but this parameter is not mandatory, including liquibase makes a query in the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table to get the name of constraint.

If you look at the error, you’ll see that liquibase performs a function just for that.

Does this work with Liquibase 4.3.5? If so it’s a bug we introduced in 4.4.x. Please create an issue on our Github site.

If it still fails, then we can look at more options.