Обсуждение: Another user error?

When trying to populate the locations table I get this error:
psql:locations.sql:2105: ERROR:  syntax error at or near ";"
LINE 2105: ...E Airport Way',null,'Portland','OR','97218',null,null,null);
                                                                          ^
Line 2105 is the last line in the file and is terminated with the only
semi-colon in the file. Every line prior to this one is terminated with a
colon. All other syntax errors have been corrected.

I've not encountered this issue before. Where should I look for the error?

TIA,

Rich

On 11/1/20 10:53 AM, Rich Shepard wrote:
> When trying to populate the locations table I get this error:
> psql:locations.sql:2105: ERROR:  syntax error at or near ";"
> LINE 2105: ...E Airport Way',null,'Portland','OR','97218',null,null,null);
>                                                                           ^
> Line 2105 is the last line in the file and is terminated with the only
> semi-colon in the file. Every line prior to this one is terminated with a
> colon. All other syntax errors have been corrected.

I'm hoping that it is:

'Every line prior to this one is terminated with a comma'

not colon.

> 
> I've not encountered this issue before. Where should I look for the error?
> 
> TIA,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Sun, 1 Nov 2020, Adrian Klaver wrote:

> I'm hoping that it is:
> 'Every line prior to this one is terminated with a comma'
> not colon.

Adrian,

That's the case. The only colons are within strings.

Thanks,

Rich

On Sun, Nov 1, 2020 at 7:54 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> When trying to populate the locations table I get this error:
> psql:locations.sql:2105: ERROR:  syntax error at or near ";"
> LINE 2105: ...E Airport Way',null,'Portland','OR','97218',null,null,null);
>                                                                           ^
> Line 2105 is the last line in the file and is terminated with the only
> semi-colon in the file. Every line prior to this one is terminated with a
> colon. All other syntax errors have been corrected.
>
> I've not encountered this issue before. Where should I look for the error?

This is nearly impossible to diagnose without a chunk of the query (
i,e, the first lines and the last ones ).

It smells to missing parentheses. If you use some editor witch matches
them, try adding one BEFORE the semicolon. If it matches something
above you've got it.

But really, show your code, otherwise you are not going to get much help.

Francisco Olarte.

On Sun, 1 Nov 2020, Rich Shepard wrote:

> Every line prior to this one is terminated with a colon. All other syntax
> errors have been corrected.

Er, that's comma, not colon. Need to recaffinate.

Rich

On Sun, 1 Nov 2020, Francisco Olarte wrote:

> This is nearly impossible to diagnose without a chunk of the query (
> i,e, the first lines and the last ones ).

Francisco,

$ psql -d bustrac -f locations.sql

> It smells to missing parentheses. If you use some editor witch matches
> them, try adding one BEFORE the semicolon. If it matches something
> above you've got it.

