CODEWITHSUNDEEP
postgresql
John
John

Reputation: 13719

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

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.

Upvotes: 68

Views: 133262

Answers (6)

Keshav Koirala
Keshav Koirala

Reputation: 1

IN my case there was syntax error in sub query.

Upvotes: -3

wildplasser
wildplasser

Reputation: 44240

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.

Upvotes: 85

Mr Coder
Mr Coder

Reputation: 523

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 ]

Upvotes: 1

Hari Bharathi
Hari Bharathi

Reputation: 441

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

Upvotes: 6

Christophe Roussy
Christophe Roussy

Reputation: 16999

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 ... :-)

Upvotes: 8

2dor
2dor

Reputation: 528

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.

Upvotes: 38

Related Questions