Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sqF5d-0086v0-Ph for pgsql-general@arkaria.postgresql.org; Mon, 16 Sep 2024 17:00:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sqF5d-00GU2B-F2 for pgsql-general@arkaria.postgresql.org; Mon, 16 Sep 2024 17:00:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sqF5d-00GU23-2p for pgsql-general@lists.postgresql.org; Mon, 16 Sep 2024 17:00:41 +0000 Received: from mail-yb1-xb36.google.com ([2607:f8b0:4864:20::b36]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sqF5Y-001Vpw-JH for pgsql-general@lists.postgresql.org; Mon, 16 Sep 2024 17:00:40 +0000 Received: by mail-yb1-xb36.google.com with SMTP id 3f1490d57ef6-e1a989bd17aso2966713276.1 for ; Mon, 16 Sep 2024 10:00:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726506036; x=1727110836; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=GScnq1HFvxF3mhW+OhJLjBBPl+ERnjJEMBLoDCeh+no=; b=WzBzKtdRsSBLdiHujeWmMeOFFc1GDwZ0AfpvPf6522pBS/43uks9bF0SNUBrl8Ef7y ERufiNNaCy59yWHfgG/sdudpV38rsEA0zFq0hkKh+XhvTuI+jNs6mTYSljhvQEZRuYo0 U9ex1VW1fI7HVn90qENCCNaGfvsGuXzgZHT1Tw6PV0lSzzyu/Dlm7p8jcWwJu6bY3Gwm I4oZ/agGr8+vH1MjpZ6NwiSizGECArjWT1EkT0a8zx6u5FfV1TAqdSzgq/zVsJzpO2dp D5UZurMFMybyWDrOZisVNlMCxnobVi+jRGYk6RV39E/B+KE7JYHeILliMA2Cw38iRjxy caDQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726506036; x=1727110836; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=GScnq1HFvxF3mhW+OhJLjBBPl+ERnjJEMBLoDCeh+no=; b=ZOSJmVEWQnj/9aVdiZdc3z6STarEj/+75Wa7B+ss0ROuxtZarxsCtlmb8jlRd2sTZK CYwzt0g4ilLuSld+DfWvHpFQJYhVLYZNumO8NvD5biJN2xOaDXcjTMRV1tqgfD8EcJfH IBwlQ+LoG8AFxducvJzdW6BmTqPPvb1zUjrepeKIQOu+lePGwM+n0kkOPr4FwBfv0z96 L42tZbUl8CQ5Q1o8o0yVVRyh/A+LHB4qDNpYkp2jZ1wfBFEwJaui3gGVrKm6DSnnfSbO LQv0bR0OYRIJrl1oeQg+CAbtEWbxO/qO4WKkuQQjkeWFhTP6OOpQUB7lqd0MuovJSL+8 4lyQ== X-Forwarded-Encrypted: i=1; AJvYcCU54EHlkwwDjspiZJzyi1i0g7hsZbcoOIRySk4N/upOKrQp8Nb3cTmMnU/Drv5gMifNniW6W8txMKmboONc@lists.postgresql.org X-Gm-Message-State: AOJu0YzOu6pW82BcvcACNtLb+JdBiy4eOr0hdszHOL4BBFsM3G9BYGea Udi1RcQe+40UMy1zPw6tYmq7KghY2yO1z6w3SmXylQhlz/xLCzcykiWadopWQYPriSv3Tt6v+mi 3RcZxudg+o5IKGe8gmMNkyn5Ev9I= X-Google-Smtp-Source: AGHT+IGMvbdXn5b8AnkqbPg7Ecil2RCIlCCtkcN5qZAzgRhMsEkCULlj+wpd26fWdMxHngTsD3bMjnuWvTkZbP3VJKQ= X-Received: by 2002:a05:6902:dca:b0:e14:85d5:858b with SMTP id 3f1490d57ef6-e1db00ed210mr9822855276.32.1726506035001; Mon, 16 Sep 2024 10:00:35 -0700 (PDT) MIME-Version: 1.0 References: <9f5a968f-bd6d-49b6-b5b7-7e5a355f445a@aklaver.com> <48ab0c54-3bf1-451b-87d6-1b7d9b5e5e67@aklaver.com> In-Reply-To: <48ab0c54-3bf1-451b-87d6-1b7d9b5e5e67@aklaver.com> From: Andy Hartman Date: Mon, 16 Sep 2024 13:00:23 -0400 Message-ID: Subject: Re: load fom csv To: Adrian Klaver Cc: Francisco Olarte , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000eb443b06223f82a4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000eb443b06223f82a4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Adrian Klaver wrote: > On 9/16/24 09:46, Andy Hartman wrote: > > > > It Looks correct. > > > > $pgTable =3D "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=E2=80=AFPM Adrian Klaver > > > 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 need= s > to > > be double quoted in the command to preserve the casing. > > > > See: > > > > > https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTA= X-IDENTIFIERS > < > https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTA= X-IDENTIFIERS > > > > > > for why. > > > > > > > > On Mon, Sep 16, 2024 at 11:52=E2=80=AFAM Francisco Olarte > > > > > >> > wrote: > > > > > > > > > > > > On Mon, 16 Sept 2024 at 17:36, 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 =3D > > > > > > "Host=3D$pgServer;Database=3D$pgDatabase;Username=3D$pgUser;Password=3D$= pgPassword" > > > $copyCommand =3D "\COPY $pgTable FROM '$csvPath' DELIMIT= ER > > ',' 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 > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --000000000000eb443b06223f82a4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
in LOG

2024-09-16 12:55:37.29= 5 EDT [428] ERROR:=C2=A0 invalid byte sequence for encoding "UTF8"= ;: 0x00
2024-09-16 12:55:37.295 EDT [428] CONTEXT:=C2=A0 COPY ima= ge_classification_master, line 1, column spoolstarttime
On Mon, = Sep 16, 2024 at 12:56=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 9/16/24 09:46, Andy Hartma= n wrote:
>
> It Looks correct.
>
> $pgTable =3D "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&#= 39;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=E2=80=AFPM Adrian Klaver
> <adr= ian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0On 9/16/24 09:12, Andy Hartman wrote:
>=C2=A0 =C2=A0 =C2=A0 > 2024-09-16 12:06:00.968 EDT [4968] ERROR:=C2= =A0 relation
>=C2=A0 =C2=A0 =C2=A0 > "image_classification_master" does = not exist
>=C2=A0 =C2=A0 =C2=A0 > 2024-09-16 12:06:00.968 EDT [4968] STATEMENT:= =C2=A0 COPY
>=C2=A0 =C2=A0 =C2=A0 > Image_Classification_Master FROM STDIN DELIMI= TER ',' CSV HEADER;
>
>=C2=A0 =C2=A0 =C2=A0I'm assuming this is from the Postgres log.
>
>=C2=A0 =C2=A0 =C2=A0Best guess is the table name in the database is mix= ed case and needs to
>=C2=A0 =C2=A0 =C2=A0be double quoted in the command to preserve the cas= ing.
>
>=C2=A0 =C2=A0 =C2=A0See:
>
>=C2=A0 =C2=A0 =C2=A0https://www.postgresql.org/docs/current/sql-syntax-lexical.html= #SQL-SYNTAX-IDENTIFIERS <https://www.postgresql.org/docs/current/sql-syntax-lexica= l.html#SQL-SYNTAX-IDENTIFIERS>
>
>=C2=A0 =C2=A0 =C2=A0for why.
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > On Mon, Sep 16, 2024 at 11:52=E2=80=AFAM Fran= cisco Olarte
>=C2=A0 =C2=A0 =C2=A0 > <folarte@peoplecall.com <mailto:folarte@peoplecall.com> >=C2=A0 =C2=A0 =C2=A0<mailto:folarte@peoplecall.com <mailto:folarte@peoplecall.com>&g= t;> wrote:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0On Mon, 16 Sept 2024 at 17= :36, Andy Hartman
>=C2=A0 =C2=A0 =C2=A0<hartman60home@gmail.com <mailto:hartman60home@gmail.com>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0<mailto:hartman60home@gmail.com >=C2=A0 =C2=A0 =C2=A0<mailto:hartman60home@gmail.com>>> wrote:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0I'm tryi= ng to run this piece of code from Powershell and
>=C2=A0 =C2=A0 =C2=A0it just
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sits there a= nd never comes back. There are only 131
>=C2=A0 =C2=A0 =C2=A0records in
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0the csv.
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0$connectionS= tring =3D
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0
>=C2=A0 =C2=A0 =C2=A0 =C2=A0"Host=3D$pgServer;Database=3D$pgDatabas= e;Username=3D$pgUser;Password=3D$pgPassword"
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0$copyCommand= =3D "\COPY $pgTable FROM '$csvPath' DELIMITER
>=C2=A0 =C2=A0 =C2=A0',' CSV
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0HEADER;"= ;
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0psql -h $pgS= erver -d $pgDatabase -U $pgUser -c $copyCommand
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0how can I de= bug this?
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0I would start by adding -a= and -e after "psql".
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0IIRC Powershell is windows= , and in windows shell do not pass
>=C2=A0 =C2=A0 =C2=A0command
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0words preparsed as in *ix = to the executable, but a single command
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0line with the executable m= ust parse. Given the amount of
>=C2=A0 =C2=A0 =C2=A0quoting, -a
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0and -e will let you see th= e commands are properly sent, and
>=C2=A0 =C2=A0 =C2=A0if it is
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0trying to read something w= hat it is.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0I will also try to substit= ute the -c with a pipe. If it
>=C2=A0 =C2=A0 =C2=A0heals, it is
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0probably a quoting issue.<= br> >=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0Also, I just caught Ron= 9;s message, and psql might be waiting
>=C2=A0 =C2=A0 =C2=A0for a
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0password.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0Francisco Olarte.
>=C2=A0 =C2=A0 =C2=A0 >
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><= br> >

--
Adrian Klaver
adrian.klave= r@aklaver.com

--000000000000eb443b06223f82a4--