Feedback-ID: i76984098:Fastmail
Message-ID: <48ab0c54-3bf1-451b-87d6-1b7d9b5e5e67@aklaver.com>
Date: Mon, 16 Sep 2024 09:56:25 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: load fom csv
To: Andy Hartman <hartman60home@gmail.com>
Cc: Francisco Olarte <folarte@peoplecall.com>,
 pgsql-general@lists.postgresql.org
References: <CAEZv3cqEZyUouS_0tCcUG1UOi6C0VcYLgoneq-Atf8i+ikge1A@mail.gmail.com>
 <CA+bJJbzkpHw9n=_pb1vDukgty1_r6pjt1F9=MniVAYkiVjfdPA@mail.gmail.com>
 <CAEZv3cqMLSQUKDXoHJjPrEydjFvnpwS408MJAGhNmZCm1Yf7rg@mail.gmail.com>
 <9f5a968f-bd6d-49b6-b5b7-7e5a355f445a@aklaver.com>
 <CAEZv3cpWJeZcG4WLBZ-caaUdSvm8nKETubwhzDox6VkCW7J31g@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEZv3cpWJeZcG4WLBZ-caaUdSvm8nKETubwhzDox6VkCW7J31g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/48ab0c54-3bf1-451b-87d6-1b7d9b5e5e67%40aklaver.com>
Precedence: bulk

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