h$eUL$      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~                                                                                            None #$ esqueletoA type representing the access of a table value. In Esqueleto, we get a guarantee that the access will look something like: escape-char [character] escape-char . escape-char [character] escape-char ^^^^^^^^^^^ ^^^^^^^^^^^ table name column name  esqueletoParse a SqlExpr (Value Bool)*'s textual representation into a list of  esqueletoThis function uses the connEscapeName function in the o with an empty identifier to pull out an escape character. This implementation works with postgresql, mysql, and sqlite backends.  Nonek  !"#$%&'()*.-,+0/123=<;:987654@?>ABCFEDGHILKJONMPQRSTUVXWY]\[Z`_^abcdefhgnmlkjiopqrstuvwzyx{}|~None '(-03<>? esqueletoAn exception thrown by  RenderExpr - it's not designed to handle composite keys, and will blow up if you give it one. esqueleto1(Internal) Class for mapping results coming from  into actual results.This looks very similar to RawSql, and it is! However, there are some crucial differences and ultimately they're different classes. esqueleto!Creates the variable part of the SELECT query and returns the list of s that will be given to *. esqueleto(Number of columns that will be consumed. esqueleto1Transform a row of the result into the data type. esqueletoCreate  INSERT INTO clause instead. esqueleto,(Internal) Mode of query being converted by . esqueleto(Internal) This class allows  to work with different numbers of arguments; specifically it allows providing arguments to a sql function via an n-tuple of SqlExpr (Value _) values, which are not all necessarily required to be the same type. There are instances for up to 10-tuples, but for sql functions which take more than 10 arguments, you can also nest tuples, as e.g. toArgList ((a,b),(c,d)) is the same as toArgList (a,b,c,d). esqueletoPhantom type used to mark a  INSERT INTO query. esqueletoData type to support from hack esqueleto!An expression on the SQL backend.Raw expression: Contains a  and a function for building the expr. It recieves a parameter telling it whether it is in a parenthesized context, and takes information about the SQL connection (mainly for escaping names) and returns both an string (>) and a list of values to be interpolated by the SQL backend. esqueleto1Information needed to escape and use identifiers. esqueletoList of identifiers already in use and supply of temporary identifiers. esqueleto Identifier used for table names. esqueletoA locking clause. esqueletoA LIMIT clause. esqueletoA ORDER BY clause. esqueletoA HAVING cause. esqueletoA GROUP BY clause. esqueleto A complete WHERE clause. esqueleto A part of a SET clause. esqueleto A part of a FROM clause. esqueletoThe DISTINCT "clause". esqueletoThe default, everything. esqueletoOnly DISTINCT, SQL standard. esqueleto DISTINCT ON, PostgreSQL extension. esqueletoSide data written by . esqueletoConstraint synonym for  persistent entities whose backend is o. esqueletoSQL backend for  esqueleto using A. esqueletoException data type for  esqueleto internal errors esqueleto%(Internal) Class that implements the JOIN  magic (see ). esqueleto+(Internal) Class that implements the tuple  magic (see ). esqueletoClass that enables one to use : to convert an entity's key on a query into another (cf. ). esqueletoe.g. type BaseEnt MyBase = MyChild esqueletoConvert from the key of the BaseEnt(ity) to the key of the child entity. This function is not actually called, but that it typechecks proves this operation is safe. esqueletoPhantom class of data types that are treated as strings by the RDBMS. It has no methods because it's only used to avoid type errors such as trying to concatenate integers."If you have a custom data type or newtype2, feel free to make it an instance of this class. esqueleto0Different kinds of locking clauses supported by .Note that each RDBMS has different locking support. The constructors of this datatype specify only the syntax$ of the locking mechanism, not its  semantics?. For example, even though both MySQL and PostgreSQL support :, there are no guarantees that they will behave the same. esqueleto FOR UPDATE5 syntax. Supported by MySQL, Oracle and PostgreSQL. esqueletoFOR UPDATE SKIP LOCKED5 syntax. Supported by MySQL, Oracle and PostgreSQL. esqueleto FOR SHARE" syntax. Supported by PostgreSQL. esqueletoLOCK IN SHARE MODE syntax. Supported by MySQL. esqueletoPhantom type used by . esqueletoPhantom type for a SET0 operation on an entity of the given type (see  and ). esqueletoPhantom type used by  and . esqueletoPhantom type used by ,  and . esqueletoException thrown whenever  is used to create an ON clause but no matching JOIN is found. esqueleto(Internal) Functions that operate on types (that should be) of kind . esqueleto (Internal)  smartJoin a b is a JOIN of the correct kind. esqueleto(Internal) Reify a JoinKind from a JOIN . This function is non-strict. esqueleto(Internal) A kind of JOIN. esqueleto  INNER JOIN esqueleto  CROSS JOIN esqueleto LEFT OUTER JOIN esqueleto RIGHT OUTER JOIN esqueleto FULL OUTER JOIN esqueletoData type that represents a FULL OUTER JOIN (see  for an example). esqueletoData type that represents a RIGHT OUTER JOIN (see  for an example). esqueletoData type that represents a LEFT OUTER JOIN. For example,  select $  $ \(person `` pet) -> ... is translated into /SELECT ... FROM Person LEFT OUTER JOIN Pet ...  See also: . esqueletoData type that represents a  CROSS JOIN (see  for an example). esqueletoData type that represents an  INNER JOIN (see  for an example). esqueletoA class for constructors or function which result type is known. esqueletoA class of things that can be converted into a list of SomeValue. It has instances for tuples and is the reason why  can take tuples, like  (foo  FooId, foo  FooName, foo  FooType). esqueletoA wrapper type for for any expr (Value a) for all a. esqueletoA list of single values. There's a limited set of functions able to work with this data type (such as , ,  and ). esqueleto=A single value (as opposed to a whole entity). You may use () or () to get a  from an . esqueleto Copied from  persistent esqueleto(Internal) Start a  query with an entity.  does two kinds of magic using ,  and : >The simple but tedious magic of allowing tuples to be used.$The more advanced magic of creating JOIN s. The JOIN is processed from right to left. The rightmost entity of the JOIN is created with  . Each JOIN( step is then translated into a call to . In the end, ! is called to materialize the JOIN. esqueleto(Internal) Same as , but entity may be missing. esqueleto(Internal) Do a JOIN. esqueleto(Internal) Finish a JOIN. esqueletoWHERE% clause: restrict the query's result. esqueletoAn ON clause, useful to describe how two tables are related. Cross joins and tuple-joins do not need an  clause, but ! and the various outer joins do.Database.Esqueleto.Experimental) in version 4.0.0.0 of the library. The  Experimental module has a dramatically improved means for introducing tables and entities that provides more power and less potential for runtime errors.If you don't include an  clause (or include too many!) then a runtime exception will be thrown.)As an example, consider this simple join:  $  $ \(foo `` bar) -> do  (foo  FooId  bar  BarFooId) ... We need to specify the clause for joining the two columns together. If we had this:  $  $ \(foo `` bar) -> do ... Then we can safely omit the  clause, because the cross join will make pairs of all records possible.You can do multiple  clauses in a query. This query joins three tables, and has two  clauses:  $  $ \(foo `` bar `` baz) -> do  (baz  BazId  bar  BarBazId)  (foo  FooId  bar  BarFooId) ... 8Old versions of esqueleto required that you provide the  clauses in reverse order. This restriction has been lifted - you can now provide  clauses in any order, and the SQL should work itself out. The above query is now totally equivalent to this:  $  $ \(foo `` bar `` baz) -> do  (foo  FooId  bar  BarFooId)  (baz  BazId  bar  BarBazId) ...  esqueletoGROUP BY6 clause. You can enclose multiple columns in a tuple.  select $  \(foo `` bar) -> do  (foo  FooBarId  bar  BarId)  (bar  BarId, bar  BarName) return (bar  BarId, bar  BarName, countRows) With groupBy you can sort by aggregate functions, like so (we used let to restrict the more general  to SqlSqlExpr (Value Int)) to avoid ambiguity---the second use of  has its type restricted by the :: Int below): r <- select $  \(foo `` bar) -> do  (foo  FooBarId  bar  BarId)  $ bar  BarName let countRows' =   [ countRows'] return (bar " BarName, countRows') forM_ r $ \( name, 2 count) -> do print name print (count :: Int) Need more columns?The  class is defined for  and tuples of s. We only have definitions for up to 8 elements in a tuple right now, so it's possible that you may need to have more than 8 elements.%For example, consider a query with a  call like this: )groupBy (e0, e1, e2, e3, e4, e5, e6, e7) This is the biggest you can get with a single tuple. However, you can easily nest the tuples to add more: 3groupBy ((e0, e1, e2, e3, e4, e5, e6, e7), e8, e9)  esqueletoORDER BY clause. See also  and .Multiple calls to 1 get concatenated on the final query, including . esqueleto/Ascending order of this field or SqlExpression. esqueleto0Descending order of this field or SqlExpression. esqueletoLIMIT%. Limit the number of returned rows. esqueletoOFFSET. Usually used with . esqueletoDISTINCT. Change the current SELECT into SELECT DISTINCT. For example: select $ distinct $  \foo -> do ... (Note that this also has the same effect:  select $ ) \foo -> do distinct (return ()) ...  esqueleto DISTINCT ON. Change the current SELECT into #SELECT DISTINCT ON (SqlExpressions). For example:  select $  \foo ->  [ (foo ^. FooName),  (foo ^. FooState)] $ do ... &You can also chain different calls to . The above is equivalent to:  select $  \foo ->  [ (foo ^. FooName)] $  [ (foo ^. FooState)] $ do ...  Each call to & adds more SqlExpressions. Calls to  override any calls to .4Note 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.Supported by PostgreSQL only. esqueletoErase an SqlExpression's type so that it's suitable to be used by . esqueleto'A convenience function that calls both  and . In other words, + [asc foo, desc bar, desc quux] $ do ... is the same as: ' [don foo, don bar, don quux] $ do ' [asc foo, desc bar, desc quux] ...   esqueletoORDER BY random() clause. esqueletoHAVING. esqueleto1Add a locking clause to the query. Please read % documentation and your RDBMS manual.If multiple calls to 3 are made on the same query, the last one is used. esqueletoExecute a subquery SELECT in an SqlExpression. Returns a simple value so should be used only when the SELECT- query is guaranteed to return just one row.Deprecated in 3.2.0. esqueletoExecute a subquery SELECT in a . The query passed to this function will only return a single result - it has a LIMIT 1 passed in to the query to make it safe, and the return type is 7 to indicate that the subquery might result in 0 rows.If you find yourself writing  . , then consider using .If you're performing a , then you can use  which is safe.If you know that the subquery will always return exactly one row (eg a foreign key constraint guarantees that you'll get exactly one row), then consider 1, along with a comment explaining why it is safe. esqueletoExecute a subquery SELECT in a /. This function is a shorthand for the common  .  idiom, where you are calling  on an expression that would be  already.8As an example, you would use this function when calling  or , which have ( in the result type (for a 0 row query). esqueleto Performs a COUNT of the given query in a  subSelect manner. This is always guaranteed to return a result value, and is completely safe. esqueletoExecute a subquery SELECT in a , that returns a list. This is an alias for  and is provided for symmetry with the other safe subselect functions. esqueletoPerforms a sub-select using the given foreign key on the entity. This is useful to extract values that are known to be present by the database schema.  (user, # user UserProfile (^. ProfileName)  esqueletoExecute a subquery SELECT in a . This function is unsafe, because it can throw runtime exceptions in two cases: =If the query passed has 0 result rows, then it will return a NULL value. The  persistent/ parsing operations will fail on an unexpected NULL.If the query passed returns more than one row, then the SQL engine will fail with an error like "More than one row returned by a subquery used as an expression".This function is safe if you guarantee that exactly one row will be returned, or if the result already has a  type for some reason.2For variants with the safety encoded already, see  and ,. For the most common safe use of this, see . esqueletoProject a field of an entity. esqueletoProject an SqlExpression that may be null, guarding against null cases. esqueleto.Project a field of an entity that may be null. esqueleto5Lift a constant value from Haskell-land to the query. esqueletoIS NULL comparison.For  IS NOT NULL, you can negate this with , as in ¬_ (isNothing (person ^. PersonAge))>Warning: Persistent and Esqueleto have different behavior for  != Nothing:HaskellSQL Persistent Nothing IS NOT NULL Esqueleto Nothing!= NULLIn SQL, = NULL and != NULL return NULL instead of true or false. For this reason, you very likely do not want to use  Nothing# in Esqueleto. You may find these hlint rules helpful to enforce this: - error: {lhs: v Database.Esqueleto.==. Database.Esqueleto.nothing, rhs: Database.Esqueleto.isNothing v, name: Use Esqueleto's isNothing} - error: {lhs: v Database.Esqueleto.==. Database.Esqueleto.val Nothing, rhs: Database.Esqueleto.isNothing v, name: Use Esqueleto's isNothing} - error: {lhs: v Database.Esqueleto.!=. Database.Esqueleto.nothing, rhs: not_ (Database.Esqueleto.isNothing v), name: Use Esqueleto's not isNothing} - error: {lhs: v Database.Esqueleto.!=. Database.Esqueleto.val Nothing, rhs: not_ (Database.Esqueleto.isNothing v), name: Use Esqueleto's not isNothing} esqueleto Analogous to , promotes a value of type typ into one of type  Maybe typ. It should hold that  . Just === just . . esqueletoNULL value. esqueleto Join nested s in a  into one. This is useful when calling aggregate functions on nullable fields. esqueletoCOUNT(*) value. esqueletoCOUNT. esqueletoCOUNT(DISTINCT x). esqueletoBETWEEN. @since: 3.1.0  esqueletoAllow a number of one type to be used as one of another type via an implicit cast. An explicit cast is not made, this function changes only the types on the Haskell side.Caveat: Trying to use castNum from Double to Int will not result in an integer, the original fractional number will still be used! Use ,  or  instead.Safety+: This operation is mostly safe due to the  constraint between the types and the fact that RDBMSs usually allow numbers of different types to be used interchangeably. However, there may still be issues with the query not being accepted by the RDBMS or  persistent not being able to parse it.  esqueletoSame as , but for nullable values. esqueletoCOALESCE function. Evaluates the arguments in order and returns the value of the first non-NULL SqlExpression, or NULL (Nothing) otherwise. Some RDBMSs (such as SQLite) require at least two arguments; please refer to the appropriate documentation. esqueletoLike coalesce, but takes a non-nullable SqlExpression placed at the end of the SqlExpression list, which guarantees a non-NULL result. esqueletoLOWER function. esqueletoUPPER function. @since 3.3.0 esqueletoTRIM function. @since 3.3.0 esqueletoRTRIM function. @since 3.3.0 esqueletoLTRIM function. @since 3.3.0 esqueletoLENGTH function. @since 3.3.0 esqueletoLEFT function. @since 3.3.0 esqueletoRIGHT function. @since 3.3.0 esqueletoLIKE operator. esqueletoILIKE operator (case-insensitive LIKE).Supported by PostgreSQL only. esqueleto The string . May be useful while using  and concatenation ( or , depending on your database). Note that you always have to type the parenthesis, for example: name ` ` (%) ++.  "John" ++. (%)  esqueletoThe CONCAT function with a variable number of parameters. Supported by MySQL and PostgreSQL. esqueletoThe ||7 string concatenation operator (named after Haskell's % in order to avoid naming clash with '). Supported by SQLite and PostgreSQL. esqueletoCast a string type into Text4. This function is very useful if you want to use newtype.s, or if you want to apply functions such as  to strings of different types.Safety: This is a slightly unsafe function, especially if you have defined your own instances of . Also, since  is an instance of , it's possible to turn a nullable value into a non-nullable one. Avoid using this function if possible. esqueletoExecute a subquery SELECT1 in an SqlExpression. Returns a list of values. esqueleto=Lift a list of constant value from Haskell-land to the query.  esqueletoSame as  but for . Most of the time you won't need it, though, because you can use  from inside  or  from inside . esqueletoIN1 operator. For example if you want to select all Persons by a list of IDs: ,SELECT * FROM Person WHERE Person.id IN (?) In  esqueleto', we may write the same query above as:  select $  $ \person -> do  $ person  PersonId ``  personIds return person Where  personIds is of type  [Key Person]. esqueletoNOT IN operator. esqueletoEXISTS operator. For example:  select $  $ \person -> do  $  $  $ \post -> do  (post  BlogPostAuthorId  person  PersonId) return person  esqueleto NOT EXISTS operator. esqueletoSET clause used on UPDATEs. Note that while it's not a type error to use this function on a SELECT4, it will most certainly result in a runtime error. esqueletoApply a  constructor to  SqlExpr Value arguments. esqueleto Apply extra  SqlExpr Value arguments to a  constructor esqueletoCASE statement. For example: select $ return $  [  ( $  $ \p -> do  (p  PersonName   "Mike"))  ( $ , $ \v -> do let sub =  $ \c -> do  (c  PersonName  " "Mike") return (c  PersonFavNum)  (v  PersonFavNum >.  sub) return $  (v  PersonName) +.  (1 :: Int)) ] ( $  (-1)) This query is a bit complicated, but basically it checks if a person named "Mike" exists, and if that person does, run the subquery to find out how many people have a ranking (by Fav Num) higher than "Mike".NOTE:9 There are a few things to be aware about this statement.This only implements the full CASE statement, it does not implement the "simple" CASE statement. At least one  and 4 is mandatory otherwise it will emit an error.The  is also mandatory, unlike the SQL statement in which if the ELSE is omitted it will return a NULL#. You can reproduce this via . esqueleto.Convert an entity's key into another entity's.8This function is to be used when you change an entity's Id, to be that of another entity. For example:  do  ( (foo  FooId)  bar  BarId) return (bar, foo) Note: this function may be unsafe to use in conditions not like the one of the example above. esqueletoSyntax sugar for . esqueletoSyntax sugar for . esqueletoSyntax sugar for . esqueletoConvert a constructor for a  key on a record to the  that defines it. You can supply just the constructor itself, or a value of the type - the library is capable of figuring it out from there. esqueletoRender updates to be use in a SET clause for a given sql backend. esqueletoFROM# clause: bring entities into scope.8Note that this function will be replaced by the one in Database.Esqueleto.Experimental) in version 4.0.0.0 of the library. The  Experimental module has a dramatically improved means for introducing tables and entities that provides more power and less potential for runtime errors.This function internally uses two type classes in order to provide some flexibility of how you may call it. Internally we refer to these type classes as the two different magics.&The innermost magic allows you to use from with the following types:expr (Entity val),, which brings a single entity into scope.expr (Maybe (Entity val))-, which brings a single entity that may be NULL into scope. Used for  OUTER JOINs.A JOIN of any other two types allowed by the innermost magic, where a JOIN may be an , a , a , a , or a . The JOINs have left fixity.&The outermost magic allows you to use from on any tuples of types supported by innermost magic (and also tuples of tuples, and so on), up to 8-tuples.Note that using from for the same entity twice does work and corresponds to a self-join. You don't even need to use two different calls to from, you may use a JOIN or a tuple.,The following are valid examples of uses of from (the types of the arguments of the lambda are inside square brackets):  $ \person -> ...  $ \(person, blogPost) -> ...  $ \(p ` ` mb) -> ...  $ \(p1 `` f ` ` p2) -> ...  $ \((p1 `` f) ` ` p2) -> ... The types of the arguments to the lambdas above are, respectively: person :: ( Esqueleto query expr backend , PersistEntity Person , PersistEntityBackend Person ~ backend ) => expr (Entity Person) (person, blogPost) :: (...) => (expr (Entity Person), expr (Entity BlogPost)) (p `` mb) :: (...) => InnerJoin (expr (Entity Person)) (expr (Maybe (Entity BlogPost))) (p1 `` f `` p2) :: (...) => InnerJoin (InnerJoin (expr (Entity Person)) (expr (Entity Follow))) (expr (Entity Person)) (p1 `` (f `` p2)) :: :: (...) => InnerJoin (expr (Entity Person)) (InnerJoin (expr (Entity Follow)) (expr (Entity Person))) 5Note that some backends may not support all kinds of JOINs. esqueletoCollect s on !s. Returns the first unmatched *s data on error. Returns a list without  OnClauses on success. esqueletoCreate a fresh . If possible, use the given . esqueletoUse an identifier. esqueletoEmpty  if you are constructing an ! probably use this for your meta esqueletoDoes this meta contain values for composite fields. This field is field out for composite key values esqueleto#(Internal) Create a case statement. Since: 2.1.1 esqueleto1(Internal) Create a custom binary operator. You should not use this function directly since its type is very general, you should always use it with an explicit type signature. For example: (==.) :: SqlExpr (Value a) -> SqlExpr (Value a) -> SqlExpr (Value Bool) (==.) = unsafeSqlBinOp " = " In the example above, we constraint the arguments to be of the same type and constraint the result to be a boolean value. esqueleto Similar to , but may also be applied to composite keys. Uses the operator given as the second argument whenever applied to composite keys.Usage example: (==.) :: SqlExpr (Value a) -> SqlExpr (Value a) -> SqlExpr (Value Bool) (==.) = unsafeSqlBinOpComposite " = " " AND "  return p+ alone is ambiguous, but in the context of  do ps <-  $  $ \p -> return p liftIO $ mapM_ (putStrLn . personName . entityVal) ps &we are able to infer from that single personName . entityVal function composition that the p inside the query is of type SqlExpr (Entity Person). esqueleto Execute an  esqueleto SELECT query inside  persistent's A/ monad and return the first entry wrapped in a Maybe. @since 3.5.1.0 Example usage firstPerson :: MonadIO m => SqlPersistT m (Maybe (Entity Person)) firstPerson =  $ do person <-  $ table @Person return person #The above query is equivalent to a  combined with  but you would still have to transform the results from a list: firstPerson :: MonadIO m => SqlPersistT m [Entity Person] firstPerson =  $ do person <-  $ table @Person  1 return person  esqueleto(Internal) Run a  of rows. esqueleto(Internal) Execute an  esqueleto statement inside  persistent's A monad. esqueleto Execute an  esqueleto DELETE query inside  persistent's A monad. Note that currently there are no type checks for statements that should not appear on a DELETE query.Example of usage:  $  $ \appointment ->  (appointment  AppointmentDate   now) Unlike !, there is a useful way of using  that will lead to type ambiguities. If you want to delete all rows (i.e., no . clause), you'll have to use a type signature:  $  $ \(appointment ::  ( Appointment)) -> return () Database.Esqueleto.Experimental:  delete $ do userFeature <- from $ table @UserFeature where_ ((userFeature ^. UserFeatureFeature)  valList allKnownFeatureFlags)  esqueletoSame as *, but returns the number of rows affected. esqueleto Execute an  esqueleto UPDATE query inside  persistent's A monad. Note that currently there are no type checks for statements that should not appear on a UPDATE query.Example of usage:  $ \p -> do  p [ PersonAge   ( thisYear) -. p  PersonBorn ]  $ isNothing (p  PersonAge)  esqueletoSame as *, but returns the number of rows affected. esqueleto(Internal) Pretty prints a  into a SQL query.Note: if you're curious about the SQL query being generated by  esqueleto, instead of manually using this function (which is possible but tedious), see the  function (along with , , etc). esqueleto Renders a  into a Text value along with the list of -s that would be supplied to the database for ? placeholders.You must ensure that the 8 you pass to this function corresponds with the actual . If you pass a query that uses incompatible features (like an INSERT statement with a SELECT' mode) then you'll get a weird result. esqueleto Renders a  into a Text value along with the list of -s that would be supplied to the database for ? placeholders. esqueleto Renders a  into a Text value along with the list of -s that would be supplied to the database for ? placeholders. esqueleto Renders a  into a Text value along with the list of -s that would be supplied to the database for ? placeholders. esqueleto Renders a  into a Text value along with the list of -s that would be supplied to the database for ? placeholders. esqueletoMaterialize a SqlExpr (Value a). esqueleto Insert a  for every selected value. esqueleto Insert a 5 for every selected value, return the count afterward esqueletoRenders an expression into Text. Only useful for creating a textual representation of the clauses passed to an On clause. esqueleto valkey i =  .  ( 2https://github.com/prowdsponsor/esqueleto/issues/9). esqueletovalJ is like val% but for something that is already a Value0. The use case it was written for was, given a Value lift the Key for that Value into the query expression in a type safe way. However, the implementation is more generic than that so we call it valJ.Its important to note that the input entity and the output entity are constrained to be the same by the type signature on the function ( 1https://github.com/prowdsponsor/esqueleto/pull/69). esqueleto Synonym for  that does not clash with  esqueleto's . esqueleto9Avoid N+1 queries and join entities into a map structure.:This function is useful to call on the result of a single JOIN,. For example, suppose you have this query: getFoosAndNestedBarsFromParent :: ParentId -> SqlPersistT IO [(Entity Foo, Maybe (Entity Bar))] getFoosAndNestedBarsFromParent parentId = 7 $ do (foo :& bar) <- from $ table Foo  table Bar  do \(foo :& bar) -> foo ^. FooId ==. bar ?. BarFooId where_ $ foo ^. FooParentId ==. val parentId pure (foo, bar) This is a natural result type for SQL - a list of tuples. However, it's not what we usually want in Haskell - each Foo in the list will be represented multiple times, once for each Bar. We can write  ! and it will translate it into a Map that is keyed on the  of the left , and the value is a tuple of the entity's value as well as the list of each coresponding entity. getFoosAndNestedBarsFromParentHaskellese :: ParentId -> SqlPersistT (Map (Key Foo) (Foo, [Maybe (Entity Bar)])) getFoosAndNestedBarsFromParentHaskellese parentId =  , $ getFoosdAndNestedBarsFromParent parentId  What if you have multiple joins? Let's use  with a *two* join query. userPostComments :: SqlQuery (SqlExpr (Entity User, Entity Post, Entity Comment)) userPostsComment = do (u :& p :& c) <- from $ table  User  table Post  do \(u :& p) -> u ^. UserId ==. p ^. PostUserId $ table @Comment  do \(_ :& p :& c) -> p ^. PostId ==. c ^. CommentPostId pure (u, p, c) This query returns a User, with all of the users Posts, and then all of the Comments on that post.First, we *nest* the tuple. >nest :: (a, b, c) -> (a, (b, c)) nest (a, b, c) = (a, (b, c)) This makes the return of the query conform to the input expected from . nestedUserPostComments :: SqlPersistT IO [(Entity User, (Entity Post, Entity Comment))] nestedUserPostComments = fmap nest $ select userPostsComments Now, we can call  on it. associateUsers :: [(Entity User, (Entity Post, Entity Comment))] -> Map UserId (User, [(Entity Post, Entity Comment)]) associateUsers = associateJoin Next, we'll use the  instances for Map and tuple to call  on the [(Entity Post, Entity Comment)]. associatePostsAndComments :: Map UserId (User, [(Entity Post, Entity Comment)]) -> Map UserId (User, Map PostId (Post, [Entity Comment])) associatePostsAndComments = fmap (fmap associateJoin) %For more reading on this topic, see  https://www.foxhound.systems/blog/grouping-query-results-haskell/this Foxhound Systems blog post. esqueleto esqueleto esqueleto esqueleto esqueleto esqueleto esqueleto esqueleto esqueleto esqueleto esqueleto esqueleto esqueleto esqueleto&Useful for 0-argument functions, like now in Postgresql. esqueletoYou may return tuples (up to 16-tuples) and tuples of tuples from a  query. esqueleto?You may return any single value (i.e. a single column) from a  query. esqueletoYou may return a possibly-NULL  from a  query. esqueletoYou may return an  from a  query. esqueletoNot useful for , but used for  and . esqueleto INSERT INTO hack. esqueleto esqueletoNone >None >/None '(/<>? esqueleto1A helper class primarily designed to allow using SqlQuery directly in a From expression. This is also useful for embedding a SqlSetOperation, as well as supporting backwards compatibility for the data constructor join tree used prior to 3.5.0.0 esqueletoData type defining the From language. This should not constructed directly in application code.A From is a SqlQuery which returns a reference to the result of calling from and a function that produces a portion of a FROM clause. This gets passed to the FromRaw FromClause constructor directly when converting from a From to a SqlQuery using from esqueletoFROM+ clause, used to bring entities into scope.#Internally, this function uses the  datatype. Unlike the old , this does not take a function as a parameter, but rather a value that represents a JOIN& tree constructed out of instances of . This implementation eliminates certain types of runtime errors by preventing the construction of invalid SQL (e.g. illegal nested-from). esqueleto-Bring a PersistEntity into scope from a table select $ from $ table @People  esqueletoSelect from a subquery, often used in conjuction with joins but can be used without any joins. Because SqlQuery has a ToFrom instance you probably dont need to use this function directly. select $ p <- from $ selectQuery do p <- from $ table @Person limit 5 orderBy [ asc p ^. PersonAge ] ...  None >?a  esqueleto Overloaded  unionAll_! function to support use in both  and  withRecursive esqueletoUNION ALL= SQL set operation. Can be used as an infix function between  values. esqueleto Overloaded union_! function to support use in both  and  withRecursive esqueletoUNION= SQL set operation. Can be used as an infix function between  values. esqueleto$Type class to support direct use of SqlQuery in a set operation tree esqueletoData type used to implement the SqlSetOperation language this type is implemented in the same way as a FromSemantically a SqlSetOperation is always a From but not vice versa esqueleto;Helper function for defining set operations @since 3.5.0.0 esqueletoEXCEPT= SQL set operation. Can be used as an infix function between  values. esqueleto INTERSECT= SQL set operation. Can be used as an infix function between  values. None />?]  esqueletoA left-precedence pair. Pronounced "and". Used to represent expressions that have been joined together./The precedence behavior can be demonstrated by: a :& b :& c == ((a :& b) :& c) See the examples at the beginning of this module to see how this operator is used in JOIN operations. esqueletoAn ON clause that describes how two tables are related. This should be used as an infix operator after a JOIN. For example, select $ from $ table @Person `innerJoin` table @BlogPost `on` (\(p :& bP) -> p ^. PersonId ==. bP ^. BlogPostAuthorId)  esqueleto INNER JOIN%Used as an infix operator `innerJoin` select $ from $ table @Person `innerJoin` table @BlogPost `on` (\(p :& bp) -> p ^. PersonId ==. bp ^. BlogPostAuthorId)  esqueletoINNER JOIN LATERALA Lateral subquery join allows the joined query to reference entities from the left hand side of the join. Discards rows that don't match the on clause,Used as an infix operator `innerJoinLateral` See example 6 esqueleto CROSS JOINUsed as an infix `crossJoin` =select $ do from $ table @Person `crossJoin` table @BlogPost  esqueletoCROSS JOIN LATERALA Lateral subquery join allows the joined query to reference entities from the left hand side of the join.,Used as an infix operator `crossJoinLateral` See example 6 esqueletoLEFT OUTER JOINJoin where the right side may not exist. If the on clause fails then the right side will be NULL'ed Because of this the right side needs to be handled as a Maybe$Used as an infix operator `leftJoin` select $ from $ table @Person `leftJoin` table @BlogPost `on` (\(p :& bp) -> p ^. PersonId ==. bp ?. BlogPostAuthorId)  esqueletoLEFT OUTER JOIN LATERALLateral join where the right side may not exist. In the case that the query returns nothing or the on clause fails the right side of the join will be NULL'ed Because of this the right side needs to be handled as a Maybe+Used as an infix operator `leftJoinLateral`$See example 6 for how to use LATERAL esqueletoRIGHT OUTER JOINJoin where the left side may not exist. If the on clause fails then the left side will be NULL'ed Because of this the left side needs to be handled as a Maybe%Used as an infix operator `rightJoin` select $ from $ table @Person `rightJoin` table @BlogPost `on` (\(p :& bp) -> p ?. PersonId ==. bp ^. BlogPostAuthorId)  esqueletoFULL OUTER JOINJoin where both sides of the join may not exist. Because of this the result needs to be handled as a Maybe)Used as an infix operator `fullOuterJoin` select $ from $ table @Person `fullOuterJoin` table @BlogPost `on` (\(p :& bp) -> p ?. PersonId ==. bp ?. BlogPostAuthorId)  esqueleto=Identical to the tuple instance and provided for convenience. esqueleto=Identical to the tuple instance and provided for convenience. esqueleto$You may return joined values from a  query - this is identical to the tuple instance, but is provided for convenience. 229 22222222 None >, esqueletoWITH clause used to introduce a  https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expressionCommon Table Expression (CTE). CTEs are supported in most modern SQL engines and can be useful in performance tuning. In Esqueleto, CTEs should be used as a subquery memoization tactic. When writing plain SQL, CTEs are sometimes used to organize the SQL code, in Esqueleto, this is better achieved through function that return  values. select $ do cte <- with subQuery cteResult <- from cte where_ $ cteResult ... pure cteResult WARNING: In some SQL engines using a CTE can diminish performance. In these engines the CTE is treated as an optimization fence. You should always verify that using a CTE will in fact improve your performance over a regular subquery.Since: 3.4.0.0 esqueletoWITH  RECURSIVE allows one to make a recursive subquery, which can reference itself. Like WITH, this is supported in most modern SQL engines. Useful for hierarchical, self-referential data, like a tree of data. select $ do cte <- withRecursive (do person <- from $ table @Person where_ $ person ^. PersonId ==. val personId pure person ) unionAll_ (\self -> do (p :& f :& p2 :& pSelf) <- from self `innerJoin` $ table @Follow `on` (\(p :& f) -> p ^. PersonId ==. f ^. FollowFollower) `innerJoin` $ table @Person `on` (\(p :& f :& p2) -> f ^. FollowFollowed ==. p2 ^. PersonId) `leftJoin` self `on` (\(_ :& _ :& p2 :& pSelf) -> just (p2 ^. PersonId) ==. pSelf ?. PersonId) where_ $ isNothing (pSelf ?. PersonId) groupBy (p2 ^. PersonId) pure p2 ) from cte Since: 3.4.0.0None_  !"#$%&'()*.+-,0/123=<456789;:@?>ABCFEDGHILKJONMPQRSTUVXWY]Z\[`_^abcdefhgnijkmlopqrstuvwyzyx{}|~1Ytsqvurpwacfedb32 &('*)% #"!$GHIVUXW]\[Z`_^}|zyx{~hgnmlkji0/.-,+ =<;:987654C @?>FED BAToPQLKJRONMSNone'(>?  !"#$%&'()*.+-,0/123=<456789;:@?>ABCFEDGHILKJONMPQRSTUVXWY]Z\[`_^abcdefhgnijkmlopqrstuvwyzyx{}|~1Ytsqvurpwacfedb32 &('*)% #"!$GHIVUXW]\[Z`_^}|zyx{~hgnmlkji0/.-,+ =<;:987654C @?>FED BAToPQLKJRONMSNone'(>?  !"#$%&'()*.+-,0/123=<456789;:@?>ABCFEDGHILKJONMPQRSTUVXWY]Z\[`_^abcdefhgnijkmlopqrstuvwyzyx{}|~1Ytsqvurpwacfedb32 &('*)% #"!$GHIVUXW]\[Z`_^}|zyx{~hgnmlkji0/.-,+ =<;:987654C @?>FED BAToPQLKJRONMS None  esqueleto(random()) Split out into database specific modules because MySQL uses `rand()`. Since: 2.6.0 None  '(>? esqueletoAggregate mode esqueletoALL esqueletoDISTINCT esqueleto(random()) Split out into database specific modules because MySQL uses `rand()`. esqueleto-Coalesce an array with an empty default value esqueleto(Internal) Create a custom aggregate functions with aggregate modeDo not use this function directly, instead define a new function and give it a type (see ) esqueleto( array_agg/) Concatenate distinct input values, including NULLs, into an array. esqueleto( array_remove?) Remove all elements equal to the given value from the array. esqueletoRemove NULL values from an array esqueleto( string_agg5) Concatenate input values separated by a delimiter. esqueleto( string_agg5) Concatenate input values separated by a delimiter.  esqueleto(chr) Translate the given integer to a character. (Note the result will depend on the character set of your database.) esqueleto7Inserts into a table the results of a query similar to  but allows to update values that violate a constraint during insertions.Example of usage: share [ mkPersist sqlSettings , mkDeleteCascade sqlSettings , mkMigrate "migrate" ] [persistLowerCase| Bar num Int deriving Eq Show Foo num Int UniqueFoo num deriving Eq Show |] insertSelectWithConflict UniqueFoo -- (UniqueFoo undefined) or (UniqueFoo anyNumber) would also work (from $ b -> 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. esqueletoSame as ) but returns the number of rows affected. esqueleto2Allow 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  tasks) -> do on $ users ^. UserId ==. tasks ^. TaskUserId groupBy $ users ^. UserId return ( users ^. UserId , count (tasks ^. TaskId)  (tasks ^. TaskCompleted ==. val True) , count (tasks ^. TaskId) - (tasks ^. TaskCompleted ==. val False) )  esqueleto?Allows to use `VALUES (..)` in-memory set of values in RHS of  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") ] )  table User  (((bound, _boundName) :& user) -> user^.UserAge >=. bound) groupBy bound pure (bound, count @Int $ user^.UserName)  esqueleto Aggregate mode (ALL or DISTINCT) esqueleto Input values. esqueleto Delimiter. esqueletoORDER BY clauses esqueletoConcatenation. esqueleto Input values. esqueleto Delimiter. esqueletoConcatenation. esqueletonew record to insert esqueleto/updates to perform if the record already exists esqueleto.the record in the database after the operation esqueleto uniqueness constraint to find by esqueletonew record to insert esqueleto/updates to perform if the record already exists esqueleto.the record in the database after the operation esqueletoUnique constructor or a unique, this is used just to get the name of the postgres constraint, the value(s) is(are) never used, so if you have a unique "MyUnique 0", "MyUnique undefined" would work as well. esqueleto Insert query. esqueletoA list of updates to be applied in case of the constraint being violated. The expression takes the current and excluded value to produce the updates. esqueletoAggregate function esqueleto Filter clauseNone 5678< o esqueleto!Used with certain JSON operators.This data type has  and  instances for ease of use by using integer and string literals.3 :: JSONAccessor JSONIndex 3-3 :: JSONAccessor JSONIndex -3"name" :: JSONAccessorJSONKey "name"NOTE: DO NOT USE ANY OF THE  METHODS ON THIS TYPE! esqueleto of a NULL-able  value. Hence the .1Note: NULL here is a PostgreSQL NULL, not a JSON  esqueleto;Newtype wrapper around any type with a JSON representation. esqueleto5Convenience function to lift a regular value into a  expression. esqueletojsonb esqueleto esqueleto+I repeat, DO NOT use any method other than ! esqueletoDatabase type(s), should appear different from Haskell name, e.g. "integer" or INT, not Int. esqueletoIncorrect value esqueleto Error message esqueletoReceived value esqueletoAdditional error esqueleto Error message  None J  esqueleto"Requires PostgreSQL version >= 9.3This function extracts the jsonb value from a JSON array or object, depending on whether you use an int or a text. (cf. )As long as the left operand is jsonb>, this function will not throw an exception, but will return NULL when an int4 is used on anything other than a JSON array, or a text/ is used on anything other than a JSON object.PostgreSQL Documentation  | Type | Description | Example | Example Result ----+------+--------------------------------------------+--------------------------------------------------+---------------- -> | int | Get JSON array element (indexed from zero) | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"} -> | text | Get JSON object field by key | '{"a": {"b":"foo"}}'::json->a | {"b":"foo"}  esqueleto"Requires PostgreSQL version >= 9.3 Identical to !, but the resulting DB type is a text2, so it could be chained with anything that uses text.$CAUTION: if the "scalar" JSON value null is the result 3of this function, PostgreSQL will interpret it as a  PostgreSQL NULL value, and will therefore be  instead of (Just "null")PostgreSQL Documentation  | Type | Description | Example | Example Result -----+------+--------------------------------+-----------------------------+---------------- ->> | int | Get JSON array element as text | '[1,2,3]'::json->>2 | 3 ->> | text | Get JSON object field as text | '{"a":1,"b":2}'::json->>b | 2  esqueleto"Requires PostgreSQL version >= 9.3This operator can be used to select a JSON value from deep inside another one. It only works on objects and arrays and will result in NULL ()) when encountering any other JSON type.The s used in the right operand list will always select an object field, but can also select an index from a JSON array if that text is parsable as an integer.Consider the following: x ^. TestBody #>. ["0","1"] !The following JSON values in the test table's body column will be affected:  Values in column | Resulting value --------------------------------------+---------------------------- {"0":{"1":"Got it!"}} | "Got it!" {"0":[null,["Got it!","Even here!"]]} | ["Got it!", "Even here!"] [{"1":"Got it again!"}] | "Got it again!" [[null,{"Wow":"so deep!"}]] | {"Wow": "so deep!"} false | NULL "nope" | NULL 3.14 | NULL PostgreSQL Documentation  | Type | Description | Example | Example Result -----+--------+-----------------------------------+--------------------------------------------+---------------- #> | text[] | Get JSON object at specified path | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"}  esqueleto"Requires PostgreSQL version >= 9.3This function is to  as  is to $CAUTION: if the "scalar" JSON value null is the result 3of this function, PostgreSQL will interpret it as a  PostgreSQL NULL value, and will therefore be  instead of (Just "null")PostgreSQL Documentation  | Type | Description | Example | Example Result -----+--------+-------------------------------------------+---------------------------------------------+---------------- #>> | text[] | Get JSON object at specified path as text | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' | 3  esqueleto"Requires PostgreSQL version >= 9.4This operator checks for the JSON value on the right to be a subset of the JSON value on the left.Examples of the usage of this operator can be found in the Database.Persist.Postgresql.JSON module.(here:  https://hackage.haskell.org/package/persistent-postgresql-2.10.0/docs/Database-Persist-Postgresql-JSON.html)PostgreSQL Documentation  | Type | Description | Example ----+-------+-------------------------------------------------------------+--------------------------------------------- @> | jsonb | Does the left JSON value contain within it the right value? | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb  esqueleto"Requires PostgreSQL version >= 9.4 This operator works the same as , just with the arguments flipped. So it checks for the JSON value on the left to be a subset of JSON value on the right.Examples of the usage of this operator can be found in the Database.Persist.Postgresql.JSON module.(here:  https://hackage.haskell.org/package/persistent-postgresql-2.10.0/docs/Database-Persist-Postgresql-JSON.html)PostgreSQL Documentation  | Type | Description | Example ----+-------+----------------------------------------------------------+--------------------------------------------- <@ | jsonb | Is the left JSON value contained within the right value? | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb  esqueleto"Requires PostgreSQL version >= 9.4This operator checks if the given text is a top-level member of the JSON value on the left. This means a top-level field in an object, a top-level string in an array or just a string value.Examples of the usage of this operator can be found in the Database.Persist.Postgresql.JSON module.(here:  https://hackage.haskell.org/package/persistent-postgresql-2.10.0/docs/Database-Persist-Postgresql-JSON.html)PostgreSQL Documentation  | Type | Description | Example ---+------+-----------------------------------------------------------------+------------------------------- ? | text | Does the string exist as a top-level key within the JSON value? | '{"a":1, "b":2}'::jsonb ? b  esqueleto"Requires PostgreSQL version >= 9.4This operator checks if ANY of the given texts is a top-level member of the JSON value on the left. This means any top-level field in an object, any top-level string in an array or just a string value.Examples of the usage of this operator can be found in the Database.Persist.Postgresql.JSON module.(here:  https://hackage.haskell.org/package/persistent-postgresql-2.10.0/docs/Database-Persist-Postgresql-JSON.html)PostgreSQL Documentation  | Type | Description | Example ----+--------+--------------------------------------------------------+--------------------------------------------------- ?| | text[] | Do any of these array strings exist as top-level keys? | '{"a":1, "b":2, "c":3}'::jsonb ?| array[b, c]  esqueleto"Requires PostgreSQL version >= 9.4This operator checks if ALL of the given texts are top-level members of the JSON value on the left. This means a top-level field in an object, a top-level string in an array or just a string value.Examples of the usage of this operator can be found in the Database.Persist.Postgresql.JSON module.(here:  https://hackage.haskell.org/package/persistent-postgresql-2.10.0/docs/Database-Persist-Postgresql-JSON.html)PostgreSQL Documentation  | Type | Description | Example ----+--------+--------------------------------------------------------+---------------------------------------- ?& | text[] | Do all of these array strings exist as top-level keys? | '["a", "b"]'::jsonb ?& array[a, b]  esqueleto"Requires PostgreSQL version >= 9.5This operator concatenates two JSON values. The behaviour is self-evident when used on two arrays, but the behaviour on different combinations of JSON values might behave unexpectedly.=CAUTION: THIS FUNCTION THROWS AN EXCEPTION WHEN CONCATENATING 'A JSON OBJECT WITH A JSON SCALAR VALUE!ArraysThis operator is a standard concatenation function when used on arrays: [1,2] || [2,3] == [1,2,2,3] [] || [1,2,3] == [1,2,3] [1,2,3] || [] == [1,2,3] ObjectsWhen concatenating JSON objects with other JSON objects, the fields from the JSON object on the right are added to the JSON object on the left. When concatenating a JSON object with a JSON array, the object will be inserted into the array; either on the left or right, depending on the position relative to the operator.When concatening an object with a scalar value, an exception is thrown. {"a": 3.14} || {"b": true} == {"a": 3.14, "b": true} {"a": "b"} || {"a": null} == {"a": null} {"a": {"b": true, "c": false}} || {"a": {"b": false}} == {"a": {"b": false}} {"a": 3.14} || [1,null] == [{"a": 3.14},1,null] [1,null] || {"a": 3.14} == [1,null,{"a": 3.14}] 1 || {"a": 3.14} == ERROR: invalid concatenation of jsonb objects {"a": 3.14} || false == ERROR: invalid concatenation of jsonb objects  Scalar valuesScalar values can be thought of as being singleton arrays when used with this operator. This rule does not apply when concatenating with JSON objects. 1 || null == [1,null] true || "a" == [true,"a"] [1,2] || false == [1,2,false] null || [1,"a"] == [null,1,"a"] {"a":3.14} || true == ERROR: invalid concatenation of jsonb objects 3.14 || {"a":3.14} == ERROR: invalid concatenation of jsonb objects {"a":3.14} || [true] == [{"a":3.14},true] [false] || {"a":3.14} == [false,{"a":3.14}] PostgreSQL Documentation  | Type | Description | Example ----+-------+-----------------------------------------------------+-------------------------------------------- || | jsonb | Concatenate two jsonb values into a new jsonb value | '["a", "b"]'::jsonb || '["c", "d"]'::jsonb  Note: The ||7 operator concatenates the elements at the top level of 6each of its operands. It does not operate recursively.For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right  hand operand. esqueleto"Requires PostgreSQL version >= 9.5This operator can remove a key from an object or a string element from an array when using text, and remove certain elements by index from an array when using integers.Negative integers delete counting from the end of the array. (e.g. -1 being the last element, -2 being the second to last, etc.)CAUTION: THIS FUNCTION THROWS AN EXCEPTION WHEN USED ON ANYTHING OTHER THAN OBJECTS OR ARRAYS WHEN USING TEXT, AND ANYTHING OTHER THAN ARRAYS WHEN USING INTEGERS!Objects and arrays {"a": 3.14} - "a" == {} {"a": "b"} - "b" == {"a": "b"} {"a": 3.14} - "a" == {} {"a": 3.14, "c": true} - "a" == {"c": true} ["a", 2, "c"] - "a" == [2, "c"] -- can remove strings from arrays [true, "b", 5] - 0 == ["b", 5] [true, "b", 5] - 3 == [true, "b", 5] [true, "b", 5] - -1 == [true, "b"] [true, "b", 5] - -4 == [true, "b", 5] [] - 1 == [] {"1": true} - 1 == ERROR: cannot delete from object using integer index 1 - == ERROR: cannot delete from scalar "a" - == ERROR: cannot delete from scalar true - == ERROR: cannot delete from scalar null - == ERROR: cannot delete from scalar PostgreSQL Documentation  | Type | Description | Example ---+---------+------------------------------------------------------------------------+------------------------------------------------- - | text | Delete key/value pair or string element from left operand. | '{"a": "b"}'::jsonb - a | | Key/value pairs are matched based on their key value. | - | integer | Delete the array element with specified index (Negative integers count | '["a", "b"]'::jsonb - 1 | | from the end). Throws an error if top level container is not an array. |  esqueleto!Requires PostgreSQL version >= 10Removes a set of keys from an object, or string elements from an array.(This is the same operator internally as , but the option to use a  text array , instead of text or integer was only added in version 10. That's why this function is seperate from "NOTE: The following is equivalent: ${some JSON expression} -. "a" -. "b"is equivalent to ${some JSON expression} --. ["a","b"]PostgreSQL Documentation  | Type | Description | Example ---+---------+------------------------------------------------------------------------+------------------------------------------------- - | text[] | Delete multiple key/value pairs or string elements from left operand. | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] | | Key/value pairs are matched based on their key value. |  esqueleto"Requires PostgreSQL version >= 9.56This operator can remove elements nested in an object.If a  is not parsable as a number when selecting in an array (even when halfway through the selection) an exception will be thrown.Negative integers delete counting from the end of an array. (e.g. -1 being the last element, -2 being the second to last, etc.)4CAUTION: THIS FUNCTION THROWS AN EXCEPTION WHEN USED 2ON ANYTHING OTHER THAN OBJECTS OR ARRAYS, AND WILL 6ALSO THROW WHEN TRYING TO SELECT AN ARRAY ELEMENT WITH A NON-INTEGER TEXTObjects {"a": 3.14, "b": null} #- [] == {"a": 3.14, "b": null} {"a": 3.14, "b": null} #- ["a"] == {"b": null} {"a": 3.14, "b": null} #- ["a","b"] == {"a": 3.14, "b": null} {"a": {"b":false}, "b": null} #- ["a","b"] == {"a": {}, "b": null} Arrays [true, {"b":null}, 5] #- [] == [true, {"b":null}, 5] [true, {"b":null}, 5] #- ["0"] == [{"b":null}, 5] [true, {"b":null}, 5] #- ["b"] == ERROR: path element at position 1 is not an integer: "b" [true, {"b":null}, 5] #- ["1","b"] == [true, {}, 5] [true, {"b":null}, 5] #- ["-2","b"] == [true, {}, 5] {"a": {"b":[false,4,null]}} #- ["a","b","2"] == {"a": {"b":[false,4]}} {"a": {"b":[false,4,null]}} #- ["a","b","c"] == ERROR: path element at position 3 is not an integer: "c"  Other values 1 #- {anything} == ERROR: cannot delete from scalar "a" #- {anything} == ERROR: cannot delete from scalar true #- {anything} == ERROR: cannot delete from scalar null #- {anything} == ERROR: cannot delete from scalar PostgreSQL Documentation  | Type | Description | Example ----+--------+---------------------------------------------------------+------------------------------------ #- | text[] | Delete the field or element with specified path | '["a", {"b":1}]'::jsonb #- '{1,b}' | | (for JSON arrays, negative integers count from the end) |  6666666666666None K esqueleto(random()) Split out into database specific modules because MySQL uses `rand()`. Since: 2.6.0 Safe-InferredL !"#$%&'()*+*,*-*.*/*0*1*2*3*4*5*6*7898:8;8<8=8>8?8@8ABCBDBEBFBGBHIJIKILIMINIOPQRSRTUVUWUXUYUZU[U\U]U^U^U_U`UaUbUcUdUeUfUfghgigjgkglglgmgngngogpgqgrgstutvwxwyz{z|z}z~zzzz                                                                                    P(esqueleto-3.5.3.2-2kF9aeAa4DQHOfIgHc4P2SDatabase.Esqueleto.Experimental&Database.Esqueleto.Internal.ExprParser$Database.Esqueleto.Internal.Internal'Database.Esqueleto.Experimental.ToMaybe0Database.Esqueleto.Experimental.ToAliasReference'Database.Esqueleto.Experimental.ToAlias$Database.Esqueleto.Experimental.From4Database.Esqueleto.Experimental.From.SqlSetOperation)Database.Esqueleto.Experimental.From.Join:Database.Esqueleto.Experimental.From.CommonTableExpressionDatabase.Esqueleto.MySQLDatabase.Esqueleto.PostgreSQL"Database.Esqueleto.PostgreSQL.JSONDatabase.Esqueleto.SQLite,Database.Esqueleto.Internal.PersistentImportDatabase.Persist.StoredeleteDatabase.EsqueletofromDatabase.Esqueleto.Legacy,Database.Esqueleto.PostgreSQL.JSON.InstancesPaths_esqueleto*persistent-2.13.3.3-4R3Zw9pDoPP17hGxcXD5P2Database.Persist.SqltransactionUndotransactionSaveDatabase.Persist.Sql.MigrationmigraterunMigrationUnsaferunMigrationSilent runMigration getMigration showMigrationprintMigrationparseMigration'parseMigrationSqlCautiousMigration Migration(Database.Persist.Sql.Orphan.PersistQuerydecorateSQLWithLimitOffset(Database.Persist.Sql.Orphan.PersistStore fieldDBName getFieldName tableDBName getTableName fromSqlKeytoSqlKey withRawQueryunSqlBackendKey SqlBackendKeyunSqlReadBackendKeySqlReadBackendKeyunSqlWriteBackendKeySqlWriteBackendKeyDatabase.Persist.Sql.Runclose' withSqlConn createSqlPool withSqlPoolliftSqlPersistMPoolrunSqlPersistMPoolrunSqlPersistM runSqlConn runSqlPoolDatabase.Persist.Sql.RawrawSql getStmtConnrawExecuteCount rawExecute rawQueryResrawQueryDatabase.Persist.Sql.ClassrawSqlProcessRowrawSqlColCountReason rawSqlColsRawSqlsqlTypePersistFieldSqlDatabase.Persist toJsonTextDatabase.Persist.Sql.Internal mkColumnsdefaultAttributeDatabase.Persist.Sql.Types cReferencecMaxLencDefaultConstraintName cGeneratedcDefaultcSqlTypecNullcNameColumnCouldn'tGetSQLConnectionStatementAlreadyFinalizedPersistentSqlException SqlPersistT SqlPersistMConnectionPoolunSingleSingle#Database.Persist.Sql.Types.Internal readToUnknown readToWritewriteToUnknown$$sel:unSqlReadBackend:SqlReadBackendSqlReadBackend&$sel:unSqlWriteBackend:SqlWriteBackendSqlWriteBackendSqlBackendCanReadSqlBackendCanWriteSqlReadT SqlWriteT IsSqlBackendDatabase.Persist.Class PersistUnique PersistStore$Database.Persist.Class.DeleteCascade deleteCascade DeleteCascade#Database.Persist.Class.PersistQuery selectKeys selectKeysRes selectFirstselectSourceResPersistQueryRead deleteWhere updateWherePersistQueryWrite$Database.Persist.Class.PersistUnique checkUnique replaceUnique getByValue onlyUniqueinsertUniqueEntityinsertBygetByPersistUniqueReadputManyupsertByupsert insertUniquedeleteByPersistUniqueWrite$Database.Persist.SqlBackend.Internal SqlBackend#Database.Persist.Class.PersistStore insertRecord getEntity insertEntity belongsToJust belongsTo getJustEntitygetJust liftPersistpersistBackend BaseBackendHasPersistBackendIsPersistBackendprojectBackendBackendCompatiblePersistRecordBackendfromBackendKey toBackendKey ToBackendKey BackendKey PersistCoregetManygetPersistStoreRead updateGetreplace repsertManyrepsert insertKeyinsertEntityMany insertMany_ insertManyinsert_insertPersistStoreWrite$Database.Persist.Class.PersistEntityfromPersistValueJSONtoPersistValueJSONentityIdFromJSONentityIdToJSONkeyValueEntityFromJSONkeyValueEntityToJSON entityValueskeyFromRecordM fieldLenspersistUniqueToValuespersistUniqueToFieldNamespersistUniqueKeysfromPersistValuestoPersistFieldspersistFieldDef entityDefpersistIdField keyFromValues keyToValuesUnique EntityFieldKeyPersistEntityBackend PersistEntityBackendSpecificUpdate entityVal entityKeyEntity#Database.Persist.Class.PersistFieldfromPersistValuetoPersistValue PersistFieldSomePersistFieldDatabase.Persist.EntityDefgetEntityKeyFields getEntityIdgetEntityFieldsgetEntityDBNamegetEntityUniques1Database.Persist.SqlBackend.Internal.MkSqlBackendLogFunc4Database.Persist.SqlBackend.Internal.InsertSqlResult ISRManyKeys ISRInsertGet ISRSingleInsertSqlResult.Database.Persist.SqlBackend.Internal.Statement stmtQuery stmtExecute stmtReset stmtFinalize StatementDatabase.Persist.Types.BasekeyAndEntityFields entityPrimaryInactiveActive Checkmark NotNullableNullable IsNullableByNullableAttr ByMaybeAttr WhyNullable EntityDefEntityIdNaturalKey EntityIdField EntityIdDef ExtraLineAttrFTListFTAppFTTypePromoted FTTypeCon FieldType SelfReference CompositeRefEmbedRef ForeignRef NoReference ReferenceDefembeddedFieldsembeddedHaskellEmbedEntityDef emFieldEmbed emFieldDB EmbedFieldDef uniqueAttrs uniqueFields uniqueDBName uniqueHaskell UniqueDefcompositeAttrscompositeFields CompositeDefForeignFieldDefforeignToPrimaryforeignNullable foreignAttrs foreignFieldsforeignFieldCascadeforeignConstraintNameDBNameforeignConstraintNameHaskellforeignRefTableDBNameforeignRefTableHaskell ForeignDefPersistMongoDBUnsupportedPersistMongoDBErrorPersistForeignConstraintUnmetPersistInvalidFieldPersistMarshalError PersistErrorPersistExceptionSqlOtherSqlBlob SqlDayTimeSqlTimeSqlDaySqlBool SqlNumericSqlRealSqlInt64SqlInt32 SqlStringSqlTypeNotInInLeGeLtGtNeEq PersistFilter UpsertError KeyNotFoundUpdateExceptionDivideMultiplySubtractAddAssign PersistUpdatefieldIsImplicitIdColumnfieldGenerated fieldComments fieldCascadefieldReference fieldStrict fieldAttrs fieldSqlType fieldTypefieldDB fieldHaskellFieldDefDatabase.Persist.PersistValuePersistDbSpecificPersistLiteralEscapedPersistLiteralPersistLiteral_ PersistArrayPersistObjectId PersistMap PersistList PersistNullPersistUTCTimePersistTimeOfDay PersistDay PersistBoolPersistRational PersistDouble PersistInt64PersistByteString PersistText PersistValue$Database.Persist.Class.PersistConfigrunPoolcreatePoolConfigapplyEnv loadConfigPersistConfigPoolPersistConfigBackend PersistConfig ExprParser TableAccesstableAccessTabletableAccessColumn parseOnExpr mkEscapeCharonExpr skipToEscapeparseEscapedIdentifierparseTableAccessparseEscapedChars$fEqTableAccess$fOrdTableAccess$fShowTableAccessRenderExprException!RenderExprUnexpectedECompositeKey SqlSelect sqlSelectColssqlSelectColCountsqlSelectProcessRow sqlInsertIntoModeSELECTDELETEUPDATE INSERT_INTOUnsafeSqlFunctionArgument toArgList OrderByTypeASCDESC NeedParensParensNever InsertFinalPreprocessedFromSqlExprERaw SqlExprMetasqlExprMetaCompositeFieldssqlExprMetaAliassqlExprMetaIsReference IdentInfo IdentStateinUseIdentI LockingClause LimitClauseLimit OrderByClause HavingClause GroupByClauseGroupBy WhereClauseWhereNoWhere SetClause SubQueryTypeNormalSubQueryLateralSubQueryCommonTableExpressionClauseCommonTableExpressionKindRecursiveCommonTableExpressionNormalCommonTableExpression FromClause FromStartFromJoinOnClauseFromRawDistinctClause DistinctAllDistinctStandard DistinctOnSideDatasdDistinctClause sdFromClause sdSetClause sdWhereClausesdGroupByClausesdHavingClausesdOrderByClause sdLimitClausesdLockingClause sdCteClause SqlEntitySqlQueryQunQSqlBinOpCompositeErrorMismatchingLengthsErrorNullPlaceholdersErrorDeconstructionErrorUnexpectedCaseErrorEmptySqlExprValueList MakeFromErrorUnsupportedSqlInsertIntoTypeInsertionFinalErrorNewIdentForErrorUnsafeSqlCaseErrorOperationNotSupportedNotImplementedCompositeKeyErrorUnexpectedValueErrorNotErrorToInsertionErrorCombineInsertionError FoldHelpError SqlCaseErrorSqlCastAsErrorSqlFunctionErrorMakeOnClauseError MakeExcError MakeSetErrorMakeWhereErrorMakeHavingErrorFilterWhereAggErrorFilterWhereClauseErrorEsqueletoErrorCompositeKeyErrAliasedValueErrUnexpectedCaseErrSqlBinOpCompositeErrFromPreprocessfromPreprocessFromfrom_ToBaseIdBaseEnttoBaseIdWitness LockingKind ForUpdateForUpdateSkipLockedForShareLockInShareMode InsertionUpdateOrderBy$OnClauseWithoutMatchingJoinException IsJoinKind smartJoin reifyJoinKindJoinKind InnerJoinKind CrossJoinKindLeftOuterJoinKindRightOuterJoinKindFullOuterJoinKind FullOuterJoinRightOuterJoin LeftOuterJoin CrossJoin InnerJoin FinalResultfinalR KnowResult ToSomeValues toSomeValues SomeValue ValueListValueunValueDBNameunDBName fromStartfromStartMaybefromJoin fromFinishwhere_ongroupByorderByascdesc orderByExprlimitoffsetdistinct distinctOndondistinctOnOrderByrandhavinglocking sub_select subSelectsubSelectMaybesubSelectCount subSelectListsubSelectForeignsubSelectUnsafe^. withNonNull?.val isNothingjustnothingjoinV countHelper countRowscount countDistinctnot_==.>=.>.<=.<.!=.&&.||.+.-./.*.betweenrandom_round_ceiling_floor_sum_min_max_avg_castNumcastNumMcoalescecoalesceDefaultlower_upper_trim_rtrim_ltrim_length_left_right_likeilike%concat_++. castStringsubList_selectvalListjustListin_notInexists notExistsset=.+=.-=.*=./=.<#<&>case_toBaseIdwhen_then_else_ toUniqueDef renderUpdates collectIdentscollectOnClausesinitialIdentState newIdentForuseIdentnoMetahasCompositeKeyMeta entityAsValueentityAsValueMaybeparensM fieldNamesetAuxsub fromDBName existsHelper unsafeSqlCaseunsafeSqlBinOpunsafeSqlBinOpCompositeunsafeSqlValueunsafeSqlEntityvalueToFunctionArgunsafeSqlFunctionunsafeSqlExtractSubFieldunsafeSqlFunctionParensunsafeSqlCastAsveryUnsafeCoerceSqlExprValue veryUnsafeCoerceSqlExprValueListrawSelectSource selectSourceselect selectOne runSource rawEsqueleto deleteCountupdate updateCount builderToTexttoRawSqlrenderQueryToTextrenderQuerySelectrenderQueryDeleterenderQueryUpdaterenderQueryInsertIntouncommas intersperseB uncommas'makeCtemakeInsertInto makeSelectmakeFrommakeSet makeWhere makeGroupBy makeHavingmakeOrderByNoNewline makeOrderBy makeLimit makeLockingparensaliasedEntityColumnIdentaliasedColumnNameunescapedColumnNames getEntityValmaterializeExprfrom3Pfrom3to3from4Pfrom4to4from5Pfrom5to5from6Pfrom6to6from7Pfrom7to7from8Pfrom8to8from9Pfrom9to9from10Pfrom10to10from11Pto11from12Pto12from13Pto13from14Pto14from15Pto15from16Pto16 insertSelectinsertSelectCount renderExprvalkeyvalJ deleteKey associateJoin $fMonadValue$fApplicativeValue$fFunctorValue$fFinalResult->$fFinalResultUnique$fIsJoinKindFullOuterJoin$fIsJoinKindRightOuterJoin$fIsJoinKindLeftOuterJoin$fIsJoinKindCrossJoin$fIsJoinKindInnerJoin/$fExceptionOnClauseWithoutMatchingJoinException$fSqlStringMaybe$fSqlStringMarkupM$fSqlStringByteString$fSqlStringText$fSqlStringText0 $fSqlString[]$fExceptionEsqueletoError$fMonoidLimitClause$fSemigroupLimitClause$fMonoidWhereClause$fSemigroupWhereClause$fMonoidDistinctClause$fSemigroupDistinctClause$fMonoidGroupByClause$fSemigroupGroupByClause$fToSomeValuesSqlExpr$fToSomeValues(,,,,,,,)$fToSomeValues(,,,,,,)$fToSomeValues(,,,,,)$fToSomeValues(,,,,)$fToSomeValues(,,,)$fToSomeValues(,,)$fToSomeValues(,)$fShowFromClause$fMonoidSideData$fSemigroupSideData$fFromPreprocessjoin$fFromPreprocessSqlExpr$fFromPreprocessSqlExpr0$fFrom(,,,,,,,)$fFrom(,,,,,,) $fFrom(,,,,,) $fFrom(,,,,) $fFrom(,,,) $fFrom(,,) $fFrom(,)$fFromFullOuterJoin$fFromRightOuterJoin$fFromLeftOuterJoin$fFromCrossJoin$fFromInnerJoin $fFromSqlExpr$fFromSqlExpr0&$fUnsafeSqlFunctionArgument(,,,,,,,,,)%$fUnsafeSqlFunctionArgument(,,,,,,,,)$$fUnsafeSqlFunctionArgument(,,,,,,,)#$fUnsafeSqlFunctionArgument(,,,,,,)"$fUnsafeSqlFunctionArgument(,,,,,)!$fUnsafeSqlFunctionArgument(,,,,) $fUnsafeSqlFunctionArgument(,,,)$fUnsafeSqlFunctionArgument(,,)$fUnsafeSqlFunctionArgument(,)$fUnsafeSqlFunctionArgument[]"$fUnsafeSqlFunctionArgumentSqlExpr$fUnsafeSqlFunctionArgument()-$fSqlSelect(,,,,,,,,,,,,,,,)(,,,,,,,,,,,,,,,)+$fSqlSelect(,,,,,,,,,,,,,,)(,,,,,,,,,,,,,,))$fSqlSelect(,,,,,,,,,,,,,)(,,,,,,,,,,,,,)'$fSqlSelect(,,,,,,,,,,,,)(,,,,,,,,,,,,)%$fSqlSelect(,,,,,,,,,,,)(,,,,,,,,,,,)#$fSqlSelect(,,,,,,,,,,)(,,,,,,,,,,)!$fSqlSelect(,,,,,,,,,)(,,,,,,,,,)$fSqlSelect(,,,,,,,,)(,,,,,,,,)$fSqlSelect(,,,,,,,)(,,,,,,,)$fSqlSelect(,,,,,,)(,,,,,,)$fSqlSelect(,,,,,)(,,,,,)$fSqlSelect(,,,,)(,,,,)$fSqlSelect(,,,)(,,,)$fSqlSelect(,,)(,,)$fSqlSelect(,)(,)$fSqlSelectSqlExprValue$fSqlSelectSqlExprMaybe$fSqlSelectSqlExprEntity$fSqlSelect()()$fSqlSelectSqlExprInsertion$fExceptionRenderExprException$fShowRenderExprException$fFunctorSqlQuery$fApplicativeSqlQuery$fMonadSqlQuery$fEqNeedParens $fEqIdent $fOrdIdent $fShowIdent$fEqLimitClause$fShowSubQueryType$fEqCommonTableExpressionKind$fShowEsqueletoError$fShowSqlBinOpCompositeError$fShowUnexpectedCaseError$fShowUnexpectedValueError($fEqOnClauseWithoutMatchingJoinException)$fOrdOnClauseWithoutMatchingJoinException*$fShowOnClauseWithoutMatchingJoinException $fEqJoinKind$fShowJoinKind $fEqValueList$fOrdValueList$fShowValueList $fEqValue $fOrdValue $fShowValueToMaybeToMaybeTtoMaybe$fToMaybe(,,,,,,,)$fToMaybe(,,,,,,)$fToMaybe(,,,,,)$fToMaybe(,,,,)$fToMaybe(,,,) $fToMaybe(,,) $fToMaybe(,)$fToMaybeSqlExpr$fToMaybeSqlExpr0$fToMaybeSqlExpr1ToAliasReferencetoAliasReferenceToAliasReferenceT$fToAliasReference(,,,,,,,)$fToAliasReference(,,,,,,)$fToAliasReference(,,,,,)$fToAliasReference(,,,,)$fToAliasReference(,,,)$fToAliasReference(,,)$fToAliasReference(,)$fToAliasReferenceSqlExpr$fToAliasReferenceSqlExpr0$fToAliasReferenceSqlExpr1ToAliastoAliasToAliasT$fToAlias(,,,,,,,)$fToAlias(,,,,,,)$fToAlias(,,,,,)$fToAlias(,,,,)$fToAlias(,,,) $fToAlias(,,) $fToAlias(,)$fToAliasSqlExpr$fToAliasSqlExpr0$fToAliasSqlExpr1SubQueryTableToFromtoFromunFromRawFntable selectQuery$fToFromSqlQuerya $fToFromFroma$fToFromTableSqlExpr$fToFromSubQuerya IntersectExceptUnionAll UnionAll_ unionAll_Union_union_UnionToSqlSetOperationtoSqlSetOperationSqlSetOperationunSqlSetOperation SelectQuerymkSetOperationexcept_ intersect_$fToFromSqlSetOperationa$fToSqlSetOperationSqlQuerya#$fToSqlSetOperationSqlSetOperationa $fUnion_->$fToSqlSetOperationUniona' $fUnionAll_->$fToSqlSetOperationUnionAlla'$fToSqlSetOperationExcepta'$fToSqlSetOperationIntersecta' DoCrossJoin doCrossJoin DoLeftJoin doLeftJoin DoInnerJoin doInnerJoin IsLateral NotLateralLateral HasOnClauseErrorOnLateral ValidOnClause:& innerJoininnerJoinLateral crossJoincrossJoinLateralleftJoinleftJoinLateral rightJoin fullOuterJoin$fToAliasReference:& $fToAlias:&$fSqlSelect:&:& $fToMaybe:&$fValidOnClause->$fValidOnClausea$fToFromFullOuterJoin:&$fToFromRightOuterJoin:&$fToFromInnerJoinr$fDoInnerJoinLaterala(,)d$fDoInnerJoinNotLateralarhs:&$fToFromLeftOuterJoinr$fDoLeftJoinLaterala(,)d$fDoLeftJoinNotLateralarhs:&$fToFromCrossJoinr$fDoCrossJoinLaterala->:&$fDoCrossJoinNotLateralab:& UnionKind unUnionKindwith withRecursive$fUnionAll_UnionKind$fUnion_UnionKindAggMode AggModeAllAggModeDistinct maybeArrayunsafeSqlAggregateFunction arrayAggWitharrayAggarrayAggDistinct arrayRemovearrayRemoveNull stringAggWith stringAggchrnow_insertSelectWithConflictinsertSelectWithConflictCount filterWherevalues $fShowAggMode JSONAccessor JSONIndexJSONKey JSONBExprJSONBunJSONBjsonbVal->.->>.#>.#>>.@>.<@.?|.?&.--.#-. text-1.2.3.2Data.Text.Internal.BuilderBuilderbase GHC.MaybeMaybeGHC.BasepureJustGHC.NumNum++&conduit-1.3.4.2-7KYAtb3DPJrD4Opsq8fn7PData.Conduit.Internal.ConduitSourcefmapFunctor Data.StringIsString Data.Foldablenull$fPersistFieldSqlJSONB$fPersistFieldJSONB$fNumJSONAccessor fromIntegerfromPersistValueErrorfromPersistValueParseErrornumErrbadParseNothingData.Text.InternalTextversion getBinDir getLibDir getDynLibDir getDataDir getLibexecDir getSysconfDirgetDataFileName