Back
on-constraint (clj)
(source)function
(on-constraint constraint)
Accepts a single constraint name.
Examples
honeysql
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest on-conflict-tests
;; these examples are taken from https://www.postgresqltutorial.com/postgresql-upsert/
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict {:on-constraint :customers_name_key}
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT
ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict []
:on-constraint :customers_name_key
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [:name]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ((foo + ?), name, (TRIM(email)))
DO NOTHING
" 1]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [[:+ :foo 1] :name [:trim :email]]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO UPDATE SET email = EXCLUDED.email || ';' || customers.email
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-update-set {:email [:|| :EXCLUDED.email [:inline ";"] :customers.email]}}
{:pretty true}))))
honeysql
(ns honey.sql.postgres-test
(:refer-clojure :exclude [update partition-by set])
(:require [clojure.test :refer [deftest is testing]]
;; pull in all the PostgreSQL helpers that the nilenso
;; library provided (as well as the regular HoneySQL ones):
[honey.sql.helpers :as sqlh :refer
[upsert on-conflict do-nothing on-constraint
returning do-update-set
;; not needed because do-update-set can do this directly
#_do-update-set!
alter-table rename-column drop-column
add-column partition-by
;; not needed because insert-into can do this directly
#_insert-into-as
create-table rename-table drop-table
window create-view over with-columns
create-extension drop-extension
select-distinct-on
;; already part of HoneySQL
insert-into values where select
from order-by update set]]
[honey.sql :as sql]))
(deftest upsert-test
(testing "upsert sql generation for postgresql"
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(on-conflict :did)
(do-update-set :dname)
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(upsert (-> (on-conflict :did)
(do-update-set :dname)))
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(on-conflict :did)
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(upsert (-> (on-conflict :did)
do-nothing))
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with both name and clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did, dname) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with multiple names and a clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did :dname (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; almost identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
;; in nilenso, this was (on-conflict-constraint :distributors_pkey)
(upsert (-> (on-conflict (on-constraint :distributors_pkey))
do-nothing))
sql/format)))
(is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET into = ((STATE(?), MODIFIED(NOW()))) WHERE state = ?" 1 42 "enabled" "disabled"]
(sql/format (-> (insert-into :foo)
(values [{:id 1 :data 42}])
(upsert (-> (on-conflict :id)
(do-update-set [:state "enabled"]
[:modified [:now]])
(where [:= :state "disabled"])))))))
(is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET state = ?, modified = NOW() WHERE state = ?" 1 42 "enabled" "disabled"]
(sql/format (-> (insert-into :foo)
(values [{:id 1 :data 42}])
(upsert (-> (on-conflict :id)
(do-update-set {:state "enabled"
:modified [:now]})
(where [:= :state "disabled"])))))))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname" 10 "Pinp Design" 11 "Foo Bar Works"]
(sql/format {:insert-into :distributors
:values [{:did 10 :dname "Pinp Design"}
{:did 11 :dname "Foo Bar Works"}]
;; in nilenso, these two were a submap under :upsert
:on-conflict :did
:do-update-set :dname})))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname || ? || d.dname || ?" 23 "Foo Distributors" " (formerly " ")"]
(-> (insert-into :distributors)
(values [{:did 23 :dname "Foo Distributors"}])
(on-conflict :did)
;; nilenso:
#_(do-update-set! [:dname "EXCLUDED.dname || ' (formerly ' || d.dname || ')'"])
;; honeysql
(do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :d.dname ")"]})
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) SELECT ?, ? ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 1 "whatever"]
;; honeysql version:
(-> (insert-into :distributors
[:did :dname]
(select 1 "whatever"))
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)
;; nilenso version:
#_(-> (insert-into :distributors)
(columns :did :dname)
(query-values (select 1 "whatever"))
(upsert (-> (on-conflict-constraint :distributors_pkey)
do-nothing))
sql/format)))))
babashka/babashka
(ns honey.sql.postgres-test
(:refer-clojure :exclude [update partition-by set])
(:require [clojure.test :refer [deftest is testing]]
;; pull in all the PostgreSQL helpers that the nilenso
;; library provided (as well as the regular HoneySQL ones):
[honey.sql.helpers :as sqlh :refer
[upsert on-conflict do-nothing on-constraint
returning do-update-set
;; not needed because do-update-set can do this directly
#_do-update-set!
alter-table rename-column drop-column
add-column partition-by
;; not needed because insert-into can do this directly
#_insert-into-as
create-table rename-table drop-table
window create-view over with-columns
create-extension drop-extension
select-distinct-on
;; already part of HoneySQL
insert-into values where select
from order-by update set]]
[honey.sql :as sql]))
(deftest upsert-test
(testing "upsert sql generation for postgresql"
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(on-conflict :did)
(do-update-set :dname)
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(upsert (-> (on-conflict :did)
(do-update-set :dname)))
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(on-conflict :did)
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(upsert (-> (on-conflict :did)
do-nothing))
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with both name and clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did, dname) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with multiple names and a clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did :dname (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; almost identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
;; in nilenso, this was (on-conflict-constraint :distributors_pkey)
(upsert (-> (on-conflict (on-constraint :distributors_pkey))
do-nothing))
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname" 10 "Pinp Design" 11 "Foo Bar Works"]
(sql/format {:insert-into :distributors
:values [{:did 10 :dname "Pinp Design"}
{:did 11 :dname "Foo Bar Works"}]
;; in nilenso, these two were a submap under :upsert
:on-conflict :did
:do-update-set :dname})))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname || ? || d.dname || ?" 23 "Foo Distributors" " (formerly " ")"]
(-> (insert-into :distributors)
(values [{:did 23 :dname "Foo Distributors"}])
(on-conflict :did)
;; nilenso:
#_(do-update-set! [:dname "EXCLUDED.dname || ' (formerly ' || d.dname || ')'"])
;; honeysql
(do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :d.dname ")"]})
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) SELECT ?, ? ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 1 "whatever"]
;; honeysql version:
(-> (insert-into :distributors
[:did :dname]
(select 1 "whatever"))
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)
;; nilenso version:
#_(-> (insert-into :distributors)
(columns :did :dname)
(query-values (select 1 "whatever"))
(upsert (-> (on-conflict-constraint :distributors_pkey)
do-nothing))
sql/format)))))
babashka/babashka
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest on-conflict-tests
;; these examples are taken from https://www.postgresqltutorial.com/postgresql-upsert/
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict {:on-constraint :customers_name_key}
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT
ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict []
:on-constraint :customers_name_key
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [:name]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name, email)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [:name :email]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO UPDATE SET email = EXCLUDED.email || ';' || customers.email
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-update-set {:email [:|| :EXCLUDED.email [:inline ";"] :customers.email]}}
{:pretty true}))))
seancorfield/honeysql
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest on-conflict-tests
;; these examples are taken from https://www.postgresqltutorial.com/postgresql-upsert/
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict {:on-constraint :customers_name_key}
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT
ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict []
:on-constraint :customers_name_key
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [:name]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ((foo + ?), name, (TRIM(email)))
DO NOTHING
" 1]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [[:+ :foo 1] :name [:trim :email]]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO UPDATE SET email = EXCLUDED.email || ';' || customers.email
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-update-set {:email [:|| :EXCLUDED.email [:inline ";"] :customers.email]}}
{:pretty true}))))
seancorfield/honeysql
(ns honey.sql.postgres-test
(:refer-clojure :exclude [update partition-by set])
(:require [clojure.test :refer [deftest is testing]]
;; pull in all the PostgreSQL helpers that the nilenso
;; library provided (as well as the regular HoneySQL ones):
[honey.sql.helpers :as sqlh :refer
[upsert on-conflict do-nothing on-constraint
returning do-update-set
;; not needed because do-update-set can do this directly
#_do-update-set!
alter-table rename-column drop-column
add-column partition-by
;; not needed because insert-into can do this directly
#_insert-into-as
create-table rename-table drop-table
window create-view over with-columns
create-extension drop-extension
select-distinct-on
;; already part of HoneySQL
insert-into values where select
from order-by update set]]
[honey.sql :as sql]))
(deftest upsert-test
(testing "upsert sql generation for postgresql"
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(on-conflict :did)
(do-update-set :dname)
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(upsert (-> (on-conflict :did)
(do-update-set :dname)))
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(on-conflict :did)
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(upsert (-> (on-conflict :did)
do-nothing))
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with both name and clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did, dname) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with multiple names and a clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did :dname (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; almost identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
;; in nilenso, this was (on-conflict-constraint :distributors_pkey)
(upsert (-> (on-conflict (on-constraint :distributors_pkey))
do-nothing))
sql/format)))
(is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET into = ((STATE(?), MODIFIED(NOW()))) WHERE state = ?" 1 42 "enabled" "disabled"]
(sql/format (-> (insert-into :foo)
(values [{:id 1 :data 42}])
(upsert (-> (on-conflict :id)
(do-update-set [:state "enabled"]
[:modified [:now]])
(where [:= :state "disabled"])))))))
(is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET state = ?, modified = NOW() WHERE state = ?" 1 42 "enabled" "disabled"]
(sql/format (-> (insert-into :foo)
(values [{:id 1 :data 42}])
(upsert (-> (on-conflict :id)
(do-update-set {:state "enabled"
:modified [:now]})
(where [:= :state "disabled"])))))))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname" 10 "Pinp Design" 11 "Foo Bar Works"]
(sql/format {:insert-into :distributors
:values [{:did 10 :dname "Pinp Design"}
{:did 11 :dname "Foo Bar Works"}]
;; in nilenso, these two were a submap under :upsert
:on-conflict :did
:do-update-set :dname})))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname || ? || d.dname || ?" 23 "Foo Distributors" " (formerly " ")"]
(-> (insert-into :distributors)
(values [{:did 23 :dname "Foo Distributors"}])
(on-conflict :did)
;; nilenso:
#_(do-update-set! [:dname "EXCLUDED.dname || ' (formerly ' || d.dname || ')'"])
;; honeysql
(do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :d.dname ")"]})
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) SELECT ?, ? ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 1 "whatever"]
;; honeysql version:
(-> (insert-into :distributors
[:did :dname]
(select 1 "whatever"))
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)
;; nilenso version:
#_(-> (insert-into :distributors)
(columns :did :dname)
(query-values (select 1 "whatever"))
(upsert (-> (on-conflict-constraint :distributors_pkey)
do-nothing))
sql/format)))))