EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL Data Types

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL Data Types

PostgreSQL Data Types

Introduction to PostgreSQL Data Types

PostgreSQL Data Types are the supported data types that are defined while creating the database tables. There are several types of data types available with PostgreSQL, such as numeric, monetary, character, binary, Boolean, date/time and enumerated types. Each of the data types is used to store and process specific types of data. It supports both single-value data types such as integer and character, float and Boolean and mufti value or complex data types such as arrays, JSON, and interval. The enumerated data type has specific functionality to create a set of static values data set, such as defining months of a year.

All the applications that operate using the data must have a database where all of the application related to data is stored. As we know that the data has been considered a crucial part of any system, some platform must exist that can help in processing and managing the data. The data type may be defined as a kind of data. In simple terms, the variable that is assigned with one of the data types can store the value of that data type only. For instance, if any variable is supposed to store the integer values only, then in all the cases, it will store the integer value only. If the user tries to feed the value of different data types in that variable, it will lead to an error.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Different PostgreSQL Data Types

Given below are different PostgreSQL Types:

  • Numeric data types
  • Monetary data types
  • Character data types
  • Binary data types
  • Date/Time data types
  • Boolean data types
  • Enumerated data types

1. Numeric Data Types

It mainly provides two distinct types of numeric data types.

  • Integer
  • Floating point numbers
Integer Data Type

The variable defined with the integer data type can only store the integer value. The value must be the integer value else;, it will end up with throwing the error. For example, if a variable named i is supposed to store the integer value, it will only hold the integer value.

Example: – 1,200,459,354 etc.

In PostgreSQL, basically, three kinds of integer present are as follows:

  • Small Integer: The storage size of the small integer is 2 bytes.

The range of small integer is -32768 to +32767

  • Integer: The storage size of the integer is 4 bytes.

The range of integer is -2147483648 to +2147483647

  • Big Integer: The storage size of big integer is 8 bytes.

The range of big integer is -9223372036854775808 to 9223372036854775807

Code:

# create table num_test (id smallint, number int, salary bigint);

Output:

postgre SQL 1

Floating point number

Floating-point numbers mainly divide into three types are as follows:

  • Float
  • Numeric
  • Real or float8

Float: This is floating point number precision in PostgreSQL. Range if floating point is at least n and a maximum of 8 bytes. It is also called double-precision data types.

Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (5,267 ratings)
Course Price

View Course

Related Courses

Numeric:  This is a real number data type in PostgreSQL. Numeric p and s are the exact numbers in numeric data types.

Real: It is a 4-byte floating point no in PostgreSQL data types.

Code:

# create table num_test1 (id real, number numeric, salary float);

Output:

PostgreSQL Data Types

2. Monetary Data Types

Monetary data types in PostgreSQL stores the current amount with a fractional fixed precision number. The range of monetary data type in PostgreSQL is -92233720368547758.08 to +92233720368547758.07

Code:

# create table mon_test (salary money, currency money);

Output:

postgre SQL 3

3. Character Data Types

In this, there are mainly three distinct character data types available.

  • Char
  • Varchar
  • Text

Char Data Type:  The char data type is used to store a single character value. It is usually preferred to be used at the place where it needs to store just a single character. If the user tries to put more than one character in this, it will lead to an error. The variable storing the char values occupies very little space of the storage.

Example: ‘A’, ‘a’, ‘m’ etc

Text Data Type:  The variable with data type as the text can store the long string values. In all the cases where the length of the text that has to be stored is unknown, one can use the text data type. It is also known as a variable-length character string in PostgreSQL. In these data types, text data is called a character string with unlimited records.

Example: “Hello”, “Congrats” etc

Varchar(n) Data Type: Varchar data type with some number written along with it denotes that it can store or hold only the number of characters written next to it. In these data types, we can store n number of characters.

For example, if we write varchar(9), it means that the variable will only be able to hold the maximum of nice characters.

Example: “ABCDEFGHI”, “Hello Hey”

Code:

# create table char_test (first_name char(10), middle_name varchar(20), last_name text);

Output:

postgre SQL 4

4. Binary Data Types

PostgreSQL binary data types allow the storage of binary string in PostgreSQL. Its variable length is binary data types.

Code:

# create table bin_test (id bytea, dept_no bytea, salary bytea);

Output:

postgre SQL 5

5. Date/Time data Types  

The time data type is assigned to the variable that is supposed to store only the time value. There should be some particular format of time that has to be stored in the time data type variable. It is one of the most important data types in PostgreSQL as it is used to keep track of the transactions.

