Quick Contact

     

    PostgreSQL Data Types

    The PostgreSQL is a database that manages multiple values of data.

    What is the data type?

    A data type (or) only types refers to the specific type of data that the variable holds. Moreover, these include the date, timestamps, varchar, and some other formats.

    Numeric Datatypes

    PostgreSQL supports two types of Numeric datatypes, including integers and Floating point numbers.

    Name Storage Size Range
    Small 2 bytes -32768 – +32767
    Integer 4 bytes -2147483648 to +2147483647
    Bigint 8 bytes -9223372036854775808 to 9223372036854775807
    Real 4 bytes It support 06 digits precision
    Double precision 8 bytes It supports 15digit decimal precision
    Decimal Variable It enables up to 131072 before the decimal point, up to 16383 after the decimal point.
    Numeric Variable It enables up to 131072 before the decimal point, up to 16383 after the decimal point.
    Small serial 2 bytes 1-32767
    Serial 4 bytes 1 – 2147483647
    Double serial 8 bytes 1 to 9223372036854775807
    Monetary data types

    This is the detailed data that the database provides. Here in this data type, numeric values, int and bigint can be modified into money. So now many person say that we can use Float datatype. But this is not recommended to manage money due to the potential for rounding bugs.

    Name Storage Range
    Money 8 bytes -92233720368547758.08 to +92233720368547758.07
    Character data types

    The following table includes the PostgreSQL character datatypes.

    Name Descriptions
    Char(size)/ Character(size) Hence size is the number of characters to save. It includes the fixed-length strings. Here the space padded on the right to equal size characters.
    Varchar(size) Hence size is the number of characters to store. It contains the variable string length.
    Date/time data type

    The date/time datatype is used to represent the columns using the date and time values.

    Name Size Range Resolution
    TimeStamp
    With or without zone
    8 bytes 4713 BC to 294276 AD 1 microsecond / 14 digits
    Date 4 bytes 4713 BC to 294276 AD One day
    Time Without Time Zone 8 bytes 00:00:00 to 24:00:00 1 microseconds/14 digits
    Timewith timezone 12 bytes 00:00:00 + 1459 to 24:00:00-1459 1 microseconds/14 digits
    Interval 12 bytes -178000000 to 178000000 years I microsecond/14 digits
    Geometric data types

    The geometric data types represent two-dimensional data objects. Moreover, these data types help to perform various operation like rotation, scaling and translation etc.

    Name Storage Representation Description
    Point 16 bytes Point on a Plane (x, y)
    Line 32 bytes Infinite Line ((x1.y1).(x2.y2))
    Line Segment 32 bytes Finite Line Segment ((x1.y1).(x2.y2))
    Box 32 bytes Rectangular Box ((x1.y1).(x2.y2))
    Path 16 n + 16 n bytes Close and Open path ((x1.y1)……)
    Polygon 40 + 16 n bytes Polygon [(x1.y1)….]
    Circle 24 bytes Circle <(x.y).r>
    Network Address type

    Today we do have many network address types like IPV4, IP V6, and MAC address. Moreover, this PostgreSQL offers different data types to store in this address.

    It can store in a network address, instead of plain text, it is preferable to keep these data types.

    Name Storage Size Description
    Cidr 7 (or) 19 bytes IPV4 and IPV6 networks
    Inet 7 (or) 19 bytes IPV4 and IPV 6 hosts and networks
    Macaddr 6 bytes MAC address
    Enumerated data types

    In PostgreSQL, enumerated data types are useful for representing the rarely changing information such as country code (or) branch id. So to ensure data integrity, the enumerated data type is defined with a table with a foreign key.

    Range type

    These represent data that uses the data range. These range types can be a discrete range (or) continuous ranges.

    Name Description
    Int4range Range of the integer
    Int8range Range of the big int
    Numrange Range of the numeric
    Strange Without timezone, time stamp range
    Tstzrange With timezone, time stamp range
    Date range It represents the range of the date.
    Pseudo types

    PostgreSQL contains several special-purpose entries that are collectively called as pseudotypes. Moreover, a pseudo data type cannot be used as a column data type that can be used as a column data type. Additionally, this can be used to declare a function argument (or) a result type.

    Name Description
    Any It denoted the function accepts any input data type.
    Any Element It denoted the function accepts any data type.
    Any Array It denoted the function accepts any array data type.
    Anynonarray It denoted the function accepts any non-array data type.
    Anyenum It denoted the function accepts any enum data type.
    Anyrange It denoted the function accepts any range data type.
    Internal It denoted the function accepts (or) returns an internal server data.
    Record It denoted the function of returning an unspecified row type.
    Void It denoted the function that has no value.
    CString It denoted the function accepts (or) returns the Nullterminated C string.

    Enroll Yourself: Python Training

    Copyright 1999- Ducat Creative, All rights reserved.