On 9/4/2011 12:25 AM, Ivan Boldyrev wrote:
On Sun, Sep 4, 2011 at 2:12 PM, Antony<lisp.linux@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#DATATYP... 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