SQL is the standard language for relational database systems. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.
The SQL data type is an attribute that specifies the type of data for any object. Each column, variable and expression has a related data type in SQL. We use these data types while creating tables. We need to select a particular data type for a table column based on our requirement.
Numeric data type
Numeric data types consist of two- and four-byte integers, four- and eight-byte floating point numbers and fixed-precision decimals. The numeric types have a full set of corresponding arithmetic operators and functions.
1. Decimal: Its storage is variable and the range is equivalent to 8000 digits.
2. Float4: Its storage is 4 bytes and the range is up to 6 decimal places.
3. Float8: Its storage is 8 bytes and range is up to 15 decimal places.
4. Int2: Its storage is 2 bytes and ranges between -32768 and +32767.
5. Int4: Its storage is 4 bytes and ranges between -2147483648 and +2147483747.
6. Int8: Its storage is 8 bytes and ranges up to +/- > 18 decimal places.
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 and +2147483647.
The serial type is a special-case type constructed by Postgres from other existing components. It is typically used to create unique identifiers for table entries. In the current implementation, when it specifies the following:
CREATE TABLE tablename (colname SERIAL);
it is equivalent to specifying:
CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename(colname INT4 DEFAULT nextval(tablename_colname_seq); CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);
Character data type
SQL defines two primary character types: char and varchar. Postgres supports these types, in addition to the more general text type, which unlike varchar does not require an explicit declared upper limit on the size of the field.
1. Char: Storage is 1 byte 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 de-scripted by fixed- length blank padded.
There is one other fixed-length character type in Postgres, which is the name type, the only purpose of which is the storage of internal catalogue names. It is not intended for use by the general user. Its length is currently defined as 32 bytes (31 characters plus the terminator).
Date/Time data type
The following types are supported by Postgres.
1. TimeStamp: Storage is 8 bytes and works for both date and time.
2. Timestamp with timezone: Storage is 8 bytes, and works for both date and time with time zone.
3. Interval: Storage is 12 bytes and works for time intervals only.
4. Date: Storage is 4 bytes and works for dates only.
5. Time: Storage is 4 bytes and 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. A third state, unknown, is not implemented and is not suggested in SQL3; NULL is an effective substitute. bool can be used in any Boolean expression, and Boolean expressions always evaluate to a result compatible with this type. bool uses 1 byte of storage.
Geometric data type
Geometric data types represent two-dimensional spatial objects. The most fundamental type, the point, forms the basis for all of the other types.
1. Point: Storage is 16 bytes, represented as (x,y) and works for a point in space.
2. Line: Storage is 32 bytes, represented as ((x1,y1),(x2,y2)) and works for an infinite line.
3. Lseg: Storage is 32 bytes, represented by ((x1,y1),(x2,y2)) and works for a finite line segment.
4. Box: Storage of 32 bytes, represented by ((x1,y1),(x2,y2)) and works for a rectangular box.
5. Path: Storage of 4+32n bytes, represented by ((x1,y1),…) and works for a closed path (similar to a polygon).
6. Path: Storage of 4+32n bytes, represented by [(x1,y1),…] and works for an open path.
7. Polygon: Storage of 4+32n bytes, represented by ((x1,y1),…) and works for a polygon (similar to a closed path).
8. Circle: Storage of 24 bytes, represented by <(x,y),r> and works for a circle (centre and radius).
