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 1sRJIS-00EQF0-Me for pgsql-general@arkaria.postgresql.org; Tue, 09 Jul 2024 22:26:53 +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 1sRJIR-000Coo-2m for pgsql-general@arkaria.postgresql.org; Tue, 09 Jul 2024 22:26:51 +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 1sRJIQ-000Cod-Lb for pgsql-general@lists.postgresql.org; Tue, 09 Jul 2024 22:26:50 +0000 Received: from srv01.teladesign.ie ([2001:41c9:1:41d::10]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRJIN-001I2w-6N for pgsql-general@postgresql.org; Tue, 09 Jul 2024 22:26:50 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=rodonnell.ie; s=exim; h=Content-Type:MIME-Version:Subject:References: In-Reply-To:Message-ID:Date:To:From:Content-Transfer-Encoding:Sender:Reply-To :Cc:Content-ID:Content-Description:Resent-Message-ID; bh=I/zCm4nMBoREpb1RQOBXf5dpTGUi51Z5Xzow4VNlDUw=; b=V4oQmhHgkUO4za1Y66xWoGYP3q 0PbtPRpyxzxOOsnzBE7968raAaUcRNLYebSGKOardzfGHE0XVgbjLWEUrV7eM1j+KI9NLzyRsqnrV K6/rLYX/2A2GXwpgaGDhjoLWESe3D1ltks/jzjEFPFx71LKe3miqKIVHlcEhZz/t5oH8=; Received: from [93.107.215.202] (helo=[192.168.1.151]) by srv01.teladesign.ie with esmtpsa (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1sRJIG-0007sr-PD; Tue, 09 Jul 2024 23:26:45 +0100 From: "Ray O'Donnell" To: Rich Shepard , Date: Tue, 09 Jul 2024 23:26:33 +0100 Message-ID: <190999b09a8.2834.f9dd809031fc0469edf0bbbf79c1d468@rodonnell.ie> In-Reply-To: References: User-Agent: AquaMail/1.51.5 (build: 105105504) Subject: Re: Finding error in long input file MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="190999b0b3a6f592834e1ece42" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --190999b0b3a6f592834e1ece42 Content-Type: text/plain; format=flowed; charset="us-ascii" Content-Transfer-Encoding: 8bit On 9 July 2024 23:14:33 Rich Shepard wrote: > I've a file with 488 rows to be input into a table. I run the script using > psql with the `\i' option. > > After psql found a few missing commas I thought the script was clean. But, > psql returned this error: > bustrac=# \i scripts/insert-addrs.sql > psql:scripts/insert-addrs.sql:488: ERROR: syntax error at or near ";" > LINE 488: ...2832,1,default,'85250 Red House Rd','Paisley','OR','97636'); > ^ Am > I correct that some rows were inserted into the table before this error was > generated somewhere prior to the end of the file? If so, how do I determine > which rows were inserted and what is the recommended procedure to locate > just where that error is? Did you run the entire thing inside a transaction? If so, then it will have been rolled back after the error, and no rows will have been inserted. Ray. > > I've learned from experience that using the old coding approach of dividing > the file in half, then half again and again until the row with the error is > found produces multiple rows with different PKs. Not what I want to clean > up. > > A clue stick will be much appreciated. > > TIA, > > Rich --190999b0b3a6f592834e1ece42 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

On 9 July 2024 23:14:33 Rich Shepard <rshepard@appl-ec= osys.com> wrote:

I've a file with 488 rows to be input into a table. I run= the script using
psql with the `\i' option.

After psql found a few missing commas I thought the scrip= t was clean. But,
psql returned this error:
bustrac=3D# \i scripts/insert-addrs.sql
psql:scripts/insert-addrs.sql:488: ERROR:  syntax er= ror at or near ";"
LINE 488: ...2832,1,default,'85250 Red House Rd','Paisley= ','OR','97636');
                &= nbsp;                    =                     &nbs= p;              ^ Am
I correct that some rows were inserted into the table bef= ore this error was
generated somewhere prior to the end of the file? If so, = how do I determine
which rows were inserted and what is the recommended proc= edure to locate
just where that error is?


Did you run the entire thing insi= de a transaction? If so, then it will have been rolled back after the error= , and no rows will have been inserted.

Ray.


=



I've learned from experience that using the old coding ap= proach of dividing
the file in half, then half again and again until the row= with the error is
found produces multiple rows with different PKs. Not what= I want to clean
up.

A clue stick will be much appreciated.

TIA,

Rich

--190999b0b3a6f592834e1ece42--