S-SQL Examples I

ilike

In

(query (:select 'name
              :from 'countries
              :where (:in 'id
                          (:set 20 21 23))))

Insert, Insert-into

First, without s-sql

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

With s-sql

There are two ways to use s-sql with basic single row inserts. First, directly setting the columns, using the :set keyword and alternating column names and values. 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"))))

The second method, without the :set keyword, uses :select, followed by values.

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

(query (:insert-into 'test2 (:select '* :from 'test1)))

Returning the Primary Key

Suppose your table has a serial key of id and you want the insert function to return the newly generated id for that new record.

(query (:insert-into 'categories :set 'name "test-cat3" :returning 'id)
     :single)

The next example shows the same example using parameterized variables.

(let ((name "test-cat4"))
  (query (:insert-into 'categories :set 'name '$1 :returning 'id)
         name :single))

Note: This is a postgresql extension to the standard From the Documentation:

INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension, as is the ability to use WITH with INSERT, and the ability to specify an alternative action with ON CONFLICT. Also, the case in which a column name list is omitted, but not all the columns are filled from the VALUES clause or query, is disallowed by the standard.

The SQL standard specifies that OVERRIDING SYSTEM VALUE can only be specified if an identity column that is generated always exists. PostgreSQL allows the clause in any case and ignores it if it is not applicable. With DAOs

Insert-DAO

Postmodern has an update-dao and an insert-dao function. You could check and see if the dao that you are currently dealing with has its primary key set (in which case you are editing an existing object and need to use update-dao. If not, then you 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)))

However, you could also just use upsert.

(postmodern:upsert-dao item)

Multiple Row Inserts

You can use s-sql to generate INSERT INTO clauses with multiple rows, e.g. "INSERT INTO \"table\" (a, b) VALUES (10, 20), (30, 40)". (This ability does not yet exist with respect to inserting multiple daos.

First, without specifying columns:

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

Now with specifying columns:

(query (:insert-rows-into 'table
                 :columns 'a 'b
                 :values '((10 20) (30 40))))

(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))))

Using a select statement:

(query (:insert-rows-into 'cd.facilities
                 :columns 'facid 'name 'membercost 'guestcost 'initialoutlay 'monthlymaintenance
                 :values '(((:select (:+ (:select (:max 'facid)
                                          :from 'cd.facilities)
                                      1))
                            "Spa" 20 30 100000 800))))

Overriding System Values:

(query (:insert-rows-into 'table1
        :columns 'c1 'c2
        :overriding-system-value
        :values '((1 "a") (2 "b"))))

Insert-Rows-On-Conflict-Do-Nothing

Inserting rows with on conflict do nothing

(query (:insert-rows-into 'distributors
                 :columns 'did 'dname
                 :values '((7 "Readline GmbH"))
                 :on-conflict-do-nothing))

: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))

With where condition

(query (:insert-rows-into 'distributors
                 :columns 'did 'dname
                 :values '((10 "Conrad International"))
                 :on-conflict 'did
                 :do-nothing
                 :where 'is-active))

With returning

(query (:insert-rows-into 'distributors
        :columns 'did 'dname
                          :values '((8 "Readline GmbH"))
                          :on-conflict 'did 'dname
        :do-nothing
                          :returning 'id))

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))

With on-conflict constraint with separate :do-nothing keyword and returning

(sql (:insert-rows-into 'test :columns 'some-key 'some-val
                        :values '(("a" 4) ("b" 6) ("c" 7))
                        :on-conflict 'some-key
                        :do-nothing
      :returning '*))

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 '*))

Insert-Rows-On-Conflict-Update

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))

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))

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")))

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")))

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")))

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))

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 '*))

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

(queryl (:insert-rows-into 'test
                 :columns 'some-key
                 :values '(("a"))
                 :on-conflict-on-constraint 'somekey
                 :update-set 'some-val (:+ 'test.some-val 1)))

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"))
                   3
                   "No Show"))
        :on-conflict-on-constraint 'attendance-pkey
        :update-set 'attend-status 'excluded.attend_status))

Inserting from an alist

Assume that you have a list of alists and you want to insert the data. If the list of alists contain all the columns, then you do not need to specify the columns and simply loop across to get the total lists of values:

(let ((alst '(((name . "John") (age . 34)) ((name . "Susan") (age . 37)))))
  (query (:insert-rows-into 'table
          :values (loop :for x :in alst
                        :collect
                        (loop :for y :in x
                              :collect (cdr y))))))

If the alists only contain a subset of the columns, then you would need to specify the columns:

(let ((alst '(((name . "John") (age . 34)) ((name . "Susan") (age . 37)))))
  (query (:insert-rows-into 'table
          :columns 'name 'age
          :values (loop :for x :in alst
                        :collect
                        (loop :for y :in x
                              :collect (cdr y))))))

Inserting from a plist

What happens if you want to insert from a plist?

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)))

That gives you the opportunity to generalize into something like this:

(defun insert-db-from-plist (table plst)
  "Takes a table and a plist and inserts the plist into the table as a new record."
  (when (stringp table)
    (setf table (intern (string-upcase table))))
     (with-connection (db)
                      (query
                       (sql-compile
                        (append `(:insert-into ,table :set)
                                  (loop for x in plst counting x into y collect
                                        (if (oddp y)
                                            (cond ((symbolp x)
                                                   x)
                                                  ((stringp x)
                                                   (intern (string-upcase x)))
                                                  (t nil))
                                          x)))))))

Intersect

Intersect produces a result that contain rows that appear on all the sub-selects.

  (query (:intersect (:select 'countries.name
                            :from 'countries
                            :where (:< 'latitude 16.44))
                   (:select 'countries.name
                            :from 'countries 'regions
                            :where (:and (:= 'region-id 'regions.id)
                                         (:= 'regions.name "Caribbean")))))

(("Aruba") ("Netherlands Antilles") ("Grenada") ("Barbados") ("Trinidad and Tobago"))

Intervals

See Interval Notes

S-SQL Examples Home Page