fix: cast json column to text for PostgreSQL equality comparison#193
fix: cast json column to text for PostgreSQL equality comparison#193ManukMinasyan merged 1 commit intomainfrom
Conversation
PostgreSQL's `json` type does not support the `=` operator. Cast `json_value` to `text` via `::text` before comparing in the ULID migration's email and domain field value queries. Fixes #178
There was a problem hiding this comment.
Pull request overview
Fixes the ULID migration failing on PostgreSQL due to comparing a json column with = by attempting to cast json_value to text before equality checks.
Changes:
- Update
phaseA8_migrateEmailFieldValues()to comparejson_value::textagainst[]/null. - Update
phaseA9_migrateDomainFieldValues()to comparejson_value::textagainst[]/null.
| ->where(function (Builder $query): void { | ||
| $query->whereNull('json_value') | ||
| ->orWhere('json_value', '=', '[]') | ||
| ->orWhere('json_value', '=', 'null'); | ||
| ->orWhereRaw('"json_value"::text = ?', ['[]']) | ||
| ->orWhereRaw('"json_value"::text = ?', ['null']); |
There was a problem hiding this comment.
The raw SQL string is wrapped in single quotes, so \" is not an escape sequence in PHP and the backslashes will be sent to PostgreSQL literally (producing \"json_value\"::text, which is invalid SQL). Use plain "json_value"::text without backslashes (or just json_value::text) so the cast actually runs.
| ->where(function (Builder $query): void { | ||
| $query->whereNull('json_value') | ||
| ->orWhere('json_value', '=', '[]') | ||
| ->orWhere('json_value', '=', 'null'); | ||
| ->orWhereRaw('"json_value"::text = ?', ['[]']) | ||
| ->orWhereRaw('"json_value"::text = ?', ['null']); | ||
| }) |
There was a problem hiding this comment.
::text is PostgreSQL-specific syntax, but this migration’s relational DB path is also used for MySQL/MariaDB (migrateForRelationalDb). As written, this will cause a syntax error on non-PostgreSQL drivers. Either branch on DB::getDriverName() (pgsql vs others) or use driver-appropriate casting (e.g., CAST(json_value AS CHAR) / JSON_UNQUOTE(...)) to keep the migration runnable across the supported drivers.
| ->where(function (Builder $query): void { | ||
| $query->whereNull('json_value') | ||
| ->orWhere('json_value', '=', '[]') | ||
| ->orWhere('json_value', '=', 'null'); | ||
| ->orWhereRaw('"json_value"::text = ?', ['[]']) | ||
| ->orWhereRaw('"json_value"::text = ?', ['null']); |
There was a problem hiding this comment.
The raw SQL string is wrapped in single quotes, so \" is not an escape sequence in PHP and the backslashes will be sent to PostgreSQL literally (producing \"json_value\"::text, which is invalid SQL). Use plain "json_value"::text without backslashes (or just json_value::text) so the cast actually runs.
| ->where(function (Builder $query): void { | ||
| $query->whereNull('json_value') | ||
| ->orWhere('json_value', '=', '[]') | ||
| ->orWhere('json_value', '=', 'null'); | ||
| ->orWhereRaw('"json_value"::text = ?', ['[]']) | ||
| ->orWhereRaw('"json_value"::text = ?', ['null']); | ||
| }) |
There was a problem hiding this comment.
::text is PostgreSQL-specific syntax, but this migration’s relational DB path is also used for MySQL/MariaDB (migrateForRelationalDb). As written, this will cause a syntax error on non-PostgreSQL drivers. Either branch on DB::getDriverName() (pgsql vs others) or use driver-appropriate casting (e.g., CAST(json_value AS CHAR) / JSON_UNQUOTE(...)) to keep the migration runnable across the supported drivers.
Summary
jsontype does not support the=equality operator (onlyjsonbdoes)json_valuedirectly with=, causingoperator does not exist: json = unknownon PostgreSQLjson_value::textbefore comparing in bothphaseA8_migrateEmailFieldValuesandphaseA9_migrateDomainFieldValuesFixes #178
Test plan
php artisan migrateon a fresh PostgreSQL database with existing custom field values