An Introduction to SQL Data Types – PostgreSQL

Hi Friends,
Everybody know that SQL is a structured Query Language is a computer language. It is used for storing, manipulating and retrieving data stored in relational databases. This article give you a basic idea about data types in PostgreSQL.

SQL support standard language for relational database system. The all relational database management system like: MySQL, MS Access, Oracle, Sybase, Informix, Postgre and SQL Server use SQL as their standard database language.
Numeric Data Type
Numeric data types consist of two-and four-byte integers, four-and eight-bytes floating point numbers and fixed precision decimals.
The numeric data types have a full set of corresponding arithmetic operators and functions.
1) Decimal: It is used for stores the variable, variable and the range is equivalent to 8000 digits.
2) Float4: Its storage is 4 bytes and the range is up to 15 decimal places.
3) Float8: Its storage is 8 bytes and the range up to 15 decimal places.

4) Int2: Its storage is 2 bytes.
5) Int4: Its storage is 4 bytes.
6) Int8: Its storage is 8 bytes.
7) Numeric: Its storage is variable and there is no limit for the range.
8) Serial: Its storage is 4 bytes, and range is between 0 to +2147483647

The serial type is a special- case type constructed by Postgre from other existing components. This is helps to create unique identifiers for table entries.

CREATE TABLE tablename (columnName SERIAL

Character Data Type
SQL defines two primary character types: char and varchar. Postgre supports these types, in addition to the more general type.

1. Char: Storage is 1 bytes and de-scripted by single character only.
2. Varchar(n): Storage is (4+n) bytes and de-scripted by variable length with a limit.
3. Text: Storage is (4+x) bytes and de-scripted by variable length.
4. Char(n): Storage is(4+n) bytes and descripted by fixed length blank padded.

Date/Time Data Type
The following types are supported by PostgreSQL
1) TimeStamp: Storage is 8 bytes and works for oth date and time.
2) Timestamp with timezone: Storage is 8 bytes, and works for both date and time with time zone.

3) Interval: Srorage is 12 bytes and works for tme intervals only.
4) Date: Storage is 4 bytes, works for dates only.
5) Time: Storage is 4 bytes, works for time of the day only.
6) Time with Timezone: Storage is 4 bytes and works for time of the day.

Boolean Data Type
Postgres supports bool as the SQL3 Boolean type. Bool can have one of only two states: “true” or “false”. Bool can be used in any Boolean expression, and Boolean expression always evaluate to a result compatible with this type. Bool uses 1 byte of storage.

Geometric Data Type
Geometric data types represents two dimensional spatial objects.
1) Point: Storage is 16 bytes.
2) Line: Storage is 32 bytes.
3) Lseg: Storage is 32 bytrtes.
4) Box: Storage is 32 bytes.

5) Path: Storage of 4+32n bytes.
6) Polygon: Storage of 4+32n bytes.
7) Circle: Storage of 24 bytes.

If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

Leave a Reply

Your email address will not be published. Required fields are marked *