It seems the serial datatype is a frequently asked questions for novices
introduced to PostgreSQL. It appears that the competing RDBMSes are simpler
to handle in that respect, when you want to insert data and have a column
autoincremented, then fetch the value that was inserted to use in the next
insert.
In Sybase (ASE and ASA) and MS SQL I can define a column as identity, do an
insert and then select @@identity to find out what was generated for me. If I
replace identity columns with type serial as a simple search and replace, I
find that I still have to know the underlying sequence name. What is the
simplest way to do this?
What I would like to do is either:
create table foo(
colA serial,
bar varchar(255)
);
begin tran
insert into foo (bar) values ('abc');
select currval(colA) from foo;
commit
I assume here that nextval would be implicitly called by the insert so I
could directly call currval *after* inserting. Doing a manual nextval()
before the insert is OK, too, but from my understanding it needs the sequence
name and not the column name?
or,
create table foo(
colA serial,
bar varchar(255)
);
begin tran
insert into foo (bar) values ('abc');
select currval(seqname(colA)) from foo;
commit
Where seqname returns the underlying sequence name for the serial column.
The reason this is needed is that during the initial design phase a column
name is decided, the actual implementation on each RDBMS comes much later.
When the RDBMS is decided, you want to hide the annoyances of each platform
as much as possible. Having to know at a later phase that a certain sequence
name has been associated with the serial column is unneccessary for the
front-end developers. I can't find any simple way of programatically finding
the sequence name either. I believe this is much simpler in Sybase ASE/ASA,
Microsoft Access/SQL Server and MySQL so PostgreSQL should improve the
functionality too?