0

I have spent a few hours looking at PostgreSQL INSERT array documentation and examples. But I am just getting more and more confused.

I've created the table word_mash_word_cube_definitions where column definition is type text[].

When I created the column I used text[][]. But \d+ shows it as text[].

In PHP the array is:

$design[1] = [ 'letter' => null , 'direction' => 'down' , 'x-axis' => 7 , 'y-axis' => 3 ];
$design[2] = [ 'letter' => null , 'direction' => 'left' , 'x-axis' => 7 , 'y-axis' => 4 ];
$design[3] = [ 'letter' => null , 'direction' => 'up' , 'x-axis' => 6 , 'y-axis' => 4 ];
$design[4] = [ 'letter' => null , 'direction' => 'left' , 'x-axis' => 6 , 'y-axis' => 3 ];
$design[5] = [ 'letter' => null , 'direction' => 'down' , 'x-axis' => 5 , 'y-axis' => 3 ];
$design[6] = [ 'letter' => null , 'direction' => 'down' , 'x-axis' => 5 , 'y-axis' => 4 ];

I am aiming to store the entire array in 1 row. But I don't know if this is even possible.

Would someone please show me the correct

INSERT INTO word_mash_word_cube_definitions ( definition ) VALUE ( ??? )

I am lost.

1
  • 1
    Your data look like a perfect fit for a table. Don't make the mistake to store them as a composite type or an array. Oct 14, 2022 at 6:10

1 Answer 1

1

If you need to store keyed/associative arrays, then instead of a text[] column, use JSON. Set the column type to jsonb, and use json_encode($design) in PHP and store the resultant string. When you read the row, use json_decode($design, true) to turn it back into the original PHP array.

Storing it this way provides format validation and PostgreSQL functions to query inside the JSON.

There are limitations to this method, especially around character encoding, that will become important if you ever, say, add foreign language support to your app.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.