Skip to content

Arrays of enums with PGArray #57

Closed
@MaxGabriel

Description

@MaxGabriel

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

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions