framework: Eloquent unable to save Postgres arrays

Laravel Version: v5.7.27
PHP Version: 7.2.2
Database Driver & Version: PostgreSQL 10.5

Description:

The eloquent code is treating a database array and a json object as if they are the same. But in PostGres the format for an array like an INTEGER[] needs formatting to be as

INSERT INTO schema.table ( field ) ( '{1,2,3}' );

but because of current setup using json_encode, the result is

INSERT INTO schema.table ( field ) ( '[1,2,3]' );

The minor but crucial difference being the [ and the {

Steps To Reproduce:

CREATE TABLE testschema.testtable (
  arrayfield integer[]
);

with Model cast defining array:

use Illuminate\Database\Eloquent\Model;
class TestTable extends Model {
	protected $casts = [
		'arrayfield ' => 'array',
	];
}

\App\Models\Incoming\TestSchema\Placement::updateOrCreate( [
 'arrayfield' => [1,2,3]
] );

will generate…

insert into schema.testtable ( arrayfield ) values ([11])

which gives the error

Invalid text representation: 7 ERROR:  malformed array literal: "[11]"

About this issue

Original URL
State: closed
Created 5 years ago
Reactions: 1
Comments: 25 (11 by maintainers)

Most upvoted comments

This is not supported. Eloquent can’t know whether you want to store a PostgreSQL array or a JSON string.

We could stop having the array cast as an alias for json and only use it for PostgreSQL arrays, but that would be a huge breaking change.

You’ll have to add a custom accessor and mutator:

class Test extends Model {

    public function getFieldAttribute($value) {
        return json_decode(str_replace(['{', '}'], ['[', ']'], $value));
    }

    public function setFieldAttribute($value) {
        $this->attributes['field'] = str_replace(['[', ']'], ['{', '}'], json_encode($value));
    }

}

staudenmeir on Feb 22, 2019

JSON and ARRAY are specifically separate things. So yes the code that treats them the same never was correct. It was a never tested always broken scenario. The json_encode method cannot be used at all for a Postgres ARRAY. Array’s can also be strings, example: CHARACTER VARYING[] which can contain those [ ] { } characters. It’s not just for INTEGER[]. And Eloquent specifically already knows by the $cast whether that field is an ARRAY or JSON.

So the lines like this…

            case 'array':
            case 'json':
                return $this->fromJson($value);

from the castAttribute method in laravel/framework/src/Illuminate/Database/Eloquent/Concerns/HasAttributes.php

which ignored the differences between those two data types, are the issue.

gareth-ib on Feb 22, 2019

Since it seems that this isn’t really a bug but more of a request for more native Postgres support, I think it’s best that this is moved to the ideas repo.

driesvints on Feb 22, 2019

ok but still this is not a conversation about json, it’s about arrays. Not the Laravel specific idea to store json into a text field and call it array. And an array field is not in json format. Which is why this faked feature is breaking arrays.

gareth-ib on Feb 22, 2019