178

John uses CHARACTER VARYING in the places where I use VARCHAR. I am a beginner, while he is an expert. This suggests me that there is something which I do not know.

What is the difference between CHARACTER VARYING and VARCHAR in PostgreSQL?

Community
  • 1
  • 1
Léo Léopold Hertz 준영
  • 134,464
  • 179
  • 445
  • 697
  • 1
    the real question should be what is the difference with type TEXT. But perhaps back then in 2009 this type was not yet around – Roland Jan 10 '23 at 07:56

5 Answers5

198

VARCHAR is an alias for CHARACTER VARYING, so no difference, see documentation :)

The notations varchar(n) and char(n) are aliases for character varying(n) and character(n), respectively. character without length specifier is equivalent to character(1). If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

Note on capitalization: The PostgreSQL documentation uses the all lower case stylization: character varying. In contrast the official SQL standard uses the stylization with all caps throughout its 1000 pages: CHARACTER VARYING.

StackzOfZtuff
  • 2,534
  • 1
  • 28
  • 25
Charles Ma
  • 47,141
  • 22
  • 87
  • 101
  • 17
    It's the other way round: varchar is an alias for varying. Not to be pedantic, but it's right there in your quote. – zerzevul Feb 06 '19 at 17:12
  • 4
    @zerzevul One might disagree with the documentation and agree with Charles here, given that `varchar(n)`, etc. are the ANSI standards and this syntax is peculiar to PostgreSQL. – BobRodes Jun 10 '20 at 03:00
25

The PostgreSQL documentation on Character Types is a good reference for this. They are two different names for the same type.

Greg Hewgill
  • 951,095
  • 183
  • 1,149
  • 1,285
15

The short answer: there is no difference.

The long answer: CHARACTER VARYING is the official type name from the ANSI SQL standard, which all compliant databases are required to support. (SQL compliance Feature ID E021-02.)

VARCHAR is a shorter alias which all modern databases also support. I prefer VARCHAR because it's shorter and because the longer name feels pedantic. However, postgres tools like pg_dump and \d will output character varying.

StackzOfZtuff
  • 2,534
  • 1
  • 28
  • 25
Gordon Hopper
  • 718
  • 6
  • 7
7

The only difference is that CHARACTER VARYING is more human friendly than VARCHAR

StackzOfZtuff
  • 2,534
  • 1
  • 28
  • 25
inor
  • 2,781
  • 2
  • 32
  • 42
  • 2
    @joop I haven't had the privilege of being exposed to COBOL but I have been exposed to English and i think "VARCH ARe what?" :-) – inor Oct 01 '14 at 04:54
  • 13
    Beside I think it is confusing top programmers ;) They are used to varchar – manocha_ak Oct 20 '15 at 06:26
3

Both are the same thing but many of the databases are not providing the varying char mainly postgreSQL is providing. So for the multi database like Oracle Postgre and DB2 it is good to use the Varchar

Daya Shanker
  • 41
  • 2