Hacker News Reader

Comment by nickjj | Hacker News Reader

In your main example video, you're using varchar(45) for your name fields.

What's your take on using varchar(X) vs having a text field with a char_length(X) constraint? Isn't the text approach considered more of a best practice nowadays?

View on HN

Yep, I tend to prefer using the text type in most cases (in Postgres in particular, both varchar and text have the same performance characteristics with varchar effectively being just the text type with a check constraint on the length). I'm not super strict about it though - it's more just a preference and there's some good arguments I've seen around 'signalling intent' by using fixed length text types vs "unlimited" length text types.

> In Postgres in particular, both varchar and text have the same performance characteristics with varchar effectively being just the text type with a check constraint on the length

Are both pretty much the same from a stand point of being able to change them in the future once they have a substantial amount of data saved?

For example if you have 1,000,000 rows of varchar(50) vs. text with a check_length(50). Which one wins if you want to decrease X from 50 to 25? Does the answer change if you want to increase X from 50 to 100?

This is with PG11 by the way if it matters.

Performance wise, there is no distinction in Postgres 11. As I understand it, internally varchar(N) is basically just text with a character limit. varchar(N) really only exists in Postgres these days for backwards compatibility, and the fact that it is ANSI SQL.

Which is why I still use this nomenclature for SQL

Would it be equally as performant to change a large table's varchar(50) field to a text field with a range constraint (min / max) vs. just changing the existing text's constraint to include a min? That type of use case might come up in the wild where you're like, "well... maybe zip codes shouldn't just have a max size, they should also have a min too".

Really just pulling at straws here to see if it's ever viable to use text / constraint when it sounds like varchar(N) might be the way to go? Even if it's only there for backwards compatibility, I must say varchar(50) is a lot friendlier to type than having to wire up a char_length constraint.

Postgres org recommends Text. I use a text domain defined with 2 constraints such as min chars 1 / max chars 255 Domains are great - a fast flexible and easy way to enforce scalar constraints including more complex checks such as regex - reusable across multiple columns and easy to change.

A major difference is that if you have a view which uses that varchar(50) column, you'll have to drop and recreate the view in the same transaction that you increase the length of the column. (And if that view has any dependent objects, you'll have to do the same for them.)

With the text type and check constraints, none of this is necessary.

The PostgresQL manual recommends using text over char or varchar unconditionally. I think that taking it straight from the source is the best approach here.

Can you provide a link to that recommendation?

There's pretty much no difference between those types, they are all stored exactly the same way. Varchar has a bit of extra cost for the length check, but that's OK when you want to enforce that limit.

I just took a look at the manual (version for 9.3 and 11) and saw no such recommendation for character or character varying types... seems like the manual should be agnostic to such choices outside of deprecation. It did mention that common assumptions of performance differences weren't real in the PostgreSQL implementation.

I think you're thinking of the Wiki rather than the manual: https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storag...

I generally agree with the advice in this section of the Wiki, but recognize that it's more opinionated... and less authoritative... than the manual.

I was indeed referring to the section on text types in the manual. But I admit that I somehow lumped char and varchar together in my head while reading that section a couple of days ago. So you shouldn't ever use char (and only char) unless you have very specific requirements. This means that yes, I didn't get it straight.

> varchar(45)

That's crashing with my full name

Reply on news.ycombinator.com