Here're the last two lines:
(2297,1,'Principal place of business','600 SE Bay
Boulevard',null,'Newport','OR','97365','541-265-7758','541-265-4235',null),
(2298,1,'Principal place of business','7200 NE Airport Way',null,'Portland','OR','97218',null,null,null);

And psql found a few missing terminating parentheses and commas which were
fixed. I expected this last run to successfully complete.

Regards,

Rich

On 11/1/20 11:04 AM, Rich Shepard wrote:
> On Sun, 1 Nov 2020, Francisco Olarte wrote:
> 
>> This is nearly impossible to diagnose without a chunk of the query (
>> i,e, the first lines and the last ones ).
> 
> Francisco,
> 
> $ psql -d bustrac -f locations.sql
> 
>> It smells to missing parentheses. If you use some editor witch matches
>> them, try adding one BEFORE the semicolon. If it matches something
>> above you've got it.
> 
> Here're the last two lines:
> (2297,1,'Principal place of business','600 SE Bay 
> Boulevard',null,'Newport','OR','97365','541-265-7758','541-265-4235',null),
> (2298,1,'Principal place of business','7200 NE Airport 
> Way',null,'Portland','OR','97218',null,null,null);

Also need the beginning lines including the INSERT part.

> 
> And psql found a few missing terminating parentheses and commas which were
> fixed. I expected this last run to successfully complete.
> 
> Regards,
> 
> Rich
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Sun, 1 Nov 2020, Adrian Klaver wrote:

> Also need the beginning lines including the INSERT part.

insert into locations
(org_nbr,loc_nbr,loc_name,loc_addr1,loc_addr2,loc_city,state_code,loc_postcode,loc_phone,loc_fax,comment)values 
 
(1,1,'2nd Bridge Gravel Bar','16156 Hwy 101 S',null,'Brookings','OR','97415-0224','541-469-5341',null,null),

Again, while in previous runs psql had found errors none other than the final semicolon
showed up in this last one.

Rich

Rich:

On Sun, Nov 1, 2020 at 8:04 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> On Sun, 1 Nov 2020, Francisco Olarte wrote:
>
> > This is nearly impossible to diagnose without a chunk of the query (
> > i,e, the first lines and the last ones ).

> Francisco,

> $ psql -d bustrac -f locations.sql

This is NOT the first line of the query. Even "head locations.sql" is
not guaranteed to give the first line of the query ( you know it, we
do not, it may have a few selects ata the top.

What I mean is something like ....

>>>>>>>>
insert into locations balh, blerg, blub values
(1,2,3),
...... snip the middle lines.
(4,5,6),
(7,8,9);
<<<<<<<<<<<<

> > It smells to missing parentheses. If you use some editor witch matches
> > them, try adding one BEFORE the semicolon. If it matches something
> > above you've got it.
> Here're the last two lines:
> (2297,1,'Principal place of business','600 SE Bay
Boulevard',null,'Newport','OR','97365','541-265-7758','541-265-4235',null),
> (2298,1,'Principal place of business','7200 NE Airport Way',null,'Portland','OR','97218',null,null,null);

> And psql found a few missing terminating parentheses and commas which were
> fixed. I expected this last run to successfully complete.

Good luck. BTW, if you know a bit of scripting in perl/awk/python
whatever, it's normally useful to generate big insertion queries
programatically, to avoid mismatched quotes problems. And if your
editor is half-decent ad you have th disk space you can normally try
to make a big file, create an empty table like the target one, insert
into so you can bisect the input ( and once you have everything in
spool tables you just insert the whole table into the final one and
drop it ).

I mean, you do "create table location_tmp like locations". Then, split
your input files into easily managed ones, say some thousand lines.
Insert every file into tmp, do bisections on the ones which fail to
find errors. Once you have everything in tmp just insert from it into
final table.

Francisco Olarte.

On Sun, 1 Nov 2020, David G. Johnston wrote:

> You encountered and asked on this exact same issue Friday...the advice in
> that "Multi-row insert: error at terminal row." all still applies.

David J.

Which is why I did this file chunk-by-chunk.

Re-doing this by portions I find psql reporting errors it did not report
before. So, I keep re-running the command until it all works.

Regards,

Rich

On 2020-11-01 11:50:31 -0800, Rich Shepard wrote:
> On Sun, 1 Nov 2020, David G. Johnston wrote:
> > You encountered and asked on this exact same issue Friday...the advice in
> > that "Multi-row insert: error at terminal row." all still applies.
>
> Which is why I did this file chunk-by-chunk.

Your chunk still seems to be 2105 lines long.

As others have already suggested, your best chance is bisection.
Split that single 2000+ line insert into two inserts of 1000+ lines
each. If you are lucky, one of them will work and the other won't.
Then split the insert which doesn't work. Repeat until you either can
see and fix the error or until you are down to a single line.


> Re-doing this by portions I find psql reporting errors it did not report
> before. So, I keep re-running the command until it all works.

This is normal. PostgreSQL doesn't try to find all errors. If it finds
an error, it reports it and aborts the query. So if your statement
contains more than one error (which is quite likely in a statement over
2000 lines long), fixing one error will just show the next.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On Sun, 1 Nov 2020, Peter J. Holzer wrote:

> Your chunk still seems to be 2105 lines long.

Peter,

I've started from the top and work in 50-100 line chunks. I'm finding psql
errors that it had not flagged the first time through. So I'm working slowly
and carfully and expect to find all my typos and other errors when I'm done.

Thanks,

Rich