Closed
Description
Hi, I looked into improving the persistent library to support JSON arrays in Postgres. Persistent uses PGArray, which I think needs a cast to support arrays of enums. Here's some example code:
CREATE TYPE colors AS ENUM ('red');
CREATE TABLE colors_table(colors colors[]);
execute conn "INSERT INTO colors_table (colors) values (?)" [PGArray ["red"]]
<interactive>:22:1: warning: [-Wtype-defaults]
• Defaulting the following constraints to type ‘[Char]’
(Database.PostgreSQL.Simple.ToField.ToField a0)
arising from a use of ‘execute’ at <interactive>:22:1-77
(Data.String.IsString a0)
arising from the literal ‘"red"’ at <interactive>:22:71-75
• In the first argument of ‘GHC.GHCi.ghciStepIO ::
forall a. IO a -> IO a’, namely
‘(execute
conn
"INSERT INTO colors_table (colors) values (?)"
[PGArray ["red"]])’
In a stmt of an interactive GHCi command:
it <- GHC.GHCi.ghciStepIO :: forall a. IO a -> IO a
(execute
conn
"INSERT INTO colors_table (colors) values (?)"
[PGArray ["red"]])
*** Exception: SqlError {sqlState = "42804", sqlExecStatus = FatalError, sqlErrorMsg = "column \"colors\" is of type colors[] but expression is of type text[]", sqlErrorDetail = "", sqlErrorHint = "You will need to rewrite or cast the expression."}
PGArray uses the ARRAY syntax:
instance (ToField a) => ToField (PGArray a) where
toField pgArray =
case fromPGArray pgArray of
[] -> Plain (byteString "'{}'")
xs -> Many $
Plain (byteString "ARRAY[") :
(intersperse (Plain (char8 ',')) . map toField $ xs) ++
[Plain (char8 ']')]
-- Because the ARRAY[...] input syntax is being used, it is possible
-- that the use of type-specific separator characters is unnecessary.
Which if you do that in SQL you get:
mercury-web-backend-development=# INSERT INTO colors_table (colors) VALUES (ARRAY['red']) ;
ERROR: column "colors" is of type colors[] but expression is of type text[]
LINE 1: INSERT INTO colors_table (colors) VALUES (ARRAY['red']) ;
^
HINT: You will need to rewrite or cast the expression.
(Note that array literal syntax does work here)
INSERT INTO colors_table colors VALUES ( ('{"red"}') );
Is the recommended way to insert arrays of enums to use Many
to add in a cast after the array? This works:
execute conn "INSERT INTO colors_table (colors) values (?)" [Many [Many [toField (PGArray ["red"]), Plain "::colors[]"],Plain "::colors[]"]]
Which is the same as:
execute conn "INSERT INTO colors_table (colors) values (?)" [Many [Many [Plain "ARRAY[",Escape "red",Plain "]"],Plain "::colors[]"]]
If so, would something like a PGCastedArray
or something else be a good addition to the library?
Alternatively I'd be happy to just document this approach in postgresql-simple.
Metadata
Metadata
Assignees
Labels
No labels