Fresh eyeballs needed: input into error

On 4/26/22 2:31 PM, Rich Shepard wrote:

I'm getting a syntax error with an input into statement and I cannot see
the
cause.

A MWE and the result (N.B.: line one wrapped for readability; it ends with
'values'):
insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment)
values (6000,'No','Name',null,404,1,null,null,null,null,null,null,null);

2: ERROR:  syntax error at end of input
LINE 2: (
         ^
What do I keep missing?

I'm guessing some sort of hidden character.

What client are you using to run this?

Where is the query string coming from?

TIA,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, 26 Apr 2022, Adrian Klaver wrote:

I'm guessing some sort of hidden character.

Adrian,

The 'insert into ...' string is a template I've been using for years. It's
worked earlier today, too.

What client are you using to run this?

psql in a linux virtual terminal.

Where is the query string coming from?

I type each row to add to the table by hand.

Rich

On Tue, 26 Apr 2022, Adrian Klaver wrote:

I'm guessing some sort of hidden character.

Adrian,

Oh, ... forgot to mention in my response that the MWE values were added to
the template in emacs while I get the same error using psql -d <database> -f
<filename> in a v.t. or entering 'insert into people values (...)' within
the psql shell.

All throw the same error.

Rich

On Tue, Apr 26, 2022 at 2:54 PM Rich Shepard <rshepard@appl-ecosys.com>
wrote:

On Tue, 26 Apr 2022, Adrian Klaver wrote:

I'm guessing some sort of hidden character.

Adrian,

Oh, ... forgot to mention in my response that the MWE values were added to
the template in emacs while I get the same error using psql -d <database>
-f
<filename> in a v.t. or entering 'insert into people values (...)' within
the psql shell.

All throw the same error.

What version of PostgreSQL?

Testing on HEAD (and by inspection) nothing you've shown us provokes a
syntax error in PostgreSQL.

regression=> insert into people
(person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
regression(>
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment)
values
regression->
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR: relation "people" does not exist
LINE 1: insert into people (person_nbr,lname,fname,job_title,company...

No syntax error, that it didn't find the table is expected.

Copied and pasted right from your email.

David J.

Rich Shepard <rshepard@appl-ecosys.com> writes:

I'm getting a syntax error with an input into statement and I cannot see the
cause.

A MWE and the result (N.B.: line one wrapped for readability; it ends with
'values'):
insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);

2: ERROR: syntax error at end of input
LINE 2: (
^
What do I keep missing?

Copying-and-pasting from your mail gets me

ERROR: relation "people" does not exist
LINE 1: insert into people (person_nbr,lname,fname,job_title,company...
^

so there's nothing wrong with the syntax as-presented. I agree with
Adrian's guess about invisible characters in your input file;
perhaps a control-D or ASCII NUL would produce that symptom.

regards, tom lane

On 4/26/22 2:53 PM, Rich Shepard wrote:

On Tue, 26 Apr 2022, Adrian Klaver wrote:

I'm guessing some sort of hidden character.

Adrian,

Oh, ... forgot to mention in my response that the MWE values were added to
the template in emacs while I get the same error using psql -d
<database> -f
<filename> in a v.t. or entering 'insert into people values (...)' within
the psql shell.

All throw the same error.

I cannot replicate. You are going to have to scan the string on your
end, I am still guessing an odd character.

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, 26 Apr 2022, David G. Johnston wrote:

What version of PostgreSQL?

David,

12.7

Testing on HEAD (and by inspection) nothing you've shown us provokes a
syntax error in PostgreSQL.

regression=> insert into people
(person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
regression(>
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment)
values
regression->
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR: relation "people" does not exist
LINE 1: insert into people (person_nbr,lname,fname,job_title,company...

No syntax error, that it didn't find the table is expected.

Well, as I wrote, that syntax has worked for years, including earlier this
morning. Perhaps it will work again tomorrow when I log back in to the
workstation.

Thanks,

Rich

On Tue, 26 Apr 2022, Tom Lane wrote:

so there's nothing wrong with the syntax as-presented. I agree with
Adrian's guess about invisible characters in your input file; perhaps a
control-D or ASCII NUL would produce that symptom.

tom,

It's probably a transient glitch that will go away after I log out.

Thanks,

Rich

On 4/26/22 3:08 PM, Rich Shepard wrote:

On Tue, 26 Apr 2022, David G. Johnston wrote:

What version of PostgreSQL?

David,

12.7

Testing on HEAD (and by inspection) nothing you've shown us provokes a
syntax error in PostgreSQL.

regression=> insert into people
(person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
regression(>
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment)
values
regression->
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR:  relation "people" does not exist
LINE 1: insert into people (person_nbr,lname,fname,job_title,company...

No syntax error, that it didn't find the table is expected.

Well, as I wrote, that syntax has worked for years, including earlier this
morning. Perhaps it will work again tomorrow when I log back in to the
workstation.

It is not the syntax, somewhere since this morning you introduced a
hidden character into the string.

Thanks,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, 26 Apr 2022, Rich Shepard wrote:

It's probably a transient glitch that will go away after I log out.

Yep. I logged out and back in just now. The insert script worked as it
always had before and both rows were added to the table.

I've seen these sort of hang-ups before and didn't want to log out, back
in, and recreate the working environment. Had I done so I'd have saved a lot
of time.

Thanks for your inputs, everyone,

Rich

On Tue, Apr 26, 2022 at 06:09:42PM -0700, Rich Shepard wrote:

On Tue, 26 Apr 2022, Rich Shepard wrote:

It's probably a transient glitch that will go away after I log out.

Yep. I logged out and back in just now. The insert script worked as it
always had before and both rows were added to the table.

I've seen these sort of hang-ups before and didn't want to log out, back
in, and recreate the working environment. Had I done so I'd have saved a lot
of time.

Thanks for your inputs, everyone,

I am curious what OS psql was using that was fixed by a re-login?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

On 4/26/22 20:11, Bruce Momjian wrote:

On Tue, Apr 26, 2022 at 06:09:42PM -0700, Rich Shepard wrote:

On Tue, 26 Apr 2022, Rich Shepard wrote:

I am curious what OS psql was using that was fixed by a re-login?

Rich uses Slackware.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, 2022-04-26 at 14:31 -0700, Rich Shepard wrote:

I'm getting a syntax error with an input into statement and I cannot see the
cause.

A MWE and the result (N.B.: line one wrapped for readability; it ends with
'values'):
insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);

2: ERROR:  syntax error at end of input
LINE 2: (
          ^
What do I keep missing?

Another explanation is that there was already something in your query buffer
when you entered that statement, so that together it caused a syntactically
incorrect statement, something like (in psql):

test=> insert
test-> insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR: syntax error at or near "insert"
LINE 2: insert into people (person_nbr,lname,fname,job_title,company...
^

Yours,
Laurenz Albe

On Tue, 26 Apr 2022, Bruce Momjian wrote:

I am curious what OS psql was using that was fixed by a re-login?

Bruce,

This desktop's running Slackware64-14.2.

Regards,

Rich

On Wed, 27 Apr 2022, Laurenz Albe wrote:

test=> insert
test-> insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR: syntax error at or near "insert"
LINE 2: insert into people (person_nbr,lname,fname,job_title,company...
^

Laurenz,

From time to time I mis-type a character or make another error and psql
responds with a different prompt and no explicit explanation. I'll then type
C-c and get the default => prompt back. Your example is appreciated and may
very well have been the cause, but I kept trying at the default prompt.

I'll re-read the psql man page to better understand the different prompts.

Thank you,

Rich

On Apr 27, 2022, at 5:43 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Wed, 27 Apr 2022, Laurenz Albe wrote:

From time to time I mis-type a character or make another error and psql
responds with a different prompt and no explicit explanation. I'll then type
C-c and get the default => prompt back. Your example is appreciated and may
very well have been the cause, but I kept trying at the default prompt.

I'll re-read the psql man page to better understand the different prompts.

Thank you,

Rich

Have you tried \r instead of C-c?

On Wed, 27 Apr 2022, Rob Sargent wrote:

Have you tried \r instead of C-c?

Rob,

No, I haven't. I'll try it to reset the query buffer the next time I make a
mistake entering a command.

Thanks,

Rich

Rich:

On Wed, 27 Apr 2022 at 14:38, Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Tue, 26 Apr 2022, Bruce Momjian wrote:

I am curious what OS psql was using that was fixed by a re-login?

This desktop's running Slackware64-14.2.

Where you using cut&paste / selection or something similar? Lately
I've been getting weird errors on paste operations due to bracketed
paste, which are puzzling, It seems to have interactions with readline
and other stuff depending on where the cursor is and some other
things. Last I remembered when pasting some things to guile invisible
chars ( paste brackets ) where intercepted by its REPL leading to
bizarre errors. And sometimes after a relogin things are done a bit
different and make problem disappear.

Francisco Olarte.

On Wed, 27 Apr 2022, Francisco Olarte wrote:

Where you using cut&paste / selection or something similar? Lately I've
been getting weird errors on paste operations due to bracketed paste,
which are puzzling, It seems to have interactions with readline and other
stuff depending on where the cursor is and some other things. Last I
remembered when pasting some things to guile invisible chars ( paste
brackets ) where intercepted by its REPL leading to bizarre errors. And
sometimes after a relogin things are done a bit different and make problem
disappear.

Francisco,

Yes. I often use the trackball to highlight plain ASCII text, including the
enclosing '...', and paste it to the psql shell command line. Most of the
time it works. When psql presents prompt 2 instead of command success I
re-enter by re-keyboarding. That usually clears up the issue.

Thanks,

Rich