EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Character Varying
 

PostgreSQL Character Varying

Priya Pedamkar
Article byPriya Pedamkar

Updated May 19, 2023

PostgreSQL Character Varying

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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;

character varying PostgreSQL

  • 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;

PostgreSQL Character Varying 1

  • 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;

Output 2

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;

Output 3

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;

Output 4

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.

  1. PostgreSQL Datetime
  2. OR PostgreSQL
  3. PostgreSQL enum
  4. PostgreSQL Log Queries

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW