SERIAL/BIGSERIAL Support

[Jesse Long]

Hi All,

I was excited to see the new support for SERIAL and BIGSERIAL in
1.3.170. We work predominantly in H2 and PostgreSQL, having consistent
DDL will help us a lot. Today I tried using these PostgreSQL data types
in H2 1.3.170, but found them lacking.

create table x (id bigserial not null primary key)
org.h2.jdbc.JdbcSQLException: Unknown data type: "BIGSERIAL"; SQL statement:
create table x (id bigserial not null primary key) [50004-170]

If I use SERIAL instead of BIGSERIAL, then it works, but it creates the
field as a BIGINT, not a normal INT, as is the case in PostgreSQL.

Please consider changing it ti match PostgreSQL behaviour as described
above. I think this patch may do it:

Thanks,
Jesse

Index: src/main/org/h2/value/DataType.java
===================================================================
--- src/main/org/h2/value/DataType.java (revision 4548)
+++ src/main/org/h2/value/DataType.java (working copy)
@@ -242,6 +242,12 @@
// in many cases the value is in the cache
20
);
+ add(Value.INT, Types.INTEGER, "Int",
+ createDecimal(ValueInt.PRECISION, ValueInt.PRECISION, 0,
+ ValueInt.DISPLAY_SIZE, false, true),
+ new String[]{"SERIAL"},
+ 20
+ );
add(Value.LONG, Types.BIGINT, "Long",
createDecimal(ValueLong.PRECISION, ValueLong.PRECISION, 0,
ValueLong.DISPLAY_SIZE, false, false),
@@ -251,7 +257,7 @@
add(Value.LONG, Types.BIGINT, "Long",
createDecimal(ValueLong.PRECISION, ValueLong.PRECISION, 0,
ValueLong.DISPLAY_SIZE, false, true),
- new String[]{"IDENTITY", "SERIAL"},
+ new String[]{"IDENTITY", "BIGSERIAL"},
24
);
add(Value.DECIMAL, Types.DECIMAL, "BigDecimal",
Index: src/main/org/h2/command/Parser.java
===================================================================
--- src/main/org/h2/command/Parser.java (revision 4548)
+++ src/main/org/h2/command/Parser.java (working copy)
@@ -3507,11 +3507,16 @@
private Column parseColumnForTable(String columnName, boolean
defaultNullable) {
Column column;
boolean isIdentity = false;
- if (readIf("IDENTITY") || readIf("SERIAL")) {
+ if (readIf("IDENTITY") || readIf("BIGSERIAL")) {
column = new Column(columnName, Value.LONG);
column.setOriginalSQL("IDENTITY");
parseAutoIncrement(column);
column.setPrimaryKey(true);
+ } else if (readIf("SERIAL")) {
+ column = new Column(columnName, Value.INT);
+ column.setOriginalSQL("SERIAL");
+ parseAutoIncrement(column);
+ column.setPrimaryKey(true);
} else {
column = parseColumnWithType(columnName);
}
Index: src/main/org/h2/table/Column.java
===================================================================
--- src/main/org/h2/table/Column.java (revision 4548)
+++ src/main/org/h2/table/Column.java (working copy)
@@ -355,6 +355,8 @@
}
if ("IDENTITY".equals(originalSQL)) {
originalSQL = "BIGINT";
+ } else if ("SERIAL".equals(originalSQL)) {
+ originalSQL = "INT";
}
String sequenceName;
while (true) {

[Noel Grandin]

Hmmm, I can't see us doing something like this.
We're not trying to be a copy of PostgeSQL, and being compatible for
testing purposes is a secondary goal, not a primary one.

But what we could do is to make BIGSERIAL a synonym for SERIAL.

How does that sound?

[Erwan Leroux]

Sorry, I commented on the wrong post, I wanted to comment on this post : https://groups.google.com/d/msg/h2-database/UM5oUsVvkmQ/8lHFaF4kas4J

[Jesse Long]

Hi Noel,

1. Why not? My patch is really small, and works, at least for me. Is there a reason why H2 would not consider applying it?
2. Current implementation is *incompatible* with PostgreSQL, in that SERIAL is a BIGINT, where in PostgreSQL it is INT.
3. SERIAL/BIGSERIAL is added *only* for PostgreSQL compatibility, if it is not going to be compatible, why even add it?
4. Changelog says BIGSERIAL is supported. It clearly isn't, so this is a bug needing fixing?

Thanks,
Jesse

[Noel Grandin]

Hmm, looks like BIGSERIAL was already added a synonym for SERIAL by Thomas.

SERIAL is something we've had for a very very long time, so messing with that is a distinct no-no.

[Jesse Long]

I suppose I could live with SERIAL being BIGINT. My main problem was that BIGSERIAL did not work in 1.3.170.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Thomas Mueller]

Hi,

Currently, within H2, both SERIAL and BIGSERIAL are not data types but an alias for AUTO_INCREMENT. This is implemented in the parser. But the statement

    create table x (id bigserial not null primary key)

is not supported. You would need to write:

    create table x (id bigint bigserial not null primary key)

But this is not compatible with PostgreSQL. SERIAL and BIGSERIAL are for PostgreSQL compatibility only, so in my view changing the behavior is OK. As far as I see SERIAL is not documented within H2 otherwise.

So I guess the patch from Jesse would help. But what is missing in the patch are test cases and a change log entry. If somebody could provide them, that would be great!

Regards,

Thomas

[Luno de Souza]

this compatibility will be very useful for me! :)

[Noel Grandin]

On 2013-03-11 20:32, Thomas Mueller wrote:
> So I guess the patch from Jesse would help. But what is missing in the
> patch are test cases and a change log entry. If somebody could provide
> them, that would be great!
>

Patch committed, along with test cases and changelog entry.

[Jesse Long]

Thanks Noel.

[Erwan Leroux]

If I read the changelog correctly, your patch has been added in the version 1.3.172, thank you for submitting it, I'm already using it.