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)
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" |