DBI/DBD::Pg changing my sql query somehow?

cormanaz has asked for the wisdom of the Perl Monks concerning the following question:

I have a subroutine I use to insert a records into a postgres database using DBI and DBD::Pg as a driver. I've used it for years with no problems. $dbh is an established connection, and $data is a reference to a hash with the column names and values to be inserted:

sub insertsql {
    my ($dbh,$table,$data) = @_;
    my @qm;
    my @keys;
    my @values;
    my $i = -1;
    foreach my $k (keys %$data) {
        if (defined($data->{$k})) {
            $i++;
            $keys[$i] = $k;
            $values[$i] = $data->{$k};
            $qm[$i] = '?';
        }
    }
    my $keylist = join(",",@keys);
    my $qlist = join(",",@qm);
    my $sqlstatement = "insert into $table ($keylist) values ($qlist) 
+returning id";    
    my $sth = $dbh->prepare($sqlstatement);
    $sth->execute(@values) || die $sth->errstr;
    $sth->finish();
    return;
}
[download]

I am using this to insert some crowdtangle data. After the 17th line, $sqlstatement looks like this:

insert into crowdtangle (postUrl,crowdtangle_id,statistics,account,dew
+indowfy_content,expandedLinks,score,description,message,posted,media,
+subscriberCount,updated,platform,type) values (?,?,?,?,?,?,?,?,?,?,?,
+?,?,?,?) returning id
[download]

When I execute the query I get this:

DBD::Pg::st execute failed: ERROR:  column "posturl" of relation "crow
+dtangle" does not exist
LINE 1: insert into crowdtangle (postUrl,crowdtangle_id,statistics,a..
+.
                                 ^ at c:/perlmodules/SqlSupport.pm lin
+e 70.
[download]

It is right, column posturl does not exist. The column name is postUrl with a capital U, like I had it in the query. What could be going on here?

Comment on DBI/DBD::Pg changing my sql query somehow? Select or Download Code

Replies are listed 'Best First'.
Re: DBI/DBD::Pg changing my sql query somehow? by talexb (Chancellor) on Jul 25, 2024 at 03:56 UTC
Hmm .. I see insert into crowdtangle (postUrl, .. and then DBD::Pg::st execute failed: ERROR: column "posturl" of relation .. which leads me to believe that these fields are case-sensitive. (So, the field name should be a uniform choice of posturl, and not postUrl.) Alex / talexb / Toronto Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.	[reply]
Re^2: DBI/DBD::Pg changing my sql query somehow? by cavac (Prior) on Jul 25, 2024 at 07:17 UTC
PostgreSQL is case sensitive. But it auto-lowercases table and column names (among other identifiers) in statements, unless you enclose them in double-quotes "". In this case, this leads to a mismatch. Mostly because someone unwisely created the table with mixed-case column names. PerlMonks XP is useless? Not anymore: XPD - Do more with your PerlMonks XP Also check out my sisters artwork and my rather simple sketches/one-panel comics	[reply]
Re^3: DBI/DBD::Pg changing my sql query somehow? by parv (Parson) on Jul 25, 2024 at 08:15 UTC
Using mixed case for column names is "unwise" if they are not quoted (as mentioned at URLs linked elsewhere in this thread). OP could get on with life with quoting them (in their queries).	[reply]
Re^3: DBI/DBD::Pg changing my sql query somehow? by hippo (Archbishop) on Jul 25, 2024 at 08:43 UTC
Mostly because someone unwisely created the table with mixed-case column names. It's even worse than that. Someone created the table with column names arbitrarily scattered between camelCase and snake_case. That's just inviting doom. 🦛	[reply]
Re: DBI/DBD::Pg changing my sql query somehow? by bliako (Abbot) on Jul 24, 2024 at 19:42 UTC
lolling out with what google fetched me: https://www.paessler.com/help/postgre-sql-errors#5 https://www.janbasktraining.com/community/sql-server/how-to-resolve-the-error-postgresql-relation-does-not-exist the last one seems more plausible as the column name it is complaining about contains cased letters. ps. i am just the googler. there are Pg experts here	[reply]
Re^2: DBI/DBD::Pg changing my sql query somehow? by kikuchiyo (Hermit) on Jul 24, 2024 at 20:32 UTC
Ugh, that second link. It scratches at the right place, but the conclusion is wildly wrong. This Stackoverflow answer explains it better: https://stackoverflow.com/questions/21796446/postgres-case-sensitivity But it's best to rely on the official documentation: https://www.postgresql.org/docs/16/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS	[reply]