postmodern-insert

Postmodern Examples Table of Contents Dao

Insert and Insert-into

First, without s-sql

(query "insert into countries values (920, 'Oz', 'Oz Dollars')")

or, if you have a list of text values

(query (format nil "insert into table_name values (~{ '~a'~^,~})" list-of-text-values))

With s-sql

Now using s-sql. There are two operators: :insert-into and :insert-rows-into with slightly different use cases.

:insert-into can only insert a single row unless you pass it a select clause, in which case you have no control over the columns being set (they will just be filled from first to however many columns you have in your select statement.

:insert-rows-into can insert multiple rows, but is a bit more difficult to use with a select statement.

The base use of :insert-into is shown in this section, directly setting the columns, using the :set keyword and alternating column names and values with the :insert-into operator. This means that the :insert-into clause expects the arguments to :set to be in multiples of two. Odd arguments will trigger an error.

(query (:insert-into 'countries :set 'id 920 'name "Oz" 'currency "Oz Dollars"))

Notice that a list with two items is a single item, not a multiple of two. You can use sql-compile to deal with this. See this example from the mailing list.

(sql-compile `(:insert-into 'kupci :set ,@(when t (list 'ime "a"))))

There is a second method with :insert-into, without the :set keyword, which uses :select, followed by values.

(query (:insert-into 'countries (:select "Oz" "Oz Dollars")))

Assume you have a plist where the keys are interned that you want to insert as a record. Consider the following:

(query  (sql-compile (append `(:insert-into ,table :set) plst)))

More Detailed Examples of Insert-Into and Insert-Rows-Into.

