postmodern-u (union, union-all, unique, update, upsert, using)

[Special Characters][A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]

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 (see Update Page)

Upsert

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.

This capability was added to postmodern in a commit on 28 Jan 2018.

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

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