CODEWITHSUNDEEP
haskell
Sal
Sal

Reputation: 4326

Retrieving timestamp with time zone in Postgresql-simple

I am aware that this issue has been raised in the past before, and now marked as resolved.

I am using postgresql-simple package along with Data.Time. If I make a query to retrieve a single column of type timestamptz, I get pattern match error. Pseudo-code below:

import Data.Text as T
import Data.Time (UTCTime,LocalTime,ZonedTime)
import Database.PostgreSQL.Simple as Pg
import Database.PostgreSQL.Simple.FromRow
import Database.PostgreSQL.Simple.ToRow
import Database.PostgreSQL.Simple.ToField
import Database.PostgreSQL.Simple.Time
import qualified Data.Vector as V
...
--- in main this is the code
[Pg.Only (i::UTCTime)] <- Pg.query conn "select lastupdated from constraints where name=?" ( Only ("carrier"::T.Text))
...

I get this error at run-time when executing the query (Please note above :

*** Exception: user error (Pattern match failure in do expression at ...)

If I change above to an invalid type like below:

[Pg.Only (i::T.Text)] <- Pg.query conn "select lastupdated from constraints where name=?" ( Only ("carrier"::T.Text))

I get exception about type which shows that the expected type is indeed timestamptz:

*** Exception: Incompatible {errSQLType = "timestamptz",...,
errSQLField = "lastupdated", errHaskellType = "Text", errMessage = "types incompatible"}

Also, this is how the above query result looks in psql console:

          lastupdated          
-------------------------------
 2016-04-13 00:08:33.789761+00
 2016-04-13 14:33:38.27739+00
(2 rows)

My understanding is that Data.Time.UTCTime or Database.PostgreSQL.Simple.Time.UTCTimestamp should map to timestamptz type of PostgreSQL. Apparently, that understanding is wrong if I am getting those errors above. Will appreciate help with this.

Upvotes: 2

Views: 369

Answers (1)

hao
hao

Reputation: 10238

Try this instead:

(times :: [Pg.Only UTCTime]) <-
    Pg.query conn "select lastupdated from constraints where name=?" (Only ("carrier" :: T.Text))

Your pattern match was not only enforcing that there'd be a list of Only UTCTimes, but also that there'd be exactly one element in the list. As you have two records, query will return a list with two elements and the pattern match fails.

Upvotes: 3

Related Questions