[MonetDB-users] serial vs auto_increment
After creating a table with a 'serial' type id field I noticed this when describing it: sql>CREATE TABLE "schtest"."testt" ("id" serial, ... sql>\d testt CREATE TABLE "schtest"."testt" ( "id" int NOT NULL DEFAULT next value for "schtest"."seq_4291", ... The following page: http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Data-Types.html Says this about the serial data type: "special 64 bit integer (sequence generator)" and this about the 'int' data type: "32 bit integer" This looks like it created a table with a 32-bit integer sequence rather than a 64-bit integer sequence. If you use the auto_increment method you can set the type explicitly to be bigint, but should "serial" generate a bigint column also? 73, Matthew W. Jones (KI4ZIB) http://matburt.net
On 18-08-2009 16:00:07 -0400, Matthew Jones wrote:
After creating a table with a 'serial' type id field I noticed this when describing it: sql>CREATE TABLE "schtest"."testt" ("id" serial, ... sql>\d testt CREATE TABLE "schtest"."testt" ( "id" int NOT NULL DEFAULT next value for "schtest"."seq_4291", ...
The following page: http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/ Data-Types.html
Says this about the serial data type: "special 64 bit integer (sequence generator)"
and this about the 'int' data type: "32 bit integer"
This looks like it created a table with a 32-bit integer sequence rather than a 64-bit integer sequence.
If you use the auto_increment method you can set the type explicitly to be bigint, but should "serial" generate a bigint column also?
In fact "serial" is nothing more than a PostgreSQL convenience alias (like MySQL's auto_increment that MonetDB also supports), mapping onto the standard using sequences, hence that output when you describe the table again. I'd not be against making serial use a bigint, however, the best way to do it in MonetDB is to do it using a sequence. That said, our documentation still looks wrong to me, so thanks for the notice!
On Tue, Aug 18, 2009 at 10:15:15PM +0200, Fabian Groffen wrote:
In fact "serial" is nothing more than a PostgreSQL convenience alias (like MySQL's auto_increment that MonetDB also supports), mapping onto the standard using sequences, hence that output when you describe the table again.
Huh, I always thought that PGs syntax sugar for SERIAL "types" was to support the SQL standard. I can't find much mention of them in my copy of the 2003 specs though. Lots of stuff about generators, but the syntax doesn't look very pretty. PG does make a distinction between SERIAL and BIGSERIAL "types" though, the former ending up as column of type 32bit signed integer and the latter of type 64bit signed integer. Maybe it would be useful for MonetDB to support both, if it's not too much fiddling it may be useful. -- Sam http://samason.me.uk/
On 21-08-2009 16:04:55 +0100, Sam Mason wrote:
On Tue, Aug 18, 2009 at 10:15:15PM +0200, Fabian Groffen wrote:
In fact "serial" is nothing more than a PostgreSQL convenience alias (like MySQL's auto_increment that MonetDB also supports), mapping onto the standard using sequences, hence that output when you describe the table again.
Huh, I always thought that PGs syntax sugar for SERIAL "types" was to support the SQL standard. I can't find much mention of them in my copy of the 2003 specs though. Lots of stuff about generators, but the syntax doesn't look very pretty.
To the best of my knowledge, SERIAL is a PostgreSQL addition.
PG does make a distinction between SERIAL and BIGSERIAL "types" though, the former ending up as column of type 32bit signed integer and the latter of type 64bit signed integer. Maybe it would be useful for MonetDB to support both, if it's not too much fiddling it may be useful.
Supporting BIGSERIAL would not be too hard.
participants (3)
-
Fabian Groffen -
Matthew Jones -
Sam Mason