CODEWITHSUNDEEP
postgresql
harsha kumar Reddy
harsha kumar Reddy

Reputation: 1396

Need explanation on character types in PostgreSQL

I went through the documentation on character types of PostgreSQL. But still I have some questions

"char" is fixed length i,e 1 if so what is length of "char[]" because I am not able to change it in pgadmin I thought it is used for variable length character array. So, what is the actual default size?

What is use of character as compared to "char"[]? For now I assume character is used for fixed length character array for which we define size.

Why character[] is used?

What is difference between character varying and character varying[]?

Upvotes: 5

Views: 10790

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248195

In case you come from a C background, a character string type in PostgreSQL is not an array of characters.

For each type foo in PostgreSQL, there is a corresponding array type foo[] that denotes an array of values of type foo. Use array types only if you do not plan to manipulate them much inside the database; if you do, it is usually better to normalize the array into a separate table.

Leaving aside array types, there are different character types:

  • "char" (the double quotes are required): a single character. Mostly used in catalog tables. Don't use this type unless you know what you are doing.

  • character(n) or char(n): fixed length characters string. No matter what you store there, it will always be padded with spaces on the right side. The behaviour, as dictated by the SQL standard, is sometimes surprising, so you rarely want this type.

  • text: arbitrary-length character string. This is the type you want for characters strings unless you want the database to impose a length limit.

  • character varying(n) or varchar(n): this is the same as text with an additional length limit.

To round it off with an example:

CREATE TABLE strtest(
   id serial PRIMARY KEY,
   sc "char",
   c character(10),
   vc character varying(10),
   vca character varying(10)[]
);

INSERT INTO strtest (sc, c, vc, vca)
   VALUES (
      'x',
      'short',
      'short',
      ARRAY['short1', 'short2', 'short3']
   );

SELECT sc, c, vc, vca[2] FROM strtest;
 sc |     c      |  vc   |  vca
----+------------+-------+--------
 x  | short      | short | short2
(1 row)

Upvotes: 11

Related Questions