Thread: BUG #6095: Misleading error message: INSERT has more expressions than target columns
BUG #6095: Misleading error message: INSERT has more expressions than target columns
From
"Gavin Flower"
Date:
The following bug has been logged online: Bug reference: 6095 Logged by: Gavin Flower Email address: gavin.flower@archidevsys.co.nz PostgreSQL version: 9.1beta2 Operating system: x86_64 Linux Description: Misleading error message: INSERT has more expressions than target columns Details: Using pg 9.1beta2 compiled using gcc (GCC) 4.5.1 20100924 (Red Hat 4.5.1-4), on a 64 bit Linux system. In the 'INSERT INTO' construct: I think that psql should first check that the fields listed after the table name are valid, before checking that the number of columns specified is sufficient for the number of expressions. Due to my poor eyesight: it took me a few minutes before I realized that I had used a fullstop '.' rather than a comma ',' in the list of columns. There is neither a schema, nor a database, called 'id' - so I would have thought that pg should have been able to detect the error. DROP TABLE wage; CREATE TABLE wage ( id int PRIMARY KEY, pay numeric(5,2) ); INSERT INTO wage (id. pay) VALUES (1, 4.2); TABLE wage; //// output $ psql < bug.sql DROP TABLE NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "wage_pkey" for table "wage" CREATE TABLE ERROR: INSERT has more expressions than target columns LINE 2: VALUES (1, 4.2); ^ id | pay ----+----- (0 rows) $
Re: BUG #6095: Misleading error message: INSERT has more expressions than target columns
From
Tom Lane
Date:
"Gavin Flower" <gavin.flower@archidevsys.co.nz> writes: > In the 'INSERT INTO' construct: I think that psql should first check that > the fields listed after the table name are valid, before checking that the > number of columns specified is sufficient for the number of expressions. Hmm ... I think this would just move the set of unpleasant cases around. Usually I find it's better if we check the count first. For example, if you accidentally stick an extra value into the VALUES list, and we weren't checking the count first, you'd likely get weird invalid-input-value complaints that stem from matching subsequent input items to the wrong column. In such cases it saves a lot of time if we first point out the wrong number of values. > Due to my poor eyesight: it took me a few minutes before I realized that I > had used a fullstop '.' rather than a comma ',' in the list of columns. > There is neither a schema, nor a database, called 'id' - so I would have > thought that pg should have been able to detect the error. Actually, what you get if you change the number of VALUES columns is: ERROR: cannot assign to field "pay" of column "id" because its type integer is not a composite type LINE 1: INSERT INTO wage (id. pay) ^ This *is* valid syntax, if id is a composite-type column. regards, tom lane