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 Number Types
 

PostgreSQL Number Types

Priya Pedamkar
Article byPriya Pedamkar

Updated May 19, 2023

PostgreSQL Number Types

 

 

Introduction to PostgreSQL Number Types

PostgreSQL number type consists of two, four, and 8 byte integer or floating point number values, there multiple types of number is available in PostgreSQL. Basically, there are main four number types available in PostgreSQL, i.e., integer types, arbitrary precision numbers, floating point types, and serial types, each numeric type have its subtypes available in PostgreSQL. It is used to store the number values in the database table.

Watch our Demo Courses and Videos

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

Different PostgreSQL Number Types

Given below are the different Number Types:

  • Smallint
  • Integer
  • Bigint
  • Decimal
  • Numeric
  • Real
  • Double precision
  • Smallserial
  • Serial
  • Big serial

Each number type have its different storage size and have its own range. Number type in PostgreSQL consist of the 2 byte, 4 byte, and 8 byte integer types, 4 byte and 8 byte floating point numbers, and the precision decimal in PostgreSQL.

1. Smallint

The storage size of the smallint number type in PostgreSQL is 2 bytes, and the range of the small int type in PostgreSQL is -32768 to 32767. Smallint numeric type is used when disk size is less, and we don’t require a value greater than 32767.

Syntax:

Create table name_of_table (name_of_column smallint, name_of_column datatype);

Example:

Code:

create table smallint_test (id smallint);
\d+ smallint_test;
insert into smallint_test values (32767);
insert into smallint_test values (32768);

Output:

PostgreSQL Number Types 1

2. Integer

The storage size of the integer number type is 4 bytes, and the range of integer type in PostgreSQL is -2147483648 to 2147483647.

Syntax:

Create table name_of_table (name_of_column int, name_of_column datatype);

Example:

Code:

create table int_test (id int);
\d+ int_test;
insert into int_test values (2147483647);
insert into int_test values (21474836478);

Output:

PostgreSQL Number Types 2

3. Bigint

The storage size of the bigint number type is 8 bytes, and the range of the bigint int type is -9223372036854775808 to +9223372036854775807.

Syntax:

Create table name_of_table (name_of_column bigint, name_of_column datatype);

Example:

Code:

create table bigint_test (id bigint);
\d+ bigint_test;
insert into bigint_test values (9223372036854775807);
insert into bigint_test values (9223372036854775808);

Output:

Bigint

4. Decimal

The decimal number type storage size is variable, and the range of decimal type is 16384 digits after the decimal point and 131072 before the decimal point.

Syntax:

Create table name_of_table (name_of_column decimal, name_of_column datatype);

Example:

Code:

create table decimal_test (id decimal);
\d+ decimal_test;
insert into decimal_test values (131072.16384);

Output:

Decimal

5. Numeric

The storage size of the numeric number type is variable, and the range of the numeric type is 16384 digits after the decimal point and 131072 before the decimal point.

Syntax:

Create table name_of_table (name_of_column numeric, name_of_column datatype);

Example:

Code:

create table numeric_test (id numeric);
\d+ numeric_test;
insert into numeric_test values (131072.16384);

Output:

Numeric

6. Real

The storage size of the real number type is 4 bytes, and the range of the real type is 6 decimal digits precision.

Syntax:

Create table name_of_table (name_of_column real, name_of_column datatype);

Example:

Code:

create table real_test (id real);
\d+ real_test;
insert into real_test values (123456.123456);

Output:

Real

7. Double precision

The storage size of the double precision number type is 8 bytes, and the range of the double precision type is 15 decimal digits precision.

Syntax:

Create table name_of_table (name_of_column double precision, name_of_column datatype);

Example:

Code:

create table double_precision (id double precision);
\d+ double_precision;
insert into double_precisionvalues (1234567890.1234567890);

Output:

Double precision

8. Smallserial

The storage size of smallserial number type is 2 bytes, and the range of smallserial type is 1 to 32767.

Syntax:

Create table name_of_table (name_of_column smallserial, name_of_column datatype);

Example:

Code:

create table smallserial_test (id smallserial);
\d+ smallserial_test;
insert into smallserial_test values (32767);
insert into smallserial_test values (32768);

Output:

Smallserial

9. Serial

The storage size of the serial number type is 4 bytes, and the range of serial type is 1 to 2147483647.

Syntax:

Create table name_of_table (name_of_column serial, name_of_column datatype);

Example:

Code:

create table serial_test (id serial);
\d+ serial_test;
insert into serial_test values (2147483647);
insert into serial_test values (2147483648);

Output:

PostgreSQL Number Types 9

10. Bigserial

The storage size of bigserial number type is 8 bytes, and the range of bigserial type is 1 to 9223372036854775807.

Syntax:

Create table name_of_table (name_of_column bigserial, name_of_column datatype);

Example:

Code:

create table bigserial_test (id bigserial);
\d+ bigserial_test;
insert into bigserial_test values (9223372036854775807);
insert into bigserial_test values (9223372036854775808);

Output:

PostgreSQL Number Types 10

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Number Types” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Roles
  2. PostgreSQL Compare Date
  3. DATE_PART() PostgreSQL
  4. Array in PostgreSQL

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