PostgreSQL Varchar Data Type
Introduction
The VARCHAR
, or variable character, is a special type of string or text data type where the maximum number of characters are limited to a specific, specified number. The variable character data type is similar to the TEXT
data type, with both being composed of string-like data. However, the TEXT data type does not require passing a specific number to it when creating a table. However, the character length limit the VARCHAR function places on data permits for faster access than the TEXT data type. This tutorial will explain on how to use the PostgreSQL Varchar data type to input records in PostgreSQL.
Prerequisites
A basic understanding of database management systems is needed to learn how to use the PostgreSQL Varchar function.
Both psql and Postgres must both be properly installed and working.
Postgres and the VARCHAR data type
The variable character in PostgreSQL is a data type that refers to a column or field in a database management system containing a set of characters, i.e. letters and/or numbers, of a fixed length. PostgreSQL will return an error if a value that exceeds the limit of a VARCHAR
column is entered.
Connect to psql
Before beginning, there must be access to the command line that allows for editing, entering and executing queries into the PostgreSQL terminal.
Input a password to obtain privileges to log into Postgres as a super user.
Next, use the psql
syntax to connect to the command-line console.
Now execute the following command:
1 | sudo su - postgres |
Next, enter the following command to start the interactive terminal for postgres:
1 | psql |
A database can then be created after connecting to the psql terminal.
Create a database for Postgres
Always be sure to use a unique name for the database name in PostgreSQL when creating a new database, or an error will occur.
The basic syntax for creating a database follows:
1 | CREATE DATABASE my_dbn; |
A list of all the databases can be displayed by executing the \l
command. To exit, press the -kbd-Q-/kbd- key.
Enter the \c
command followed by the database name to connect to the PostgreSQL database, as shown in the following example:
1 2 | postgres=# \c mydbn; You are now connected TO DATABASE "mydbn" AS USER "postgres". |
PostgreSQL CREATE TABLE example
A table must first be created to demonstrate the varchar
function by executing the following command:
1 2 | CREATE TABLE test_ex (id INT PRIMARY KEY, name VARCHAR(10), address VARCHAR(15), age INT); |
NOTE: The number values that are specified, or passed, to VARCHAR()
is the maximum number of characters that can be used in the variable data types.
Postgres ‘value too long’ error
Now attempt to insert a new record into the test_ex
table with a VARCHAR
value that exceeds the above specified character limits by executing the following command:
1 2 | INSERT INTO test_ex(id, name, address) VALUES (1,'alexander_taylor','Mexico'); |
The following error should be returned:
1 | ERROR: VALUE too long FOR TYPE CHARACTER VARYING(10) |
This error occurs because the varchar has a length-specifier (n) of 10 characters, whereas the name _alexandertaylor exceeds the 10-character limit.
Here is another example:
1 2 | INSERT INTO test_ex(id, name, address) VALUES (1,'alexander','Philippines__hometown'); |
This example shows the address has a VARCHAR
data type length-specifier that exceeds 15 characters. Similar to the previous example, the following error occurs:
1 | ERROR: VALUE too long FOR TYPE CHARACTER VARYING(15) |
The VARCHAR()
for the two data types used in the above examples would result in errors because of trying to insert a string with more than the specified limit into the table columns. This is shown in the following image:
Why use VARCHAR instead of TEXT
It may seem that purposely placing limits on data could complicate things and raise errors, especially when the TEXT
data type allows for virtually unlimited character lengths. However, VARCHAR
character length constraints allows for faster reading than the TEXT
function with possibly better memory allocation that will increase database performance and efficiency.
Practical uses of the VARCHAR
column function would be for names of people, places or things, titles or even brief descriptions. The best way to determine which function will work better is to evaluate data on a case-by-case basis, before insertion, to avoid possible errors.
Conclusion
This tutorial explain on how to use the PostgreSQL Varchar data type to input records in PostgreSQL. The article explained the VARCHAR data type, how to connect to psql and create a database and an example table for Postgres. Finally the tutorial explained the purpose of using VARCHAR instead of the TEXT function. When deciding which function to use, remember that while the TEXT function has an unlimited character length, it may not be as efficiently as the VARCHAR data type. The TEXT function should be used when there is uncertainty about the length of the column values or if the values will include large blocks of text, like used in blogs or webpages. However, use the VARCHAR function if the length of all column values are anticipated to be short.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started