How to insert bytea value using postgresql-simple in Haskell

user_1722399 0
Viewed 324

I have a table defined as

CREATE TABLE users (id SERIAL PRIMARY KEY, val BYTEA);

Then I want to serialize my data structure with binary and store in the table, and then retrieve and deserialize back.

{-# LANGUAGE OverloadedStrings, DeriveAnyClass #-}
import Control.Monad (forM_)
import Data.Binary (encode, decode, Binary)
import Database.PostgreSQL.Simple
import GHC.Generics (Generic)

data User = { name :: Text, email :: Text } deriving (Show, Generic, Binary)
main = do
  conn <- connect --...
  let encoded = encode User {name = "me", email = "me@home.net" }
  execute conn "INSERT INTO users(val) values(?)" $ Only encoded
  rs <- query_ conn "SELECT id, val FROM users"
  forM_ rs $ \(id,val) ->
    putStrLn $ (show (id :: Int)) ++ ": " ++ show (decode val :: User)

But I get error Data.Binary.Get.runGet at position 0: not enough bytes.

Query

SELECT * FROM users;

gives

 id | val 
----+-----
  1 | \x

I can't figure out how to map ByteStrings to 'BYTEA`s. According to the docs everything should be OK. What am I doing wrong?

postgresql haskell
1 Answers

Fixed by replacing the line

execute conn "INSERT INTO users(val) values(?)" $ Only encoded

with

execute conn "INSERT INTO users(val) values(?)" $ Only $ Binary encoded

It's because toField(ByteString) yields Escape whereas toField(Binary ByteString) yields EscapeByteA

user_17223990
Related