PostgreSQL quoting problem

rnturn · 07-01-2008, 10:56 PM

I'm working on a Perl script to generate SQL commands that will be used to load a table in a PostgreSQL 8.3 database. So far the SQL I'm generating is only being dumped into a file for use in a "psql -f filename" command; I'll get the DBI code added once I get past the problem I've run into when a string contains a particularly problematic character: commas.

The insert that's being generated will look something like:

Code:

INSERT INTO atable (name,model,options) VALUES ('Widget','Red','A,B,C');

(The "options" column will almost certainly need to contain a string that contains commas but I don't really need these separated when they're stored in the database.) The insert fails with an error message that indicates that there are more values than columns. Obviously, the quoting of the third value isn't working as one would think and the embedded commas are being interpreted as value delimiters. I've tried dealing with the commas in various ways:

Code:

'A,B,C' -> 'A\,B\,C'

Code:

'A,B,C' -> $$A,B,C$$

etc. to no avail. FWIW, an insert similar to the first code block above works on an 8.2 database.

The PostgreSQL documentation hasn't been any help. There's a fair amount of discussion of how to deal with certain special characters, how to deal with quotation marks in strings, etc. but haven't been able to find anything on how to handle commas in a string. (The closest thing I have found that offers any hope is to define any column as an array if it might contain a comma which seems like a serious over-complication not to mention a nightmare to code).

This seems like a common thing that one would encounter. I have to believe that there's an example of how to handle this situation. So far I haven't found anything in my reference library and Google's sure not been coming up with any good URLs.

Any ideas?

TIA

--
Rick

Tinkster · 07-02-2008, 01:37 AM

Well, the code snippet with the insert works in psql.
Are you sure that this is the string you're throwing at
the DB in your perl code?

Code:

tink  =# create TABLE atable (name varchar(20), model varchar(20), options varchar(20));
CREATE TABLE
tink  =# INSERT INTO atable (name,model,options) VALUES ('Widget','Red','A,B,C');
INSERT 0 1
tink  =# SELECT * from atable ;
  name  | model | options 
--------+-------+---------
 Widget | Red   | A,B,C

Cheers,
Tink

j-ray · 07-02-2008, 04:31 AM

this may depend on how the options field is defined in the database. It should be no problem if it is defined as char or varchar or similar. So is it an enumeration field or similar?

Perl provides you with two functions to quote strings "automatically", qq and qw. You can find documentation typing
perldoc -f qq
These functions circumvent the problem that variables dont get interpolated eventually ++.

if you simply want to erase the commas sth like that should work:
$value_str=~s/,/ /g;

rnturn · 07-02-2008, 08:27 AM

Quote:

if you simply want to erase the commas sth like that should work:
$value_str=~s/,/ /g;

I considered that but then I thought that when it comes time to pull that information out of the database, I'd just have to put the commas back in. I wasn't too keen on having to do that.

--
Rick

rnturn · 07-02-2008, 08:44 AM

Quote:

Originally Posted by Tinkster

Well, the code snippet with the insert works in psql.

Yah, it does when I manually insert data as well. So I took a look at the actual SQL that was being generated and found that I seem to have an error in the Perl source somewhere.

The problem turns out to be caused not by an extra comma in the values but rather a missing column name in the list. The list of columns and values are being generated dynamically as the Perl parses the data file. I have an error in the code that not putting one of the column names in the list. So instead of ten column names and ten values, I only have nine column names.

Time to turn 'strict' back on. I must have a typo in there somewhere. Now to find it. (Before the phone rings with the next fire alarm.)

Thanks for the mental nudge. Sometimes that's all it takes.

--
Rick

Tinkster · 07-02-2008, 01:19 PM

Quote:

Originally Posted by rnturn

The list of columns and values are being generated dynamically as the Perl parses the data file. I have an error in the code that not putting one of the column names in the list. So instead of ten column names and ten values, I only have nine column names.

That would give that error, I guess :}

I didn't suspect the values in the single quotes (I might have
had you mentioned array types, but even then it would have been
quite unlikely to be the error you saw).

Cheers,
Tink

chrism01 · 07-02-2008, 08:05 PM

You should always use

#!/usr/bin/perl -w
use strict;

at the top of your progs. It'll save you a lot of hassle.
As for funny chars in strings, use this

dbh->quote($string)

http://search.cpan.org/~timb/DBI-1.605/DBI.pm#quote