DEFAULT nextval('sequence_name') in CREATE TABLE not working

[Ryan de Laplante]

Hi,

I'm trying to use H2 in unit tests. After I start it up I execute all
of the database update scripts in a directory to make it the same as
our PostgreSQL database. I'm finding some syntax doesn't work in H2
even though I set ;mode=PostgreSQL in my JDBC connect string:

CREATE SEQUENCE history_id_seq INCREMENT 1 START 1;

CREATE TABLE history
(
id integer NOT NULL PRIMARY KEY DEFAULT nextval('history_id_seq')
)

H2 complains about DEFAULT[*] and says it expect DEFAULT 0 ? How
can I tell it to use the next value from a sequence as the default
value, as I do in postgres?

Another query that fails is my CREATE ROLE queries:

CREATE ROLE myapp LOGIN ENCRYPTED PASSWORD
'md5.............................'
NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

It complains about LOGIN. I looked at the H2 documentation and CREATE
ROLE doesn't show any of those keywords.


Thanks,
Ryan

[Thomas Mueller]

Hi,

> CREATE TABLE history
> (
> id integer NOT NULL PRIMARY KEY DEFAULT nextval('history_id_seq')
> )

The following works for both PostgreSQL and H2:

CREATE TABLE history
(id integer NOT NULL DEFAULT nextval('history_id_seq') PRIMARY KEY);

The PostgreSQL documentation says DEFAULT should be before PRIMARY KEY:

http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint ] ...

> Another query that fails is my CREATE ROLE queries:
>
> CREATE ROLE myapp LOGIN ENCRYPTED PASSWORD
> 'md5.............................'
> NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
>
> It complains about LOGIN. I looked at the H2 documentation and CREATE
> ROLE doesn't show any of those keywords.

In this area, the syntax for H2 is quite different to PostgreSQL. For
H2, a role is a user group (or a group of roles). For PostgreSQL, a
role may be a user or a user group.

H2 is not 100% compatible with PostgreSQL, and it will probably never
be. The most important features are implemented, and missing features
are added if they are popular. So far, the features you describe were
not very popular, that's why H2 doesn't know them yet.

Regards,
Thomas