Bug #7978 SERIAL type alias isn't compatible with PostgreSQL serial type

• Submitted: 18 Jan 2005 2:16
• Modified: 31 Jan 2005 17:59
• Reporter: Stewart Smith
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.0 and up
• Assigned to: Paul DuBois
• CPU Architecture: Any

[18 Jan 2005 2:16] Stewart Smith

Description:
from the mysql CREATE TABLE documentation:
From MySQL 4.1.0 on, the attribute SERIAL can be used as an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. This is a compatibility feature.

however, the Postgresql documentation lists:
CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);

So you cannot just bring a postgresql db into mysql using foreign keys on serial columns (as you would define the foreign key columns in postgresql as integer).

postgresql's bigserial would probably be closer to our SERIAL.

How to repeat:
set sql_mode=ansi;

create table event_types (
	id serial unique not null,
	type varchar(50),
	CONSTRAINT "event_types_pkey" PRIMARY KEY (id)
);

create table events (
	id serial not null,
	event_type_id int not null,
	name varchar(80) not null,
	description text,
	start_datetime timestamp default now() NOT NULL,
	end_datetime timestamp default now()+1 NOT NULL,
	url varchar(255) default NULL,
	cost varchar(100),
	CONSTRAINT "events_pkey" PRIMARY KEY (id),
	CONSTRAINT "events_event_type_id_fkey" FOREIGN KEY (event_type_id) references event_types(id) on update restrict
);

Suggested fix:
Update documentation to reflect this difference or change implementation to be more compatible. Funnily enough, AUTO_INCREMENT compatibility is mentioned on the documentation for the serial type in postgresql.

[31 Jan 2005 17:59] Paul DuBois

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

The manual didn't imply that SERIAL was for compatible
with PostgreSQL.

Unfortunately, it didn't specify what it was supposed to
be compatible with, so I've simply removed the sentence
about compatibility so as not to set up expectations.