Subject: Re: load fom csv

From: Andy Hartman <[email-masked]>
Date: Mon, 16 Sep 2024 12:12:02 -0400

2024-09-16 12:06:00.968 EDT [4968] ERROR:  relation
"image_classification_master" does not exist
2024-09-16 12:06:00.968 EDT [4968] STATEMENT:  COPY
Image_Classification_Master FROM STDIN DELIMITER ',' CSV HEADER;

On Mon, Sep 16, 2024 at 11:52 AM Francisco Olarte <[email-masked]>
wrote:

>
>
> On Mon, 16 Sept 2024 at 17:36, Andy Hartman <[email-masked]>
> wrote:
>
>> I'm trying to run this piece of code from Powershell and it just sits
>> there and never comes back. There are only 131 records in the csv.
>> $connectionString =
>> "Host=$pgServer;Database=$pgDatabase;Username=$pgUser;Password=$pgPassword"
>> $copyCommand = "\COPY $pgTable FROM '$csvPath' DELIMITER ',' CSV HEADER;"
>> psql -h $pgServer -d $pgDatabase -U $pgUser -c $copyCommand
>> how can I debug this?
>>
>
> I would start by adding -a and -e after "psql".
>
> IIRC Powershell is windows, and in windows shell do not pass command words
> preparsed as in *ix to the executable, but a single command line with the
> executable must parse. Given the amount of quoting, -a and -e will let you
> see the commands are properly sent, and if it is trying to read something
> what it is.
>
> I will also try to substitute the -c with a pipe. If it heals, it is
> probably a quoting issue.
>
> Also, I just caught Ron's message, and psql might be waiting for a
> password.
>
> Francisco Olarte.
>
>

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 16 Sep 2024 09:17:09 -0700

> Image_Classification_Master FROM STDIN DELIMITER ',' CSV HEADER;

I'm assuming this is from the Postgres log.

Best guess is the table name in the database is mixed case and needs to 
be double quoted in the command to preserve the casing.

See:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

for why.

> 
> On Mon, Sep 16, 2024 at 11:52 AM Francisco Olarte 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
> 
> 
>     On Mon, 16 Sept 2024 at 17:36, Andy Hartman <[email-masked]
>     <mailto:[email-masked]>> wrote:
> 
>         I'm trying to run this piece of code from Powershell and it just
>         sits there and never comes back. There are only 131 records in
>         the csv.
>         $connectionString =
>     IIRC Powershell is windows, and in windows shell do not pass command
>     words preparsed as in *ix to the executable, but a single command
>     line with the executable must parse. Given the amount of quoting, -a
>     and -e will let you see the commands are properly sent, and if it is
>     trying to read something what it is.
> 

-- 
Adrian Klaver
[email-masked]

---

From: Andy Hartman <[email-masked]>
Date: Mon, 16 Sep 2024 12:46:53 -0400

It Looks correct.

$pgTable = "image_classification_master"

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 16 Sep 2024 09:56:25 -0700

> $pgTable = "image_classification_master"

Connect to the database with psql and look at the table name. I'm 
betting it is not image_classification_master. Instead some mixed or all 
upper case version of the name.

I don't use PowerShell or Windows for that matter these days so I can't 
be of much use on the script. I do suspect you will need to some 
escaping to get the table name properly quoted in the script. To work 
through this you need to try what I call the crawl/walk/run process. In 
this case that is:

1) Crawl. Connect using psql and run the \copy in it with hard coded values.

2) Walk. Use psql with the -c argument and supply the command again with 
hard coded values

3) Run. Then use PowerShell and do the variable substitution.

> 
> 
> 
> 
> On Mon, Sep 16, 2024 at 12:17 PM Adrian Klaver 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
>     https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS <https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS>
> 
>     for why.
> 
>      >
>      > <[email-masked] <mailto:[email-masked]>
>     <mailto:[email-masked] <mailto:[email-masked]>>> wrote:
>      >
>      >
>      >
>      >     On Mon, 16 Sept 2024 at 17:36, Andy Hartman
>     <[email-masked] <mailto:[email-masked]>
>      >     <mailto:[email-masked]
>     <mailto:[email-masked]>>> wrote:
>      >
>      >         $copyCommand = "\COPY $pgTable FROM '$csvPath' DELIMITER
>     ',' CSV
>      >         HEADER;"
>     [email-masked] <mailto:[email-masked]>
>

---

From: Andy Hartman <[email-masked]>
Date: Mon, 16 Sep 2024 13:00:23 -0400

in LOG

2024-09-16 12:55:37.295 EDT [428] ERROR:  invalid byte sequence for
encoding "UTF8": 0x00
2024-09-16 12:55:37.295 EDT [428] CONTEXT:  COPY
image_classification_master, line 1, column spoolstarttime

On Mon, Sep 16, 2024 at 12:56 PM Adrian Klaver <[email-masked]>
wrote:

> <

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 16 Sep 2024 10:10:49 -0700

> image_classification_master, line 1, column spoolstarttime

I'm heading out the door I can't step you through the process, I can 
point you at:

https://www.postgresql.org/docs/current/multibyte.html#MULTIBYTE-AUTOMATIC-CONVERSION

Others will be able to answer the specifics.