haskellari / postgresql-simple

Mid-level client library for accessing PostgreSQL from Haskell
Other
88 stars 46 forks source link

Arrays of enums with PGArray #57

Closed MaxGabriel closed 3 years ago

MaxGabriel commented 3 years ago

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.

phadej commented 3 years ago

Create a newtype which would have an explicit cast, generate not just 'red' but 'red' :: colour. I.e. don't cast the array, but rather the enum already. I suspect you'll run into other ambiguities elsewhere as well.

An example:

# CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

# select array ['sad' :: mood, 'ok' :: mood];
  array   
----------
 {sad,ok}
(1 row)
MaxGabriel commented 3 years ago

Seems reasonable, thanks @phadej