Skip to content

INSERT

SQL INSERT expressions allow you to insert rows in the database.

There is a lot of variety in how you can provide new data, and Beam supports all standard ways.

The insert function from Database.Beam.Query can be used to insert rows into a particular table. insert takes a table and a source of values, represented by SqlInsertValues, and returns a SqlInsert object that can be run in a MonadBeam with runInsert.

The SqlInsertValues type takes two type parameters. The first is the underlying database syntax, and the second is the shape of the data it carries, specified as a beam table type. For example, a source of values in Postgres that can be inserted in the Chinook customers table would have the type SqlInsertValues PgInsertValuesSyntax CustomerT. This abstracts over where those values actually are. The values may be explicit haskell values, expressions returning customers, a query returning customers, or something else. Either way, they can all be used in the same way with the insert function.

Inserting explicit new values

If you have a record of explicit Haskell values, use the insertValues function. For example, to insert a new playlist into our chinook database

runInsert $ insert (playlist chinookDb) $
  insertValues [ Playlist 700 (Just "My New Playlist")
               , Playlist 701 (Just "Another Playlist")
               , Playlist 702 (Just "Look... more playlists") ]

insertedPlaylists <-
  runSelectReturningList $
  select $ filter_ (\p -> playlistId p >=. 700) $
  all_ (playlist chinookDb)

putStrLn "Inserted playlists:"
forM_ insertedPlaylists $ \p ->
  putStrLn (show p)

INSERT INTO "Playlist"("PlaylistId",
                       "Name")
VALUES (700, 'My New Playlist'),
       (701, 'Another Playlist'),
       (702, 'Look... more playlists');


SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId") >= (700);

-- Output: Inserted playlists:
-- Output: Playlist {playlistId = 700, playlistName = Just "My New Playlist"}
-- Output: Playlist {playlistId = 701, playlistName = Just "Another Playlist"}
-- Output: Playlist {playlistId = 702, playlistName = Just "Look... more playlists"}

INSERT INTO "Playlist"("PlaylistId",
                       "Name")
VALUES (?, ?),
       (?, ?),
       (?, ?);

-- With values: [SQLInteger 700,SQLText "My New Playlist",SQLInteger 701,SQLText "Another Playlist",SQLInteger 702,SQLText "Look... more playlists"];

SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId")>=(?);

-- With values: [SQLInteger 700];
-- Output: Inserted playlists:
-- Output: Playlist {playlistId = 700, playlistName = Just "My New Playlist"}
-- Output: Playlist {playlistId = 701, playlistName = Just "Another Playlist"}
-- Output: Playlist {playlistId = 702, playlistName = Just "Look... more playlists"}

Inserting calculated values

Inserting explicit values is all well and good, but sometimes we want to defer some processing to the database. For example, perhaps we want to create a new invoice and use the current time as the invoice date. We could grab the current time using getCurrentTime and then use this to construct an explicit Haskell value, but this may cause synchronization issues for our application. To do this, beam allows us to specify arbitrary expressions as a source of values using the insertExpressions function.

runInsert $ insert (invoice chinookDb) $
  insertExpressions [ Invoice (val_ 800) (CustomerId (val_ 1)) currentTimestamp_
                              (val_ (Address (Just "123 My Street") (Just "Buenos Noches") (Just "Rio") (Just "Mozambique") (Just "ABCDEF")))
                              (val_ 1000) ]

Just newInvoice <-
  runSelectReturningOne $
  lookup_ (invoice chinookDb) (InvoiceId 800)

putStrLn ("Inserted invoice: " ++ show newInvoice)

