PostgreSQL – VARCHAR Data Type
PostgreSQL supports a character data type called VARCHAR. This data type is used to store characters of limited length. It is represented as
varchar(n) in PostgreSQL, where n represents the limit of the length of the characters. If n is not specified it defaults to
varchar which has unlimited length. Any attempt to store a longer string in the column that defined with
varchar(n) results in PostgreSQL issuing an error. However, one exception is that if the excess characters are all spaces, PostgreSQL will truncate the spaces to the maximum length and store the string. The only advantage of specifying the length specifier for the varchar data type is that PostgreSQL will check and issue an error if you try to insert a longer string into the varchar(n) column.
Syntax: variable_name VARCHAR(n)
Let’s create a new table(say, char_test) for the demonstration using the below commands:
CREATE TABLE varchar_test ( id serial PRIMARY KEY, x VARCHAR (1), y VARCHAR(10) );
Now let’s insert a new row into the char_test table using the below command:
INSERT INTO varchar_test (x, y) VALUES ( 'Geeks', 'This is a test for char' );
At this stage PostgreSQL will raise an error as the data type of the x column is char(1) and we tried to insert a string with three characters into this column as shown below:
ERROR: value too long for type character varying(1)
So, now let’s fix it.
INSERT INTO varchar_test (x, y) VALUES ( 'G', 'This is a test for char' );
Now, we will get the same error for the y column as the number of characters entered is greater than 10 as shown below:
ERROR: value too long for type character varying(10)
Let’s fix it too.
INSERT INTO varchar_test (x, y) VALUES ( 'G', 'hello Geek' );
Now that we have managed to successfully assign the values to the character data type, check it by running the below command:
SELECT * FROM varchar_test;