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=# \!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
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?
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 majordomo@postgresql.org so that your message can get through to the mailing list cleanly
If you would like to report an abuse of our service, such as a spam message, please . Если Вы хотите пожаловаться на содержимое этой страницы, пожалуйста .
