Dump from Postgres is not working in HyperSQL

Question

I have a requirement where I take a dump from Postgres server and run it in in-memory HyperSQL server.

For example let's consider below query to create a customer table:

CREATE TABLE customer (
id integer DEFAULT nextval('public.customer_seq'::regclass) NOT NULL,
name character varying(255) DEFAULT NULL::character varying,
contact character varying(255) DEFAULT NULL::character varying,
address character varying(255) DEFAULT NULL::character varying);

Above query is generated from pg_dump but when I run the same query on HyperSQL it is not able to identify things starting with :: in this case ::character varying and ::regclass.

Couldn't find anything useful in the pg_dump or Postgres manual to exclude these things from dump.

For now I am replacing these things with other unwanted things programmatically in Java. Is there any better way to do so?

Note: I am already using SET DATABASE SQL SYNTAX PGS TRUE; but it is not helping in this case.

Answer 1

NULL is the default by default. You can change your table to get rid of the explicit (and apparently pointless) default clause by doing:

alter table customer alter name drop default;

etc. I am a little surprised pg_dump doesn't drop it for you.