Cannot parse string: ALTER TABLE public.xxxx ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY

[jometho]

Met this exception while trying to compare two PostgreSQL dumps exported using pg_dump from IntelliJ

Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Cannot parse string: ALTER TABLE public.agents ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME public.xxxx_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
Unsupported command at position 43 'ADD GENERATED BY DEF'
at cz.startnet.utils.pgdiff.parsers.Parser.throwUnsupportedCommand(Parser.java:403)
at cz.startnet.utils.pgdiff.parsers.AlterRelationParser.parseAlterColumn(AlterRelationParser.java:343)
at cz.startnet.utils.pgdiff.parsers.AlterRelationParser.parse(AlterRelationParser.java:86)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:239)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:311)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:35)
at cz.startnet.utils.pgdiff.Main.main(Main.java:39)

[jalissonmello]

Can you help testing ? There is 2.6.1 jar on https://github.com/jalissonmello/apgdiff/tree/develop/releases

[AVee]

I just tested this using 2.7.0, the basic case seems to work. However it still fails when dealing with permissions on the sequence, because it doesn't know the sequence exists.

This can be reproduced with this database:

CREATE TABLE test (id bigint generated always as identity, value text);
CREATE ROLE idtest;
GRANT select, update on sequence test_id_seq to idtest;

This generated the following dump:

--
-- Name: test; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.test (
    id bigint NOT NULL,
    value text
);


ALTER TABLE public.test OWNER TO postgres;

--
-- Name: test_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.test ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.test_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: SEQUENCE test_id_seq; Type: ACL; Schema: public; Owner: postgres
--

GRANT SELECT,UPDATE ON SEQUENCE public.test_id_seq TO idtest;

And when compared to itself generates this error:

$ apgdiff idtest.sql idtest.sql 
Exception in thread "main" java.lang.RuntimeException: Cannot find sequence 'public.test_id_seq' for statement 'GRANT SELECT,UPDATE ON SEQUENCE public.test_id_seq TO idtest;'. Missing CREATE SEQUENCE?
        at cz.startnet.utils.pgdiff.parsers.GrantRevokeParser.parse(GrantRevokeParser.java:275)
        at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:278)
        at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:322)
        at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:35)
        at cz.startnet.utils.pgdiff.Main.main(Main.java:39)