Обсуждение: Multi-row insert: error at terminal row.
I'm loading data into tables with the 'insert' statement. There are many
rows containing values and each is bracketed by parentheses except for the
last row. That's terminated with a semicolon, but psql reports an error
there:
psql:organizations.sql:1926: ERROR: syntax error at or near ";"
LINE 1925: ...m',null,'Port','Opportunity',null);
^
I'm not seeing why that's an error. All previous rows terminate with a comma
and I don't know where else to look for the reason. What am I missing
seeing?
TIA,
Rich
On 10/29/20 9:43 AM, Rich Shepard wrote: > I'm loading data into tables with the 'insert' statement. There are many > rows containing values and each is bracketed by parentheses except for the > last row. That's terminated with a semicolon, but psql reports an error > there: > > psql:organizations.sql:1926: ERROR: syntax error at or near ";" > LINE 1925: ...m',null,'Port','Opportunity',null); > ^ > I'm not seeing why that's an error. All previous rows terminate with a > comma > and I don't know where else to look for the reason. What am I missing > seeing? > > TIA, > > Rich > > > Can we see the last two line of the file (1924, 1925)?
Rich Shepard <rshepard@appl-ecosys.com> writes:
> I'm loading data into tables with the 'insert' statement. There are many
> rows containing values and each is bracketed by parentheses except for the
> last row. That's terminated with a semicolon, but psql reports an error
> there:
> psql:organizations.sql:1926: ERROR: syntax error at or near ";"
> LINE 1925: ...m',null,'Port','Opportunity',null);
> ^
> I'm not seeing why that's an error. All previous rows terminate with a comma
> and I don't know where else to look for the reason. What am I missing
> seeing?
First thought is that you might need another right parenthesis there.
regards, tom lane
On Thu, 29 Oct 2020, Rob Sargent wrote: > Can we see the last two line of the file (1924, 1925)? Rob, (2697,'Port of Newport','http://www.portofnewport.com',null,'Port','Opportunity',null), (2698,'Port of Portland','http://www.portofportland.com',null,'Port','Opportunity',null); Each line is enclosed in parentheses and is terminated with a comma. Regards, Rich
Hi Rich, > On 29. Oct, 2020, at 16:58, Rich Shepard <rshepard@appl-ecosys.com> wrote: > > On Thu, 29 Oct 2020, Rob Sargent wrote: > >> Can we see the last two line of the file (1924, 1925)? > > Rob, > > (2697,'Port of Newport','http://www.portofnewport.com',null,'Port','Opportunity',null), > (2698,'Port of Portland','http://www.portofportland.com',null,'Port','Opportunity',null); > > Each line is enclosed in parentheses and is terminated with a comma. the last line has a closing parenthesis missing. Cheers, Paul
On Thu, 29 Oct 2020, Paul Förster wrote: >> (2698,'Port of Portland','http://www.portofportland.com',null,'Port','Opportunity',null); > the last line has a closing parenthesis missing. Paul, I see a closing parenthesis immediately in front of the semi-colon and emacs shows it matches the opening parenthesis. Rich
On 10/29/20 9:08 AM, Rich Shepard wrote: > On Thu, 29 Oct 2020, Paul Förster wrote: > >>> (2698,'Port of >>> Portland','http://www.portofportland.com',null,'Port','Opportunity',null); >>> >> the last line has a closing parenthesis missing. > > Paul, > > I see a closing parenthesis immediately in front of the semi-colon and > emacs > shows it matches the opening parenthesis. Pretty sure the thinking is that the opening parenthesis is further upstream, say around VALUES? > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Rich, > On 29. Oct, 2020, at 17:08, Rich Shepard <rshepard@appl-ecosys.com> wrote: > > On Thu, 29 Oct 2020, Paul Förster wrote: > >>> (2698,'Port of Portland','http://www.portofportland.com',null,'Port','Opportunity',null); >> the last line has a closing parenthesis missing. > > Paul, > > I see a closing parenthesis immediately in front of the semi-colon and emacs > shows it matches the opening parenthesis. I don't know Emacs, I'm a vi guy. ;-) But I guess that Emacs shows the matching closing bracket at the beginning of the line, which matches that single tuple.But you also need a closing bracket for the set of tuples like this: insert ... ( (v1, v2, v3), (v4, v5, v6), (v7, v8, v9) <= this is the bracket pair that Emacs shows as matching. ); <= this is the missing bracket. Cheers, Paul
On Thu, Oct 29, 2020 at 9:16 AM Paul Förster <paul.foerster@gmail.com> wrote:
But I guess that Emacs shows the matching closing bracket at the beginning of the line, which matches that single tuple. But you also need a closing bracket for the set of tuples like this:
insert ...
(
(v1, v2, v3),
(v4, v5, v6),
(v7, v8, v9) <= this is the bracket pair that Emacs shows as matching.
); <= this is the missing bracket.
Except that isn't valid INSERT statement syntax. You are missing "values" and there is no enclosing parens:
INSERT INTO tbl (i) VALUES (1), (2), (3); --this is perfectly valid
That said seeing the first few rows, in addition to the last few, would help.
David J.
On Thu, 29 Oct 2020, Adrian Klaver wrote: > Pretty sure the thinking is that the opening parenthesis is further upstream, > say around VALUES? Well, duh! Of course. I forgot to enclose all value rows. Mea culpa! Thanks, Rich
Hi David,
> On 29. Oct, 2020, at 17:21, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Thu, Oct 29, 2020 at 9:16 AM Paul Förster <paul.foerster@gmail.com> wrote:
> But I guess that Emacs shows the matching closing bracket at the beginning of the line, which matches that single
tuple.But you also need a closing bracket for the set of tuples like this:
>
> insert ...
> (
> (v1, v2, v3),
> (v4, v5, v6),
> (v7, v8, v9) <= this is the bracket pair that Emacs shows as matching.
> ); <= this is the missing bracket.
>
>
> Except that isn't valid INSERT statement syntax. You are missing "values" and there is no enclosing parens:
>
> INSERT INTO tbl (i) VALUES (1), (2), (3); --this is perfectly valid
>
> That said seeing the first few rows, in addition to the last few, would help.
sorry, I didn't check. My point was that there is a mismatch between a closing parent and an initial opening parent
somewhereeven if Emacs shows the last parent as matching the last opening parent.
So, you are right of course.
postgres=# create table t1(v1 int, v2 int, v3 int);
CREATE TABLE
postgres=# insert into t1(v1, v2, v3)
values
(1, 2, 3),
(4, 5, 6),
(7, 8, 9);
INSERT 0 3
Cheers,
Paul
On Thu, 29 Oct 2020, Paul Förster wrote:
> insert ...
> (
> (v1, v2, v3),
> (v4, v5, v6),
> (v7, v8, v9) <= this is the bracket pair that Emacs shows as matching.
> ); <= this is the missing bracket.
Paul/Adrian/Tom:
First thing I did was look at the postgres 12 manual. On page 155 I see:
You can insert multiple rows in a single command:
INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99);
Which is what I have. There are no extra parentheses enclosing multiple rows
of VALUES. But, adding them makes no difference: same error reported.
Thanks,
Rich
On Thu, 29 Oct 2020, David G. Johnston wrote: > That said seeing the first few rows, in addition to the last few, would > help. David, insert into organizations (org_nbr,org_name,org_url,org_email,industry,status,comment) values (1,'Tidewater Contractors Inc',null,null,'Mining','Opportunity','GEN12A'), (2,'All Rock LLC','www.allrockllc.com',null,'Mining','Opportunity','GEN12A'), ... Thanks, Rich
On Thu, Oct 29, 2020 at 9:37 AM Rich Shepard <rshepard@appl-ecosys.com> wrote:
On Thu, 29 Oct 2020, David G. Johnston wrote:
> That said seeing the first few rows, in addition to the last few, would
> help.
David,
insert into organizations (org_nbr,org_name,org_url,org_email,industry,status,comment) values
(1,'Tidewater Contractors Inc',null,null,'Mining','Opportunity','GEN12A'),
(2,'All Rock LLC','www.allrockllc.com',null,'Mining','Opportunity','GEN12A'),
...
As your general syntax seems correct I would suspect an issue in the data values - like having a single quote in an organization name that isn't properly escaped (doubled). I'd first make sure insert one record works then begin bisecting your values, only inserting subsets at a time, until you narrow down the offending record.
David J.
On 10/29/20 9:35 AM, Rich Shepard wrote:
> On Thu, 29 Oct 2020, Paul Förster wrote:
>
>> insert ...
>> (
>> (v1, v2, v3),
>> (v4, v5, v6),
>> (v7, v8, v9) <= this is the bracket pair that Emacs shows as
>> matching.
>> ); <= this is the missing bracket.
>
> Paul/Adrian/Tom:
>
> First thing I did was look at the postgres 12 manual. On page 155 I see:
>
> You can insert multiple rows in a single command:
> INSERT INTO products (product_no, name, price) VALUES
> (1, 'Cheese', 9.99),
> (2, 'Bread', 1.99),
> (3, 'Milk', 2.99);
>
> Which is what I have. There are no extra parentheses enclosing multiple
> rows
> of VALUES. But, adding them makes no difference: same error reported.
If you did something like:
INSERT INTO products (product_no, name, price) VALUES
(
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99)
);
then you should have gotten a different error. Something like:
ERROR: column "product_no" is of type integer but expression is of type
record
I'm going to say the issue is more like what David posted, an escaping
problem in the data. Not sure how many rows you are dealing with, but it
might be helpful to break them down into smaller batches to isolate the
problem.
>
> Thanks,
>
> Rich
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Thu, 29 Oct 2020, David G. Johnston wrote: > As your general syntax seems correct I would suspect an issue in the data > values - like having a single quote in an organization name that isn't > properly escaped (doubled). I'd first make sure insert one record works > then begin bisecting your values, only inserting subsets at a time, until > you narrow down the offending record. David, I checked and repaired all single apostrophe's with doubled apostrophies. Postgres found other syntax errors (period rather than comma; missing comma) but didn't reject single quotes as apostrophes within a string. Also, there are no strings in this table that would have apostrophes. Thanks, Rich
On Thu, 29 Oct 2020, Adrian Klaver wrote: > INSERT INTO products (product_no, name, price) VALUES > ( > (1, 'Cheese', 9.99), > (2, 'Bread', 1.99), > (3, 'Milk', 2.99) > ); > > then you should have gotten a different error. Something like: > > ERROR: column "product_no" is of type integer but expression is of type > record Adrian, Tried that but the error was the same: the closing semi-colon. > I'm going to say the issue is more like what David posted, an escaping > problem in the data. Not sure how many rows you are dealing with, but it > might be helpful to break them down into smaller batches to isolate the > problem. Yep, that's what I'll do. Thanks, Rich
On 10/29/20 9:59 AM, Rich Shepard wrote: > On Thu, 29 Oct 2020, Adrian Klaver wrote: > >> INSERT INTO products (product_no, name, price) VALUES >> ( >> (1, 'Cheese', 9.99), >> (2, 'Bread', 1.99), >> (3, 'Milk', 2.99) >> ); >> >> then you should have gotten a different error. Something like: >> >> ERROR: column "product_no" is of type integer but expression is of >> type record > > Adrian, > > Tried that but the error was the same: the closing semi-colon. It should not have been the same, so there is something else amiss. > >> I'm going to say the issue is more like what David posted, an escaping >> problem in the data. Not sure how many rows you are dealing with, but it >> might be helpful to break them down into smaller batches to isolate the >> problem. > > Yep, that's what I'll do. > > Thanks, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, 29 Oct 2020, Rich Shepard wrote:
> psql:organizations.sql:1926: ERROR: syntax error at or near ";"
> LINE 1925: ...m',null,'Port','Opportunity',null);
^
> I'm not seeing why that's an error. All previous rows terminate with a comma
> and I don't know where else to look for the reason. What am I missing
> seeing?
Rob/Paul/David/Tom/Adrian:
Found the problem in line 26 of 1925 rows: a non-null column had 'null'
entered rather than one of the allowed values. That row was in the original
database and I've no idea when I might have changed that.
Why postgres didn't highlight that until I had only a 50-line .sql file I
don't know. But, when bifircating the original file into smaller pieces and
I got down to 50 lines postgres showed me exactly what the error was:
psql:orgs-1.sql:50: ERROR: null value in column "industry" violates
not-null constraint.
Does this happen in newer versions than the 12.2 installed here?
Whew!
Stay well all ... and VOTE!
Rich
Rich Shepard <rshepard@appl-ecosys.com> writes:
> Found the problem in line 26 of 1925 rows: a non-null column had 'null'
> entered rather than one of the allowed values. That row was in the original
> database and I've no idea when I might have changed that.
> Why postgres didn't highlight that until I had only a 50-line .sql file I
> don't know. But, when bifircating the original file into smaller pieces and
> I got down to 50 lines postgres showed me exactly what the error was:
> psql:orgs-1.sql:50: ERROR: null value in column "industry" violates
> not-null constraint.
> Does this happen in newer versions than the 12.2 installed here?
That seems a bit odd. You're doing this in psql? Can you show
an exact example where the error report goes missing?
regards, tom lane
On Thu, Oct 29, 2020, 10:39 Rich Shepard <rshepard@appl-ecosys.com> wrote:
On Thu, 29 Oct 2020, Rich Shepard wrote:
> psql:organizations.sql:1926: ERROR: syntax error at or near ";"
> LINE 1925: ...m',null,'Port','Opportunity',null);
^
> I'm not seeing why that's an error. All previous rows terminate with a comma
> and I don't know where else to look for the reason. What am I missing
> seeing?
Rob/Paul/David/Tom/Adrian:
Found the problem in line 26 of 1925 rows: a non-null column had 'null'
entered rather than one of the allowed values. That row was in the original
database and I've no idea when I might have changed that.
Why postgres didn't highlight that until I had only a 50-line .sql file I
don't know. But, when bifircating the original file into smaller pieces and
I got down to 50 lines postgres showed me exactly what the error was:
psql:orgs-1.sql:50: ERROR: null value in column "industry" violates
not-null constraint.
Does this happen in newer versions than the 12.2 installed here?
I think you found a second problem...covered up by the first, which prevented the insert from even progressing to the point where a constraint violation could be encountered. I.e., your original error is syntax.
David J.
On 10/29/20 10:39 AM, Rich Shepard wrote: > On Thu, 29 Oct 2020, Rich Shepard wrote: > >> psql:organizations.sql:1926: ERROR: syntax error at or near ";" >> LINE 1925: ...m',null,'Port','Opportunity',null); > ^ >> I'm not seeing why that's an error. All previous rows terminate with a >> comma >> and I don't know where else to look for the reason. What am I missing >> seeing? > > Rob/Paul/David/Tom/Adrian: > > Found the problem in line 26 of 1925 rows: a non-null column had 'null' > entered rather than one of the allowed values. That row was in the original > database and I've no idea when I might have changed that. > > Why postgres didn't highlight that until I had only a 50-line .sql file I > don't know. But, when bifircating the original file into smaller pieces and > I got down to 50 lines postgres showed me exactly what the error was: > > psql:orgs-1.sql:50: ERROR: null value in column "industry" violates > not-null constraint. > > Does this happen in newer versions than the 12.2 installed here? Per Tom's response, I'm not sure how a constraint error got transformed into a syntax error? Is orgs-1.sql just the INSERT? How is that file being fed to psql? > > Whew! > > Stay well all ... and VOTE! > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, 29 Oct 2020, Adrian Klaver wrote: > Is orgs-1.sql just the INSERT? Yes. > How is that file being fed to psql? $ psql -d bustrac -f orgs-1.sql Rich
On 10/29/20 10:52 AM, Rich Shepard wrote: > On Thu, 29 Oct 2020, Adrian Klaver wrote: > >> Is orgs-1.sql just the INSERT? > > Yes. Is it just the 50 line version? If it is, what happens if you go back to original 1925 line version and correct the NULL issue in the line 26 and run it again? > >> How is that file being fed to psql? > > $ psql -d bustrac -f orgs-1.sql > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, 29 Oct 2020, Adrian Klaver wrote: > Is it just the 50 line version? Adrian, Nope. > If it is, what happens if you go back to original 1925 line version and > correct the NULL issue in the line 26 and run it again? I'm finding typos and column tranposition errors that I had not spotted when I checked my work. Now going through the file in small chunks and fixing what broke. Thanks, Rich
On 10/29/20 2:07 PM, Rich Shepard wrote: > On Thu, 29 Oct 2020, Adrian Klaver wrote: > >> Is it just the 50 line version? > > Adrian, > > Nope. > >> If it is, what happens if you go back to original 1925 line version >> and correct the NULL issue in the line 26 and run it again? > > I'm finding typos and column tranposition errors that I had not spotted > when > I checked my work. Now going through the file in small chunks and fixing > what broke. > > Thanks, > > Rich > > Would it be rude to suggest that you re-visit how that file was made? Seems you'll be in the same boat "next time".
On Thu, 29 Oct 2020, Rob Sargent wrote: > Would it be rude to suggest that you re-visit how that file was made? > Seems you'll be in the same boat "next time". Rob, Part of the files was exported from the old version of the database. New rows were from different text files, each with a different format, and all needing to be parsed with data belonging to four different tables. There won't be a 'next time' now that I've corrected the database structure. All new information (once I get these tables correctly loaded) will be one-at-a-time. Manipulating 2K datasets over long hours and multiple days led me to make errors I didn't catch at the time. Regards, Rich