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

User · Answer

text and varchar have different implicit type conversions. The biggest impact that I've noticed is handling of trailing spaces. For example ...

select ' '::char = ' '::varchar, ' '::char = ' '::text, ' '::varchar = ' '::text

returns true, false, true and not true, true, true as you might expect.

User · Answer

On PostgreSQL manual   There is no performance difference among these three types  apart from increased storage space when using the blank-padded type  and a few extra CPU cycles to check the length when storing into a length-constrained column  While character n  has performance advantages in some other database systems  there is no such advantage in PostgreSQL  in fact character n  is usually the slowest of the three because of its additional storage costs  In most situations text or character varying should be used instead    I usually use text  References  http   www postgresql org docs current static datatype-character html

User · Answer

As  Character Types  in the documentation points out  varchar n   char n   and text are all stored the same way  The only difference is extra cycles are needed to check the length  if one is given  and the extra space and time required if padding is needed for char n    However  when you only need to store a single character  there is a slight performance advantage to using the special type  char   keep the double-quotes     they re part of the type name   You get faster access to the field  and there is no overhead to store the length   I just made a table of 1 000 000 random  char  chosen from the lower-case alphabet  A query to get a frequency distribution  select count     field     group by field  takes about 650 milliseconds  vs about 760 on the same data using a text field

User · Answer

There is no difference  under the hood it s all varlena  variable length array    Check this article from Depesz  http   www depesz com index php 2010 03 02 charx-vs-varcharx-vs-varchar-vs-text   A couple of highlights      To sum it all up          char n      takes too much space when dealing with values shorter than n  pads them to n   and can lead to subtle errors because of adding trailing   spaces  plus it is problematic to change the limit   varchar n      it s problematic to change the limit in live environment  requires exclusive lock while altering table    varchar     just like text   text     for me a winner     over  n  data types because it lacks their problems  and over varchar     because it has distinct name      The article does detailed testing to show that the performance of inserts and selects for all 4 data types are similar  It also takes a detailed look at alternate ways on constraining the length when needed  Function based constraints or domains provide the advantage of instant increase of the length constraint  and on the basis that decreasing a string length constraint is rare  depesz concludes that one of them is usually the best choice for a length limit

User · Answer

A good explanation from http   www sqlines com postgresql datatypes text      The only difference between TEXT and VARCHAR n  is that you can limit   the maximum length of a VARCHAR column  for example  VARCHAR 255  does   not allow inserting a string more than 255 characters long       Both TEXT and VARCHAR have the upper limit at 1 Gb  and there is no   performance difference among them  according to the PostgreSQL   documentation

User · Answer

character varying n   varchar n  -  Both the same   value will be truncated to n characters without raising an error   character n   char n  -  Both the same   fixed-length and will pad with blanks till the end of the length   text - Unlimited length    Example   Table test     a character 7     b varchar 7   insert  ok      to a insert  ok      to b   We get the results   a           a char length   b        b char length ---------- ---------------- ------- ----------------  ok        7                 ok     2

User · Answer

In my opinion  varchar n  has it s own advantages  Yes  they all use the same underlying type and all that  But  it should be pointed out that indexes in PostgreSQL has its size limit of 2712 bytes per row    TL DR  If you use text type without a constraint and have indexes on these columns  it is very possible that you hit this limit for some of your columns and get error when you try to insert data but with using varchar n   you can prevent it   Some more details  The problem here is that PostgreSQL doesn t give any exceptions when creating indexes for text type or varchar n  where n is greater than 2712  However  it will give error when a record with compressed size of greater than 2712 is tried to be inserted  It means that you can insert 100 000 character of string which is composed by repetitive characters easily because it will be compressed far below 2712 but you may not be able to insert some string with 4000 characters because the compressed size is greater than 2712 bytes  Using varchar n  where n is not too much greater than 2712  you re safe from these errors

User · Answer

UPDATING BENCHMARKS FOR 2016  pg9 5    And using  pure SQL  benchmarks  without any external script    use any string generator with UTF8 main benchmarks   2 1  INSERT  2 2  SELECT comparing and counting     CREATE FUNCTION string generator int DEFAULT 20 int DEFAULT 10  RETURNS text AS  f    SELECT array to string  array agg      substring md5 random    text  1  1   chr  9824    random   10   int              as s   FROM generate series 1   2  i x    f  LANGUAGE SQL IMMUTABLE    Prepare specific test  examples   DROP TABLE IF EXISTS test  -- CREATE TABLE test   f varchar 500    -- CREATE TABLE test   f text    CREATE TABLE test   f text  CHECK char length f  lt  500       Perform a basic test   INSERT INTO test      SELECT string generator 20  random    i 11    int     FROM generate series 1  99000  t i     And other tests   CREATE INDEX q on test  f    SELECT count    FROM     SELECT substring f 1 1     f FROM test WHERE f lt  a0  ORDER BY 1 LIMIT 80000   t        And use EXPLAIN ANALYZE    UPDATED AGAIN 2018  pg10   little edit to add 2018 s results and reinforce recommendations     Results in 2016 and 2018  My results  after average  in many machines and many tests  all the same  statistically less tham standard deviation    Recommendation   Use text datatype  avoid old varchar x  because sometimes it is not a standard  e g  in  CREATE FUNCTION clauses   varchar x  varchar y   express limits  with same varchar performance   by with CHECK clause in the CREATE TABLE e g  CHECK char length x  lt  10   With a negligible loss of performance in INSERT UPDATE you can also to control ranges and string structure  e g  CHECK char length x  gt 5 AND char length x  lt  20 AND x LIKE  Hello

User · Answer

Somewhat OT  If you re using Rails  the standard formatting of webpages may be different  For data entry forms text boxes are scrollable  but character varying  Rails string  boxes are one-line  Show views are as long as needed

[string] Difference between text and varchar (character varying)

Examples related to string

Examples related to postgresql

Examples related to text

Examples related to types

Examples related to varchar