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:

Next, enter the following command to start the interactive terminal for postgres:

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:

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:

PostgreSQL CREATE TABLE example

A table must first be created to demonstrate the varchar function by executing the following command:

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:

The following error should be returned:

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:

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:

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.