Any downsides of using data type “text” for storing strings?

by

Generally, there is no downside to using text in terms of performance/memory. On the contrary: text is the optimum. Other types have more or less relevant downsides. text is literally the “preferred” type among string types in the Postgres type system, which can affect function or operator type resolution.

In particular, never use char(n) (alias for character(n)), unless you know what you are doing. char or character are just short for character(1), so all the same. The internal name is bpchar (stands fore “blank-padded character”). The type is only there for compatibility with old code and standards. It makes very little sense nowadays, wastes memory and is likely to cause trouble:

You can use varchar(n) with length modifier (alias for character varying(n)). But varchar(255) typically indicates a misunderstanding carried over from other RDBMS where it might be a local optimum for performance. In Postgres, the length modifier (255) has no special meaning and rarely makes sense.

Older versions caused various problems when trying to change the length modifier of varchar(n) later. Most of those have been alleviated in modern Postgres, but text or varchar (alias for character varying) without length specifier (and a CHECK constraint instead) never had any of these issues.

A CHECK constraint is just as fast and less likely to cause troubles with depending views, functions, FK constraints etc. which depend on the column type. And it can do more than just enforce a maximum character length – anything you can put into a boolean expression. See:

Finally, there is also "char" (with double-quotes): a 1-byte data type for a single ASCII letter used as cheap internal enumeration type.

I rarely use anything but text for character data in Postgres.

More Related Contents:

  1. Optimize GROUP BY query to retrieve latest row per user
  2. Auto increment table column
  3. How to concatenate columns in a Postgres SELECT?
  4. Time zone storage in data type “timestamp with time zone”
  5. Change PostgreSQL columns used in views
  6. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  7. Way to try multiple SELECTs till a result is available?
  8. Finding similar strings with PostgreSQL quickly
  9. Casting NULL type when updating multiple rows
  10. Best way to delete millions of rows by ID
  11. How much disk-space is needed to store a NULL value using postgresql DB?
  12. PostgreSQL ORDER BY issue – natural sort
  13. Postgres not using index when index scan is much better option
  14. How to store array or multiple values in one column
  15. How do I speed up counting rows in a PostgreSQL table?
  16. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  17. Fetch records that are non zero after the decimal point in PostgreSQL
  18. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  19. Is it possible to make a recursive SQL query?
  20. Column ‘mary’ does not exist
  21. Get top row(s) with highest value, with ties
  22. Delete parent if it’s not referenced by any other child
  23. Query that ignore the spaces
  24. SQL query to get most recent row for each instance of a given key
  25. Postgres constraint for unique datetime range
  26. Get values from first and last row per group
  27. Create PostgreSQL ROLE (user) if it doesn’t exist
  28. What is the most appropriate data type for storing an IP address in SQL server? [duplicate]
  29. PostgreSQL: Full Text Search – How to search partial words?
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment