使用postgresql-simple的简单选择查询出现`Incompatible {errSQLType =“varchar”,...`错误

ffx8fchx  于 2023-05-22  发布在  PostgreSQL
关注(0)|答案(1)|浏览(125)

我定义了一个PostgreSQL表,如下所示:

> \d+ public."Users"
                                                                   Table "public.Users"
   Column    |           Type           | Collation | Nullable |               Default               | Storage  | Compression | Stats target | Description 
-------------+--------------------------+-----------+----------+-------------------------------------+----------+-------------+--------------+-------------
 id          | integer                  |           | not null | nextval('"Users_id_seq"'::regclass) | plain    |             |              | 
 username    | character varying(255)   |           | not null |                                     | extended |             |              | 
 userId      | character varying(255)   |           |          |                                     | extended |             |              |

我尝试使用postgresql-simple查询该表:

maybeUsername <- liftIO (listToMaybe <$> query conn "SELECT username FROM public.\"Users\" WHERE \"userId\" = ?" (Only userId) :: IO (Maybe String))
  case maybeUsername of
    Just username -> liftIO $ putStrLn $ "Username is " ++ username
    Nothing -> liftIO $ putStrLn $ "Could not find a record for userId " ++ userId

由于某种原因,我得到了以下错误:

Incompatible {errSQLType = "varchar", errSQLTableOid = Just (Oid 24782), errSQLField = "username", errHaskellType = "Char", errMessage = ""}

有办法解决吗?

5fjcxozz

5fjcxozz1#

当返回单个列时,库很难推断出类型(记录在这里:https://hackage.haskell.org/package/postgresql-simple-0.6.5/docs/Database-PostgreSQL-Simple.html#g:4)。因此,我们需要通过执行Only String而不是String来解决这个问题。
这样它就像预期的那样工作:

usernames <- liftIO (query conn "SELECT username FROM public.\"Users\" WHERE \"userId\" = ?" (Only userId) :: IO [Only String])
  liftIO $ putStrLn $ "Usernames are " ++ intercalate ", " (map fromOnly usernames)

相关问题