Database.Esqueleto.PostgreSQL

Aggregate mode

(array_agg) Concatenate distinct input values, including NULLs, into an array.

(array_remove) Remove all elements equal to the given value from the array.

Remove NULL values from an array

(string_agg) Concatenate input values separated by a delimiter.

(string_agg) Concatenate input values separated by a delimiter.

Coalesce an array with an empty default value

(chr) Translate the given integer to a character. (Note the result will depend on the character set of your database.)

(random()) Split out into database specific modules because MySQL uses `rand()`.

Perform an upsert operation on the given record.

If the record exists in the database already, then the updates will be performed on that record. If the record does not exist, then the provided record will be inserted.

If you wish to provide an empty list of updates (ie "if the record exists, do nothing"), then you will need to call upsertMaybe. Postgres will not return anything if there are no modifications or inserts made.

Like upsert, but permits an empty list of updates to be performed.

If no updates are provided and the record already was present in the database, then this will return Nothing. If you want to fetch the record out of the database, you can write:

 mresult <- upsertMaybe record []
 case mresult of
     Nothing ->
         getBy (onlyUniqueP record)
     Just res ->
         pure (Just res)

Attempt to insert a record into the database. If the record already exists for the given Unique record, then a list of updates will be performed.

If you provide an empty list of updates, then this function will return Nothing if the record already exists in the database.

Inserts into a table the results of a query similar to insertSelect but allows to update values that violate a constraint during insertions.

Example of usage:

mkPersist sqlSettings [persistLowerCase|
  Bar
    num Int
    deriving Eq Show
  Foo
    num Int
    UniqueFoo num
    deriving Eq Show
|]

action = do
    insertSelectWithConflict
        UniqueFoo -- (UniqueFoo undefined) or (UniqueFoo anyNumber) would also work
        (do
            b <- from $ table @Bar
            return $ Foo <# (b ^. BarNum)
        )
        (\current excluded ->
            [FooNum =. (current ^. FooNum) +. (excluded ^. FooNum)]
        )

Inserts to table Foo all Bar.num values and in case of conflict SomeFooUnique, the conflicting value is updated to the current plus the excluded.

Same as insertSelectWithConflict but returns the number of rows affected.

NOWAIT syntax for postgres locking error will be thrown if locked rows are attempted to be selected

default behaviour of postgres locks. will attempt to wait for locks to expire

`SKIP LOCKED` syntax for postgres locking locked rows will be skipped

`FOR UPDATE OF` syntax for postgres locking allows locking of specific tables with an update lock in a view or join

`FOR NO KEY UPDATE OF` syntax for postgres locking allows locking of specific tables with a no key update lock in a view or join

FOR SHARE syntax for Postgres locking.

Example use:

 locking forShare

`FOR SHARE OF` syntax for postgres locking allows locking of specific tables with a share lock in a view or join

`FOR KEY SHARE OF` syntax for postgres locking allows locking of specific tables with a key share lock in a view or join

Allow aggregate functions to take a filter clause.

Example of usage:

share [mkPersist sqlSettings] [persistLowerCase|
  User
    name Text
    deriving Eq Show
  Task
    userId UserId
    completed Bool
    deriving Eq Show
|]

select $ from $ (users InnerJoin tasks) -> do
  on $ users ^. UserId ==. tasks ^. TaskUserId
  groupBy $ users ^. UserId
  return
   ( users ^. UserId
   , count (tasks ^. TaskId) filterWhere (tasks ^. TaskCompleted ==. val True)
   , count (tasks ^. TaskId) filterWhere (tasks ^. TaskCompleted ==. val False)
   )

Allows to use `VALUES (..)` in-memory set of values in RHS of from expressions. Useful for JOIN's on known values which also can be additionally preprocessed somehow on db side with usage of inner PostgreSQL capabilities.

Example of usage:

share [mkPersist sqlSettings] [persistLowerCase|
  User
    name Text
    age Int
    deriving Eq Show

select $ do
 bound :& user <- from $
     values (   (val (10 :: Int), val ("ten" :: Text))
           :| [ (val 20, val "twenty")
              , (val 30, val "thirty") ]
           )
     InnerJoin table User
     on (((bound, _boundName) :& user) -> user^.UserAge >=. bound)
 groupBy bound
 pure (bound, count @Int $ user^.UserName)

ILIKE operator (case-insensitive LIKE).

DISTINCT ON. Change the current SELECT into SELECT DISTINCT ON (SqlExpressions). For example:

select $ do
  foo <- from $ table @Foo
  distinctOn [don (foo ^. FooName), don (foo ^. FooState)]
  pure foo

You can also chain different calls to distinctOn. The above is equivalent to:

select $ do
  foo <- from $ table @Foo
  distinctOn [don (foo ^. FooName)]
  distinctOn [don (foo ^. FooState)]
  pure foo

Each call to distinctOn adds more SqlExpressions. Calls to distinctOn override any calls to distinct.

Note that PostgreSQL requires the SqlExpressions on DISTINCT ON to be the first ones to appear on a ORDER BY. This is not managed automatically by esqueleto, keeping its spirit of trying to be close to raw SQL.

A convenience function that calls both distinctOn and orderBy. In other words,

distinctOnOrderBy [asc foo, desc bar, desc quux]

is the same as:

distinctOn [don foo, don  bar, don  quux]
orderBy  [asc foo, desc bar, desc quux]
  ...

WITH MATERIALIZED clause is used to introduce a Common Table Expression (CTE) with the MATERIALIZED keyword. The MATERIALIZED keyword is only supported in PostgreSQL >= version 12. In Esqueleto, CTEs should be used as a subquery memoization tactic. PostgreSQL treats a materialized CTE as an optimization fence. A materialized CTE is always fully calculated, and is not "inlined" with other table joins. Without the MATERIALIZED keyword, PostgreSQL >= 12 may "inline" the CTE as though it was any other join. You should always verify that using a materialized CTE will in fact improve your performance over a regular subquery.

select $ do
cte <- withMaterialized subQuery
cteResult <- from cte
where_ $ cteResult ...
pure cteResult

For more information on materialized CTEs, see the PostgreSQL manual documentation on Common Table Expression Materialization.

WITH NOT MATERIALIZED clause is used to introduce a Common Table Expression (CTE) with the NOT MATERIALIZED keywords. These are only supported in PostgreSQL >= version 12. In Esqueleto, CTEs should be used as a subquery memoization tactic. PostgreSQL treats a materialized CTE as an optimization fence. A MATERIALIZED CTE is always fully calculated, and is not "inlined" with other table joins. Sometimes, this is undesirable, so postgres provides the NOT MATERIALIZED modifier to prevent this behavior, thus enabling it to possibly decide to treat the CTE as any other join.

Given the above, it is unlikely that this function will be useful, as a normal join should be used instead, but is provided for completeness.

select $ do
cte <- withNotMaterialized subQuery
cteResult <- from cte
where_ $ cteResult ...
pure cteResult

For more information on materialized CTEs, see the PostgreSQL manual documentation on Common Table Expression Materialization.

Ascending order of this field or SqlExpression with nulls coming first.

Ascending order of this field or SqlExpression with nulls coming last. Note that this is the same as normal ascending ordering in Postgres, but it has been included for completeness.

Descending order of this field or SqlExpression with nulls coming first. Note that this is the same as normal ascending ordering in Postgres, but it has been included for completeness.

Descending order of this field or SqlExpression with nulls coming last.

(Internal) Create a custom aggregate functions with aggregate mode

Do not use this function directly, instead define a new function and give it a type (see unsafeSqlBinOp)