creativeprogramming.it: PostgreSQL serial vs. MySQL auto

This article moved here: http://creativeprogramming.it/it/knowshare/devs/item/55-postgresql-serial-vs-mysql-auto-increment-vs-oracle-sequence

In PostgreSQL integer counters are more advanced than the MySQL auto_increments:

Them are sequences (like in Oracle) this mean that them can be used like "shared counters in the db" (very usefull in my opinion to implement a global object id between tables and can be usefull aslo in class table inheritance implementations)

This powerfull strument is a little boring to implement in Oracle:

you need to create the sequence:

create table mytable (id number,txt varchar(255));

create sequence mytable_seq
start with 1
increment by 1
nomaxvalue;

and to associate it to the column inserts with a trigger:

create trigger mytable_seq_trigger
before insert on test
for each row
begin
select mytable_seq.nextval into :new.id from dual;
end;

or instead of the trigger you can simply hardcode sequence next value call into insert statments:

insert into mytable values(mytable_seq.nextval, 'yes but who does 
hardcoded inserts today?');

but i think the best way to do this in Oracle is the following:

create table mytable (id number default mytable_seq.nextval,
 txt varchar(255));

in this way you emulate the mysql way, that's usefull too: infact in this way if you want, you can specify the id value in an insert query or get the magic just not providing any value for the id in the insert query.

this method is also the PostgreSQL's one, infact postgre combine the simplicty and comfort of MySQL auto_increments with the power of Oracle sequences:

how to declare auto_increment in postgre:

create table mytable (id serial, name chararter varying(255));

that's all!

but "serial" is just a shortcut-keyword: this is the real auto generated sql code :

CREATE SEQUENCE inscritto_column_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE inscritto_column_seq OWNER TO rdbapp;

and

ALTER TABLE mytable ALTER COLUMN id
        SET DEFAULT nextval('mytable_column_seq'::regclass);

(i think in MySQL too happen a thing of this type, but the difference is that in PostgreSQL you can create and manage manually sequences like in Oracle, and take advantage of them power... for instance think at how easy and secure can be getting the last_insert_id of a table: select currval('ente_column_seq'::regclass); )

but attention, in Postgre you got an error when try to change an integer column to serial:

create table mytable (id integer, name chararter varying(255));


alter table mytable alter column id type serial; 
(ERROR: Type serial does not exist)

this can be an issue?

uhm.. no! that's how to alter integer to sequences in postgre:

CREATE SEQUENCE mytable_id_seq
        INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;
    ALTER TABLE test_table ALTER COLUMN test_column
        SET DEFAULT nextval('mytable_id_seq'::regclass);

Note: if you have data in the table, instead of

MINVALUE 1

use the value returned form this query:

select max(id) from mytable;

Older Post Home Newer Post