Horton
Menu

Thread

  1. load fom csv

    Andy Hartman <hartman60home@gmail.com> — 2024-09-16T15:35:35Z

    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?

Table layout

[image: image.png]

  2. Re: load fom csv

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-09-16T15:45:46Z

    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

  3. Re: load fom csv

    Ron Johnson <ronljohnsonjr@gmail.com> — 2024-09-16T15:47:16Z

    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!

  4. Re: load fom csv

    Francisco Olarte <folarte@peoplecall.com> — 2024-09-16T15:51:28Z

    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.

  5. Re: load fom csv

    Andy Hartman <hartman60home@gmail.com> — 2024-09-16T16:12:02Z

    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 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.
>
>

  6. Re: load fom csv

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-09-16T16:17:09Z

    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

  7. Re: load fom csv

    Andy Hartman <hartman60home@gmail.com> — 2024-09-16T16:46:53Z

    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: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
>
>

  8. Re: load fom csv

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-09-16T16:56:25Z

    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>
> 
>     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

  9. Re: load fom csv

    Andy Hartman <hartman60home@gmail.com> — 2024-09-16T17:00:23Z

    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 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
> >
> >
> >     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
>
>

  10. Re: load fom csv

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-09-16T17:10:49Z

    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

  11. Re: load fom csv

    Muhammad Usman Khan <usman.k@bitnine.net> — 2024-09-17T05:55:24Z

    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'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?
>
> Table layout
>
> [image: image.png]
>

  12. Re: load fom csv

    Andy Hartman <hartman60home@gmail.com> — 2024-09-17T12:31:49Z

    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:

> 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'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?
>>
>> Table layout
>>
>> [image: image.png]
>>
>

  13. Re: load fom csv

    Andy Hartman <hartman60home@gmail.com> — 2024-09-17T15:13:02Z

    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:

> 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:
>
>> 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'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?
>>>
>>> Table layout
>>>
>>> [image: image.png]
>>>
>>

  14. Re: load fom csv

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-09-17T15:23:29Z

    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

  15. Re: load fom csv

    Andy Hartman <hartman60home@gmail.com> — 2024-09-17T15:35:31Z

    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: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
>
>

  16. Re: load fom csv

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-09-17T15:39:03Z

    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

  17. Re: load fom csv

    Andy Hartman <hartman60home@gmail.com> — 2024-09-17T16:21:33Z

    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 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
>
>

  18. Re: load fom csv

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-09-17T16:25:32Z

    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

  19. Re: load fom csv

    Rob Sargent <robjsargent@gmail.com> — 2024-09-17T19:45:53Z

    
> 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

> 
>

  20. Re: load fom csv

    Andy Hartman <hartman60home@gmail.com> — 2024-09-17T23:10:05Z

    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:

>
>
> > 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
>
> >
> >
>

  21. Re: load fom csv

    Andy Hartman <hartman60home@gmail.com> — 2024-09-18T12:39:24Z

    psql -h $pgServer -d $pgDatabase -U $pgUser -c $copyCommand

I'm wondering if it's waiting on P/w ?

Thanks.

On Tue, Sep 17, 2024 at 7:10 PM Andy Hartman <hartman60home@gmail.com>
wrote:

> 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:
>
>>
>>
>> > 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
>>
>> >
>> >
>>
>

  22. Re: load fom csv

    Rob Sargent <robjsargent@gmail.com> — 2024-09-18T13:29:57Z

    
  



  

  
    On Sep 18, 2024, at 6:39 AM, Andy Hartman <hartman60home@gmail.com> wrote:

    

  


  
    
    
      
        psql -h $pgServer -d $pgDatabase -U $pgUser -c $copyCommand 
      
      
        

      
      
        I'm wondering if it's waiting on P/w ?

        

        Thanks.
      
    
  


  

Very likely.  Can you show the authentication 

  mechanisms used (pg_hba)?

  
    
      

      
        
          On Tue, Sep 17, 2024 at 7:10 PM Andy Hartman <hartman60home@gmail.com> wrote:

        
        
          
            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:

            
            
              

              

              > 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

              

              >

              >

  23. Re: load fom csv

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-09-18T14:50:26Z

    On 9/18/24 06:29, Rob Sargent wrote:
> 
> 
>> On Sep 18, 2024, at 6:39 AM, Andy Hartman <hartman60home@gmail.com> wrote:
>>
>> 
>> psql -h $pgServer -d $pgDatabase -U $pgUser -c $copyCommand
>>
>> I'm wondering if it's waiting on P/w ?

In a previous post I suggested:

"
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.
"

Did you do this with the same command at each stage? If so at either 1) 
or 2) where you asked for a password?

In a later posted I asked:

"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.
"

Did you enable connection logging?

Did you look at the Postgres log?

If both the answers are yes you should see something like the below in 
case of password authentication:

2024-09-18 07:47:38.692 PDT [8090] [unknown]@[unknown] LOG:  connection 
received: host=127.0.0.1 port=44840
2024-09-18 07:47:42.410 PDT [8095] [unknown]@[unknown] LOG:  connection 
received: host=127.0.0.1 port=44848
2024-09-18 07:47:42.414 PDT [8095] aklaver@test LOG:  connection 
authenticated: identity="aklaver" method=md5 
(/etc/postgresql/16/main/pg_hba.conf:125)
2024-09-18 07:47:42.414 PDT [8095] aklaver@test LOG:  connection 
authorized: user=aklaver database=test application_name=psql SSL enabled 
(protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)


>>
>> Thanks.
> 
> Very likely.  Can you show the authentication
> mechanisms used (pg_hba)?
>>
>> On Tue, Sep 17, 2024 at 7:10 PM Andy Hartman <hartman60home@gmail.com 
>> <mailto:hartman60home@gmail.com>> wrote:
>>
>>     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
>>     <mailto:robjsargent@gmail.com>> wrote:
>>
>>
>>
>>         > On Sep 17, 2024, at 12:25 PM, Adrian Klaver
>>         <adrian.klaver@aklaver.com <mailto: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
>>
>>         >
>>         >
>>

-- 
Adrian Klaver
adrian.klaver@aklaver.com