Multi-row insert: error at terminal row.

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&#39;,null,&#39;Port&#39;,&#39;Opportunity&#39;,null),
(2698,'Port of Portland','http://www.portofportland.com&#39;,null,&#39;Port&#39;,&#39;Opportunity&#39;,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&#39;,null,&#39;Port&#39;,&#39;Opportunity&#39;,null),
(2698,'Port of Portland','http://www.portofportland.com&#39;,null,&#39;Port&#39;,&#39;Opportunity&#39;,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&#39;,null,&#39;Port&#39;,&#39;Opportunity&#39;,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&#39;,null,&#39;Port&#39;,&#39;Opportunity&#39;,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&#39;,null,&#39;Port&#39;,&#39;Opportunity&#39;,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 somewhere even 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