Flyway gives type not found or user lacks privilege for Postgres11 fields BIGSERIAL and TIMESTAMPTZ

kpdude7

Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration V1__create_ingest_schema.sql failed

---------------------------------------------

SQL State  : 42509

Error Code : -5509

Message    : type not found or user lacks privilege: TIMESTAMPTZ

Location   : db_migrations/V1__create_ingest_schema.sql (/Users/kpickensus.ibm.com/Documents/git/vlm-cloud-apis/bin/main/db_migrations/V1__create_ingest_schema.sql)

Line       : 1

Statement  : CREATE TABLE IF NOT EXISTS asset (

                       id BIGINT primary key not NULL,

                       media_id VARCHAR(50) NULL,

                       "state" VARCHAR(30) not NULL,

                       ingest_source_id BIGINT not null,

                       create_date TIMESTAMPTZ not null,

                       update_date TIMESTAMPTZ not null

)

at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:384) ~[flyway-core-9.15.2.jar:na]

at org.flywaydb.core.internal.command.DbMigrate.lambda$applyMigrations$1(DbMigrate.java:274) ~[flyway-core-9.15.2.jar:na]

Note in the above error that BIGINT was originally BIGSERIAL and I got a similar error; changing to BIGINT to avoid the error I then got the TIMESTAMPTZ error.

DanC

Hi @kpdude7

Can I ask what version of Flyway Desktop you're using?

When using the latest version of FWD and creating the same table, it gets output as:

CREATE TABLE public.asset (

    id bigint NOT NULL,

    media_id character varying(50),

    state character varying(30) NOT NULL,

    ingest_source_id bigint NOT NULL,

    create_date timestamp with time zone NOT NULL,

    update_date timestamp with time zone NOT NULL

);

ALTER TABLE public.asset ADD CONSTRAINT asset_pkey PRIMARY KEY (id);