load fom csv

On 9/16/24 08:35, Andy Hartman 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?

1) Look at the Postgres log.

2) Run the psql command outside PowerShell with hard coded connection
values and -c command.

Table layout

image.png

--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Sep 16, 2024 at 11:36 AM Andy Hartman <hartman60home@gmail.com>
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?

Maybe I'm missing something obvious, but where in the psql command are you
using $connectionString?

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!

On Mon, 16 Sept 2024 at 17:36, Andy Hartman <hartman60home@gmail.com> 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.

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 <folarte@peoplecall.com>
wrote:

On 9/16/24 09:12, Andy Hartman wrote:

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;

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
<folarte@peoplecall.com <mailto:folarte@peoplecall.com>> wrote:

On Mon, 16 Sept 2024 at 17:36, Andy Hartman <hartman60home@gmail.com
<mailto:hartman60home@gmail.com>> 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.

--
Adrian Klaver
adrian.klaver@aklaver.com

It Looks correct.

$pgTable = "image_classification_master"

On Mon, Sep 16, 2024 at 12:17 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 9/16/24 09:46, Andy Hartman wrote:

It Looks correct.

$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
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 9/16/24 09:12, Andy Hartman wrote:

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;

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 <https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS&gt;

for why.

On Mon, Sep 16, 2024 at 11:52 AM Francisco Olarte
<folarte@peoplecall.com <mailto:folarte@peoplecall.com>

<mailto:folarte@peoplecall.com <mailto:folarte@peoplecall.com>>> wrote:

     On Mon, 16 Sept 2024 at 17:36, Andy Hartman

<hartman60home@gmail.com <mailto:hartman60home@gmail.com>

     <mailto:hartman60home@gmail.com

<mailto:hartman60home@gmail.com>>> 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.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

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 <adrian.klaver@aklaver.com>
wrote:

On 9/16/24 10:00, Andy Hartman wrote:

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

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi,
Try the following options:

- Check if psql is working independently:
psql -h $pgServer -d $pgDatabase -U $pgUser -c "SELECT 1;"
- Check for permission issues on the CSV file
- Run the command manually without variables
psql -h your_host -d your_db -U your_user -c "\COPY your_table FROM
'C:/path/to/your/file.csv' DELIMITER ',' CSV HEADER;"
- set a timeout using the PGCONNECT_TIMEOUT environment variable:
$env:PGCONNECT_TIMEOUT=30

On Mon, 16 Sept 2024 at 20:35, Andy Hartman <hartman60home@gmail.com> wrote:

I have bad data in an Int field...

Thanks for your help.

On Tue, Sep 17, 2024 at 1:55 AM Muhammad Usman Khan <usman.k@bitnine.net>
wrote:

Still when I try to run from my powershell script it hangs...

On Tue, Sep 17, 2024 at 8:31 AM Andy Hartman <hartman60home@gmail.com>
wrote:

On 9/17/24 08:13, Andy Hartman wrote:

Still when I try to run from my powershell script it hangs...

And the Postgres log shows?

--
Adrian Klaver
adrian.klaver@aklaver.com

I don't see anything in LOG

On Tue, Sep 17, 2024 at 11:23 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 9/17/24 08:35, Andy Hartman wrote:

I don't see anything in LOG

1) Are you logging connections/disconnection per?:

https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

If not do so as it will show you if a connection is being attempted.

2) Do the commands work when run in psql or supplied directly to psql
outside of PowerShell?

On Tue, Sep 17, 2024 at 11:23 AM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 9/17/24 08:13, Andy Hartman wrote:

Still when I try to run from my powershell script it hangs...

And the Postgres log shows?

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

The command work outside of powershell yes

On Tue, Sep 17, 2024 at 11:39 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 9/17/24 09:21, Andy Hartman wrote:

The command work outside of powershell  yes

Then you are going to need to use whatever debugging tools PowerShell
has available to step through the script to figure out where the problem is.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sep 17, 2024, at 12:25 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 9/17/24 09:21, Andy Hartman wrote:

The command work outside of powershell yes

Then you are going to need to use whatever debugging tools PowerShell has available to step through the script to figure out where the problem is.

Visual Studio can run/debug PS 1 files. I am not at my desk but have done ps1 oneliner queries against mssql

Suggest echoing ALL vars used in psql command

I'll echo vars and see if something looks strange.

THanks.

On Tue, Sep 17, 2024 at 3:46 PM Rob Sargent <robjsargent@gmail.com> wrote: