Back
insert-into (clj)
(source)function
(insert-into table)
(insert-into table cols)
(insert-into table statement)
(insert-into table cols statement)
Accepts a table name or a table/alias pair. That
can optionally be followed by a collection of
column names. That can optionally be followed by
a (select) statement clause.
(insert-into :table)
(insert-into [:table :t])
(insert-into :table [:id :name :cost])
(insert-into :table (-> (select :*) (from :other)))
(insert-into [:table :t]
[:id :name :cost]
(-> (select :*) (from :other)))
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 insert-into
(is (= (format {:insert-into :foo})
["INSERT INTO foo"]))
(is (= (format {:insert-into [:foo {:select [:bar] :from [:baz]}]})
["INSERT INTO foo SELECT bar FROM baz"]))
(is (= (format {:insert-into [[:foo [:a :b :c]] {:select [:d :e :f] :from [:baz]}]})
["INSERT INTO foo (a, b, c) SELECT d, e, f FROM baz"]))
(is (= (format {:insert-into [[:foo [:a :b :c]] {:select [:d :e :f] :from [:baz]}]})
["INSERT INTO foo (a, b, c) SELECT d, e, f FROM baz"])))
(deftest insert-into-namespaced
;; un-namespaced: works as expected:
(is (= (format {:insert-into :foo :values [{:foo/id 1}]})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]})
["INSERT INTO foo (id) VALUES (?)" 2]))
(is (= (format {:insert-into :foo :values [{:foo/id 1}]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 2])))
(deftest insert-into-uneven-maps
;; we can't rely on ordering when the set of keys differs between maps:
(let [res (format {:insert-into :foo :values [{:id 1} {:id 2, :bar "quux"}]})]
(is (or (= res ["INSERT INTO foo (id, bar) VALUES (?, NULL), (?, ?)" 1 2 "quux"])
(= res ["INSERT INTO foo (bar, id) VALUES (NULL, ?), (?, ?)" 1 "quux" 2]))))
(let [res (format {:insert-into :foo :values [{:id 1, :bar "quux"} {:id 2}]})]
(is (or (= res ["INSERT INTO foo (id, bar) VALUES (?, ?), (?, NULL)" 1 "quux" 2])
(= res ["INSERT INTO foo (bar, id) VALUES (?, ?), (NULL, ?)" "quux" 1 2])))))
(deftest insert-into-functions
;; needs [[:raw ..]] because it's the columns case:
(is (= (format {:insert-into [[[:raw "My-Table Name"]] {:select [:bar] :from [:baz]}]})
["INSERT INTO My-Table Name SELECT bar FROM baz"]))
;; this variant only needs [:raw ..]
(is (= (format {:insert-into [[:raw "My-Table Name"]] :values [{:foo/id 1}]})
["INSERT INTO My-Table Name (id) VALUES (?)" 1]))
(is (= (format {:insert-into [:foo :bar] :values [{:foo/id 1}]})
["INSERT INTO foo AS bar (id) VALUES (?)" 1])))
(deftest array-test
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?, ?])" 1 2 3 4]))
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array ["one" "two" "three"]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?])" "one" "two" "three"]))
#_ ;; requested feature -- does not work yet
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array :?vals]]]}
{:params {:vals [1 2 3 4]}})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?, ?])" 1 2 3 4]))
(testing "typed array"
(is (= (format {:select [[[:array [] :integer]]]})
["SELECT ARRAY[]::INTEGER[]"]))
(is (= (format {:select [[[:array [1 2] :text]]]})
["SELECT ARRAY[?, ?]::TEXT[]" 1 2])))
(testing "array subquery"
(is (= (format {:select [[[:array {:select [:foo] :from [:bar]}]]]})
["SELECT ARRAY(SELECT foo FROM bar)"]))
(is (= (format {:select [[[:array {:select ^{:as :struct} [:foo :bar] :from [:bar]}]]]})
["SELECT ARRAY(SELECT AS STRUCT foo, bar FROM bar)"]))
;; documented subquery workaround:
(is (= (format {:select [[[:'ARRAY {:select [:foo] :from [:bar]}]]]})
["SELECT ARRAY (SELECT foo FROM bar)"]))))
(deftest parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]}
{:inline true})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(deftest inline-was-parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array (mapv vector
(repeat :inline)
[1 2 3 4])]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(deftest insert-example-tests
;; these examples are taken from https://www.postgresql.org/docs/13/sql-insert.html
(is (= ["
INSERT INTO films
VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes')
"]
(format {:insert-into :films
:values [[[:inline "UA502"] [:inline "Bananas"] [:inline 105]
[:inline "1971-07-13"] [:inline "Comedy"]
[:inline "82 minutes"]]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, ?, ?, ?)
" "UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 "1971-07-13" "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films (code, title, did, date_prod, kind)
VALUES (?, ?, ?, ?, ?)
" "T_601", "Yojimo", 106, "1961-06-16", "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, "1961-06-16", "Drama"]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, DEFAULT, ?, ?)
" "UA502", "Bananas", 105, "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 [:default] "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films (code, title, did, date_prod, kind)
VALUES (?, ?, ?, DEFAULT, ?)
" "T_601", "Yojimo", 106, "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, [:default], "Drama"]]}
{:pretty true}))))
(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}))))
(deftest issue-299-test
(let [name "test field"
;; this was a bug in 1.x -- adding here to prevent regression:
enabled [true, "); SELECT case when (SELECT current_setting('is_superuser'))='off' then pg_sleep(0.2) end; -- "]]
(is (= ["INSERT INTO table (name, enabled) VALUES (?, (TRUE, ?))" name (second enabled)]
(format {:insert-into :table
:values [{:name name
:enabled enabled}]})))))
(deftest issue-425-default-values-test
(testing "default values"
(is (= ["INSERT INTO table (a, b, c) DEFAULT VALUES"]
(format {:insert-into [:table [:a :b :c]] :values :default}))))
(testing "values with default row"
(is (= ["INSERT INTO table (a, b, c) VALUES (1, 2, 3), DEFAULT, (4, 5, 6)"]
(format {:insert-into [:table [:a :b :c]]
:values [[1 2 3] :default [4 5 6]]}
{:inline true}))))
(testing "values with default column"
(is (= ["INSERT INTO table (a, b, c) VALUES (1, DEFAULT, 3), DEFAULT"]
(format {:insert-into [:table [:a :b :c]]
:values [[1 [:default] 3] :default]}
{:inline true}))))
(testing "map values with default row, no columns"
(is (= ["INSERT INTO table (a, b, c) VALUES (1, 2, 3), DEFAULT, (4, 5, 6)"]
(format {:insert-into :table
:values [{:a 1 :b 2 :c 3} :default {:a 4 :b 5 :c 6}]}
{:inline true}))))
(testing "map values with default column, no columns"
(is (= ["INSERT INTO table (a, b, c) VALUES (1, DEFAULT, 3), DEFAULT"]
(format {:insert-into :table
:values [{:a 1 :b [:default] :c 3} :default]}
{:inline true}))))
(testing "empty values"
(is (= ["INSERT INTO table (a, b, c) VALUES ()"]
(format {:insert-into [:table [:a :b :c]]
:values []})))))
(deftest issue-421-mysql-replace-into
(is (= ["INSERT INTO table VALUES (?, ?, ?)" 1 2 3]
(sut/format {:insert-into :table :values [[1 2 3]]})))
(is (= ["REPLACE INTO table VALUES (?, ?, ?)" 1 2 3]
(sut/format {:replace-into :table :values [[1 2 3]]}
{:dialect :mysql :quoted false}))))
(deftest issue-496-overriding
(is (= ["INSERT INTO table (a, b) OVERRIDING SYSTEM VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :system} :table]
:columns [:a :b]
:values [[1 2]]})))
(is (= ["INSERT INTO table (a, b) OVERRIDING USER VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :user} :table [:a :b]]
:values [[1 2]]})))
(is (= ["INSERT INTO table (a, b) OVERRIDING SYSTEM VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :system} :table]
:values [{:a 1 :b 2}]}))))
(deftest output-clause-post-501
(sut/register-clause! :output :select :values)
(is (= ["INSERT INTO foo (bar) OUTPUT inserted.* VALUES (?)" 1]
(sut/format {:insert-into :foo :output [:inserted.*] :values [{:bar 1}]})))
(is (= ["INSERT INTO foo (bar) OUTPUT inserted.* VALUES (?)" 1]
(sut/format {:insert-into :foo :columns [:bar] :output [:inserted.*] :values [[1]]}))))
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)))))
(deftest upsert-where-test
(is (= ["INSERT INTO user (phone, name) VALUES (?, ?) ON CONFLICT (phone) WHERE phone IS NOT NULL DO UPDATE SET phone = EXCLUDED.phone, name = EXCLUDED.name WHERE user.active = FALSE" "5555555" "John"]
(sql/format
{:insert-into :user
:values [{:phone "5555555" :name "John"}]
:on-conflict [:phone
{:where [:<> :phone nil]}]
:do-update-set {:fields [:phone :name]
:where [:= :user.active false]}})
;; nilenso version
#_(sql/format
{:insert-into :user
:values [{:phone "5555555" :name "John"}]
;; nested under :upsert
:upsert {:on-conflict [:phone]
;; but :where is at the same level as :on-conflict
:where [:<> :phone nil]
;; this is the same as in honeysql:
:do-update-set {:fields [:phone :name]
:where [:= :user.active false]}}}))))
(deftest insert-into-with-alias
(testing "insert into with alias"
(is (= ["INSERT INTO distributors AS d (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname WHERE d.zipcode <> ? RETURNING d.*" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc" "21201"]
;; honeysql supports alias in insert-into:
(-> (insert-into :distributors :d)
;; nilensor required insert-into-as:
#_(insert-into-as :distributors :d)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(on-conflict :did)
;; honeysql supports names and a where clause:
(do-update-set :dname (where [:<> :d.zipcode "21201"]))
;; nilenso nested those under upsert:
#_(upsert (-> (on-conflict :did)
(do-update-set :dname)
(where [:<> :d.zipcode "21201"])))
(returning :d.*)
sql/format)))))
honeysql
(ns honey.sql.helpers-test
(:refer-clojure :exclude [filter for group-by partition-by set update])
(:require [clojure.test :refer [deftest is testing]]
[honey.sql :as sql]
[honey.sql.helpers :as h
:refer [add-column add-index alter-table columns create-table create-table-as create-view
create-materialized-view drop-view drop-materialized-view
create-index
bulk-collect-into
cross-join do-update-set drop-column drop-index drop-table
filter from full-join
group-by having insert-into
join-by join lateral left-join limit offset on-conflict
on-duplicate-key-update
order-by over partition-by refresh-materialized-view
rename-column rename-table returning right-join
select select-distinct select-top select-distinct-top
values where window with with-columns
with-data within-group]]))
(deftest test-value
(is (= ["INSERT INTO foo (bar) VALUES (?)" {:baz "my-val"}]
(->
(insert-into :foo)
(columns :bar)
(values [[[:lift {:baz "my-val"}]]])
sql/format)))
(is (= ["INSERT INTO foo (a, b, c) VALUES (?, ?, ?), (?, ?, ?)"
"a" "b" "c" "a" "b" "c"]
(-> (insert-into :foo)
(values [(array-map :a "a" :b "b" :c "c")
(hash-map :a "a" :b "b" :c "c")])
sql/format))))
(deftest issue-293-sql
;; these tests are based on the README at https://github.com/nilenso/honeysql-postgres
(is (= (-> (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)
[(str "INSERT INTO distributors (did, dname)"
" VALUES (?, ?), (?, ?)"
" ON CONFLICT (did)"
" DO UPDATE SET dname = EXCLUDED.dname"
" RETURNING *")
5 "Gizmo Transglobal"
6 "Associated Computing, Inc"]))
(is (= (-> (insert-into :distributors)
(values [{:did 23 :dname "Foo Distributors"}])
(on-conflict :did)
;; instead of do-update-set!
(do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :distributors.dname ")"]
:downer :EXCLUDED.downer})
sql/format)
[(str "INSERT INTO distributors (did, dname)"
" VALUES (?, ?)"
" ON CONFLICT (did)"
" DO UPDATE SET dname = EXCLUDED.dname || ? || distributors.dname || ?,"
" downer = EXCLUDED.downer")
23 "Foo Distributors" " (formerly " ")"]))
;; insert into / insert into as tests are below
(is (= (-> (select :id
(over [[:avg :salary] (-> (partition-by :department) (order-by :designation)) :Average]
[[:max :salary] :w :MaxSalary]))
(from :employee)
(window :w (partition-by :department))
sql/format)
[(str "SELECT id,"
" AVG(salary) OVER (PARTITION BY department ORDER BY designation ASC) AS Average,"
" MAX(salary) OVER w AS MaxSalary"
" FROM employee"
" WINDOW w AS (PARTITION BY department)")]))
;; test nil / empty window function clause:
(is (= (-> (select :id
(over [[:avg :salary] {} :Average]
[[:max :salary] nil :MaxSalary]))
(from :employee)
sql/format)
[(str "SELECT id,"
" AVG(salary) OVER () AS Average,"
" MAX(salary) OVER () AS MaxSalary"
" FROM employee")])))
(deftest issue-293-insert-into-data
;; insert into as (and other tests) based on :insert-into
;; examples in the clause reference docs:
;; first case -- table specifier:
(is (= (sql/format {:insert-into :transport
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
(is (= (sql/format {:insert-into :transport
:columns [:id :name]
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; with an alias:
(is (= (sql/format {:insert-into [:transport :t]
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport AS t VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
(is (= (sql/format {:insert-into [:transport :t]
:columns [:id :name]
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport AS t (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; second case -- table specifier and columns:
(is (= (sql/format {:insert-into [:transport [:id :name]]
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; with an alias:
(is (= (sql/format {:insert-into [[:transport :t] [:id :name]]
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport AS t (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; third case -- table/column specifier and query:
(is (= (sql/format '{insert-into (transport {select (id, name) from (cars)})})
["INSERT INTO transport SELECT id, name FROM cars"]))
;; with columns:
(is (= (sql/format '{insert-into ((transport (id, name)) {select (*) from (cars)})})
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; with an alias:
(is (= (sql/format '{insert-into ((transport t) {select (id, name) from (cars)})})
["INSERT INTO transport AS t SELECT id, name FROM cars"]))
;; with columns:
(is (= (sql/format '{insert-into ((transport (id, name)) {select (*) from (cars)})})
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; with an alias and columns:
(is (= (sql/format '{insert-into (((transport t) (id, name)) {select (*) from (cars)})})
["INSERT INTO transport AS t (id, name) SELECT * FROM cars"])))
(deftest issue-293-insert-into-helpers
;; and the same set of tests using the helper functions instead:
(is (= (sql/format (-> (insert-into :transport)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
(is (= (sql/format (-> (insert-into :transport)
(columns :id :name)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; with an alias:
(is (= (sql/format (-> (insert-into :transport :t)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport AS t VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
(is (= (sql/format (-> (insert-into :transport :t)
(columns :id :name)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport AS t (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; second case -- table specifier and columns:
(is (= (sql/format (-> (insert-into :transport [:id :name])
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; with an alias:
(is (= (sql/format (-> (insert-into [:transport :t] [:id :name])
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport AS t (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; third case -- table/column specifier and query:
(is (= (sql/format (insert-into :transport '{select (id, name) from (cars)}))
["INSERT INTO transport SELECT id, name FROM cars"]))
;; with columns:
(is (= (sql/format (insert-into [:transport [:id :name]] '{select (*) from (cars)}))
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; with an alias:
(is (= (sql/format (insert-into '(transport t) '{select (id, name) from (cars)}))
["INSERT INTO transport AS t SELECT id, name FROM cars"]))
;; with columns:
(is (= (sql/format (insert-into '(transport (id, name)) '{select (*) from (cars)}))
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; with an alias and columns:
(is (= (sql/format (insert-into ['(transport t) '(id, name)] '{select (*) from (cars)}))
["INSERT INTO transport AS t (id, name) SELECT * FROM cars"]))
;; three arguments with columns:
(is (= (sql/format (insert-into :transport [:id :name] '{select (*) from (cars)}))
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; three arguments with an alias and columns:
(is (= (sql/format (insert-into '(transport t) '(id, name) '{select (*) from (cars)}))
["INSERT INTO transport AS t (id, name) SELECT * FROM cars"])))
(deftest mysql-on-duplicate-key-update
(testing "From https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update"
(is (= (sql/format (-> (insert-into :device)
(columns :name)
(values [["Printer"]])
(on-duplicate-key-update {:name "Printer"})))
["INSERT INTO device (name) VALUES (?) ON DUPLICATE KEY UPDATE name = ?"
"Printer" "Printer"]))
(is (= (sql/format (-> (insert-into :device)
(columns :id :name)
(values [[4 "Printer"]])
(on-duplicate-key-update {:name "Central Printer"})))
["INSERT INTO device (id, name) VALUES (?, ?) ON DUPLICATE KEY UPDATE name = ?"
4 "Printer" "Central Printer"]))
(is (= (sql/format (-> (insert-into :table)
(columns :c1)
(values [[42]])
(on-duplicate-key-update {:c1 [:+ [:values :c1] 1]})))
["INSERT INTO table (c1) VALUES (?) ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + ?"
42 1]))))
(deftest issue-324
(testing "insert-into accepts statement"
(is (= (-> (with [:a])
(insert-into [:quux [:x :y]]
{:select [:id] :from [:table]}))
{:with [[:a]],
:insert-into [[:quux [:x :y]]
{:select [:id], :from [:table]}]}))))
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)))))
(deftest upsert-where-test
(is (= ["INSERT INTO user (phone, name) VALUES (?, ?) ON CONFLICT (phone) WHERE phone IS NOT NULL DO UPDATE SET phone = EXCLUDED.phone, name = EXCLUDED.name WHERE user.active = FALSE" "5555555" "John"]
(sql/format
{:insert-into :user
:values [{:phone "5555555" :name "John"}]
:on-conflict [:phone
{:where [:<> :phone nil]}]
:do-update-set {:fields [:phone :name]
:where [:= :user.active false]}})
;; nilenso version
#_(sql/format
{:insert-into :user
:values [{:phone "5555555" :name "John"}]
;; nested under :upsert
:upsert {:on-conflict [:phone]
;; but :where is at the same level as :on-conflict
:where [:<> :phone nil]
;; this is the same as in honeysql:
:do-update-set {:fields [:phone :name]
:where [:= :user.active false]}}}))))
(deftest insert-into-with-alias
(testing "insert into with alias"
(is (= ["INSERT INTO distributors AS d (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname WHERE d.zipcode <> ? RETURNING d.*" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc" "21201"]
;; honeysql supports alias in insert-into:
(-> (insert-into :distributors :d)
;; nilensor required insert-into-as:
#_(insert-into-as :distributors :d)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(on-conflict :did)
;; honeysql supports names and a where clause:
(do-update-set :dname (where [:<> :d.zipcode "21201"]))
;; nilenso nested those under upsert:
#_(upsert (-> (on-conflict :did)
(do-update-set :dname)
(where [:<> :d.zipcode "21201"])))
(returning :d.*)
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 insert-into
(is (= (format {:insert-into :foo})
["INSERT INTO foo"]))
(is (= (format {:insert-into [:foo {:select [:bar] :from [:baz]}]})
["INSERT INTO foo SELECT bar FROM baz"]))
(is (= (format {:insert-into [[:foo [:a :b :c]] {:select [:d :e :f] :from [:baz]}]})
["INSERT INTO foo (a, b, c) SELECT d, e, f FROM baz"]))
(is (= (format {:insert-into [[:foo [:a :b :c]] {:select [:d :e :f] :from [:baz]}]})
["INSERT INTO foo (a, b, c) SELECT d, e, f FROM baz"])))
(deftest insert-into-namespaced
;; un-namespaced: works as expected:
(is (= (format {:insert-into :foo :values [{:foo/id 1}]})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]})
["INSERT INTO foo (id) VALUES (?)" 2]))
(is (= (format {:insert-into :foo :values [{:foo/id 1}]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 2])))
(deftest insert-into-uneven-maps
;; we can't rely on ordering when the set of keys differs between maps:
(let [res (format {:insert-into :foo :values [{:id 1} {:id 2, :bar "quux"}]})]
(is (or (= res ["INSERT INTO foo (id, bar) VALUES (?, NULL), (?, ?)" 1 2 "quux"])
(= res ["INSERT INTO foo (bar, id) VALUES (NULL, ?), (?, ?)" 1 "quux" 2]))))
(let [res (format {:insert-into :foo :values [{:id 1, :bar "quux"} {:id 2}]})]
(is (or (= res ["INSERT INTO foo (id, bar) VALUES (?, ?), (?, NULL)" 1 "quux" 2])
(= res ["INSERT INTO foo (bar, id) VALUES (?, ?), (NULL, ?)" "quux" 1 2])))))
(deftest array-test
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?, ?])" 1 2 3 4]))
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array ["one" "two" "three"]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?])" "one" "two" "three"])))
(deftest parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]}
{:inline true})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(deftest inline-was-parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array (mapv vector
(repeat :inline)
[1 2 3 4])]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(deftest insert-example-tests
;; these examples are taken from https://www.postgresql.org/docs/13/sql-insert.html
(is (= ["
INSERT INTO films
VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes')
"]
(format {:insert-into :films
:values [[[:inline "UA502"] [:inline "Bananas"] [:inline 105]
[:inline "1971-07-13"] [:inline "Comedy"]
[:inline "82 minutes"]]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, ?, ?, ?)
" "UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 "1971-07-13" "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films
(code, title, did, date_prod, kind)
VALUES (?, ?, ?, ?, ?)
" "T_601", "Yojimo", 106, "1961-06-16", "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, "1961-06-16", "Drama"]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, DEFAULT, ?, ?)
" "UA502", "Bananas", 105, "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 [:default] "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films
(code, title, did, date_prod, kind)
VALUES (?, ?, ?, DEFAULT, ?)
" "T_601", "Yojimo", 106, "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, [:default], "Drama"]]}
{:pretty true}))))
(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}))))
(deftest issue-299-test
(let [name "test field"
;; this was a bug in 1.x -- adding here to prevent regression:
enabled [true, "); SELECT case when (SELECT current_setting('is_superuser'))='off' then pg_sleep(0.2) end; -- "]]
(is (= ["INSERT INTO table (name, enabled) VALUES (?, (TRUE, ?))" name (second enabled)]
(format {:insert-into :table
:values [{:name name
:enabled enabled}]})))))
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)))))
(deftest upsert-where-test
(is (= ["INSERT INTO user (phone, name) VALUES (?, ?) ON CONFLICT (phone) WHERE phone IS NOT NULL DO UPDATE SET phone = EXCLUDED.phone, name = EXCLUDED.name WHERE user.active = FALSE" "5555555" "John"]
(sql/format
{:insert-into :user
:values [{:phone "5555555" :name "John"}]
:on-conflict [:phone
{:where [:<> :phone nil]}]
:do-update-set {:fields [:phone :name]
:where [:= :user.active false]}})
;; nilenso version
#_(sql/format
{:insert-into :user
:values [{:phone "5555555" :name "John"}]
;; nested under :upsert
:upsert {:on-conflict [:phone]
;; but :where is at the same level as :on-conflict
:where [:<> :phone nil]
;; this is the same as in honeysql:
:do-update-set {:fields [:phone :name]
:where [:= :user.active false]}}}))))
(deftest insert-into-with-alias
(testing "insert into with alias"
(is (= ["INSERT INTO distributors AS d (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname WHERE d.zipcode <> ? RETURNING d.*" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc" "21201"]
;; honeysql supports alias in insert-into:
(-> (insert-into :distributors :d)
;; nilensor required insert-into-as:
#_(insert-into-as :distributors :d)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(on-conflict :did)
;; honeysql supports names and a where clause:
(do-update-set :dname (where [:<> :d.zipcode "21201"]))
;; nilenso nested those under upsert:
#_(upsert (-> (on-conflict :did)
(do-update-set :dname)
(where [:<> :d.zipcode "21201"])))
(returning :d.*)
sql/format)))))
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 insert-into
(is (= (format {:insert-into :foo})
["INSERT INTO foo"]))
(is (= (format {:insert-into [:foo {:select [:bar] :from [:baz]}]})
["INSERT INTO foo SELECT bar FROM baz"]))
(is (= (format {:insert-into [[:foo [:a :b :c]] {:select [:d :e :f] :from [:baz]}]})
["INSERT INTO foo (a, b, c) SELECT d, e, f FROM baz"]))
(is (= (format {:insert-into [[:foo [:a :b :c]] {:select [:d :e :f] :from [:baz]}]})
["INSERT INTO foo (a, b, c) SELECT d, e, f FROM baz"])))
(deftest insert-into-namespaced
;; un-namespaced: works as expected:
(is (= (format {:insert-into :foo :values [{:foo/id 1}]})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]})
["INSERT INTO foo (id) VALUES (?)" 2]))
(is (= (format {:insert-into :foo :values [{:foo/id 1}]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 2])))
(deftest insert-into-uneven-maps
;; we can't rely on ordering when the set of keys differs between maps:
(let [res (format {:insert-into :foo :values [{:id 1} {:id 2, :bar "quux"}]})]
(is (or (= res ["INSERT INTO foo (id, bar) VALUES (?, NULL), (?, ?)" 1 2 "quux"])
(= res ["INSERT INTO foo (bar, id) VALUES (NULL, ?), (?, ?)" 1 "quux" 2]))))
(let [res (format {:insert-into :foo :values [{:id 1, :bar "quux"} {:id 2}]})]
(is (or (= res ["INSERT INTO foo (id, bar) VALUES (?, ?), (?, NULL)" 1 "quux" 2])
(= res ["INSERT INTO foo (bar, id) VALUES (?, ?), (NULL, ?)" "quux" 1 2])))))
(deftest insert-into-functions
;; needs [[:raw ..]] because it's the columns case:
(is (= (format {:insert-into [[[:raw "My-Table Name"]] {:select [:bar] :from [:baz]}]})
["INSERT INTO My-Table Name SELECT bar FROM baz"]))
;; this variant only needs [:raw ..]
(is (= (format {:insert-into [[:raw "My-Table Name"]] :values [{:foo/id 1}]})
["INSERT INTO My-Table Name (id) VALUES (?)" 1]))
(is (= (format {:insert-into [:foo :bar] :values [{:foo/id 1}]})
["INSERT INTO foo AS bar (id) VALUES (?)" 1])))
(deftest array-test
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?, ?])" 1 2 3 4]))
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array ["one" "two" "three"]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?])" "one" "two" "three"]))
#_ ;; requested feature -- does not work yet
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array :?vals]]]}
{:params {:vals [1 2 3 4]}})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?, ?])" 1 2 3 4]))
(testing "typed array"
(is (= (format {:select [[[:array [] :integer]]]})
["SELECT ARRAY[]::INTEGER[]"]))
(is (= (format {:select [[[:array [1 2] :text]]]})
["SELECT ARRAY[?, ?]::TEXT[]" 1 2])))
(testing "array subquery"
(is (= (format {:select [[[:array {:select [:foo] :from [:bar]}]]]})
["SELECT ARRAY(SELECT foo FROM bar)"]))
(is (= (format {:select [[[:array {:select ^{:as :struct} [:foo :bar] :from [:bar]}]]]})
["SELECT ARRAY(SELECT AS STRUCT foo, bar FROM bar)"]))
;; documented subquery workaround:
(is (= (format {:select [[[:'ARRAY {:select [:foo] :from [:bar]}]]]})
["SELECT ARRAY (SELECT foo FROM bar)"]))))
(deftest parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]}
{:inline true})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(deftest inline-was-parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array (mapv vector
(repeat :inline)
[1 2 3 4])]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(deftest insert-example-tests
;; these examples are taken from https://www.postgresql.org/docs/13/sql-insert.html
(is (= ["
INSERT INTO films
VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes')
"]
(format {:insert-into :films
:values [[[:inline "UA502"] [:inline "Bananas"] [:inline 105]
[:inline "1971-07-13"] [:inline "Comedy"]
[:inline "82 minutes"]]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, ?, ?, ?)
" "UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 "1971-07-13" "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films (code, title, did, date_prod, kind)
VALUES (?, ?, ?, ?, ?)
" "T_601", "Yojimo", 106, "1961-06-16", "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, "1961-06-16", "Drama"]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, DEFAULT, ?, ?)
" "UA502", "Bananas", 105, "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 [:default] "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films (code, title, did, date_prod, kind)
VALUES (?, ?, ?, DEFAULT, ?)
" "T_601", "Yojimo", 106, "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, [:default], "Drama"]]}
{:pretty true}))))
(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}))))
(deftest issue-299-test
(let [name "test field"
;; this was a bug in 1.x -- adding here to prevent regression:
enabled [true, "); SELECT case when (SELECT current_setting('is_superuser'))='off' then pg_sleep(0.2) end; -- "]]
(is (= ["INSERT INTO table (name, enabled) VALUES (?, (TRUE, ?))" name (second enabled)]
(format {:insert-into :table
:values [{:name name
:enabled enabled}]})))))
(deftest issue-425-default-values-test
(testing "default values"
(is (= ["INSERT INTO table (a, b, c) DEFAULT VALUES"]
(format {:insert-into [:table [:a :b :c]] :values :default}))))
(testing "values with default row"
(is (= ["INSERT INTO table (a, b, c) VALUES (1, 2, 3), DEFAULT, (4, 5, 6)"]
(format {:insert-into [:table [:a :b :c]]
:values [[1 2 3] :default [4 5 6]]}
{:inline true}))))
(testing "values with default column"
(is (= ["INSERT INTO table (a, b, c) VALUES (1, DEFAULT, 3), DEFAULT"]
(format {:insert-into [:table [:a :b :c]]
:values [[1 [:default] 3] :default]}
{:inline true}))))
(testing "map values with default row, no columns"
(is (= ["INSERT INTO table (a, b, c) VALUES (1, 2, 3), DEFAULT, (4, 5, 6)"]
(format {:insert-into :table
:values [{:a 1 :b 2 :c 3} :default {:a 4 :b 5 :c 6}]}
{:inline true}))))
(testing "map values with default column, no columns"
(is (= ["INSERT INTO table (a, b, c) VALUES (1, DEFAULT, 3), DEFAULT"]
(format {:insert-into :table
:values [{:a 1 :b [:default] :c 3} :default]}
{:inline true}))))
(testing "empty values"
(is (= ["INSERT INTO table (a, b, c) VALUES ()"]
(format {:insert-into [:table [:a :b :c]]
:values []})))))
(deftest issue-421-mysql-replace-into
(is (= ["INSERT INTO table VALUES (?, ?, ?)" 1 2 3]
(sut/format {:insert-into :table :values [[1 2 3]]})))
(is (= ["REPLACE INTO table VALUES (?, ?, ?)" 1 2 3]
(sut/format {:replace-into :table :values [[1 2 3]]}
{:dialect :mysql :quoted false}))))
(deftest issue-496-overriding
(is (= ["INSERT INTO table (a, b) OVERRIDING SYSTEM VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :system} :table]
:columns [:a :b]
:values [[1 2]]})))
(is (= ["INSERT INTO table (a, b) OVERRIDING USER VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :user} :table [:a :b]]
:values [[1 2]]})))
(is (= ["INSERT INTO table (a, b) OVERRIDING SYSTEM VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :system} :table]
:values [{:a 1 :b 2}]}))))
(deftest output-clause-post-501
(sut/register-clause! :output :select :values)
(is (= ["INSERT INTO foo (bar) OUTPUT inserted.* VALUES (?)" 1]
(sut/format {:insert-into :foo :output [:inserted.*] :values [{:bar 1}]})))
(is (= ["INSERT INTO foo (bar) OUTPUT inserted.* VALUES (?)" 1]
(sut/format {:insert-into :foo :columns [:bar] :output [:inserted.*] :values [[1]]}))))