473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

syntax error but command executes anyway?

Using PG 7.4.3 on Mac OS X 10.2.8, the following "insert into ... select ..."
statement completed and then announced a syntax error, which seems bizarre.

(Don't be confused by the fact that the two tables referred to
(public.identifiers and original.identifiers) have slightly different column
names.)

egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT INTO public.identifiers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identifiers;
egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT 0 1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql:15: ERROR:
syntax error at or near "sourcecode" at character 2

A fuller psql transcript showing table layouts is below.

What should I make of this?

Thanks,
Kevin Murphy

P.S. Full transcript:

egenome_dev=# \d original.identifiers
Table "original.identifiers"
Column | Type | Modifiers
------------+-----------------------+-----------
elementid | integer |
nameid | character varying(80) |
name | character varying(80) |
source | character varying(39) |
title | text |
sourcecode | character varying(2) |
Indexes:
"identifiers_elementid_idx" btree (elementid)
"identifiers_name_idx" btree (name)
"identifiers_nameid_idx" btree (nameid)
"identifiers_source_idx" btree (source)

egenome_dev=# select count(*) from original.identifiers;
count
---------
1685440
(1 row)

egenome_dev=# \d identifiers
Table "public.identifiers"
Column | Type | Modifiers
-------------+-----------------------+-----------
element_id | integer |
name | character varying(80) |
source | character varying(39) |
source_code | character varying(2) |
title | text |
Indexes:
"identifiers_multi1_idx" btree (name, source)
"identifiers_name_idx" btree (name)

[NOTE: the above indexes on original.identifiers are not the intended final
indexes; in fact, I had forgotten that they were there.]

egenome_dev=# truncate identifiers;
TRUNCATE TABLE

egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT INTO public.identifiers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identifiers;

egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT 0 1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql:15: ERROR:
syntax error at or near "sourcecode" at character 2

egenome_dev=# select count(*) from identifiers;
count
---------
1672036
(1 row)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
8 2195
On Sat, 2004-06-19 at 07:14, Kevin Murphy wrote:
Using PG 7.4.3 on Mac OS X 10.2.8, the following "insert into ... select ..."
statement completed and then announced a syntax error, which seems bizarre.
It's not actually inserting anything...
(Don't be confused by the fact that the two tables referred to
(public.identifiers and original.identifiers) have slightly different column
names.)

egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT INTO public.identifiers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identifiers;
egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT 0 1672036


This INSERT 0 part tells you it didn't actually insert anything.
Since I don't see any obvious error, I'm wondering if maybe there are
characters in the sql file that don't print that are causing this
problem. Try recreating the SQL by hand and see if you get the same
problem. Use the simplest editor on your system, like vi or notepad or
whatever.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2
On Sat, 2004-06-19 at 07:14, Kevin Murphy wrote:
Using PG 7.4.3 on Mac OS X 10.2.8, the following "insert into ... select ..."
statement completed and then announced a syntax error, which seems bizarre.
It's not actually inserting anything...
(Don't be confused by the fact that the two tables referred to
(public.identifiers and original.identifiers) have slightly different column
names.)

egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT INTO public.identifiers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identifiers;
egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT 0 1672036


This INSERT 0 part tells you it didn't actually insert anything.
Since I don't see any obvious error, I'm wondering if maybe there are
characters in the sql file that don't print that are causing this
problem. Try recreating the SQL by hand and see if you get the same
problem. Use the simplest editor on your system, like vi or notepad or
whatever.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3
Kevin Murphy <mu****@genome.chop.edu> writes:
What should I make of this? egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT INTO public.identifiers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identifiers; egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT 0 1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql:15: ERROR:
syntax error at or near "sourcecode" at character 2


It seems mighty suspicious that psql is reporting a syntax error at line
15 of the file when cat is only showing 9 lines. I suspect that the
insert you are showing us did execute, but then something further on
in the file is producing the syntax error.

I am wondering whether cat on OS X stops at embedded nulls, or something
stupid like that. It sure looks like there must be garbage in the
port_identifiers.sql file beyond what cat has printed here. What do you
see when you examine the file with other tools? (Try "od -c" if nothing
else springs to mind.)

It is possible that the problem is not entirely cat's fault but has
something to do with the way that psql's \! command invokes cat.
Does cat at the shell prompt produce the same output?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4
Kevin Murphy <mu****@genome.chop.edu> writes:
What should I make of this? egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT INTO public.identifiers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identifiers; egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT 0 1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql:15: ERROR:
syntax error at or near "sourcecode" at character 2


It seems mighty suspicious that psql is reporting a syntax error at line
15 of the file when cat is only showing 9 lines. I suspect that the
insert you are showing us did execute, but then something further on
in the file is producing the syntax error.

I am wondering whether cat on OS X stops at embedded nulls, or something
stupid like that. It sure looks like there must be garbage in the
port_identifiers.sql file beyond what cat has printed here. What do you
see when you examine the file with other tools? (Try "od -c" if nothing
else springs to mind.)

It is possible that the problem is not entirely cat's fault but has
something to do with the way that psql's \! command invokes cat.
Does cat at the shell prompt produce the same output?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #5
Scott,
egenome_dev=# \i
/Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT 0 1672036


This INSERT 0 part tells you it didn't actually insert anything.


