[postmodern-devel] setting default value for serial column in prepared insert statement execution

[Antony]

On 9/4/2011 12:25 AM, Ivan Boldyrev wrote:
> On Sun, Sep 4, 2011 at 2:12 PM, Antony<lisp.linux at gmail.com>  wrote:
>> Hi
>>
>> I was trying to find how to use the 'serial' (auto increment) type through
>> cl-postgres prepared insert statement execution.
>>
>> According to
>> http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html#DATATYPE-SERIAL
>> I basically have two choices for defaulting a serial column value
>> 1. pass the DEFAULT keyword for the column value
> CL-USER>  (s-sql:sql (:insert-into 'table :set 'id  (:raw "default")
> 'name "Antony"))
> "INSERT INTO \"table\" (id, name) VALUES (default, E'Antony')"
I am trying to do this using __prepared__ statements.  I don't think the 
above helps for that.

To be explicit I am talking about the calls to the method
cl-postgres:exec-prepared
in file postmodern/cl-postgres/public.lisp

If I pass :null for the serial col parameter I get
Database error 23502: null value in column "seq_id" violates not-null 
constraint
    [Condition of type CL-POSTGRES-ERROR:NOT-NULL-VIOLATION]
(this is a pity since in most databases a null is considered the ticket 
to insert the auto increment value, but I understand why in postgres 
they have chosen this to be explicitly indicated by the use of reserved 
sql keyword 'default')

If I pass the string "default" I get
Database error 22P02: invalid input syntax for integer: "default"
    [Condition of type CL-POSTGRES-ERROR:DATA-EXCEPTION]

If I pass the keyword :default I get
Value :DEFAULT can not be converted to an SQL literal.
    [Condition of type SIMPLE-ERROR]
in CL-POSTGRES:TO-SQL-STRING

I wasn't expecting any of the above to work, just saying this is what I 
have tried.
Basically the parameter serialization low level code needs to learn how 
to deal with  ___DEFAULT___
keyword for postgres sql syntax and then provide a means of specifying 
that (may be through use of CL keyword :default).
I am not sure where/how to do that.

-Antony