On 9/4/2011 12:25 AM, Ivan Boldyrev wrote:
On Sun, Sep 4, 2011 at 2:12 PM, Antonylisp.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
- 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