Updated May 19, 2023
Definition of PostgreSQL Character Varying
In PostgreSQL, the character varying type can be used without a length specifier. When used in this way, the character varying type accepts strings of any size. In PostgreSQL, basically varying is the alias name of varchar, so there is only one difference between character varying and varchar: character varying is more friendly than varchar in PostgreSQL. The notation of char (n) is the aliases of character (n), and varchar (n) is the aliases of character varying (n). If we have used character varying without the length specifier, it will accept the string of any size.
Syntax:
Below is the syntax of character varying.
-
Character varying (n)
OR
Create table table_name (column_name character varying (n));
OR
-
Alter table table_name (Name of table name which column we have altering) alter column column_name (Name of column which we have altering) type character varying (n);
Below is the parameter description syntax of character varying in PostgreSQL.
- Character varying – This specifies that we have using column data type as character varying. Character varying is work the same as a varchar data type. We can specify the desired number of characters allowed in the column field.
- Table name –We can define character varying in data type to the column name at the time of table creation. Table name is important when we have defining character varying data type to the column.
- Column name – The column name refers to the name assigned to the column where we have defined the data type as “character varying.” We can define data type at the time of table creation, and we can also define after table creation using the alter command.
- Alter – To modify columns, you can utilize the “ALTER” command, which enables you to change the data type to “character varying.”
How character varying work in PostgreSQL?
- Below is the working of character varying data types.
- In PostgreSQL, there are two primary data types of character, i.e., character and character varying, we can use n as a positive integer to define the value of the data type.
- The maximum limit of size character using character varying data type in PostgreSQL is 10485760.
- The example below shows that the character’s size using character varying data type is 10485760.
create table stud_test(stud_id serial primary key, str_test character varying(10485761));
create table stud_test(stud_id serial primary key, str_test character varying(10485760));
\d+ stud_test;
- In the first example provided, attempting to define the size of the character varying data type as 10485761 is not allowed in PostgreSQL. The maximum allowed size for the character varying data type in PostgreSQL is 10485760.
- It will display the error as “ERROR: length for type varchar cannot exceed 10485760”.
- In the second example, we have define the size of the character varying data type as 10485760. It is allowed, so using this size table is created.
- The minimum limit of size character using character varying data type in PostgreSQL is 1. Zero and negative values are not permitted when using the character varying data type.
- Below example shows that the minimum size of character varying data type, zero, and a negative value is not allowed.
create table stud_test1(stud_id serial primary key, str_test character varying(0));
create table stud_test1(stud_id serial primary key, str_test character varying(-1));
create table stud_test1(stud_id serial primary key, str_test character varying(1));
\d+ stud_test1;
- In the above first example, we have used the size of character varying datatype is zero, but zero value is not allowed, so it will display an error message as “ERROR: length for type varchar must be at least 1”.
- In the above second example, we used a size of -1 for the character varying data type. However, negative values are not allowed, resulting in an error message being displayed: “ERROR: syntax error at or near “-“”.
- In the third example, we used a size of one. This creates a table with a size of one because the minimum size allowed for the character varying data type in PostgreSQL is one.
- Character varying and varchar are the same, but most of the databases do not provide the character varying data type, but PostgreSQL is providing for the same.
- Character varying is the official type of SQL ANSI standard. It will support all SQL compliances.
- Character varying is the most useful and important data type used without a length specifier.
Examples
Below is the example of a character varying data type.
Example #1 – Define character varying data type at the time of table creation
The below example shows that we have defined the data type at the time of table creation. In the below example, we have to define character varying data type of stud_name, str_test, and stud_address column.
create table stud_char (stud_id serial primary key, stud_name character varying(100), str_test character varying(1000), stud_address character varying(100), stud_phone int, pincode int);
\d+ stud_char;
Example #2 – Insert value into character varying data type column
The below example shows the insert value on the column, which contains the data type as character varying.
- We have used the table name stud_char1 to insert data into the character varying data type column.
create table stud_char1 (stud_id serial primary key, stud_name character varying(2), str_test character varying(1000), stud_address character varying(100), stud_phone int, pincode int);
\d+ stud_char1;
insert into stud_char1 values (1, 'ABC', 'PQR', 'XYZ', 1234567890, 123456);
insert into stud_char1 values (1, 'AB', 'PQR', 'XYZ', 1234567890, 123456);
insert into stud_char1 values (2, 'CD', 'PQR', 'XYZ', 1234567890, 123456);
select * from stud_char1;
Example #3 – Change the data type of column as a character varying after table creation
The example below shows that change the column’s datatype after table creation. We have to change the data type of the Pincode column.
\d+ stud_char1;
alter table stud_char1 alter column pincode type character varying(10);
\d+ stud_char1;
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL Character Varying” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
