Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

DBD::Pg insert data into an array

by rdfield (Priest)
on Dec 19, 2018 at 17:59 UTC ( #1227477=CUFP: print w/replies, xml ) Need Help??

An internet search didn't show up an example of inserting data into a PostgreSQL array (quite a few for retrieving data, though), so I thought I'd post this here.

Given a table:
create table my_table( id serial, description varchar(255), associated_str_data varchar(255)[], associated_int_data integer[]);
just using arrayrefs for data supplied to the placeholders does the job:
my $desc = "A description"; my @assoc_str_data = ("string 1", "string 2"); my @assoc_int_data = (1,2,3); $dbh->do("insert into my_table(description, associated_str_data, assoc +iated_int_data) values (?,?,?)", undef, $desc, \@assoc_str_data, \@assoc_int_data);

Checking the result in psql shows the data inserted OK:

# select * from my_table; id | description | associated_str_data | associated_int_data ----+---------------+-------------------------+--------------------- 2 | A description | {"string 1","string 2"} | {1,2,3} (1 row)


Replies are listed 'Best First'.
Re: DBD::Pg insert data into an array
by pme (Monsignor) on Dec 19, 2018 at 19:41 UTC
    This is how to do that in a more effective way, using prepare_cached().
    my @data = ( ["A description", ["string 1", "string 2"], [ 1, 2, 3]], ["B description", ["string 3", "string 4"], [ 4, 5, 6]], ["C description", ["string 5", "string 6"], [ 7, 8, 9]], ["D description", ["string 7", "string 8"], [10,11,12]], ); my $sth = $dbh->prepare_cached("insert into my_table(description, asso +ciated_str_data, associated_int_data) values (?, ?, ?)"); $sth->execute(@$_) for @data; $dbh->commit;
      In a batch environment, yes, your way might be more effective - but in a batch environment I'd question the wisdom of burying data inside an array column.


Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: CUFP [id://1227477]
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (3)
As of 2023-03-21 07:09 GMT
Find Nodes?
    Voting Booth?
    Which type of climate do you prefer to live in?

    Results (59 votes). Check out past polls.