16 ms·

> varchar (without the (n)) or text are similar, but without the length limit. If you insert the same string into the three field types they will take up exactl

• Sharlin 8y ago
  With Postgres, yes. Unless there's a real semantic need for a max length, you should always just use `text`.
  • moring 8y ago
    I sometimes use a limit (constraint or varchar(n)) to enforce an upper bound when the domain doesn't really demand one, but you'd rather get an error that a longer string in your database. Say, 100 characters for a login name or 1000 characters for a real person's name. Even if somebody actually shows up with a longer name, you know that your application will have other problems in that case (e.g. the UI explodes or something), but you prevent the occasional bug or other problem from writing megabytes into the name field, and causing follow-up problems.
  • fabian2k 8y ago
    Even then you should use text, just with a CHECK constraint. That's easier to change later than a varchar column.
    • radiowave 8y ago
      Yes. The most complicated migrations I've ever done have involved increasing the length of varchar(n) columns, because every dependent view needed to be dropped and recreated (and all of their dependents, and so on, recursively) within the migration transaction, in the right sequence. If I had just used the text datatype in the first place (along with a check constraint), these migrations would have been so much simpler.
      • skrebbel 8y ago
        I think is super weird that databases can have developer-hostile designs like this and still be considered "world class". If a web all framework had pitfalls like these, people would rageblog them into oblivion. I'm not trying to single out postgres because I have the impression that the same holds for all databases over some age. I mean, most of this article reads as a list of features postgres never should've had. If PHP can deprecate magic quotes, then why can't postgres do something similar? Why do the "string data type" docs contain anything other than the "text" type, outside a section titled "deprecated, do not use"? Why isn't there a strict mode that warns me when I'm trying to use Bad Ideas From The Nineties?
        • Daishiman 8y ago
          Every single technology that lives long enough has legacy features like these and antipatterns. When compared to other software of similar age, this is remarkable little cruft. Every tool that's powerful enough needs to have caveats. I agree it's a PITA, but this is orders of magnitude better than say, bash, C++, Linux, or PHP.
        • jrumbut 8y ago
          Strict mode sounds like an awesome idea. From time to time people release database schema linting scripts, those might help? They can't deprecate varchar and some other ill advised field types because they are required to comply with the SQL standard. The text thing, though, has never been a secret. Learning about it is sort of a coming of age ritual for postgres developers.
        • samus 7y ago
          Postgres' warts are nowhere as unpredictable and problematic as magic quotes. Apart from those things, most RDBMs are incredibly stable and ironed-out workhouses and are nowhere as controversial than PHP is.
      • gigatexal 8y ago
        You had views dependent on views? Object oriented database schemes sound problematic.
    • snuxoll 8y ago
      Even better, make a domain type encapsulating the constraint.
• GlennS 8y ago
  So, does this mean there is no longer a good reason for fixed-width text fields in any databases? Why not? Surely if it's variable-width, that means you can't guarantee it will be on the same page? And therefore there's a performance cost?
  • mytherin 8y ago
    Indeed, that is the reason character fields have traditionally had a length in RDBMS. However, many of these optimizations get broken by unicode support. Consider a CHAR(1) field. Without unicode support this field is always 1 byte, and therefore easy to store. With unicode support, however, the value that fits in this field is actually between 1-4 bytes. However, in many cases users will only store 1 byte in this field. Storing this field as a fixed length 4-byte field will in many cases result in a 2-4X increase in size and hence waste a lot of space. Thus even with CHAR(1) it is usually better to store the strings in a variable-length manner to avoid wasting space; and CHAR(1) is the absolute best case for this type of optimisation. This gets way worse with larger CHAR lengths. It's also worth noting that even if we could store a fixed one byte that that is likely still not the best way of storing a CHAR(1) field. A CHAR(1) field is typically used to store types or categories. Typically, a column like this would have few unique values (~2-8 values), which means we can reduce the size of these values to 1-4 bits using dictionary compression instead of a single byte, resulting in a size savings of factor 2-8X over storing a fixed one byte length.
    • bunderbunder 8y ago
      FWIW, I am guessing it's a rare case that you can be confident that all strings you'll want to store are of a specific length, but cannot predict the set of characters they may use. The use cases for CHAR instead of VARCHAR that I'm thinking of would be things like serial numbers. CHAR(1) seems even more specific; the only time I've ever seen that used is for some version of enumerations with mnemonic keys.
      • shawnz 8y ago
        I don't think the problem is so much about predicting the encoding that will be used. Let's say you KNOW that non-ascii characters will be used in the field, but only very occasionally. That means the field has to be 4x as wide as it would have been otherwise even if non-ascii characters will almost never be used.
• brobdingnagians 8y ago
  Yeah, I used to do the same thing, I had dozens of limited VARCHAR fields and sometimes I forgot to change them when my application validation logic changed, and then we'd get errors from length limits being hit. It was a very liberating day when I realized I didn't need to have the length limits in database and that it made no performance impact, I went through and removed them all promptly. Life has been better since.
