• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Databases

      • Home
      • All courses
      • Databases
      • PostgreSQL Database
      CoursesIT & SoftwareDatabasesPostgreSQL Database
      • BASIC POSTGRESQL
        6
        • Lecture1.1
          PostgreSQL Brief History 30 min
        • Lecture1.2
          PostgreSQL Features 20 min
        • Lecture1.3
          PostgreSQL Monitoring Tools Overview 30 min
        • Lecture1.4
          PostgreSQL – Oracle Vs PostgreSQL 30 min
        • Lecture1.5
          PostgreSQL Data Types 30 min
        • Lecture1.6
          PostgreSQL Maximum Objects Size 30 min
      • VMWARE & POSTGRESQL INSTALLATION
        9
        • Lecture2.1
          Vmware Installation 30 min
        • Lecture2.2
          Creating Linux Virtual Machine 01 hour
        • Lecture2.3
          PostgreSQL Installation Types 30 min
        • Lecture2.4
          PostgreSQL GUI Installation 30 min
        • Lecture2.5
          PostgreSQL Text Mode Installation 30 min
        • Lecture2.6
          PostgreSQL Unattended Mode Installation 30 min
        • Lecture2.7
          Configure the network & Disk Partition 30 min
        • Lecture2.8
          How to install PostgreSQL10 On Linux Server ? 20 min
        • Lecture2.9
          PostgreSQL -11 Installation (rpm & source code) 30 min
      • POSTGRESQL DATABASE
        6
        • Lecture3.1
          Connect Postgres Server 10 min
        • Lecture3.2
          PostgreSQL startup / shutdown /restart the postgresql server 30 min
        • Lecture3.3
          PostgreSQL .bash_profile Set Up 30 min
        • Lecture3.4
          PostgreSQL Database Creation 30 min
        • Lecture3.5
          PostgreSQL Connect Database 30 min
        • Lecture3.6
          PostgreSQL Drop Database 30 min
      • POSTGRESQL TABLE
        16
        • Lecture4.1
          PostgreSQL Languages 30 min
        • Lecture4.2
          PostgreSQL Create Table 30 min
        • Lecture4.3
          PostgreSQL Select Table 30 min
        • Lecture4.4
          PostgreSQL Alter Table 30 min
        • Lecture4.5
          PostgreSQL Drop Table 30 min
        • Lecture4.6
          PostgreSQL Truncate Table 30 min
        • Lecture4.7
          PostgreSQL Rename 30 min
        • Lecture4.8
          PostgreSQL Comment 30 min
        • Lecture4.9
          PostgreSQL Insert 30 min
        • Lecture4.10
          PostgreSQL Update 30 min
        • Lecture4.11
          PostgreSQL Table Delete 30 min
        • Lecture4.12
          PostgreSQL Merge Table 30 min
        • Lecture4.13
          PostgreSQL UNION 30 min
        • Lecture4.14
          PostgreSQL UNION ALL 30 min
        • Lecture4.15
          PostgreSQL INTERSECT 30 min
        • Lecture4.16
          PostgreSQL DISTINCT 30 min
      • USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
        3
        • Lecture5.1
          PostgreSQL Grant 30 min
        • Lecture5.2
          PostgreSQL Revoke 30 min
        • Lecture5.3
          Postgres user creation and restrict DDL statement & other database access 30 min
      • TRANSACTIONS - MVCC
        3
        • Lecture6.1
          PostgreSQL Commit 30 min
        • Lecture6.2
          PostgreSQL Rollback 30 min
        • Lecture6.3
          PostgreSQL Savepoint 30 min
      • POSTGRESQL USER/SCHEMA MANAGEMENT
        9
        • Lecture7.1
          PostgreSQL User Creation 30 min
        • Lecture7.2
          PostgreSQL User Creation Using Utility 30 min
        • Lecture7.3
          PostgreSQL Drop user 30 min
        • Lecture7.4
          PostgreSQL Drop User Using Utility 30 min
        • Lecture7.5
          PostgreSQL Password Changing and Views 30 min
        • Lecture7.6
          PostgreSQL Group/role 30 min
        • Lecture7.7
          Alter PostgreSQL User/Role/group 30 min
        • Lecture7.8
          PostgreSQL Schema 30 min
        • Lecture7.9
          PostgreSQL user creation and restrict DDL & database access 30 min
      • POSTGRESQL CONSTRAINTS
        6
        • Lecture8.1
          PostgreSQL Constraints 30 min
        • Lecture8.2
          PostgreSQL Primary Key 30 min
        • Lecture8.3
          PostgreSQL UNIQUE Constraint 30 min
        • Lecture8.4
          PostgreSQL CHECK Constraint 30 min
        • Lecture8.5
          PostgreSQL NOT NULL Constraint 30 min
        • Lecture8.6
          PostgreSQL Foreign Key 30 min
      • POSTGRESQL ADVANCE DATA TYPE
        5
        • Lecture9.1
          PostgreSQL DOMAIN Data Type 30 min
        • Lecture9.2
          PostgreSQL Alter Domain 30 min
        • Lecture9.3
          PostgreSQL Drop DOMAIN 30 min
        • Lecture9.4
          PostgreSQL Json Data Type 30 min
        • Lecture9.5
          PostgreSQL Sequence 30 min
      • POSTGRESQL VIEWS
        1
        • Lecture10.1
          How to Create PostgreSQL View 30 min
      • POSTGRESQL MONITORING OBJECT USUAGE/SIZE
        1
        • Lecture11.1
          How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database 30 min
      • POSTGRESQL DATABASE ARCHITECTURE
        4
        • Lecture12.1
          PostgreSQL Tablespace 30 min
        • Lecture12.2
          PostgreSQL UnderStanding Of Objects 30 min
        • Lecture12.3
          PostgreSQL Architecture 30 min
        • Lecture12.4
          PostgreSQL Query Flow 30 min
      • POSTGRESQL BACKUP AND RECOVERY
        13
        • Lecture13.1
          PostgreSQL Backup & Recovery Overview 30 min
        • Lecture13.2
          PostgreSQL Database Backup Tools – Pg_dump 30 min
        • Lecture13.3
          PostgreSQL Database Backup Tools – Pg_dumpall 30 min
        • Lecture13.4
          PostgreSQL Database Backup Tools – Pg_basebackup 30 min
        • Lecture13.5
          PostgreSQL COPY data From Text File 30 min
        • Lecture13.6
          PostgreSQL COPY data to Text File 30 min
        • Lecture13.7
          PostgreSQL Clone Table 20 min
        • Lecture13.8
          Postgres Database Schema Migration 30 min
        • Lecture13.9
          PostgreSQL Database clone/migration 30 min
        • Lecture13.10
          PostgreSQL Compress backup format 30 min
        • Lecture13.11
          PostgreSQL Archivelog 30 min
        • Lecture13.12
          PostgreSQL Point In Time Recovery 30 min
        • Lecture13.13
          Taking Backup On Postgres Slave (Standby) Server 30 min
      • POSTGRESQL PERFORMANCE TUNING
        5
        • Lecture14.1
          PostgreSQL Index 30 min
        • Lecture14.2
          PostgreSQL Reindex 30 min
        • Lecture14.3
          PostgreSQL PerformanceTuning 30 min
        • Lecture14.4
          Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 min
        • Lecture14.5
          Postgres Autovacuum Configuration/Setup 30 min
      • HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
        11
        • Lecture15.1
          POSTGRESQL STREAMING REPLICATION 30 min
        • Lecture15.2
          SSL Streaming replication with PostgreSQL 10 30 min
        • Lecture15.3
          How to make Linux partition and configure the network on Redhat Linux7.3 30 min
        • Lecture15.4
          How To Fix Firewall Issues while connecting the other server? 30 min
        • Lecture15.5
          How to install the PostgreSQL10 On Linux ? 30 min
        • Lecture15.6
          How to Configure the cascade replication On PostgreSQL 10.3 ? 30 min
        • Lecture15.7
          How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 min
        • Lecture15.8
          PostgreSQL Switchover 30 min
        • Lecture15.9
          Postgres Failover 30 min
        • Lecture15.10
          Postgres Upgrade 30 min
        • Lecture15.11
          PostgreSQL Upgrade 9.5 to 11.3 . 30 min

        PostgreSQL Data Types

        • This chapter discusses PostgreSQL Data Types While creating table, for each column, you specify a data type, i.e., what kind of data you want to store in the table fields.
        • There are different categories of data types in PostgreSQL. They are discussed as below:

        Numeric datatype:

        • Numeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals.
        Name Storage Size Description Range
        smallint 2 bytes small-range integer -32768 to +32767
        integer 4 bytes typical choice for integer -2147483648 to +2147483647
        bigint 8 bytes large-range integer -9223372036854775808 to +9223372036854775807
        decimal variable user-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
        numeric variable user-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
        real 4 bytes variable-precision, inexact 6 decimal digits precision
        Double

        precision

        8 bytes variable-precision, inexact 15 decimal digits precision
        smallserial 2 bytes small autoincrementing integer 1 to 32767
        serial 4 bytes autoincrementing integer 1 to 2147483647
        bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807

        Monetary Types:

        • The money type stores a currency amount with a fixed fractional precision;
        • The fractional precision is determined by the database’s lc_monetary setting. The range shown in the table assumes there are two fractional digits. Input is accepted in a variety of formats, including integer and floating-point literals, as well as typical currency formatting, such as ‘$1,000.00’. Output is generally in the latter form but depends on the locale.
        Name Storage Size Description Range
        money 8 bytes currency amount -92233720368547758.08 to +92233720368547758.07

        Character Types:

        • The table below lists general-purpose character types available in PostgreSQL.
        Name Description
        character varying(n), varchar(n) variable-length with limit
        character(n), char(n) fixed-length, blank padded
        text variable unlimited length

        Binary Data Types:

        • The bytea data type allows storage of binary strings
        Name Storage Size Description
        bytea 1 or 4 bytes plus the actual binary string variable-length binary string

        Date/Time Types:

        • PostgreSQL supports the full set of SQL date and time types,
        • Dates are counted according to the Gregorian calendar, even in years before that calendar was introduced
        Name Storage Size Description Low Value High Value Resolution
        timestamp [ (p) ] [ without time zone ] 8 bytes both date and time (no time zone) 4713 BC 294276 AD 1 microsecond / 14 digits
        timestamp [ (p) ] with time zone 8 bytes both date and time, with time zone 4713 BC 294276 AD 1 microsecond / 14 digits
        date 4 bytes date (no time of day) 4713 BC 5874897 AD 1 day
        time [ (p) ] [ without time zone ] 8 bytes time of day (no date) 00:00:00 24:00:00 1 microsecond / 14 digits
        time [ (p) ] with time zone 12 bytes times of day only, with time zone 00:00:00+1459 24:00:00-1459 1 microsecond / 14 digits
        interval [ fields ] [ (p) ] 16 bytes time interval -178000000 years 178000000 years 1 microsecond / 14 digits


        Boolean Type:

        • PostgreSQL provides the standard SQL type boolean;
        • The boolean type can have several states: “true”, “false”, and a third state, “unknown”, which is represented by the SQL null value.
        Name Storage Size Description
        boolean 1 byte state of true or false

        Valid literal values for the “true” state are:
        TRUE
        ‘t’
        ‘true’
        ‘y’
        ‘yes’
        ‘on’
        ‘1’
        For the “false” state, the following values can be used:
        FALSE
        ‘f’
        ‘false’
        ‘n’
        ‘no’
        ‘off’
        ‘0’

        Leading or trailing whitespace is ignored, and case does not matter. The key words TRUE and FALSE are the preferred (SQL-compliant) usage.
        Enumerated Types:

        • Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages.
        • An example of an enum type might be the days of the week, or a set of status values for a piece of data.

        Examples:
        CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
        CREATE TABLE person (
        name text,
        current_mood mood
        );
        INSERT INTO person VALUES ('Moe', 'happy');
        SELECT * FROM person WHERE current_mood = 'happy';
        name | current_mood
        ------+--------------
        Moe | happy
        (1 row)

        Geometric Types:

        • Geometric data types represent two-dimensional spatial objects
        • A rich set of functions and operators is available to perform various geometric operations such as scaling, translation, rotation, and determining intersections
        Name Storage Size Description Representation
        point 16 bytes Point on a plane (x,y)
        line 32 bytes Infinite line {A,B,C}
        lseg 32 bytes Finite line segment ((x1,y1),(x2,y2))
        box 32 bytes Rectangular box ((x1,y1),(x2,y2))
        path 16+16n bytes Closed path (similar to polygon) ((x1,y1),…)
        path 16+16n bytes Open path [(x1,y1),…]
        polygon 40+16n bytes Polygon (similar to closed path) ((x1,y1),…)
        circle 24 bytes Circle <(x,y),r> (center point and radius)

        Network Address Types:

        • PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses.
        • It is better to use these types instead of plain text types to store network addresses, because these types offer input error checking and specialized operators and functions
        Name Storage Size Description
        cidr 7 or 19 bytes IPv4 and IPv6 networks
        inet 7 or 19 bytes IPv4 and IPv6 hosts and networks
        macaddr 6 bytes MAC addresses

        Bit String Type:

        • Bit String Types are used to store bit masks. They are either 0 or 1. There are two SQL bit types: bit(n) and bit varying(n), where n is a positive integer.

        Text Search Types:

        • PostgreSQL provides two data types that are designed to support full text search, which is the activity of searching through a collection of natural-language documents to locate those that best match a query.
        • The tsvector type represents a document in a form optimized for text search;
        • the tsquery type similarly represents a text query.

        UUID Types:

        • A UUID (Universally Unique Identifiers) is written as a sequence of lower-case hexadecimal digits,
        • In several groups separated by hyphens, specifically a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits.

        Example:ba0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

        XML Types

        • The xml data type can be used to store XML data. Its advantage over storing XML data in a text field is that it checks the input values for well-formedness,
        •  There are support functions to perform type-safe operations on it;.
        • Use of this data type requires the installation to have been built with configure –with-libxml.

        JSON Types:

        • JSON data types are for storing JSON (JavaScript Object Notation) data, as specified in RFC 7159.
        • Such data can also be stored as text, but the JSON data types have the advantage of enforcing that each stored value is valid according to the JSON rules.
        • There are also assorted JSON-specific functions and operators available for data stored in these data types

        JSON primitive types and corresponding PostgreSQL types

         

        JSON primitive type PostgreSQL type Notes
        string text \u0000 is disallowed, as are non-ASCII Unicode escapes if database encoding is not UTF8
        number numeric NaN and infinity values are disallowed
        boolean boolean Only lowercase true and false spellings are accepted
        null (none) SQL NULL is a different concept

        Arrays Types:

        • PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, or composite type can be created. Arrays of domains are not yet supported.

        Composite Types:

        • A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types.
        •  PostgreSQL allows composite types to be used in many of the same ways that simple types can be used.
        • For example, a column of a table can be declared to be of a composite type.

        Range Types:

        • Range types are data types representing a range of values of some element type (called the range’s subtype). For instance, ranges of timestamp might be used to represent the ranges of time that a meeting room is reserved. In this case the data type is tsrange (short for “timestamp range”), and timestamp is the subtype. The subtype must have a total order so that it is well-defined whether element values are within, before, or after a range of values.
        • Range types are useful because they represent many element values in a single range value, and because concepts such as overlapping ranges can be expressed clearly.
        • The use of time and date ranges for scheduling purposes is the clearest example; but price ranges, measurement ranges from an instrument, and so forth can also be useful.

        PostgreSQL comes with the following built-in range types:

        int4range — Range of integer

        int8range — Range of bigint

        numrange — Range of numeric

        tsrange — Range of timestamp without time zone

        tstzrange — Range of timestamp with time zone

        daterange — Range of date

         

        Object Identifier Types:

        • Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.
        • OIDs are not added to user-created tables, unless WITH OIDS is specified when the table is created, or the default_with_oids configuration variable is enabled.
        • Type oid represents an object identifier. There are also several alias types for oid: regproc, regprocedure, regoper, regoperator, regclass, regtype, regrole, regnamespace, regconfig, and regdictionary

        pg_lsn Type:

        • The pg_lsn data type can be used to store LSN (Log Sequence Number) data which is a pointer to a location in the XLOG.
        • This type is a representation of XLogRecPtr and an internal system type of PostgreSQL.
        • Internally, an LSN is a 64-bit integer, representing a byte position in the write-ahead log stream. It is printed as two hexadecimal numbers of up to 8 digits each, separated by a slash; for example, 16/B374D848. The pg_lsn type supports the standard comparison operators, like = and >. Two LSNs can be subtracted using the – operator; the result is the number of bytes separating those write-ahead log positions.

        Pseudo-Types:

        • The PostgreSQL type system contains a number of special-purpose entries that are collectively called pseudo-types.
        • A pseudo-type cannot be used as a column data type, but it can be used to declare a function’s argument or result type. Each of the available pseudo-types is useful in situations where a function’s behavior does not correspond to simply taking or returning a value of a specific SQL data type
        Name Description
        any Indicates that a function accepts any input data type.
        anyelement Indicates that a function accepts any data type
        anyarray Indicates that a function accepts any array data type
        anynonarray Indicates that a function accepts any non-array data type
        anyenum Indicates that a function accepts any enum data type
        anyrange Indicates that a function accepts any range data type
        cstring Indicates that a function accepts or returns a null-terminated C string.
        internal Indicates that a function accepts or returns a server-internal data type.
        language_handler A procedural language call handler is declared to return language_handler.
        fdw_handler A foreign-data wrapper handler is declared to return fdw_handler.
        tsm_handler A tablesample method handler is declared to return tsm_handler.
        record Identifies a function taking or returning an unspecified row type.
        trigger A trigger function is declared to return trigger.
        event_trigger An event trigger function is declared to return event_trigger.
        pg_ddl_command Identifies a representation of DDL commands that is available to event triggers.
        void Indicates that a function returns no value.
        opaque An obsolete type name that formerly served all the above purposes.

        Postgres Domain Data Type:

        • user-defined data type with a range, optional DEFAULT, NOT NULL and CHECK constraint.
        • domain will be check faster than primary key  you will understand from Domain Data Type Example

        Prev PostgreSQL – Oracle Vs PostgreSQL
        Next PostgreSQL Maximum Objects Size

        ADVERTISEMENT

        Latest Courses

        PostgreSQL Database

        PostgreSQL Database

        $600.00 $500.00
        Greenplum Database

        Greenplum Database

        $2,000.00 $1,500.00

        Preview Course

        Free

        Latest Posts

        PostgreSQL Patching version 9, 10,11
        10Oct2019
        Tools for PostgreSQL
        16Sep2019
        Postgres user creation and restrict DDL & database access
        13Sep2019

        Recent Forum Topics

        • thought behind whiteboard activity
        • Are you going to take your first ste
        • How to start working on an application?
        • please let me know pre requirements to increase work_mem
        • how to copy some data in one table to another table in postgres

        2ndquadrant.in

        (+91) 8838953252

        ITsupport@rayafeel.com

        Company

        • About Us
        • Contact
        • Our Team
        • Blog

        COURSES

        • List Of Course
        • Become An Instructor
        • Events
        • Postgres Support Blog

        Support

        • DBA Support
        • Consultancy Services
        • Postgres Migration Blogs
        • Forum

        Recommend

        • Groups
        • Login
        • FAQs
        • SignUp

        IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

        • Privacy
        • Terms

        Become An Instructor?

        Join thousand of instructors and earn money hassle free!

        Get Started Now

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

        Login with your site account

        Lost your password?

        Not a member yet? Register now

        Register a new account

        Are you a member? Login now

        Modal title

        Message modal