Definition of PostgreSQL Character Varying
PostgreSQL character varying is used without the length specifier, character varying type will accept the string of any size in PostgreSQL. In PostgreSQL basically varying is the alias name of varchar, so there is only one difference between character varying and varchar is character varying 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) in PostgreSQL. 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 in PostgreSQL.
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 in PostgreSQL. We can pass a number of character which was allowed in the column field.
- Table name –We can define character varying 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 – This is defined as the name of the column on which we have defined 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 –This command is used to alter the column to change the data type as character varying.
How character varying work in PostgreSQL?
- Below is the working of character varying data types in PostgreSQL.
- 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 data type.
- The maximum limit of size character using character varying data type in PostgreSQL is 10485760.
- The below example shows that the size of the character using character varying data type in PostgreSQL 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 above first example, we have to define the size of character varying data type as 10485761 but this value is not allowed in PostgreSQL because the max size of character varying data type 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 a negative value is not allowed using character varying data type in PostgreSQL.
- 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 have used size of the character varying data type is -1, but the negative value is not allowed so it will display an error message as “ERROR: syntax error at or near “-“”.
- In third example we have used size as one, using one size table is created, because the minimum size of character varying data type is one in PostgreSQL.
- Character varying and varchar is the same but most of the databases are not providing 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 most useful and important data type in PostgreSQL used without a length specifier.
Examples
Below is the example of character varying data type in PostgreSQL.
Example #1 – Define character varying data type at the time of table creation
The below example shows that we have defined 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 using the table name as stud_char1 to insert data into 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 character varying after table creation
The below example show that change the datatype of the column after table creation. We have to change the data type of Pincode column.
\d+ stud_char1;
alter table stud_char1 alter column pincode type character varying(10);
\d+ stud_char1;
Recommended Articles
This is a guide to PostgreSQL Character Varying. Here we discuss the introduction, How character varying work in PostgreSQL? with examples respectively. You may also have a look at the following articles to learn more –