INSERT INTO "Invoice"("InvoiceId",
                      "CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
VALUES (800, 1, CURRENT_TIMESTAMP, '123 My Street', 'Buenos Noches', 'Rio', 'Mozambique', 'ABCDEF', '1000.0');


SELECT "t0"."InvoiceId" AS "res0",
       "t0"."CustomerId" AS "res1",
       "t0"."InvoiceDate" AS "res2",
       "t0"."BillingAddress" AS "res3",
       "t0"."BillingCity" AS "res4",
       "t0"."BillingState" AS "res5",
       "t0"."BillingCountry" AS "res6",
       "t0"."BillingPostalCode" AS "res7",
       "t0"."Total" AS "res8"
FROM "Invoice" AS "t0"
WHERE ("t0"."InvoiceId") = (800);

-- Output: Inserted invoice: Invoice {invoiceId = SqlSerial {unSerial = 800}, invoiceCustomer = CustomerId 1, invoiceDate = 2022-01-18 01:31:21.436807, invoiceBillingAddress = Address {address = Just "123 My Street", addressCity = Just "Buenos Noches", addressState = Just "Rio", addressCountry = Just "Mozambique", addressPostalCode = Just "ABCDEF"}, invoiceTotal = 1000.0}

INSERT INTO "Invoice"("InvoiceId",
                      "CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
VALUES (?, ?, CURRENT_TIMESTAMP, ?, ?, ?, ?, ?, ?);

-- With values: [SQLInteger 800,SQLInteger 1,SQLText "123 My Street",SQLText "Buenos Noches",SQLText "Rio",SQLText "Mozambique",SQLText "ABCDEF",SQLText "1000.0"];

SELECT "t0"."InvoiceId" AS "res0",
       "t0"."CustomerId" AS "res1",
       "t0"."InvoiceDate" AS "res2",
       "t0"."BillingAddress" AS "res3",
       "t0"."BillingCity" AS "res4",
       "t0"."BillingState" AS "res5",
       "t0"."BillingCountry" AS "res6",
       "t0"."BillingPostalCode" AS "res7",
       "t0"."Total" AS "res8"
FROM "Invoice" AS "t0"
WHERE ("t0"."InvoiceId")=(?);

-- With values: [SQLInteger 800];
-- Output: Inserted invoice: Invoice {invoiceId = SqlSerial {unSerial = 800}, invoiceCustomer = CustomerId 1, invoiceDate = 2022-01-18 01:31:24, invoiceBillingAddress = Address {address = Just "123 My Street", addressCity = Just "Buenos Noches", addressState = Just "Rio", addressCountry = Just "Mozambique", addressPostalCode = Just "ABCDEF"}, invoiceTotal = 1000.0}

insertExpressions is strictly more general than insertValues. We can turn any insertValues to an insertExpressions by running every table value through the val_ function to convert a Haskell literal to an expression.

For example, we can write the playlist example above as

runInsert $ insert (playlist chinookDb) $
  insertExpressions [ val_ $ Playlist 700 (Just "My New Playlist")
                    , val_ $ Playlist 701 (Just "Another Playlist")
                    , val_ $ Playlist 702 (Just "Look... more playlists") ]

insertedPlaylists <-
  runSelectReturningList $
  select $ filter_ (\p -> playlistId p >=. 700) $
  all_ (playlist chinookDb)

putStrLn "Inserted playlists:"
forM_ insertedPlaylists $ \p ->
  putStrLn (show p)

INSERT INTO "Playlist"("PlaylistId",
                       "Name")
VALUES (700, 'My New Playlist'),
       (701, 'Another Playlist'),
       (702, 'Look... more playlists');


SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId") >= (700);

-- Output: Inserted playlists:
-- Output: Playlist {playlistId = 700, playlistName = Just "My New Playlist"}
-- Output: Playlist {playlistId = 701, playlistName = Just "Another Playlist"}
-- Output: Playlist {playlistId = 702, playlistName = Just "Look... more playlists"}

INSERT INTO "Playlist"("PlaylistId",
                       "Name")
VALUES (?, ?),
       (?, ?),
       (?, ?);

-- With values: [SQLInteger 700,SQLText "My New Playlist",SQLInteger 701,SQLText "Another Playlist",SQLInteger 702,SQLText "Look... more playlists"];

SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId")>=(?);

-- With values: [SQLInteger 700];
-- Output: Inserted playlists:
-- Output: Playlist {playlistId = 700, playlistName = Just "My New Playlist"}
-- Output: Playlist {playlistId = 701, playlistName = Just "Another Playlist"}
-- Output: Playlist {playlistId = 702, playlistName = Just "Look... more playlists"}

One common use of insertExpressions_ is when adding new rows to tables where one field needs to be set to the default value. For example, auto-incrementing keys or random UUIDs are a common way to assign primary keys to rows. You can use insertExpressions_ using the default_ expression for each column that you want to use the default value for.

For example, the query below adds a new invoice asking the database to assign a new id.

runInsert $ insert (invoice chinookDb) $
  insertExpressions [ Invoice default_ -- Ask the database to give us a default id
                              (val_ (CustomerId 1)) currentTimestamp_
                              (val_ (Address (Just "123 My Street") (Just "Buenos Noches") (Just "Rio") (Just "Mozambique") (Just "ABCDEF")))
                              (val_ 1000) ]

INSERT INTO "Invoice"("InvoiceId",
                      "CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
VALUES (DEFAULT, 1, CURRENT_TIMESTAMP, '123 My Street', 'Buenos Noches', 'Rio', 'Mozambique', 'ABCDEF', '1000.0');

Warning

SQLite is a great little backend, but it doesn't support some standard SQL features, like the DEFAULT keyword in inserts. You can retrieve the same functionality by only inserting into a subset of columns. See the section on that below.

Retrieving the rows inserted

However, now we have no way of knowing what value the database assigned. Unfortunately, there is no database-agnostic solution to this problem. However, it's a common enough use case that beam provides a backend-agnostic way for some backends. Backends that provide this functionality provide an instance of MonadBeamInsertReturning. In order to use this class, you'll need to explicitly import Database.Beam.Backend.SQL.BeamExtensions. Below, we've imported this module qualified.

[newInvoice] <-
  BeamExtensions.runInsertReturningList $ insert (invoice chinookDb) $
  insertExpressions [ Invoice default_ -- Ask the database to give us a default id
                              (val_ (CustomerId 1)) currentTimestamp_
                              (val_ (Address (Just "123 My Street") (Just "Buenos Noches") (Just "Rio") (Just "Mozambique") (Just "ABCDEF")))
                              (val_ 1000) ]

putStrLn ("We inserted a new invoice, and the result was " ++ show newInvoice)

INSERT INTO "Invoice"("InvoiceId",
                      "CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
VALUES (DEFAULT, 1, CURRENT_TIMESTAMP, '123 My Street', 'Buenos Noches', 'Rio', 'Mozambique', 'ABCDEF', '1000.0') RETURNING "InvoiceId",
                                                                                                                            "CustomerId",
                                                                                                                            "InvoiceDate",
                                                                                                                            "BillingAddress",
                                                                                                                            "BillingCity",
                                                                                                                            "BillingState",
                                                                                                                            "BillingCountry",
                                                                                                                            "BillingPostalCode",
                                                                                                                            "Total";

-- Output: We inserted a new invoice, and the result was Invoice {invoiceId = SqlSerial {unSerial = 501}, invoiceCustomer = CustomerId 1, invoiceDate = 2022-01-18 01:31:34.451145, invoiceBillingAddress = Address {address = Just "123 My Street", addressCity = Just "Buenos Noches", addressState = Just "Rio", addressCountry = Just "Mozambique", addressPostalCode = Just "ABCDEF"}, invoiceTotal = 1000.0}

INSERT INTO "Invoice"("CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
VALUES (?, CURRENT_TIMESTAMP, ?, ?, ?, ?, ?, ?);

-- With values: [SQLInteger 1,SQLText "123 My Street",SQLText "Buenos Noches",SQLText "Rio",SQLText "Mozambique",SQLText "ABCDEF",SQLText "1000.0"];
-- Output: We inserted a new invoice, and the result was Invoice {invoiceId = SqlSerial {unSerial = 413}, invoiceCustomer = CustomerId 1, invoiceDate = 2022-01-18 01:31:37, invoiceBillingAddress = Address {address = Just "123 My Street", addressCity = Just "Buenos Noches", addressState = Just "Rio", addressCountry = Just "Mozambique", addressPostalCode = Just "ABCDEF"}, invoiceTotal = 1000.0}

The pattern match on the single newInvoice is safe, even though its partial. In general, you can expect the same amount of rows returned as specified in your SqlInsertValues. If you know what this is statically, then you can feel free to pattern match directly. Otherwise (if you used insertFrom, for example), you'll need to handle the possibility that nothing was inserted.

Note

Although SQLite has no support for the DEFAULT clause, MonadBeamInsertReturning in beam-sqlite inserts rows one at a time and will detect usage of the DEFAULT keyword. The beam authors consider this okay. While most beam statements are guaranteed to translate directly to the underlying DBMS system, runInsertReturningList is explicitly marked as emulated functionality.

Inserting from the result of a SELECT statement

Sometimes you want to use existing data to insert values. For example, perhaps we want to give every customer their own playlist, titled "'s playlist".

We can use the insertFrom function to make a SqlInsertValues corresponding to the result of a query. Make sure to return a projection with the same 'shape' as your data. If not, you'll get a compile time error.

For example, to create the playlists as above

runInsert $ insert (playlist chinookDb) $
  insertFrom $ do
    c <- all_ (customer chinookDb)
    pure (Playlist (customerId c + 1000) (just_ (concat_ [ customerFirstName c, "'s Playlist" ])))

playlists <- runSelectReturningList $ select $ limit_ 10 $
             orderBy_ (\p -> asc_ (playlistId p)) $
             filter_ (\p -> playlistId p >=. 1000) $
             all_ (playlist chinookDb)

putStrLn "Inserted playlists"
forM_ playlists $ \playlist ->
  putStrLn ("  - " ++ show playlist)

INSERT INTO "Playlist"("PlaylistId",
                       "Name")
SELECT ("t0"."CustomerId") + (1000) AS "res0",
       CONCAT("t0"."FirstName", '''s Playlist') AS "res1"
FROM "Customer" AS "t0";


SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId") >= (1000)
ORDER BY "t0"."PlaylistId" ASC
LIMIT 10;

-- Output: Inserted playlists
-- Output:   - Playlist {playlistId = 1001, playlistName = Just "Lu\237s's Playlist"}
-- Output:   - Playlist {playlistId = 1002, playlistName = Just "Leonie's Playlist"}
-- Output:   - Playlist {playlistId = 1003, playlistName = Just "Fran\231ois's Playlist"}
-- Output:   - Playlist {playlistId = 1004, playlistName = Just "Bj\345rn's Playlist"}
-- Output:   - Playlist {playlistId = 1005, playlistName = Just "Franti\154ek's Playlist"}
-- Output:   - Playlist {playlistId = 1006, playlistName = Just "Helena's Playlist"}
-- Output:   - Playlist {playlistId = 1007, playlistName = Just "Astrid's Playlist"}
-- Output:   - Playlist {playlistId = 1008, playlistName = Just "Daan's Playlist"}
-- Output:   - Playlist {playlistId = 1009, playlistName = Just "Kara's Playlist"}
-- Output:   - Playlist {playlistId = 1010, playlistName = Just "Eduardo's Playlist"}

INSERT INTO "Playlist"("PlaylistId",
                       "Name")
SELECT ("t0"."CustomerId") + (?) AS "res0",
       ("t0"."FirstName" || (?)) AS "res1"
FROM "Customer" AS "t0";

-- With values: [SQLInteger 1000,SQLText "'s Playlist"];

SELECT "t0"."PlaylistId" AS "res0",
       "t0"."Name" AS "res1"
FROM "Playlist" AS "t0"
WHERE ("t0"."PlaylistId")>=(?)
ORDER BY "t0"."PlaylistId" ASC
LIMIT 10;

-- With values: [SQLInteger 1000];
-- Output: Inserted playlists
-- Output:   - Playlist {playlistId = 1001, playlistName = Just "Lu\237s's Playlist"}
-- Output:   - Playlist {playlistId = 1002, playlistName = Just "Leonie's Playlist"}
-- Output:   - Playlist {playlistId = 1003, playlistName = Just "Fran\231ois's Playlist"}
-- Output:   - Playlist {playlistId = 1004, playlistName = Just "Bj\248rn's Playlist"}
-- Output:   - Playlist {playlistId = 1005, playlistName = Just "Franti\353ek's Playlist"}
-- Output:   - Playlist {playlistId = 1006, playlistName = Just "Helena's Playlist"}
-- Output:   - Playlist {playlistId = 1007, playlistName = Just "Astrid's Playlist"}
-- Output:   - Playlist {playlistId = 1008, playlistName = Just "Daan's Playlist"}
-- Output:   - Playlist {playlistId = 1009, playlistName = Just "Kara's Playlist"}
-- Output:   - Playlist {playlistId = 1010, playlistName = Just "Eduardo's Playlist"}

Choosing a subset of columns

Above, we used the default_ clause to set a column to a default value. Unfortunately, not all backends support default_ (SQLite being a notable exception). Moreover, some INSERT forms simply can't use default_, such as insertFrom_ (you can't return default_ from a query). The standard SQL tool used in these cases is limiting the inserted data to specific columns. For example, suppose we want to insert new invoices for every customer with today's date. We can use the insertOnly function to project which field's are being inserted.

runInsert $
  insertOnly (invoice chinookDb)
             (\i -> ( invoiceCustomer i, invoiceDate i, invoiceBillingAddress i, invoiceTotal i ) ) $
  insertFrom $ do
    c <- all_ (customer chinookDb)

    -- We'll just charge each customer $10 to be mean!
    pure (primaryKey c, currentTimestamp_, customerAddress c, as_ @Scientific $ val_ 10)

INSERT INTO "Invoice"("CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
SELECT "t0"."CustomerId" AS "res0",
       CURRENT_TIMESTAMP AS "res1",
                            "t0"."Address" AS "res2",
                            "t0"."City" AS "res3",
                            "t0"."State" AS "res4",
                            "t0"."Country" AS "res5",
                            "t0"."PostalCode" AS "res6",
                            '10.0' AS "res7"
FROM "Customer" AS "t0";

INSERT INTO "Invoice"("CustomerId",
                      "InvoiceDate",
                      "BillingAddress",
                      "BillingCity",
                      "BillingState",
                      "BillingCountry",
                      "BillingPostalCode",
                      "Total")
SELECT "t0"."CustomerId" AS "res0",
       CURRENT_TIMESTAMP AS "res1",
                            "t0"."Address" AS "res2",
                            "t0"."City" AS "res3",
                            "t0"."State" AS "res4",
                            "t0"."Country" AS "res5",
                            "t0"."PostalCode" AS "res6",
                            ? AS "res7"
FROM "Customer" AS "t0";

-- With values: [SQLText "10.0"];

Inserting nothing

Oftentimes, the values to be inserted are generated automatically by some Haskell function, and you just insert the resulting list. Sometimes, these lists may be empty. If you blindly translated this into SQL, you'd end up with INSERTs with empty VALUE clauses, which are illegal. Beam actually handles this gracefully. If a SqlInsertValues has no rows to insert, the SqlInsert returned by insert will know that it is empty. Running this SqlInsert results in nothing being sent to the database, which you can verify below.

let superComplicatedAction = pure [] -- Hopefully, you're more creative!

valuesToInsert <- superComplicatedAction

putStrLn "The following runInsert will send no commands to the database"
runInsert $ insert (playlist chinookDb) $
  insertValues valuesToInsert
putStrLn "See! I told you!"

-- Output: The following runInsert will send no commands to the database
-- Output: See! I told you!

-- Output: The following runInsert will send no commands to the database
-- Output: See! I told you!

ON CONFLICT

Several backends (such as Postgres and SQLite) support ON CONFLICT subexpressions that specify what action to take when an INSERT statement conlicts with already present data.

Beam support backend-agnostic ON CONFLICT statements via the BeamHasInsertOnConflictsyntax. This class contains a new function to generate an SqlInsert. The insertOnConflict function can be used to attach ON CONFLICT actions to a SqlInsert.

  insertOnConflict
    :: Beamable table
    => DatabaseEntity be db (TableEntity table)
    -> SqlInsertValues be (table (QExpr be s))
    -> SqlConflictTarget be table
    -> SqlConflictAction be table
    -> SqlInsert be table

The SqlConflictTarget specifies on which kinds of conflicts the action should run. You have a few options

  • anyConflict - run the action on any conflict
  • conflictingFields - run the action only when certain fields conflict
  • conflictingFieldsWhere - run the action only when certain fields conflict and a particular expression evaluates to true.

The SqlConflictAction specifies what to do when a conflict happens.

  • onConflictDoNothing - this cancels the insertion
  • onConflictUpdateSet - sets fields to new values based on the current values
  • onConflictUpdateSetWhere - sets fields to new values if a particular condition holds

Acting on any conflict

A common use case of ON CONFLICT is to upsert rows into a database. Upsertion refers to only inserting a row if another conflicting row does not already exist. For example, if you have a new customer with primary key 42, and you don't know if it's in the database or not, but you want to insert it if not, you can use the insertOnConflict function with the anyConflict target.

let
  newCustomer = Customer 42 "John" "Doe" Nothing (Address (Just "Street") (Just "City") (Just "State") Nothing Nothing) Nothing Nothing "john.doe@johndoe.com" nothing_

runInsert $
  insertOnConflict (customer chinookDb) (insertValues [newCustomer])
     anyConflict
     onConflictDoNothing

Acting only on certain conflicts

Sometimes you only want to perform an action if a certain constraint is violated. If the conflicting index or constraint is on a field you can specify which fields with the function conflictingFields.

--! import Database.Beam.Backend.SQL.BeamExtensions (BeamHasInsertOnConflict(..))
let
  newCustomer = Customer 42 "John" "Doe" Nothing (Address (Just "Street") (Just "City") (Just "State") Nothing Nothing) Nothing Nothing "john.doe@johndoe.com" nothing_

runInsert $
  insertOnConflict (customer chinookDb) (insertValues [newCustomer])
    (conflictingFields (\tbl -> primaryKey tbl))
    (onConflictUpdateSet (\fields oldValues -> fields <-. val_ newCustomer))

INSERT INTO "Customer"("CustomerId",
                       "FirstName",
                       "LastName",
                       "Company",
                       "Address",
                       "City",
                       "State",
                       "Country",
                       "PostalCode",
                       "Phone",
                       "Fax",
                       "Email",
                       "SupportRepId")
VALUES (42, 'John', 'Doe', null, 'Street', 'City', 'State', null, null, null, null, 'john.doe@johndoe.com', null) ON CONFLICT ("CustomerId") DO
UPDATE
SET "CustomerId"=(42),
    "FirstName"=('John'),
    "LastName"=('Doe'),
    "Company"=(null),
    "Address"=('Street'),
    "City"=('City'),
    "State"=('State'),
    "Country"=(null),
    "PostalCode"=(null),
    "Phone"=(null),
    "Fax"=(null),
    "Email"=('john.doe@johndoe.com'),
    "SupportRepId"=(null);

INSERT INTO "Customer"("CustomerId",
                       "FirstName",
                       "LastName",
                       "Company",
                       "Address",
                       "City",
                       "State",
                       "Country",
                       "PostalCode",
                       "Phone",
                       "Fax",
                       "Email",
                       "SupportRepId")
VALUES (?, ?, ?, NULL, ?, ?, ?, NULL, NULL, NULL, NULL, ?, NULL) ON CONFLICT ("CustomerId") DO
UPDATE
SET "CustomerId" = ?,
    "FirstName" = ?,
    "LastName" = ?,
    "Company" = NULL,
    "Address" = ?,
    "City" = ?,
    "State" = ?,
    "Country" = NULL,
    "PostalCode" = NULL,
    "Phone" = NULL,
    "Fax" = NULL,
    "Email" = ?,
    "SupportRepId" = NULL;

-- With values: [SQLInteger 42,SQLText "John",SQLText "Doe",SQLText "Street",SQLText "City",SQLText "State",SQLText "john.doe@johndoe.com",SQLInteger 42,SQLText "John",SQLText "Doe",SQLText "Street",SQLText "City",SQLText "State",SQLText "john.doe@johndoe.com"];

Tip

To specify a conflict on the primary keys, use conflictingFields primaryKey.

You can also specify how to change the record should it not match. For example, to append the e-mail as an alternate when you insert an existing row, you can use the oldValues argument to get access to the old value.

--! import Database.Beam.Backend.SQL.BeamExtensions (BeamHasInsertOnConflict(..))
let
  newCustomer = Customer 42 "John" "Doe" Nothing (Address (Just "Street") (Just "City") (Just "State") Nothing Nothing) Nothing Nothing "john.doe@johndoe.com" nothing_

runInsert $
  insertOnConflict (customer chinookDb) (insertValues [newCustomer])
    (conflictingFields (\tbl -> primaryKey tbl))
    (onConflictUpdateSet (\fields oldValues -> customerEmail fields <-. concat_ [ customerEmail oldValues, ";", val_ (customerEmail newCustomer)]))

INSERT INTO "Customer"("CustomerId",
                       "FirstName",
                       "LastName",
                       "Company",
                       "Address",
                       "City",
                       "State",
                       "Country",
                       "PostalCode",
                       "Phone",
                       "Fax",
                       "Email",
                       "SupportRepId")
VALUES (42, 'John', 'Doe', null, 'Street', 'City', 'State', null, null, null, null, 'john.doe@johndoe.com', null) ON CONFLICT ("CustomerId") DO
UPDATE
SET "Email"=(CONCAT("excluded"."Email", ';', 'john.doe@johndoe.com'));

INSERT INTO "Customer"("CustomerId",
                       "FirstName",
                       "LastName",
                       "Company",
                       "Address",
                       "City",
                       "State",
                       "Country",
                       "PostalCode",
                       "Phone",
                       "Fax",
                       "Email",
                       "SupportRepId")
VALUES (?, ?, ?, NULL, ?, ?, ?, NULL, NULL, NULL, NULL, ?, NULL) ON CONFLICT ("CustomerId") DO
UPDATE
SET "Email" = ("excluded"."Email" || (?) || (?));

-- With values: [SQLInteger 42,SQLText "John",SQLText "Doe",SQLText "Street",SQLText "City",SQLText "State",SQLText "john.doe@johndoe.com",SQLText ";",SQLText "john.doe@johndoe.com"];

If you want to be even more particular and only do this transformation on rows corresponding to customers from one state, use conflictingFieldsWhere.

--! import Database.Beam.Backend.SQL.BeamExtensions (BeamHasInsertOnConflict(..))
let
  newCustomer = Customer 42 "John" "Doe" Nothing (Address (Just "Street") (Just "City") (Just "State") Nothing Nothing) Nothing Nothing "john.doe@johndoe.com" nothing_

runInsert $
  insertOnConflict (customer chinookDb) (insertValues [newCustomer])
    (conflictingFieldsWhere (\tbl -> primaryKey tbl) (\tbl -> addressState (customerAddress tbl) ==. val_ (Just "CA")))
    (onConflictUpdateSet (\fields oldValues -> customerEmail fields <-. concat_ [ customerEmail oldValues, ";", val_ (customerEmail newCustomer)]))

INSERT INTO "Customer"("CustomerId",
                       "FirstName",
                       "LastName",
                       "Company",
                       "Address",
                       "City",
                       "State",
                       "Country",
                       "PostalCode",
                       "Phone",
                       "Fax",
                       "Email",
                       "SupportRepId")
VALUES (42, 'John', 'Doe', null, 'Street', 'City', 'State', null, null, null, null, 'john.doe@johndoe.com', null) ON CONFLICT ("CustomerId")
WHERE (("State") IS NOT DISTINCT
       FROM ('CA')) DO
  UPDATE
  SET "Email"=(CONCAT("excluded"."Email", ';', 'john.doe@johndoe.com'));

INSERT INTO "Customer"("CustomerId",
                       "FirstName",
                       "LastName",
                       "Company",
                       "Address",
                       "City",
                       "State",
                       "Country",
                       "PostalCode",
                       "Phone",
                       "Fax",
                       "Email",
                       "SupportRepId")
VALUES (?, ?, ?, NULL, ?, ?, ?, NULL, NULL, NULL, NULL, ?, NULL) ON CONFLICT ("CustomerId")
WHERE CASE
          WHEN (("State") IS NULL)
               AND ((?) IS NULL) THEN ?
          WHEN (("State") IS NULL)
               OR ((?) IS NULL) THEN ?
          ELSE ("State")=(?)
      END DO
  UPDATE
  SET "Email" = ("excluded"."Email" || (?) || (?));

-- With values: [SQLInteger 42,SQLText "John",SQLText "Doe",SQLText "Street",SQLText "City",SQLText "State",SQLText "john.doe@johndoe.com",SQLText "CA",SQLInteger 1,SQLText "CA",SQLInteger 0,SQLText "CA",SQLText ";",SQLText "john.doe@johndoe.com"];