Example: 12:00:36, 01:06:56

Below is the date/time data types available in PostgreSQL are as follows:

  • Date
  • Time
  • Timestamp
  • Timestamptz
  • Interval

Date: This data type only stores date. It will not store any time. The low value of this data type is 4713 BC, and the highest value is 294276 AD.

Time: This data type only stores time. It will not store any date. The low value of this data type is 4713 BC, and the highest value is 5874897 AD.

Timestamp: This data type stores the date and time. It will not store any timestamp. The low value of this data type is 4713 BC, and the highest value is 294276 AD.

Timestamptz: This data type stores the date and time with the timestamp. The low value of this data type is 4713 BC, and the highest value is 294276 AD.

Interval: The storage size of these data types is 12 bytes. It will describe the time interval value.

Code:

testing=# create table dt_test (start_date date, start_time time, completion_time timestamp, completion_date timestamptz, no_of_year interval);

Output:

postgre SQL 6

6. Boolean Data Types

Boolean is one of the data types supported by PostgreSQL. This data type can store two values only that are “True” and “False”. In usual cases, the Boolean values are used to verify if the statement is correct as when the statement is correct, it returns the true value; else, the value will be false. This data type is also used while decision making, and based on either of the two values; the decision has to be taken by the program.

Example: “True”, “False”

Code:

# create table boolean_test (status boolean, flag boolean, state boolean);

Output:

PostgreSQL Data Types

7. Enumerated Data Types

It comprises a static order set of values. This is closely similar to the enum data types, which was supported in the programming language. Enumerated data type in PostgreSQL creates using create type command. In this, if we create enumerated data types once, it will use in any other type.

Code:

# CREATE TYPE year AS ENUM ('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC');

Output:

PostgreSQL Data Types

Some other Data Types in PostgreSQL

There are some other additional data types in PostgreSQL listed below.

  • Interval Data Type: The variable that is assigned with the interval data type is capable of storing a particular time interval. It is concerned about the time and can be used to estimate the time in several cases. In the database, it is sincerely observed that which transaction has taken place at what interval, and this data type helps us to manage the intervals. Example – ‘7 months ago, ‘2 years 5 hours 40 minutes
  • Array: The array is used to store the set of string or set of any values, but the only restriction is, all the values in the array have to be of the same data type. Using the array makes the program ample convenient to be understood by everyone who was not the part while code was developed. Example – ARRAY [ 408)-589-5846′,'(408)-589-5555′ ]
  • UUID Data Type:  The UUID data type is used to store the Universally Unique Identifiers, which are actually the values that are used throughout the program to identify anything uniquely. It is a special kind of data type that cannot be found in high-level programming languages. Example – 0e37df36-f698-11e6-8dd4–cb9ced3df976, a81bc81b-dead-4e5d-abff-90865d1e13b1
  • JSON Data Type: The variable that is supposed to store the JSON values are assigned with the JSON data type. It is one of the most important data types used in PostgreSQL that is capable of holding the complex JSON data. Example – { “client”: “Doe”, “items”: {“product”: “Application”,”qty”: 7}}

Conclusion

PostgreSQL database management can be considered very useful while used for any application that has to work with the data. It has been designed to work with several kinds of data types where all the data types hold their own importance. Also, to keep track of the time while the transactions are done, there are data types like time, date, interval, etc., are present. In some of the operating systems like Kali Linux, it is available inbuilt. It makes it very easy for the developers to integrate their application with the database. They can also use the different types of data types that have been made available in PostgreSQL.

Recommended Articles

This has been a guide to PostgreSQL Data Types. Here we have discussed the basic meaning of different data types of PostgreSQL with additional data types in detail. You can also go through our other suggested articles to learn more –

  1. PostgreSQL String Functions
  2. Guide to Different PostgreSQL Architecture
  3. Learn the Various PostgreSQL Features
  4. Indexes in PostgreSQL | How to Create? | Types
  5. Guide to Examples of PostgreSQL Triggers
  6. PostgreSQL Database | How to Create?

SQL Training Program (7 Courses, 8+ Projects)

7 Online Courses

8 Hands-on Projects

73+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
  • 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 WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL log_statement
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE 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 Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • 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 Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • 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 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 Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • 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 cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • 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 JDBC Driver
    • PostgreSQL Interview Questions
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • 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

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

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
Book Your One Instructor : One Learner Free Class

Let’s Get Started

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

EDUCBA

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

Forgot Password?

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

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More