PostgreSQL: Arrays Data Type with PHP

Question

I'm working on PostgeSQL with PHP and I know that PosrgeSQL allow columns of a table to be defined as arrays.

So let's say I have a table like this:

CREATE TABLE sal_emp (
    a            text ARRAY,
    b            text ARRAY,
    c            text ARRAY,
);

These are my arrays:

$a = array(aa,bb,cc);
$b = array(dd,dd,aa);
$c = array(bb,ff,ee);

and I want to insert them into respective column each like this:

     a     |     b      |      c     
-----------+------------+------------
{aa,bb,cc} | {dd,dd,aa} |  {bb,ff,ee}

Can I insert it this way?

$a = implode(',', $a);
$b = implode(',', $b);
$c = implode(',', $c);
$a = array('a' => $a, 'b' => $b, 'c' => $c);
pg_insert($dbconn, 'table', $a);

Or is there a better way to achieve the same result?

It's just a matter of string manipulation to convert PHP's array into a string representation that Postgres will accept in a query: postgresql.org/docs/9.1/static/arrays.html — Marc B
– Marc B, Commented Apr 3, 2012 at 5:15
@MarcB Yeah I know but I am just not sure if I get it correct and if there is a better way doing it. I'm very new to PostgreSQL.. — user1282226
– user1282226, Commented Apr 3, 2012 at 5:40
The right way to do it is to normalize your database properly and not use PostgreSQL arrays at all. — Daniel Lyons
– Daniel Lyons, Commented Apr 3, 2012 at 15:43
You could look up this question stackoverflow.com/questions/3068683/… — Phd. Burak Öztürk
– Phd. Burak Öztürk, Commented Oct 28, 2019 at 11:10

Clodoaldo Neto · Accepted Answer · 2012-04-03 11:25:14Z

0

Your creation statement has an error. This is correct:

CREATE TABLE sal_emp (
    a            text ARRAY,
    b            text ARRAY,
    c            text ARRAY
);

If you were to insert manually you would do:

insert into sal_emp (a, b, c) values 
('{"some text"}', '{"some more", "a bit more"}', '{"even more"}');

or

insert into sal_emp (a, b, c) values 
(array ['some text'], array['some more', 'a bit more'], array['even more']);

So... I can't test it now but it would be something like this:

$a = "array['" . implode("'],['", $a) . "']";
...
$a = array('a' => $a, 'b' => $b, 'c' => $c);
pg_insert($dbconn, 'table', $a);

answered Apr 3, 2012 at 11:25

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1