Actually, that's not true. I didn't know what this number was until just now,
but I looked it up: for a single-row insert, it's the OID of the new row;
otherwise, it's 0. The 1672036, on the other hand, means that 1,672,036 rows
were inserted.

-Kevin Murphy

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #6
Scott,
egenome_dev=# \i
/Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql
INSERT 0 1672036


This INSERT 0 part tells you it didn't actually insert anything.


Actually, that's not true. I didn't know what this number was until just now,
but I looked it up: for a single-row insert, it's the OID of the new row;
otherwise, it's 0. The 1672036, on the other hand, means that 1,672,036 rows
were inserted.

-Kevin Murphy

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #7
On Saturday 19 June 2004 06:57 pm, Tom Lane wrote:
egenome_dev=# \i
/Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql INSERT 0
1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql:15:
ERROR: syntax error at or near "sourcecode" at character 2


It seems mighty suspicious that psql is reporting a syntax error at line
15 of the file when cat is only showing 9 lines. I suspect that the
insert you are showing us did execute, but then something further on
in the file is producing the syntax error.

I am wondering whether cat on OS X stops at embedded nulls, or something
stupid like that. It sure looks like there must be garbage in the
port_identifiers.sql file beyond what cat has printed here. What do you
see when you examine the file with other tools? (Try "od -c" if nothing
else springs to mind.)

It is possible that the problem is not entirely cat's fault but has
something to do with the way that psql's \! command invokes cat.
Does cat at the shell prompt produce the same output?

regards, tom lane


It is indeed very weird, since the script is not that long. I piped it
through 'od -a' to confirm.

However, I retract my complaint, since I can't reproduce it!

I had to take a bad DIMM out of this machine a few days ago; maybe I need to
run that memory test again. Still, it would seem remarkable if a memory
error could produce my initial results rather than a crash or hang. I am
also feeling like compiling PG again, since it was initially compiled with
that bad DIMM (but again, what would the odds be?)

Tom, as always, thanks for your mellow and rapid response to questions.

I'll let you know if this crops up again in a reproducible way.

-Kevin Murphy

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #8
On Saturday 19 June 2004 06:57 pm, Tom Lane wrote:
egenome_dev=# \i
/Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql INSERT 0
1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifiers.sql:15:
ERROR: syntax error at or near "sourcecode" at character 2


It seems mighty suspicious that psql is reporting a syntax error at line
15 of the file when cat is only showing 9 lines. I suspect that the
insert you are showing us did execute, but then something further on
in the file is producing the syntax error.

I am wondering whether cat on OS X stops at embedded nulls, or something
stupid like that. It sure looks like there must be garbage in the
port_identifiers.sql file beyond what cat has printed here. What do you
see when you examine the file with other tools? (Try "od -c" if nothing
else springs to mind.)

It is possible that the problem is not entirely cat's fault but has
something to do with the way that psql's \! command invokes cat.
Does cat at the shell prompt produce the same output?

regards, tom lane


It is indeed very weird, since the script is not that long. I piped it
through 'od -a' to confirm.

However, I retract my complaint, since I can't reproduce it!

I had to take a bad DIMM out of this machine a few days ago; maybe I need to
run that memory test again. Still, it would seem remarkable if a memory
error could produce my initial results rather than a crash or hang. I am
also feeling like compiling PG again, since it was initially compiled with
that bad DIMM (but again, what would the odds be?)

Tom, as always, thanks for your mellow and rapid response to questions.

I'll let you know if this crops up again in a reproducible way.

-Kevin Murphy

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: middletree | last post by:
I've seen posts here and elsewhere which read something along the lines of "PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind of subject line isn't descriptive, but I sure can...
2
by: micahstrasser | last post by:
I have been trying for days to send a command to the command prompt through the shell() function in vb.net. For some reason it is not working. Here is the code: Private Sub Button1_Click(ByVal...
0
by: Kevin Murphy | last post by:
Using PG 7.4.3 on Mac OS X 10.2.8, the following "insert into ... select ..." statement completed and then announced a syntax error, which seems bizarre. (Don't be confused by the fact that the...
10
by: Jim Underwood | last post by:
I am having a problem with my web page timng out while retrieving a long runnign report (90-120 seconds. I have tried modifying several settings in various places and cannot get it to run for more...
7
by: James | last post by:
Wrong syntax is shown below. What should be the delimiter before else? python -c 'if 1==1: print "yes"; else print "no"' James
669
by: Xah Lee | last post by:
in March, i posted a essay “What is Expressiveness in a Computer Language”, archived at: http://xahlee.org/perl-python/what_is_expresiveness.html I was informed then that there is a academic...
1
by: PI | last post by:
Hi guys, I need some urgent help here please: I am using an include_once() command to include a file that sits a level above the file view.php. when the code is run, I have an error as follows: ...
7
by: bryant | last post by:
Hi all. I am new to ASP and working in Expression Web. The following query displays the information I need in the gridview for a single record. SELECT "OE_HDR"."ORD_NO", "OE_HDR"."CUST_NAM",...
7
by: HSXWillH | last post by:
I have a field in a database that contains last names. In some of those names, like O'Brien and O'Connor, there is a ' symbol. I am using combo boxes on a form to build a form filter and in...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shllpp 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.