Adding a serial column

[wister]

I tried adding a serial column.

{
  "name": "01_add_test_id",
  "operations": [
    {
      "add_column": {
        "table": "tests",
        "column": {
          "name": "test_id",
          "type": "serial",
          "nullable": false
        }
      }
    }
  ]
}

Which I thought would be equivalent to this sql query:

ALTER TABLE IF EXISTS tests ADD COLUMN test_id serial;

The error message is:
Failed to start migration: migration is invalid: field "up" is required

In this case, what would be the SQL expression for the up property? I have tried with:

...
"up": "",
...
Failed to start migration: unable to execute start operation: failed to create trigger: pq: missing expression at or near ";"

...
"up": "ROW_NUMBER() OVER(ORDER BY created_at ASC)",
...
Failed to start migration: unable to execute start operation: failed to create trigger: pq: syntax error at or near "TRIGGER"

Any suggestions? Thanks!

[exekias]

Thank you for reporting!

I gave this a try and to me, this is a bug. We need an up function in order to backfill values for the column, but in the case of SERIAL columns, the default is implicit in the column definition. Providing a default up function that does proper backfilling should be possible. This is true for any column definition that has a default.

I tested this and it worked, but this is only a workaround that relies on pgroll internal names (subject to a change at any time):

  {
    "name": "01_add_test_id",
    "operations": [
      {
        "add_column": {
          "table": "tests",
          "column": {
            "name": "test_id",
            "type": "serial",
            "nullable": false
          },
          "up": "nextval('public.customers__pgroll_new_test_id_seq)"
        }
      }
    ]
 }