PostgreSQL difference between "character varying" and "character varying[]" via pgAdmin

user_2644097 0
Viewed 1608

What is the difference between the following two data types?

I thought that character varying[] is used when we want to use also Length constraint. However, from what I see within pgAdmin, we can use this constraint also with character varying.

columns

varchar postgresql types
2 Answers

Text vs array of text. It represents single user name with possible multiple emails stored as array of type VARCHAR(100).

CREATE TABLE tab(
   username character varying(100),
   email character varying(100)[]
);

INSERT INTO tab(username, email) VALUES ('user', '{"a@domain.com", "b@domain.com"}');

SELECT *, email[1], email[2]
FROM tab;

db<>fiddle demo

Output:

+-----------+------------------------------+---------------+--------------+
| username  |            email             |    email      |    email     |
+-----------+------------------------------+---------------+--------------+
| user      | {a@domain.com,b@domain.com}  | a@domain.com  | b@domain.com |
+-----------+------------------------------+---------------+--------------+
user_50708790

"character varying" (or varchar, for short) is a string (text) field with varying length (up to 100 characters in your case). The [] denotes an array. I.e., "character varying[]" is an array of such "character varying" strings.

user_24227760
Related