PostgreSQL INSERT Multi-Dimensional Array

Question

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.

Answer 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.