S-SQL Examples U
Union, Union-all
As you probably know, the union operation generally eliminates what it thinks are duplicate rows. The union-all operation preserves duplicate rows. The examples below use the union-all operator, but the syntax would be the same with union.
(query (:select 'id 'name :from (:as (:union-all (:select 'id 'name :from 'countries :where (:<= 'name "B" )) (:select 'id 'name :from 'countries :where (:>= 'name "V" ))) 'a))) ((140 "Algeria") (83 "American Samoa") (202 "Angola") (45 "Argentina")(195 "Aruba") (38 "Australia") (1 "Austria") (117 "Azerbaijan") (121 "Antigua") (34 "All") (130 "Albania") (127 "Armenia") (82 "Afghanistan") (142 "Zimbabwe") (43 "Worldwide") (66 "Venezuela") (111 "Vanuatu") (115 "Wallis") (141 "Zambia") (68 "Vietnam") (212 "Yemen")(215 "test6")) (query (:select 'a.id 'a.name 'a.region :from (:as (:union-all (:select 'countries.id 'countries.name (:as 'regions.name 'region) :from 'countries 'regions :where (:and (:<= 'countries.name "B") (:= 'regions.id 'countries.region-id))) (:select 'countries.id 'countries.name (:as 'regions.name 'region) :from 'countries 'regions :where (:and (:>= 'countries.name "V") (:= 'regions.id 'countries.region-id )))) 'a) :group-by 'a.id 'a.region 'a.name)) ((140 "Algeria" "Africa") (1 "Austria" "Western Europe") (68 "Vietnam" "Asia") (83 "American Samoa" "Pacific") (202 "Angola""Africa") (121 "Antigua" "Caribbean") (127 "Armenia" "Eastern Europe") (66 "Venezuela""South America") (45 "Argentina" "South America") (195 "Aruba""Caribbean") (38 "Australia" "Pacific")(82 "Afghanistan" "Asia") (130 "Albania" "Eastern Europe") (111 "Vanuatu" "Pacific") (212 "Yemen" "Middle East") (115 "Wallis" "Pacific") (142 "Zimbabwe" "Africa") (117 "Azerbaijan" "Easter Europe") (141 "Zambia" "Africa"))
Unique
Unique is a constraint used in creating tables. Note the use in the following query used to build the regions table.
(query (:create-table regions ((id :type int4 :primary-key t) (name :type varchar :default "" :unique t))))
Update
In s-sql, updates operate the way you would expect them to, given what we have seen up above.
(query (:update 'countries :set 'text '$1 :where (:= 'id 284)) "now")
Now temporarily assume that we do not have a normalized database and we have a field "region_name" in the countries table in the database and a slot accessor named region-name in the countries class.
We could update a set of the countries rows to get the regional names for a particular set of countries, given a list of countries.id as follows:
(query (:update 'countries :set 'region-name (:select 'name :from 'regions :where (:= 'countries.id 'country-id)) :where (:in 'countries.id (:set 129 139 132 128 135 134 131 137 130 133 136))))
Assume you wanted to update a record with id=5 and you had a plist of the the columns to be updated.
Assuming you wanted to create something reusable, you could use a query like the following:
(query (sql-compile (append (append `(:update ,table :set) plst) `(:where (:= 'id ,id)))))
You can use the RETURNING keyword to return all or parts of the updated entries.
(query (sql (:update 'weather :set 'temp-lo (:+ 'temp-lo 1) 'temp-hi (:+ 'temp-lo 15) 'prcp :default :where (:and (:= 'city "San Francisco") (:= 'date "2003-07-03")) :returning 'temp-lo 'temp-hi 'prcp))
Alternative Column list Syntax
Use the alternative column-list syntax to do the same update:
(query (:update 'weather :columns 'temp-lo 'temp-hi 'prcp (:set (:+ 'temp-lo 1) (:+ 'temp-lo 15) :DEFAULT) :where (:and (:= 'city "San Francisco") (:= 'date "2003-07-03"))))
Single New Value for Multiple Rows
When you need to update lots of rows, a single call to the database is often more efficient, but what that call looks like will depend on your data. If you have a single value that needs to be inserted into multiple rows, you just need to manage the condition clause. To create a silly example, suppose we want to change the intermediate_region_name in the regions table to "Too Close to the UK" instead of "Channel Islands". Here are three different ways to do that
(query (:update 'regions :set 'intermediate-region-name "Too Close to the UK" :where (:= 'intermediate-region-name "Channel Islands"))) (query (:update 'regions :set 'intermediate-region-name "Too Close to the UK" :where (:in 'id (:set 179 180)))) (query (:update 'regions :set 'intermediate-region-name "Too Close to the UK" :where (:or (:= 'country "Guernsey") (:= 'country "Jersey"))))
Using a Case Statement
If you have a limited number of situations with a different value for each situation, you can reach for a case statement.
Staying with silly renames of intermediate_region_names, suppose we want "Caribbean" to be "Warm Island Americas" (thus excluding Bermuda) and "Central America" to be "Connecting Bridge Americas". One form of the case statement accomplishing this could look like this:
(query (:update 'regions :set 'intermediate-region-name (:case ((:= 'intermediate-region-name "Caribbean") "Warm Island Americas") ((:= 'intermediate-region-name "Central America") "Connecting Bridge Americas")) :where (:in 'intermediate-region-name (:set "Caribbean" "Central America"))))
Pulling Updated Info From Another Table
(query (:update 'geo :set 'iso3 'regions.iso3 :from 'regions :where (:= 'regions.iso2 'geo.iso3)))
Upsert or "On Conflict"
Some people use the term "upsert" for trying to insert a new row, but if that record already exists, then either update the row with new values or do nothing (as opposed to throwing an error).
Beginning in Postgresql versions 9.5 and above, it is possible to use what Postgresql calls on-conflict. There are two versions - "on conflict do nothing" or "on conflict update". See below for sample call in postmodern for 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)) "c" 37)
Or
(query (:select 'countries.name :from 'countries 'regions :where (:and (:or (:= 'regions.name "North America") (:= 'regions.name "Central America")) (:= 'regions.id 'countries.region-id))))
Using
From the postgresql docs: "USING is a shorthand notation: it takes a comma-separated list of column names, which the joined tables must have in common, and forms a join condition specifying equality of each of these pairs of columns. Furthermore, the output of JOIN USING has one column for each of the equated pairs of input columns, followed by the remaining columns from each table. Thus, USING (a, b, c) is equivalent to ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) with the exception that if ON is used there will be two columns a, b, and c in the result, whereas with USING there will be only one of each (and they will appear first if SELECT * is used).
Example: Sorry, real toy example here. We assume an additional table named "countries-topics" and that both countries-topics and countries have columns named country-id. We are looking for records from the countries table which do not have a match in the countries-topics table. In other words, where do we have a note, but not matched it to a topic? The difference between ":using" and ":on" is the requirement that both tables have columns with the same names. You could join using multiple columns, just adding them into the parenthetical following the keyword :using.
(defun countries-with-no-topics () (query (:order-by (:select 'countries.id 'countries.name :distinct :from 'countries :left-join 'countries-topics :using ('country-id) :where (:is-null 'countries-topics.country-id)) 'countries.country-id)))