PostgreSQL: Difference between text and varchar (character varying)

Question

What's the difference between the text data type and the character varying (varchar) data types?

According to the documentation

If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

and

In addition, PostgreSQL provides the text type, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well.

So what's the difference?

Answer 1

Generally, text columns are implementation-specific and non-standard. In different cases, depending on the database following a combination of one or more of the restrictions can exist such as: not sortable, not searchable and not indexable.

Refer to this video if you want to learn Data Type in SQL.

In Postgres, using the same C data structure all these data types (i.e. varchar, char and so on) are internally saved. 

In MySQL, the text column has restrictions on indexing and it’s also the specialized version of the BLOB.

Just these two examples can be extrapolated to the other RDBMS systems and should be reason enough to understand when to choose one type over the other.

Note: Don’t use TEXT as it is non-standard and proprietary. If you write any SQL, it won’t be portable, and it will cause you problems in the future. But you can use types that are part of the ANSI Standards.

• When you have the variable number of characters for every entry then use VARCHAR.
• When you know you have a fixed number of characters for every entry then use CHAR.
• If you need more storage than VARCHAR is the right choice, CLOB with UTF-8 encoding or equivalent standard type.
• As TEXT is non-standard, never use it.