[Bug]: ERROR: value too long for type character varying(255)

[udf2457]

[Bug]: ERROR: value too long for type character varying(255)

[kesselb]

We cut the values for a while now: #26755

The field calendarobjects_props.value has been created as varchar(255) since the start.

Does pqsql do not enforce a limit?

[kesselb]

As much as I like the idea of only supporting one database, it's not planned, and we deal with the challenges as best as possible (e.g. chunking for in clauses with more than 1000 values).

Moreover, there are many aspects to consider. For example, our database layer also does not support vendor-specific queries: doctrine/dbal#2972.

We have a limit schema and code-wise, and there are no rows in your table with value > 255.

What are we supposed to do?

[udf2457]

Does pqsql do not enforce a limit?

Not sure how I would go about determining that, since....

select * from oc_calendarobjects_props where length(value) > 255;

Returns zero rows.

But obviously it seems to still get dumped when using pg_dump becuase the pg_restore fails.

So maybe the enforcement is silent ? The pg docs are silent on the subject.

[joshtrichards]

What PostgreSQL version(s) are involved here?

If you do an SQL style dump (i.e. w/o using the -Fc parameter), what does that row's value column actually look like?

The restore shouldn't exceed the length since the dump presumably shouldn't either.

You should not be using varchar with Postgresql, ESPECIALLY varchar(255) !! It is well documented to just use text:

That same reference also states when one might want to use it. It's not a black and white matter. Also, keep in mind we support multiple databases so there are other factors.

[udf2457]

What PostgreSQL version(s) are involved here?

Export from 14, import to 16

That same reference also states when one might want to use it.

It also shows that if you still want a limit, using a check constraint on text is the way to go.

keep in mind we support multiple databases

I don't buy that argument.

People who think "generic" or "portable" schemas exist and don't make use of database specific features usually end up being the same people who complain the database is "slow" and then go introducing an over-complicated caching layer in-between because that's what they think they need. Its demonstrated time after time.

Make use of the features the developers of databases have made available for you. Whether it be database-specific indexes or anything else. This is perhaps particularly the case with postgres that has a great deal of features that people have put a great deal of time and effort into designing and refining to make postgres one of the best open-source databases out there.

A database is not a generic term for a dumb black-box.

[joshtrichards]

That's all great, but since things are already <255 you have a mystery here at either the source or the receiving end.

Did you try looking at the data itself or doing an SQL style dump as I suggested?

Export from 14, import to 16

What versions, precisely?

This may need to be reported to PostgreSQL (depending) or it may already be addressed.