How to insert an array in a column of the Postgres array type with Laravel/Eloquent?

Question

I have a column like Postgres array and I’m trying to insert an array into it with Eloquent, only it’s giving me a wrong saying that the column is badly formed.

My model is like this:

class Leituras extends Model
{
    protected $table = 'tb_leituras';
    protected $casts = [
        'ids_autores' => 'array'
    ];
}

And on my controller I’m trying to insert it like this:

$leitura = new Leituras();
$leituras->ids_autores = [20,45];
$leituras->save();

And then you make a mistake:

"SQLSTATE[22P02]: Invalid text representation: 7 ERROR: malformed literal array"

I’ve tried using the following syntax to insert:

$leitura = new Leituras();
$leituras->ids_autores = json_encode([20,45]);
$leituras->save();

But it’s no use either. Someone knows how to solve this?

Answer 1

Hello you can use the method insert() and do as follows:

$leituras = new Leituras();
        $leituras->insert([
            ['ids_autores' => 20],
            ['ids_autores' => 15],
        ]);

You in this case pass an associative array, and if you wanted to insert other columns just pass them together.

For example:

$leituras = new Leituras();
        $leituras->insert([
            ['ids_autores' => 20, 'coluna_b' => 'valor abc'],
            ['ids_autores' => 15, 'coluna_b' => 'valor bcsd'],
        ]);

Answer 2

In the Eloquent does not yet have a plausible solution to work with this type of data array existing resource at the Bank PostGresql, that is to say, it does not support this yet, that including in its documentation the field intended for operations with array sane text and/or json.

Back still in explanation the error message is because the data gets bad formatted with two quotes, example ""[20,45]"" because of the cast present in Model which does not apply to this type of data, that is to say, only the cast of array field text or json and to create the Migrations these two types are well explained

Due to this impossibility, change the field type in the database to one of these two (text or json) and do it the way you’re doing it:

• Model

  class Leituras extends Model
  {
      protected $table = 'tb_leituras';
      protected $casts = ['ids_autores' => 'array'];
  }
  

• Inserting

  $leitura = new Leituras();
  $leituras->ids_autores = [20,45];
  $leituras->save();
  

that will work and work with this data in array both inserting both changing and returning a array when so researched.

A tip:

Observing: The method insert and update does not pass through the conversion of cast array and needs to be converted, examples:

Works:

Leitura::insert(['ids_autores' => json_encode([20,45])]);
Leitura::create(['ids_autores' => [20,45]]);   

$leitura = new Leitura();
$leitura->ids_autores = [20,45];
$leitura->save();

Does not work, you need to use the function json_encode:

Leitura::insert(['ids_autores' => [20,45]])
Leitura::where('id', 1)->update(['ids_autores' => [20,45]]);    
Leitura::where('id', 1)->update(['ids_autores' => [20,45]]);

^References

• Documentation Postgresql 9.1: 8.14. Arrays
• Laravel Migration array type (store array in database column)