Insert zero to auto increment serial column
I'm currently using an Informix Innovator-C database with Aubit4GL and I
would like to migrate to PostgreSQL, it looks like the transition will not
be too difficult, however there is one feature that I would like added to
PostgreSQL. Where is the best place to request a feature add? With
Informix inserting a 0 into a serial column will auto increment the serial
value, MySQL also works this way. With PostgreSQL I will need to do a lot
of code modification to my Aubit4GL programs, since I will need to either
insert with the key word default or omit the serial column in the insert
statement. A typical insert with Aubit4GL looks like this:
create table table_name (link serial, col2 integer, col3 integer ....)
define var_rec record like table_name.*
let table_name.link = 0
insert into table_name values(var_rec.*)
As you can see, with Informix the serial column is set to 0 before the
insert, with PostgreSQL I will need to list all the columns and will not be
able to use the wildcard syntax, which supplies all the column names to the
insert. Setting the serial column to null to auto increment would also
work for me.
loc wrote:
I'm currently using an Informix Innovator-C database with Aubit4GL and I would like to migrate to
PostgreSQL, it looks like the transition will not be too difficult, however there is one feature that
I would like added to PostgreSQL. Where is the best place to request a feature add? With Informix
inserting a 0 into a serial column will auto increment the serial value, MySQL also works this way.
With PostgreSQL I will need to do a lot of code modification to my Aubit4GL programs, since I will
need to either insert with the key word default or omit the serial column in the insert statement. A
typical insert with Aubit4GL looks like this:create table table_name (link serial, col2 integer, col3 integer ....)
define var_rec record like table_name.*
let table_name.link = 0
insert into table_name values(var_rec.*)As you can see, with Informix the serial column is set to 0 before the insert, with PostgreSQL I will
need to list all the columns and will not be able to use the wildcard syntax, which supplies all the
column names to the insert. Setting the serial column to null to auto increment would also work for
me.
If you don't want to modify your code, you could write a FOR EACH ROW BEFORE INSERT trigger
for the table that pulls the next value from the sequence if you insert 0.
I don't think that there is any chance that PostgreSQL's behaviour in this
case will be modified - first, the current behaviour is fine, and second,
such a change will break existing applications.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Will a rule work?
http://www.postgresql.org/docs/9.3/static/sql-createrule.html
On Wed, Apr 2, 2014 at 5:47 PM, loc <c320sky@gmail.com> wrote:
Show quoted text
I'm currently using an Informix Innovator-C database with Aubit4GL and I
would like to migrate to PostgreSQL, it looks like the transition will not
be too difficult, however there is one feature that I would like added to
PostgreSQL. Where is the best place to request a feature add? With
Informix inserting a 0 into a serial column will auto increment the serial
value, MySQL also works this way. With PostgreSQL I will need to do a lot
of code modification to my Aubit4GL programs, since I will need to either
insert with the key word default or omit the serial column in the insert
statement. A typical insert with Aubit4GL looks like this:create table table_name (link serial, col2 integer, col3 integer ....)
define var_rec record like table_name.*
let table_name.link = 0
insert into table_name values(var_rec.*)As you can see, with Informix the serial column is set to 0 before the
insert, with PostgreSQL I will need to list all the columns and will not be
able to use the wildcard syntax, which supplies all the column names to the
insert. Setting the serial column to null to auto increment would also
work for me.
On Wed, Apr 2, 2014 at 6:53 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
Will a rule work?
http://www.postgresql.org/docs/9.3/static/sql-createrule.html
There are a couple of issues you face if you use a rule, copy commands
ignore rules, and rules are slower.
Triggers are almost always better here and really aren't that hard to write.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 04/02/2014 05:17 AM, loc wrote:
I'm currently using an Informix Innovator-C database with Aubit4GL and I
would like to migrate to PostgreSQL, it looks like the transition will
not be too difficult, however there is one feature that I would like
added to PostgreSQL. Where is the best place to request a feature add?
With Informix inserting a 0 into a serial column will auto increment the
serial value, MySQL also works this way. With PostgreSQL I will need to
do a lot of code modification to my Aubit4GL programs, since I will need
to either insert with the key word default or omit the serial column in
the insert statement. A typical insert with Aubit4GL looks like this:create table table_name (link serial, col2 integer, col3 integer ....)
define var_rec record like table_name.*
let table_name.link = 0
insert into table_name values(var_rec.*)As you can see, with Informix the serial column is set to 0 before the
insert, with PostgreSQL I will need to list all the columns and will not
be able to use the wildcard syntax, which supplies all the column names
to the insert. Setting the serial column to null to auto increment
would also work for me.
Looks like Aubit4GL has provisions for doing the conversions behind the
scenes:
http://aubit4gl.sourceforge.net/aubit4gldoc/manual/index.html
and for a more detailed example:
http://openssa.sourceforge.net/aubit4gl.html
Might be worth asking for more info on the Aubit4GL list:
https://lists.sourceforge.net/lists/listinfo/aubit4gl-discuss
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
loc wrote
Setting the serial column to null to auto increment would also
work for me.
Can you set it to a literal value DEFAULT? Only helps for the insert case
(not copy) but that is the mechanism that is used to specify a column and
ask for the default.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Insert-zero-to-auto-increment-serial-column-tp5798318p5798336.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 04/02/2014 08:24 AM, Scott Marlowe wrote:
Triggers are almost always better here and really aren't that hard to
write.
Not only do I have to agree with this statement, I feel it's important
to add a clarification regarding your original question.
Translating serial values submitted as 0 to something else is will
likely never be a PostgreSQL feature, as it is probably a violation of
the SQL standard. Databases are meant to store exactly what you submit,
barring any modifications by stored procedure or trigger.
If a database engine takes it upon itself to modify your data, it is no
longer a database, but an application that mimics one.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Apr 02, 2014 at 08:17:12AM -0400, loc wrote:
value, MySQL also works this way. With PostgreSQL I will need to do a lot
of code modification to my Aubit4GL programs, since I will need to either
insert with the key word default or omit the serial column in the insert
statement.
Why don't you put a DO INSTEAD trigger or rule (I'd suggest the
former) when you put in a 0 to do nextval() instead on the sequence
for the column?
Best regards,
A
--
Andrew Sullivan
ajs@crankycanuck.ca
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general