4 ms·

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) co

• nsainsbury 7y ago
  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.
  • nickjj 7y ago
    > 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.
    • Svip 7y ago
      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.
      • C1sc0cat 7y ago
        Which is why I still use this nomenclature for SQL
      • nickjj 7y ago
        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.
        • garyclarke27 7y ago
          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.
    • radiowave 7y ago
      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.
• gmueckl 7y ago
  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.
  • pgaddict 7y ago
    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.
  • sbuttgereit 7y ago
    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_storage 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.
    • gmueckl 7y ago
      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.
• akerro 7y ago
  > varchar(45) That's crashing with my full name