Thread: Insert zero to auto increment serial column

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

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.

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

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.

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

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