I'm composing a liquibase script to create a table with Geometry column (from PostGis extenstion of PostGreSQL DB)
However, I couldn't manage to get it working. My script:
- changeSet:
id: 5-change-set-places
comment: Create places table
author: LongLe
validCheckSum: 1:any
changes:
- sql:
dbms: postgresql
endDelimiter: ;
splitStatements: true
sql: |
CREATE TABLE IF NOT EXISTS "${schema}"."places" (
"id" bigint NOT NULL,
"geom" GEOMETRY(Geometry, 4326) NOT NULL
);
GRANT SELECT, INSERT, UPDATE, DELETE ON "${schema}"."places" TO "${rolename}";
ALTER TABLE "${schema}"."places"
DROP CONSTRAINT IF EXISTS "places_pkey",
ADD CONSTRAINT "places_pkey" PRIMARY KEY ("id");
However, it failed with below error:
ERROR: type "geometry" does not exist
I also referred to liquibase-spatial extension https://github.com/lonnyj/liquibase-spatial and compose the equivalent config:
- changeSet:
id: 5-change-set-places
comment: Create places table
author: LongLe
validCheckSum: 1:any
changes:
- createTable:
columns:
- column:
constraints:
nullable: false
primaryKey: true
primaryKeyName: places_pkey
name: id
type: bigint
- column:
constraints:
nullable: false
name: geom
type: GEOMETRY(Geometry,4326)
tableName: test
But it still failed with the error:
31-03-2021 21:07:31.587 [main] WARN c.m.ms.block...applyLiquibase - Error applying liquibase attempt 5 : Migration failed for change set classpath:db/changelog/db.changelog-master.yaml::5-change-set-places::LongLe: Reason: liquibase.exception.DatabaseException: ERROR: type "geometry" does not exist Position: 67 [Failed SQL: CREATE TABLE testschema.places (id BIGINT NOT NULL, geom geometry(Geometry, 4326) NOT NULL, CONSTRAINT places_pkey PRIMARY KEY (id))]
But when I took out the SQL from the log to run:
CREATE TABLE testschema.places (id BIGINT NOT NULL, geom geometry(Geometry, 4326) NOT NULL, CONSTRAINT places_pkey PRIMARY KEY (id))
Then it works just fine.
I must have missed some very simple thing, but I checked up & down without any clue. Anyone has a better idea?
