PostgreSQL ERROR: INSERT has more target columns than expressions, when it doesn't

Question

So I'm starting with this...

SELECT * FROM parts_finishing;

...I get this...

id, id_part, id_finish, id_metal, id_description, date, inside_hours_k, inside_rate, outside_material

(0 rows)

...so everything looks fine so far so I do this...

INSERT INTO parts_finishing 
(
 id_part, id_finish, id_metal, id_description, 
 date, inside_hours_k, inside_rate, outside_material
) VALUES (
('1013', '6', '30', '1', NOW(), '0', '0', '22.43'), 
('1013', '6', '30', '2', NOW(), '0', '0', '32.45'));

...and I get...

ERROR: INSERT has more target columns than expressions

Now I've done a few things like ensuring numbers aren't in quotes, are in quotes (would love a table guide to that in regards to integers, numeric types, etc) after I obviously counted the number of column names and values being inserted. I also tried making sure that all the commas are commas...really at a loss here. There are no other columns except for id which is the bigserial primary key.

wildplasser · Accepted Answer · 2021-04-13 15:45:26Z

87

Remove the extra () :

INSERT INTO parts_finishing 
(
 id_part, id_finish, id_metal, id_description, 
 date, inside_hours_k, inside_rate, outside_material
) VALUES 
  ('1013', '6', '30', '1', NOW(), '0', '0', '22.43')
, ('1013', '6', '30', '2', NOW(), '0', '0', '32.45')
  ;

the (..., ...) in Postgres is the syntax for a tuple literal; The extra set of ( ) would create a tuple of tuples, which makes no sense.

Also: for numeric literals you don't want the quotes:

(1013, 6, 30, 1, NOW(), 0, 0, 22.43)
, ...

, assuming all these types are numerical.

edited Apr 13, 2021 at 15:45

answered Dec 24, 2014 at 15:48

wildplasser

44.4k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrea Scarafoni Over a year ago

dDBeaver exports data with these extra parentesis

JosephDoggie Over a year ago

Such a simple thing, but it seems like the () should be a valid syntax... oh well, glad I fixed my very similar problem.

wildplasser Over a year ago

The extra () are valid syntax. Only the semantics are different.

erikrunia · Accepted Answer · 2018-08-01 17:59:03Z

38

I had a similar problem when using SQL string composition with psycopg2 in Python, but the problem was slightly different. I was missing a comma after one of the fields.

INSERT INTO parts_finishing
(id_part, id_finish, id_metal)
VALUES (
    %(id_part)s <-------------------- missing comma
    %(id_finish)s,
    %(id_metal)s
);

This caused psycopg2 to yield this error:

ERROR: INSERT has more target columns than expressions.

edited Aug 1, 2018 at 17:59

erikrunia

2,4751 gold badge18 silver badges28 bronze badges

answered Aug 1, 2018 at 16:57

2dor

5285 silver badges10 bronze badges

Comments

Christophe Roussy · Accepted Answer · 2018-12-17 17:49:12Z

8

This happened to me in a large insert, everything was ok (comma-wise), it took me a while to notice I was inserting in the wrong table of course the DB does not know your intentions. Copy-paste is the root of all evil ... :-)

answered Dec 17, 2018 at 17:49

Christophe Roussy

17.2k5 gold badges94 silver badges87 bronze badges

Comments

Hari Bharathi · Accepted Answer · 2019-08-20 13:19:01Z

6

I faced the same issue as well.It will be raised, when the count of columns given and column values given is mismatched.

answered Aug 20, 2019 at 13:19

Hari Bharathi

4515 silver badges8 bronze badges

Comments

Mr Coder · Accepted Answer · 2020-05-09 09:19:43Z

I have the same error on express js with PostgreSQL

I Solved it. This is my answer.

error fire at the time of inserting record.

error occurred due to invalid column name with values passing

error: INSERT has more target columns than expressions

ERROR : error: INSERT has more target columns than expressions name: 'error', length: 116, severity: 'ERROR', code: '42601', detail: undefined, hint: undefined, position: '294', internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'analyze.c', line: '945',

here is my code dome

INSERT INTO student(
  first_name, last_name, email, phone
) 
VALUES 
  ($1, $2, $3, $4), 
values 
  : [ first_name, 
  last_name, 
  email, 
  phone ]

I’m confused by this answer. Are you saying you are currently having this problem? Or are you posting this code as a proposed solution?

Keshav Koirala · Accepted Answer · 2022-12-04 07:14:07Z

-3

IN my case there was syntax error in sub query.

edited Dec 4, 2022 at 7:14

answered Dec 4, 2022 at 7:11

Keshav Koirala

11 bronze badge

Collectives™ on Stack Overflow

PostgreSQL ERROR: INSERT has more target columns than expressions, when it doesn't

6 Answers 6

3 Comments

Comments

Comments

Comments

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

Comments

Comments

3 Comments

Comments

Linked

Related