How can I insert into PostgreSQL using Perl, DBI and placeholders?

Question

I am trying to insert a row into a pSQL table, specifying both keys and values as placeholders:

my @keys = keys %db_entr;                                                            
my @vals = values %db_entr;

my @items = (@keys, @values);

my $dbh = DBI->connect("DBI:Pg:dbname=testdb;host=localhost", "username", 'password', {'RaiseError' => 1});                                                                   
my $sth = $dbh->prepare("INSERT INTO grid ( ?, ?, ? ) values ( ?, ?, ? )");
my $rows = $sth->execute(@items);                                                    
print "$rows effected\n";

However, whatever I do, this gives me an error:

DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO grid ( $1, $2, ...
                           ^ at ./file.pl line 262, <STDIN> line 11.

Does anyone have an idea of what I might be doing wrong?

Answer 1

You cannot use placeholders for column names like this:

INSERT INTO grid (?, ?, ?) VALUES (?, ?, ?)

You must specify column names explicitly and can use placeholders for values only:

INSERT INTO grid (x, y, z) VALUES (?, ?, ?)