current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I know I can insert an array into a Postgres database with pg_query.

INSERT into table (field) VALUES ('{{element, element},{}}')

But how about using pg_insert?

$array["field"] = "{{element, element},{}}";
$result = pg_insert($con, "table", $array);

Will the string {{element, element},{}} be actually inserted into field as a 2D Postgres array?

I've always wanted to test that out but currently I don't have any PostgreSQL DB to test with..

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

I just ran your specific example.

(1) in Postgres:

CREATE TABLE atable (afield text[][]);

(2) in PHP:

$array["afield"] = "{{'element', 'element'},{}}";
$result = pg_insert($this->conn, "atable", $array);

And I got the following error:

Notice: pg_insert(): Unknown or system data type '_text' for 'afield' in ...

I tried playing around with the array value: make it 2x2 array, one-dimensional, etc., etc. - the same result. I even changed the table to have the field as one-dimensional array: text[] and changed the code accordingly - and I still get the same result.

I started digging further and found the following on PHP documentation for pg_insert:

This function is EXPERIMENTAL. The behaviour of this function, its name, and surrounding documentation may change without notice in a future release of PHP. This function should be used at your own risk.

Basically, it's pretty buggy and shouldn't be used. Interestingly, using

pg_query("INSERT INTO...")

works just fine. Hopefully, this answers your question. :)

share|improve this answer
    
Thanks a lot! I'm surprised someone would actually test that for me. I truly appreciate that :) –  user1282226 Jun 11 '12 at 13:25
    
@ArchJ No problem. I did have a postgres instance and spare 5 minutes available –  Aleks G Jun 11 '12 at 13:26
add comment
up vote 0 down vote

As many dimensions as you like, but two is fine.

=> CREATE SCHEMA ztest;
CREATE SCHEMA
=> CREATE TABLE tt (a int[3][3]);
CREATE TABLE
=> INSERT INTO tt VALUES (ARRAY[ARRAY[1,2,3], ARRAY[4,5,6], ARRAY[7,8,9]]), (ARRAY[ARRAY[11,12,13],ARRAY[14,15,16],ARRAY[17,18,19]]);
INSERT 0 2
=> SELECT * FROM tt;
                 a
------------------------------------
 {{1,2,3},{4,5,6},{7,8,9}}
 {{11,12,13},{14,15,16},{17,18,19}}
(2 rows)

=> INSERT INTO tt VALUES ('{{21,22,23},{24,25,26},{27,28,29}}');
INSERT 0 1
=> SELECT * FROM tt;
                 a
------------------------------------
 {{1,2,3},{4,5,6},{7,8,9}}
 {{11,12,13},{14,15,16},{17,18,19}}
 {{21,22,23},{24,25,26},{27,28,29}}
(3 rows)

=> SELECT a[2][3] FROM tt;
 a
----
  6
 16
 26
(3 rows)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.