How to make postgres tell which column is causing an error

Question

While inserting data into a table which has many columns:

INSERT INTO MyTable ("name", ..100+ columns)
 VALUES ('Michel', ... 100+ values)

I made an error creating a specific value so PostgreSQL tells us:

ERROR:  value too long for type character varying(2)

I would like to avoid going through the whole table schema to guess which column is failing.

Is there a way to configure PostgreSQL so it tells us which column is causing the error?

Tim Biegeleisen · Accepted Answer · 2022-01-24 09:42:03Z

1

One quick way might be to query the information schema table for your database and look for character columns having a maximum width of 2 (to which your error is alluding):

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'MyTable' AND
      character_maximum_length = 2;

answered Jan 24, 2022 at 9:42

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

5 Comments

Noan Cloarec Over a year ago

Problem is there are more than a dozen columns with this specific type in my schema, can postgres specifically point out the failing column?

Tim Biegeleisen Over a year ago

...well if you can narrow down the problem from 100 to ~10 columns, it is an improvement. Next you have to just check your VALUES clause and see which string literal for those columns are wider than 2 characters.

Noan Cloarec Over a year ago

You under-estimated my laziness :D But it is a solution thank you

Tim Biegeleisen Over a year ago

I guess the bigger issue is that your table and insert have so many columns involved. Over the course of your life, most tables with which you deal probably won't have so many columns.

Noan Cloarec Over a year ago

It is definitely an issue, and it's not the first problem caused by this design. Unfortunately as programmers we are not always the ones to design the databases we are using.

Collectives™ on Stack Overflow

How to make postgres tell which column is causing an error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related