• breakingcups 8y ago
  Until a troll uploads the Bee Movie script as a first name.
  • colanderman 8y ago
    Use `CHECK length(the_column) < some_maximum`. It has the benefit that you can change it without having to redefine views.
    • deleted 8y ago
      [deleted]
  • trollboy 8y ago
    Yes, why bother with software stack, just let them run their own INSERTs for registration.
  • komali2 8y ago
    Woah that's my go-to, I had no idea I wasnt the only one doing this.
    • jmt_ 8y ago
      The Bee Movie is truly the lorem ipsum of tomorrow.
  • sanderjd 8y ago
    ... or the Bee Movie as a base64 encoded string.
    • ralusek 8y ago
      But every time there's a "b" in the output string, the lyrics to All Star are added in plaintext.
      • knodi123 8y ago
        we call this beecryption
• spamizbad 8y ago
  This is one of my complaints with ORMs these days: they’re starting to feel a lot less sophisticated than the databases, and data, that they sit above.
  • malux85 8y ago
    The thing with an abstraction later is that it has to accommodate for the simplest component. In this case (the no need to specify the len on your varchars) is an implementation detail of Postgres, but the ORM still has to deal with weaker components. Also for those interested, there’s no difference between varchar and text in Postgres, I just use text fields now, since all length checking is done at the application level, and Postgres is the only DB I need to support
    • derefr 8y ago
      There are two ways to built abstractions: either by defining an interface that represents the lowest-common-denominator of functionality such that all the underlying implementations already implement it; or by defining the interface that you want to have, where some implementations can implement it directly, and others require a shim/polyfill implementation, or where the implementation “adapter” will just plain throw a NotImplementedError at your attempt to use the advanced features of the interface. Personally, I prefer the latter. But it seems like in the ORM space the latter approach is nowhere to be found. Have you ever seen an ORM that allows you to fluently deal with errors raised by triggers? DB-side computed columns [by storing to a table but querying from a related view]? Where the ORM’s “objects” expose an OOP interface to calling DB-side stored procedures that take that row-type as an input? (Or, even more crazily, where you could define a stored procedure in an abstract fluent AST against the object, and have it registered with the database? Or how about exposing DB-side BLOB handles as native-to-your-runtime IO streams, that could be fed into any runtime function that expects such? There’s really a lot you could do, that’s still abstract to the SQL level (rather than specific to a particular database) if you were okay with some implementations of your interface not supporting every feature.
      • blattimwind 8y ago
• sanderjd 8y ago
  There are other advantages of setting (large) limits that are appropriate to your data model, at least if you take data from untrusted users. You'd be surprised by the kinds of things users will put into truly unlimited fields... But yeah, there's no reason to try to figure out whether 32 characters or 64 characters would be better. But a title field probably never needs to be bigger than a couple thousand characters, for instance.
  • groestl 8y ago
    That should be rejected at the API level already. It's possible to do that at the DB layer as well, but who's to say that there isn't another class of users that has higher limits.
    • sanderjd 8y ago
      To the first part: sure, if you're confident a single API service will be the only thing inserting into the DB, then you can skip the DB level limit. I think you may as well add it, since you've already put thought into what you want the API-level limit to be, so why not stick that same value in one more place. To the second part: that way lies madness. It's true that you don't know all your future use cases, but it isn't a good reason to leave things unrestricted. It is much easier to increase the limit than to lower it (because you are likely to be deleting data if you lower a limit, which is a tough pill to swallow).
      • _Codemonkeyism 8y ago
        Totally agree, especially with larger companies there are integrations to ERP, data ware houses, partners etc. and then it's often a good idea to have communicated lengths on your domain data (title, email, ...).
      • groestl 8y ago
        In case more than one service writes to the same table, either both should be under control of the same organization, or a stored procedure layer should be introduced for the external org (there will probably be more things to check that just the limit). The phrase "untrusted users" is concerning in GP's post. For users of older postgres deployments (<9.2) it's worth noting that changing the limit on varchar is not free, it triggers a table rewrite.
• cyberpanther 8y ago
  Charfield in django is varchar so while there is no performance difference vs textfield, it does put limits on your inputs. This is most likely a good security measure. Unbounded inputs can be found and exploited.
• SigmundA 8y ago
  Does Postgresql have index size limits? I know in SQL server one thing that can bite you using varchar(max) is that if later you want to index it, it can fail if anything is over 900 bytes as that is the index size limit. Its a run-time error too so the index may get created no problem then later someone tries to insert a >900 bytes value and it throws.
  • volkadav 8y ago
    roughly 2700 bytes, from a bit of googling. (off the top of my head, the most likely use case for 2+kb text fields would be things you would be using pg's full text index on anyway, not the usual btree type indexes as you'd want to speed up a join or whatever.)
    • SigmundA 8y ago
      I agree, but sometimes its not something you think about and when every says just use varchar(max)/text then later on you want to speed it up with a btree, oops. IMO its better to set the length if it might be indexed to let everyone know ahead of time not to go over. I am not that familiar with PG full text index types, but in SQL server the full text index doesn't really work well with exact/begins with searches like the b-tree so your kinda stuck in that situation. With SQL server I like to stick with varchar(255) as it gives head room for unicode indexing and at one point there where some internal optimizations for less <=255 due to the legacy max being 255, not sure anymore. Beyond 255 its much less likely to need a btree and becomes more of a full text issue.
  • dfjhdkjfhsdkf 8y ago
    I did not know about this limitation in SQL Server, thanks!
  • tracker1 8y ago
    IIRC you can still set the index, it's just that you can't do matching if the match/content is over the length and it can still do begins with matching. That said, I still don't get why they didn't just change the behavior for TEXT/NTEXT vs adding (MAX) options. edit: I am mistaken, when setting an index, the index max is now the insert limit (from response below).
• stareatgoats 8y ago
  Coming from other database systems I found this intriguing, and did some googling. Turns out there are historical reasons that PG has functionally overlapping text and varchar(n) datatypes, where text was the original still the best datatype as detailed in this thread, whereas varchar was added for ANSI compatibility, and does not enjoy the same system support (changing length is a pain compared to other engines). PG is going to be the engine of choice for my next project, so I'm happy to learn about this quirk compared to other engines. Which doesn't mean I'm all that happy about it. I'm really in agreement with this post from 2010 (still current AFAICT) that wishes the support for varchar(n) would be better, for readability and cross-engine compatibility nothing else [0] [0] https://www.postgresonline.com/journal/archives/154-In-Defense-of-varcharx.html https://www.postgresonline.com/journal/archives/154-In-Defen...
  • tracker1 8y ago
    For contrast, MS SQL Server has `NVARCHAR(MAX)`. It's generally bugged me with MS-SQL that they added `NVARCHAR(MAX)` rather than just changing the behavior of `TEXT`
    • da_chicken 8y ago
      It's because text and ntext are extremely old LOB types, and they support SQL statements like WRITETEXT and UPDATETEXT that other statements don't. They also can't be indexed, don't support functions like LEFT(), REPLACE(), etc. Text and ntext behave wildly differently than char and varchar while varchar(max) and nvarchar(max) are almost the same. Changing that functionality would cause problems. Further, neither text nor ntext are ISO standard data types. It's the same reason that SQL Server has datetime2 and Oracle has varchar2 (the latter of which is only now being merged into varchar). That's why float, real, and money still exist even though numeric/decimal was created. They behave differently and in ways that are not exactly backwards compatible. In order to prevent your customers from having to rewrite their application just because you updated your data types, they chose to add new data types.
• orf 8y ago
  I’m pretty sure Django doesn’t actually create a limited char field that way, it merely adds validation. For exactly this reason.
  • rrauenza 8y ago
    It does pass the limit down. I've just verified my schema that has a varchar(128). One of the reasons given in the bug for it being difficult to fix is down in the depths it assumes it is a varchar() -- with parentheses. Here's the relevant comment: https://code.djangoproject.com/ticket/14094#comment:13 https://code.djangoproject.com/ticket/14094#comment:13
• knodi123 8y ago
  > I try to guess in my head what is the best length for this field to optimize for performance, but sounds like using unlimited length (textfield) field doesn't make any difference. That used to be a good idea. I guess it's fallen the way of defragging, or "parking" your hard drive.
• i_s 8y ago
  That's strange. In SQL Server, the size of the text columns impacts performance [0] by changing the amount of memory that is allocated to run queries. [0] - https://sqlperformance.com/2017/06/sql-plan/performance-myths-oversizing-strings https://sqlperformance.com/2017/06/sql-plan/performance-myth...
  • ben509 8y ago
    As the article points out, that's not normally the case in SQL Server, it's that there are certain steps like sorting that require blocks of preallocated memory. SQL Server is probably like that because varchar(max) is relatively recent; I'd expect as their support for it improves that issue will go away.
• yellowapple 8y ago
  I wonder if the same holds true for SQL Server? Data truncation errors bite us in the ass all the time where I'm at.
• hawaiian 8y ago
  >Seriously ? everytime I create a django charfield I try to guess in my head what is the best length for this field to optimize for performance, but sounds like using unlimited length (textfield) field doesn't make any difference. Good news Pretty much, except if you're using ModelForms you'll end up rendering a different element by default, and the admin (if you use it) will be different. Not really a problem with DRF without the browsable API.
  • haihaibye 8y ago
    You can change the element for a field in widgets in the Meta class of a form.