The on-conflict examples generally require Postmodern Version v1.32.7 (available at https://github.com/marijnh/Postmodern as of Dec 24, 2020 and probably on quicklisp's January release )

1 Insert-Into

(query (:insert-into 'cd.facilities

        :set 'facid 9 'name "Spa" 'membercost 20 'guestcost 30

             'initialoutlay 100000 'monthlymaintenance 800))

Example: Testing with a calculation in the value

(query (:insert-into 'test

        :set 'id 15 'number-string "12" 'numeric-item 12.45

             'ratio-item (/ 1 13) 'created-at "2018-02-01"))

Example: Testing select

(query (:insert-into 'users

         (:select (:uuid-generate-v4) "Lucie"

                  "Hawkins""Lucie-Jones@gmail.com")))

(query (:insert-into 't6 (:select 'id :from 't5)))

Example: Testing select in insert statement

(query (:insert-into 'cd.facilities

        :set 'facid

        (:select (:+ (:select (:max 'facid)

                      :from 'cd.facilities)

1))

        'name "Spa" 'membercost 20 'guestcost 30

        'initialoutlay 100000 'monthlymaintenance 800))

Example: Testing overriding-user-value

(query (:insert-into 'employee

        :set 'id 1 'name "Paul"

        :overriding-user-value

        :on-conflict-do-nothing))

Example: Testing overriding system-value

(query (:insert-into 'employee

        :set 'id 1 'name "Paul"

        :overriding-system-value

        :on-conflict-do-nothing))

1.1 insert-into on-conflict-do-nothing

(query (:insert-into 'distributors

        :set 'did 7 'dname "Readline GmbH"

        :on-conflict-do-nothing))

Example: basic :on-conflict with separate :do-nothing keyword

(query (:insert-into 'test

        :set 'some-col "a" 'some-val 5

        :on-conflict 'some-col

        :do-nothing))

Example: with where condition

(query (:insert-into 'distributors

        :set 'did 10 'dname "Conrad International"

        :on-conflict 'did

        :do-nothing

        :where 'is-active))

Example: With returning

(query (:insert-into 'distributors

        :set 'did 8 'dname "Readline GmbH"

        :on-conflict 'did 'dname

        :do-nothing

        :returning 'id))

Example: with where and returning

(query (:insert-into 'test-table

        :set 'column-A '$1 'column-B '$2

        :on-conflict 'column-A

        :do-nothing

        :where (:= 'test-table.column-A '$1)

        :returning '*))

Example: With on-conflict-on-constraint and do-nothing as a separate operator

(query (:insert-into 'distributors

        :set 'did 10 'dname "Readline GmbH"

        :on-conflict-on-constraint 'distributors-pkey

        :do-nothing

        :returning 'id))

Example: basic :on-conflict with separate :do-nothing keyword and returning

(query (:insert-into 'test

        :set 'some-key "a" 'some-val 4

        :on-conflict 'some-key

        :do-nothing

        :returning '*))

1.2 insert-into on-conflict-update

Example: Testing On Conflict update

(query (:insert-into 'test-table

        :set 'column-A '$1 'column-B '$2

        :on-conflict-update 'column-A

        :update-set 'column-B '$2

        :where (:= 'test-table.column-A '$1)))

Example: basic version single row

(query (:insert-into 'users

        (:select (:uuid-generate-v4) "Lucie" "Hawkins" "Lucie-Jones@gmail.com")

        :on-conflict-update 'email

        :update-set 'first-name 'excluded.first-name 'last-name 'excluded.last-name))

Example: Basic version multiple row and specified columns

(query (:insert-into 'distributors

        :set 'did 5 'dname "Gizmo Transglobal"

        :on-conflict-update 'did

        :update-set 'dname 'excluded.dname))

Example: with where clause

(query (:insert-into 'users

        (:select (:uuid-generate-v4) "Lucie" "Hawkins" "Lucie-Jones@gmail.com")

        :on-conflict-update 'email

        :update-set 'first-name 'excluded.first-name 'last-name 'excluded.last-name

        :where (:<> 'u.first-name "Lucie")))

Example: with concatenation function in the update-set clause

(query (:insert-into 'distributors

        :set 'did 8 'dname "Anvil Distribution"

        :on-conflict-update 'did

        :update-set 'dname (:|| 'excluded.dname  " (formerly " 'd.dname ")")

        :where (:<> 'd.zipcode "21201")))

Example: with on-conflict-on-constraint

(query (:insert-into 'test

        :set 'some-key "a" 'some-val 5

        :on-conflict-on-constraint 'somekey

        :update-set 'some-val 'excluded.some-val))

Example: with on-conflict-on-constraint and returning clause

(query (:insert-into 'test

        :set 'some-key "a" 'some-val 2

        :on-conflict-on-constraint 'somekey

        :update-set 'some-val 'excluded.some-val

        :returning '*))

Example: on-conflict-on-constraint with addition function in the update-set clause

(query (:insert-into 'test

        :set 'some-key "a"

        :on-conflict-on-constraint 'somekey

        :update-set 'some-val (:+ 'test.some-val 1)))

Example: with select clause which returns a single row

(query (:insert-into 'attendence

        :set 'event-id (:select 'id

                    :from 'event

                        :where (:= (:lower 'event-dt) "2020-01-11 17:00:00"))

             'client-id 3 'attend-status "No Show"

        :on-conflict-on-constraint 'attendance-pkey

        :update-set 'attend-status 'excluded.attend_status))

2 insert-rows-into

Example: Testing basic inserting-rows-into

(query (:insert-rows-into 'my-table :values '((42 "foobar") (23 "foobaz"))))

Example: Testing columns

(query (:insert-rows-into 'cd.facilities

        :columns 'facid 'name 'membercost 'guestcost 'initialoutlay 'monthlymaintenance

        :values '((9 "Spa" 20 30 100000 800) (10 "Squash Court 2" 3.5 17.5 5000 80))))

Example: Testing select in values in insert-rows-into

(query (:insert-rows-into 't6

        :columns 'tags

        :values '(((:select 'id

                    :from 't5)))))

Example: Overriding system values on identity columns

(query (:insert-rows-into 'table1

        :columns 'c1 'c2

        :overriding-system-value

        :values '((1 "a") (2 "b"))))

2.1 insert-rows-into on-conflict-do-nothing

Example: Testing inserting rows with on conflict do nothing

(query (:insert-rows-into 'distributors

        :columns 'did 'dname

        :values '((7 "Readline GmbH"))

        :on-conflict-do-nothing))

Example: basic :on-conflict with separate :do-nothing keyword

(query (:insert-rows-into 'test

        :columns 'some-key 'some-val

        :values '(("a" 5) ("b" 6) ("c" 7))

        :on-conflict 'some-key

        :do-nothing))

Example: With where condition

(query (:insert-rows-into 'distributors

        :columns 'did 'dname

        :values '((10 "Conrad International"))

        :on-conflict 'did

        :do-nothing

        :where 'is-active))

Example: With returning

(query (:insert-rows-into 'distributors :columns 'did 'dname

        :values '((8 "Readline GmbH"))

        :on-conflict 'did 'dname

        :do-nothing

        :returning 'id))

Example: With on-conflict-on-constraint and do-nothing as a separate operator

(query (:insert-rows-into 'distributors :columns 'did 'dname

        :values '((10 "Readline GmbH"))

        :on-conflict-on-constraint 'distributors-pkey

        :do-nothing

        :returning 'id))

Example: basic :on-conflict with separate :do-nothing keyword and returning

(query (:insert-rows-into 'test :columns 'some-key 'some-val

        :values '(("a" 4) ("b" 6) ("c" 7))

        :on-conflict 'some-key

        :do-nothing

        :returning '*))

Example: multiple values basic :on-conflict-on-constraint with separate :do-nothing keyword and returning

(query (:insert-rows-into 'test :columns 'some-key 'some-val

        :values '(("a" 3) ("b" 6) ("c" 7))

        :on-conflict-on-constraint 'somekey

        :do-nothing

        :returning '*))

2.2 insert-rows-into on-conflict-update

Example: Basic version single row

(query (:insert-rows-into 'users

        :values '(((:uuid-generate-v4) "Lucie" "Hawkins" "Lucie-Jones@gmail.com"))

        :on-conflict-update 'email

        :update-set 'first-name 'excluded.first-name 'last-name 'excluded.last-name))

Example: Basic version multiple row and specified columns

(query (:insert-rows-into 'distributors

        :columns 'did 'dname

        :values '((5 "Gizmo Transglobal") (6 "Associated Computing Inc."))

        :on-conflict-update 'did

        :update-set 'dname 'excluded.dname))

Example: with where clause

(query (:insert-rows-into 'users

        :values '(((:uuid-generate-v4) "Lucie" "Hawkins" "Lucie-Jones@gmail.com"))

        :on-conflict-update 'email

        :update-set 'first-name 'excluded.first-name 'last-name 'excluded.last-name

        :where (:<> 'u.first-name "Lucie")))

Example: with an as clause at the table level

(query (:insert-rows-into (:as 'users 'u)

        :values '(((:uuid-generate-v4) "Lucie" "Jones" "Lucie-Jones@gmail.com"))

        :on-conflict-update 'email

        :update-set 'first-name 'excluded.first-name 'last-name 'excluded.last-name

        :where (:<> 'u.first-name "Lucie")))

Example: with concatenation function in the update-set clause

(query (:insert-rows-into (:as 'distributors 'd)

        :columns 'did 'dname

        :values '((8 "Anvil Distribution"))

        :on-conflict-update 'did

        :update-set 'dname (:|| 'excluded.dname  " (formerly " 'd.dname ")")

        :where (:<> 'd.zipcode "21201")))

Example: with on-conflict-on-constraint

(query (:insert-rows-into 'test

        :columns 'some-key 'some-val

        :values '(("a" 5))

        :on-conflict-on-constraint 'somekey

        :update-set 'some-val 'excluded.some-val))

Example: with on-conflict-on-constraint and returning clause

(query (:insert-rows-into 'test

        :columns 'some-key 'some-val

        :values '(("a" 2) ("b" 6) ("c" 7))

        :on-conflict-on-constraint 'somekey

        :update-set 'some-val 'excluded.some-val

        :returning '*))

Example: on-conflict-on-constraint with addition function in the update-set clause

(query (:insert-rows-into 'test

        :columns 'some-key

        :values '(("a"))

        :on-conflict-on-constraint 'somekey

        :update-set 'some-val (:+ 'test.some-val 1)))

Example: with select clause which returns a single row

(query (:insert-rows-into 'attendence :columns 'event-id 'client-id 'attend-status

        :values '(((:select 'id

                    :from 'event

                    :where (:= (:lower 'event-dt) "2020-01-11 17:00:00"))

                   "No Show"))

        :on-conflict-on-constraint 'attendance-pkey

        :update-set 'attend-status 'excluded.attend_status))

With DAOs

Postmodern has an update-dao and an insert-dao function. My tendency is to check and see if the dao that I'm currently dealing with has its primary key set (in which case I am editing an existing object and need to use update-dao. If not, then I'm dealing with a brand new object and will need to set the primary key and use the insert-dao method, using something like this:

(if (id item)

   (postmodern:update-dao item)

   (progn

     (setf (id item) (get-next-id class-name))

     (postmodern:insert-dao item)))

In the alternative, you can rely on sequencing to handle the key field.

(defclass test1 ()

  ((id :accessor id :col-type serial :initarg :id)

  (name :accessor name :col-type text :type text :initarg :name :initform :NULL

        :documentation "Library Name"))

(:documentation "A class to hold information about each library")

(:metaclass postmodern:dao-class)

(:table-name test-table)

(:keys id))

Notice that there is no :initform in the id field. Now we create the table from the class definition.

(postmodern:execute (postmodern dao-table-definition 'test1))

Now create an instance of the class and insert it.

(let ((item (make-instance 'test1 :name "test-name")))

  (postmodern:insert-dao item))

Update