Обсуждение: ...

I have a flat text file that is pipe delimited that I am trying to load in
a postgresql table.  The data is in the following format:

COLUMN_NAME;value|COLUMN_NAME;value| ... etc

I read each line of the file into an array (in perl) and then load each
member of the array into a hash.  The reason for all of this is that I
need to convert some of the values and a hash seems to be the easiest way
to manage the data.  After all changes are commplete I use the hash to
feed my DBI insert statement.  When I do I get the following error:

Relation 'TABLE_NAME' does not have attribute 'COLUMN_NAME'

I modified my script to print out the SQL that it is trying to execute and
it is in what I believe to be the correct format.  The format is as
follows:

insert into TABLE_NAME (COLUMN_NAME, COLUMN_NAME, COLUMN_NAME) VALUES
('value', 'value', 'value')

It looks to me like it is saying that the column specified by
COLUMN_NAME does not exsist but when I do a \dt TABLE_NAME it is
there.  Because I loaded the data into a hash the COLUMN_NAME's are not in
the same order as they appear in the database but from reading the docs it
looks to me that all long as the COLUMN_NAME's I put in my insert
statement match up with the values I should be OK (this is how it worked
when I was using mysql).  I thought that maybe I did not have write perms
on the table all seems to be fine there also.  I also tried cutting and
pasting the SQL generated by my script into a psql window and got the same
error.

Thanks for any insight, I am at a loss

jth

Try using  "copy" instead of "insert" ?

Salustiano


J.T. Hartzler wrote:

> I have a flat text file that is pipe delimited that I am trying to load in
> a postgresql table.  The data is in the following format:
>
> COLUMN_NAME;value|COLUMN_NAME;value| ... etc
>
> I read each line of the file into an array (in perl) and then load each
> member of the array into a hash.  The reason for all of this is that I
> need to convert some of the values and a hash seems to be the easiest way
> to manage the data.  After all changes are commplete I use the hash to
> feed my DBI insert statement.  When I do I get the following error:
>
> Relation 'TABLE_NAME' does not have attribute 'COLUMN_NAME'
>
> I modified my script to print out the SQL that it is trying to execute and
> it is in what I believe to be the correct format.  The format is as
> follows:
>
> insert into TABLE_NAME (COLUMN_NAME, COLUMN_NAME, COLUMN_NAME) VALUES
> ('value', 'value', 'value')
>
> It looks to me like it is saying that the column specified by
> COLUMN_NAME does not exsist but when I do a \dt TABLE_NAME it is
> there.  Because I loaded the data into a hash the COLUMN_NAME's are not in
> the same order as they appear in the database but from reading the docs it
> looks to me that all long as the COLUMN_NAME's I put in my insert
> statement match up with the values I should be OK (this is how it worked
> when I was using mysql).  I thought that maybe I did not have write perms
> on the table all seems to be fine there also.  I also tried cutting and
> pasting the SQL generated by my script into a psql window and got the same
> error.
>
> Thanks for any insight, I am at a loss
>
> jth
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

"J.T. Hartzler" <dba@wap.ntelos.com> writes:
> It looks to me like it is saying that the column specified by
> COLUMN_NAME does not exsist but when I do a \dt TABLE_NAME it is
> there.

And spelled how, exactly?  I'm betting on a case-sensitivity issue
here.  Unquoted identifiers in SQL commands fold to lower case.

            regards, tom lane

That is what the problem was.  I was not double quoting the colomn names.
Thanks for the info.

On Thu, 25 Jul 2002, Tom Lane wrote:

> "J.T. Hartzler" <dba@wap.ntelos.com> writes:
> > It looks to me like it is saying that the column specified by
> > COLUMN_NAME does not exsist but when I do a \dt TABLE_NAME it is
> > there.
>
> And spelled how, exactly?  I'm betting on a case-sensitivity issue
> here.  Unquoted identifiers in SQL commands fold to lower case.
>
>             regards, tom lane
>