[postmodern-devel] setting default value for serial column in prepared insert statement execution
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 2. do not include the serial column in the insert statement
The way my code is setup, it's more convoluted to do #2
I wanted to check if it's possible to do #1. That would really make a big difference to me.
-Antony
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
- 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')"
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
Under certain circumstances (cant remember when it does not work, play around with col-type serial vs integer ) something like this in the class definition will work
(id :initarg :id :initform nil :col-type serial :col-default (:nextval "reporting-period-id-seq"))
or
when you initialize a new dao-class use something like this
:id (query (:select (:nextval "reporting-period-id-seq")) :single)
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.
I am working around by having separate insert statements one with and one without the auto increment column. Good enough for now :) -Antony
postmodern-devel@common-lisp.net
-
Antony -
Ivan Boldyrev -
Marijn Haverbeke -
Phil Marneweck