Cannot updateEasy optional field with GENERATED ALWAYS

[xave]

SQL Table

CREATE TABLE my_table (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  some_field int
);

Haskell

data MyTableT a b = MyTable {
  mtId :: a,
  mtSomeField ::b}

type MyTableR = 
  MyTable
    (Field SqlInt8)
    (Field SqlInt4)

type MyTableW = 
  MyTable
    (Maybe (Field SqlInt8))
    (Field SqlInt4)

Query

updateSomeField :: Int -> Update Int64
updateSomeField n = 
  Update 
    { uTable = myTableTable
    , uUpdateWith = updateEasy (\mt -> mt {mtSomeField = n})
    , uWhere = (\mt _ a) ->a .== toFields n
    , uReturning = rCount
   }

Problem This type checks, but throws sql error at runtime:

sqlErrorMsg = "column \"id\" can only be updated to DEFAULT", sqlErrorDetail = "Column \"id\" is an identity column defined as GENERATED ALWAYS.", 

Best practice with postgresql is to define id bigint GENERATED ALWAYS AS IDENTITY instead of id bigserial.

---

Further, I was trying to move away from the readOnly pattern that some older tutorials have spread.

readOnly :: String -> TableFields () (Field a)
readOnly = lmap (const Nothing) . optionalTableField

type MyTableW = 
  MyTable
    ()
    (Field SqlInt4)

myTableTable :: Table MyTableW MyTableR
myTableTable = 
    ...
    MyTable  { mtId = (readOnly "id")
             , mtSomeField = (requiredTableField "some_field")
             }

Outcomes There are two outcomes:

1. With the readOnly style setup, I can update this record (without updateEasy), but I get a new id each time. Cannot use updateEasy here because of type checker.
2. Without it, I cannot even update a record at all.

---

ASIDE: For the record, that readOnly pattern is nice as it guarantees that no one writes to the field and adds a bit of safety around the activity. It would be much better if that pattern could be explicitly supported.

[tomjaguarpaw]

Could you add the definition of myTableTable under Haskell in your first section? I can probably guess what it is, but if you added it that would be easier for me!

[tomjaguarpaw]

This is related to: https://github.com/tomjaguarpaw/haskell-opaleye/issues/591#issuecomment-2369960721

[tomjaguarpaw]

Can you please try omitOnWriteTableField from branch update (see comparison) and see if that resolves your problem?

[xave]

That worked.

SQL Table

CREATE TABLE my_table (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  some_field int
);

My definitions are as follows:

data MyTypeT a b = MyType {
  mtId :: a,
  mtSomeField :: b}

type MyType = 
  MyTypeT
    Int
    Int

type MyTypeTableR = 
  MyTypeT
    (Field SqlInt8)
    (Field SqlInt4)

type MyTypeTableW = 
  MyTypeT
    ()
    (Field SqlInt4)

$(makeAdaptorAndInstanceInferrable "pMyType" ''MyTypeT)

myTypeTable = Table MyTypeTableW MyTypeTableR
myTypetable = table "my_table" (pMyType
  MyType {
    mtId = (omitOnWriteTableField "id")
    , mtSomeField = (requiredTableField "some_field")
  }
)

And the query takes the form:

myUpdate :: Int -> Update Int64
myUpdate n = Update 
  { uTable = myTypeTable
  , uUpdateWith = \mt -> mt {mtSomeField = toFields n}
  , uWhere = (\mt _ a) -> a .== toFields n
  , uReturning = rCount
  }

This variant also works:

myUpdate :: Int -> Update Int64
myUpdate n = Update 
  { uTable = myTypeTable
  , uUpdateWith = \mt -> mt 
      { mtId = (), 
      , mtSomeField = toFields n
      }
  , uWhere = (\mt _ a) -> a .== toFields n
  , uReturning = rCount
  }

---

Verified that my id fields in the database remain unchanged and the stuff I want to change does change. ✅

[tomjaguarpaw]

    mtId = (omitWriteOnTableField "id")

You mean omitOnWriteTableField, right?

---

omitOtWriteTableField has been released at https://hackage.haskell.org/package/opaleye-0.10.4.0

Does this resolve the issue for you?

[xave]

Yes it does!