How can we help you today? How can we help you today?

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

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.

kpdude7
0

Comments

1 comment

Sort by
  • 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);
    DanC
    0

Add comment

Please sign in to leave a comment.