Search Postgresql Archives

Re: load fom csv

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxxxxx <mailto:folarte@xxxxxxxxxxxxxx>
>     <mailto:folarte@xxxxxxxxxxxxxx <mailto:folarte@xxxxxxxxxxxxxx>>> wrote:
>      >
>      >
>      >
>      >     On Mon, 16 Sept 2024 at 17:36, Andy Hartman
>     <hartman60home@xxxxxxxxx <mailto:hartman60home@xxxxxxxxx>
>      >     <mailto:hartman60home@xxxxxxxxx
>     <mailto:hartman60home@xxxxxxxxx>>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux