syntax error but command executes anyway?

From: Kevin Murphy <murphy(at)genome(dot)chop(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: syntax error but command executes anyway?
Date: 2004-06-19 13:14:19
Message-ID: 200406191314.19924.murphy@genome.chop.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Fitzpatrick 2004-06-19 15:32:03 INSERT BEFORE Trigger
Previous Message Daniel Heiserer 2004-06-19 12:26:08 administering files, data type "file"