EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Numeric
Secondary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • Postgres Command-Line
    • PostgreSQLTrigger Functions
    • PostgreSQL jsonb
    • PostgreSQL Table Schema
    • PostgreSQL group by day
    • Postgres ALTER TABLE
    • Postgres Connect to Database
    • PostgreSQL Numeric
    • PostgreSQL dblink
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL BIGINT
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Superuser
    • PostgreSQL ALL
    • PostgreSQL Operators
    • PostgreSQL IN Operator
    • Postgres like query
    • PostgreSQL encode
    • PostgreSQL Cheat Sheet
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL INSERT INTO
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL? ORDER BY? Random
    • PostgreSQL ORDER BY DESC
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • PostgreSQL ANY
    • Entity Framework PostgreSQL
    • PostgreSQL UPSERT
    • PostgreSQL ON CONFLICT
    • PostgreSQL Port
    • Postgres List Schemas
    • PostgreSQL Update
    • PostgreSQL Drop Schema
    • PostgreSQL VARCHAR
    • PostgreSQL Case Insensitive
    • PostgreSQL Timestamp with Timezone
    • PostgreSQL Array Functions
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL User Password
    • PostgreSQL log_statement
    • PostgreSQL String Functions
    • PostgreSQL? String Contains
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL?String Array
    • PostgreSQL where in array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL CHECK Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL LEAD()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL?Describe Table
    • PostgreSQL?Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • PostgreSQL List Tables
    • PostgreSQL TRUNCATE TABLE
    • PostgreSQL Table Partitioning
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL datediff
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL LENGTH()
    • PostgreSQL blob
    • PostgreSQL Median
    • PostgreSQL kill query
    • PostgreSQL Formatter
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL?Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL?TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL DELETE JOIN
    • PostgreSQL Float
    • PostgreSQL?OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL?MD5
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL?enum
    • PostgreSQL?OR
    • PostgreSQL Wal
    • PostgreSQL?NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL? Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL UUID
    • PostgreSQL Merge
    • PostgreSQL Database
    • PostgreSQL?Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Show Databases
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL Show Tables
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL? flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL Encryption
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL? Partition
    • PostgreSQL? column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL? Character Varying
    • PostgreSQL? Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL Incremental Backup
    • PostgreSQL Backup
    • PostgreSQL JSON vs JSONNB
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
    • PostgreSQL drop foreign key
    • PostgreSQL export database
    • PostgreSQL Export CSV
    • PostgreSQL Performance Tuning
    • PostgreSQL Unnest
    • PostgreSQL show version
    • PostgreSQL Full Text Search
    • PostgreSQL max connections
    • Postgres Switch Database
    • PostgreSQL STRING_AGG()
    • PostgreSQL Date Format
    • PostgreSQL SUM()
    • PostgreSQL FORMAT
    • BETWEEN in PostgreSQL
    • PostgreSQL EXISTS
    • PostgreSQL Synonyms
    • PostgreSQL REGEXP_REPLACE
    • PostgreSQL group by month
    • UNION in PostgreSQL
    • PostgreSQL POSITION()

PostgreSQL Numeric

PostgreSQL Numeric

Introduction to PostgreSQL Numeric

While dealing with numbers extra care needs to be incorporated while storing them and the right type of the datatype should be declared for our numeric field according to the value that is expected to be stored in it. PostgreSQL provides 10 types of data types that can be used to store and handle numeric values in PostgreSQL databases. In this article, we will discuss all the datatypes that are provided to handle numeric data in PostgreSQL and also see its storage size and range required and allowed for each of that datatype and take an example to understand how these values are stored and retrieved.

Numeric Datatypes

In all, there are 10 numeric data types present in the PostgreSQL database and contain integral values with 2,4 and 8 bytes of size, floating-point values, an auto-incrementing serial values with variable ranges. The below table lists out the name of the data type, the size that is required to store each value belonging to that particular datatype and the usage specifying when and where that data type should be used.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Numeric Datatype Size in bytes Usage
Smallserial datatype 2 bytes space Integers that will be autoncremented and are small in size.
Serial datatype 4 bytes space Integers that will be autoncremented and are medium in size.
Bigserial datatype 8 bytes space Integers that will be autoncremented and are large in size.
Smallint datatype 2 bytes space Integers that will have a small range of values.
Integer datatype 4 bytes space Most commonly used and preferred datatype for storing integral values.
Bigint datatype 8 bytes space Integers that might have a large range of values.
Decimal datatype Variable size It is a decimal value that will have user-specified and exact precision values stored in it.
Double precision datatype 8 bytes space It is a decimal value that will have variable precision values stored in it with a maximum of 15 decimal digits of precision in it.
Numeric datatype Variable size It is a decimal value that will have user-specified and exact precision values stored in it.
Real datatype 4 bytes space It is a decimal value that will have variable precision values stored in it with a maximum of 6 decimal digits of precision in it.

