TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

PostgreSQL Data Types

PostgreSQL Datatypes for beginners and professionals with examples database, table, create, select, insert, update, delete, join, function, index, clause, trigger, view, procedure etc.

<< Back to POSTGRESQL

PostgreSQL Data Types

A data type specifies what kind of data you want to store in the table field. While creating a table, for each column, you have to use a datatype. It identifies a particular type of data, like integer, Boolean, floating points, and so on.

In this section, we are going to discuss the various data types used in PostgreSQL.

In PostgreSQL, each database table has many columns and has precise data types for each column. It supports an extensive range of data types.

Also, users can create their custom data type using the CREATE TYPE SQL command. And these data types have the following advantages:

  • Performance:It enhances our performance if we are using these data types correctly and efficiently to store the data values.
  • Validation: The correct use of data types involves the validation of data and dismissal of data outside the scope of the data type.
  • Compactness:It stores efficiently, as a column can store a single type of value.
  • Consistency: The activities in contradiction of columns of the same data type provide reliable results and are usually the fastest.

In PostgreSQL, we have many sets of data types; let us understand them one by one:

PostgreSQL Data Types
  • Numeric datatype
  • Character datatype
  • Date/time datatype
  • Monetary data type
  • Binary data type
  • Boolean data type
  • Enumerated data type
  • Geometric data type
  • Text search data type
  • UUID data type
  • Network address type
  • JSON data type
  • Bit string type
  • XML data type
  • Range data type
  • Arrays
  • Composite data type
  • Object identifiers type
  • Pseudo data type
  • pg-Isn data type

Numeric Data Type

Numeric datatype is used to specify the numeric data into the table. It contains the following:

  • four- and eight-byte floating-point numbers
  • two-, four-, and eight-byte integers
  • Selectable-precision decimals.

The below table contains all numeric data types that support in PostgreSQL:

name description storage size range
smallint stores whole numbers, small range. 2 bytes -32768 to +32767
integer stores whole numbers.use this when you want to store typical integers. 4 bytes -2147483648 to +2147483647
bigint stores whole numbers, large range. 8 bytes -9223372036854775808 to 9223372036854775807
decimal user-specified precision, exact variable up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
numeric user-specified precision, exact variable
up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
real
variable-precision, inexact
 4 bytes 6 decimal digits precision.
double precision variable-precision, inexact 8 bytes 15 decimal digits precision
serial auto incrementing integer 4 bytes 1 to 2147483647
bigserial large auto incrementing integer 8 bytes 1 to 9223372036854775807

Character Data Type

In PostgreSQL, we have various general purpose character data types, and these data types are used to represent the character type values.

The below table contains all Character data types that are supported in PostgreSQL:

Datatype Explanation
char(size)
Here size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
character(size) Here size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
varchar(size) Here size is the number of characters to store. Variable-length string.
character varying(size) Here size is the number of characters to store. Variable-length string.
text Variable-length string.

Date/Time Data Type

The PostgreSQL supports the complete set of SQL date and times data types. The date/time datatypes are used to represent the columns using date and time values. Based on the Gregorian calendar, the dates are counted.

The below table contains all date/ time data types that are supported in PostgreSQL:

Name
 Description Storage size Minimum value Maximum value Resolution
timestamp [ (p) ] [ without time zone ] both date and time (no time zone) 8 bytes 4713 bc 294276 ad 1 microsecond / 14 digits
timestamp [ (p) ] with time zone both date and time, with time zone 8 bytes 4713 bc 294276 ad 1 microsecond / 14 digits
date date (no time of day) 4 bytes 4713 bc 5874897 ad 1 day
time [ (p) ] [ without time zone ] time of day (no date) 8 bytes 00:00:00 24:00:00 1 microsecond / 14 digits
time [ (p) ] with time zone times of day only, with time zone 12 bytes 00:00:00+1459 24:00:00-1459 1 microsecond / 14 digits
interval [ fields ] [ (p) ] time interval 12 bytes -178000000 years 178000000 years 1 microsecond / 14 digits

Related Links:

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf