How to insert array using postgresql simple (Haskell)?

Question

I am working on a Haskell API using Scotty and PostgreSQL-simple. I cannot figure out how to do an insert of a column containing an Array of Text. So far my queries have worked, but something about this one does not seem to compile well.

This is what I have:

addOrder :: Postgres r m => OrderIntent -> m ()
addOrder param =
  void . withConn $ \conn -> execute conn qry (orderIntentTable param, orderIntentItemsSlug param)
  where
    qry = "insert into orders (table, items, created_at, updated_at) values (?, ?, now(), now())"

My OrderIntent and its FromRow are:

data OrderIntent = OrderIntent
{ orderIntentTable :: Integer
, orderIntentItemsSlug :: [Text] 
} deriving(Eq, Show)

instance FromRow OrderIntent where
  fromRow = OrderIntent 
    <$> field
    <*> (fromPGArray <$> field)

And the error I am getting is:

• Could not deduce (Database.PostgreSQL.Simple.ToField.ToField
                          [Data.Text.Internal.Text])
        arising from a use of ‘execute’
      from the context: Postgres r m
        bound by the type signature for:
                   addOrder :: forall r (m :: * -> *).
                               Postgres r m =>
                               OrderIntent -> m ()
        at Core/Order/DAO.hs:11:1-47

I could not figure out how to use the PostgreSQL library to parse an Array of Text so that i can be inserted to the database. If any of you could help me I would really appreciate it!

PS: I also have this other blocker from a couple of days ago, if you happen to know about Scotty auth.

score 3 · Accepted Answer · answered Feb 03 '21 at 14:58

3

Use PGArray.

execute conn qry (orderIntentTable param, PGArray (orderIntentItemsSlug param))

answered Feb 03 '21 at 14:58

Daniel Wagner

145,880
9
220
380

1

Worked like a charm. Thanks! – Bianca Ritorto Feb 04 '21 at 02:03

score 0 · Answer 2 · answered Feb 03 '21 at 14:46

0

As you're inserting orderIntentItemsSlugs, you need a ToField instance for orderIntentItemsSlug (which is a [Text]). This is what your error tells you.

I recommend looking at the sources of other ToField instances to figure out how you want to do that (one hacky way would be to turn your field into a Text first, which already gas a ToField instance).

You could also write an orders table that relates an orderID to one item at a time, then you wouldn't have to write any instances at all.

answered Feb 03 '21 at 14:46

414owen

791
3
13

Oh thanks! I'm not sure what you mean by turning my field into a Text first. Could you explain a bit more? In my case I just need to insert an array such as ["a", "b", "c"] into the column "items" – Bianca Ritorto Feb 03 '21 at 14:58
See https://stackoverflow.com/a/66030065/6522680 for a better solution. You have a `[Text]`, which can't be inserted. You can turn a `[Text]` into a `Text` with `T.pack . show`, for example. – 414owen Feb 03 '21 at 15:14

How to insert array using postgresql simple (Haskell)?

2 Answers2