Integer Datatypes

These are the data types that allow us to store the integer value that does not contain any fraction part in it and are wholesome numbers. The most preferred data type for storing the integer values is Integer that gives us the range of -2147483648 to +2147483647. Smallint is used only when the memory space is at stake and you need to use your storage resource effectively in the scarcity of memory and has a range of -32768 to +32767 and can also be used for storing less valued integers. BigInt is used when the range of integer is not sufficient to store your integer values and gives us a huge range from -9223372036854775808 to +9223372036854775807.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,768 ratings)

Let us create one table named educba_integer_demo with three columns of datatype smallint, integer, and bigint.

CREATE TABLE educba_integer_demo(small smallint,medium integer, big bigint);

that gives the following output if the table is created successfully.

PostgreSQL Numeric 1

Let us enter some records in it.

INSERT INTO educba_integer_demo VALUES(32767,2147483647,9223372036854775807);

If the value is inserted properly, the output will be as follows –

PostgreSQL Numeric 2

Let us increase the value of the big column by one so that it would exceed the range and see what happens if such an attempt is made.

INSERT INTO educba_integer_demo VALUES(32767,2147483647,9223372036854775808);

The output will be as follows –

PostgreSQL Numeric 3

As can be seen, an error is thrown saying bigint is out of range.

Now, attempt to insert a value that is not in the range of small int using the following query –

INSERT INTO educba_integer_demo VALUES(32778,2147483647,9223372036854775807);

that gives the following output with an error saying smallint is out of range –

PostgreSQL Numeric 4

Inserting a value that is out of range of integer also gives the error as follows when the query like this is fired –

INSERT INTO educba_integer_demo VALUES(32767,2147483650,9223372036854775807);

PostgreSQL Numeric 5

Inserting all the three values that are not in the range gives the following error when such attempt is made by executing the following query –

INSERT INTO educba_integer_demo VALUES(32790,2147483750,9223372036854775900);

PostgreSQL Numeric 6

As can be seen, it throws the error for only the first column that it traverses which in our case is the smallint data type column.

Serial Datatypes

In Postgres, we have three data types that are available to create the columns that behave in the auto-incrementing fashion for storing values that will automatically be incremented by 1 by default and are unique fields. There are three datatypes – smallserial, serial and bigserial datatypes. Generally, the serial data type is used for storing autoincremented column values in Postgres that provide the range from 1 to 2147483647. When we don’t have to store many values then we can use smallserial datatype that has the range from 1 to 32767. If we know that our database will store a lot of rows even more than 2147483647 then we can use bigserial datatype that has a range of 1 to 9223372036854775807.

Let us create the table having all these three data typed columns named educba_serial_demo using the following query –

CREATE TABLE educba_serial_demo(small smallserial,medium serial, big bigserial);

create table

INSERT INTO educba_serial_demo VALUES (DEFAULT,DEFAULT,DEFAULT),(DEFAULT,DEFAULT,DEFAULT),(DEFAULT,DEFAULT,DEFAULT);

Executing the above query gives the following output –

output 1

Let us see what values are inserted in the  educba_serial_demo table using select statement –

select * from educba_serial_demo;

that gives the following output –

output 2

As can be seen, the values are automatically inserted incrementally for all three columns.

Floating-point data types

We have four datatypes to store floating-point numbers that have different ranges and have either user-defined or variable precision. Numeric and decimal have user-defined precision and exact up to 131072 and 16383 digits before and after the decimal point while real and double have variable precision with 6 and 15 decimal digit precision respectively. We can use them as per our use-case and requirement and specify the precision and scale in the case of numeric and decimal datatypes.

Conclusion

There are 10 numeric data types available in our PostgreSQL database that have different ranges and occupy different storage spaces in the database. We can use them as per our convenience and requirement. However, we have to be careful while using them and consider their behavior and range.

Recommended Articles

This is a guide to PostgreSQL Numeric. Here we discuss introduction, syntax, format, and examples with code implementation. You may also have a look at the following articles to learn more –

  1. PostgreSQL Link
  2. PostgreSQL SET
  3. PostgreSQL Integer
  4. PostgreSQL Character Varying
Popular Course in this category
PostgreSQL Course (2 Courses, 1 Project)
  2 Online Courses |  1 Hands-on Project |  7+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more