DAY _ 6 : PostgreSQL Query Session.

 

👊 Data Types & Constraint 

💬  Data Types :

An attribute that specifies the type of data in a column of our databse table.

PostgreSQL supports a wide variety of data types, broadly categorized into numeric, character, date/time, Boolean, Binary array,  JSON, and other specilized types like UUID and Geometric types.

These types are used to store different kinds in the database.

(1). Numeric Types : 

    (A).  Integer : PostgreSQL offers several integer types : 

                         (i). Smallint  (2 bytes)

                         (ii). bigint   (8 bytes) 

                         (iii). Integer  (4 Bytes) 

                         (iv). Serial - is the same as integer except that PostgreSQL will automatically

                                             generate and populate values into the SERIAL column.

                                            This is similar to AUTO_INCREAMENT  .

   ( B ).  Floating Types : - Are used to storing numbers with fractional parts.

                         (i). Real  (4 bytes) - 

                         (ii). Double Precision  (8 bytes ) - It Offers higher Precision. 

  ( C ).  Decimal Types :

                        (i). Numeric & Decimal - With optional precision and scale are for exact numerical                                                                            values, allowing for user- defined precision and scale.

(2). Character Types : 

    (A).  Character : PostgreSQL offers several Character  types : 

                         (i). Char  (Fix-length) - Stores fixed length character strings, with a specified number

                                                               of  characters.

                         (ii). Varchar  (Variable-length) - Stores variable length character strings, with a                                                                                            maximum length

                         (iii). Text (Variable-length) - Stores unlimited length charcter strings, essentially                                                                                     equivalent to varchar without a length constraint.

(3). Date and Time Types :

               (i). Date : Stores only date values (Year, Month, Day).

               (ii). Time : Stores only time values (Hour, minutes, second, millisecond).

               (iii). Timestamp : Stores both Date and time(Also variants with or without time -  

                                              zone information).

              (iv). Interval : Represents a time interval( difference between two timestamps).  

(4). Boolean Types :

         Boolean : Stores true or false values.

💬 Most Widely Used are :

• Numeric - INT DOUBLE FLOAT DECIMAL
• String - VARCHAR
• Date - DATE
• Boolean - BOOLEAN

 Example : What will happen when we store values like 18.36 and 45.90  as so on .. .... ? 

                       DECIMAL(5, 2)   --- 5 --Total Digit 

                                                              2 Digits after decimal

 💬 While Creating a Table it is Necessary to describe Data type and Size for every Column. Such as 

CREATE TABLE Person(

Id SERIAL

, NAME VARCHAR(20)

, Country  VARCHAR(25)

, Job_Title VARCHAR(30)

, Salary DOUBLE PRECISION

);

💬  Constraint :

• A Constraint in PostgreSQL is a rule applied to a column.
• Constraints are rules that restrict the type and range of data that can be inserted into a table or column.
• Constraint are rules applied to table columns to enforce data integrity and ensure consistency.
• Common Constraint are types include Primary Key, Foreign Key, NOT NULL, UNIQUE, and Check Constraints.

(1). NOT NULL  : Ensure that a column cannot have a NULL value.

CREATE   TABLE   Person (

Id    SERIAL   Primary Key,

Name   TEXT   NOT NULL

);

(2). PRIMARY KEY  : A  combination of NOT NULL and UNIQUE.

                                      Each table should have only on primary key.

CREATE   TABLE   Person  (

Id   SERIAL  Primary Key,

);

(3). FOREIGN KEY  : Enusre values in a column (or group of columns) Match values in another 

                                     table's primary key.

CREATE   TABLE   Person (

Id    INTEGER References Person1( Id )

);

(4). UNIQUE : Enusre all values in a column or a group of columns are unique.

CREATE   TABLE  Person  ( 

Name    TEXT   UNIQUE

);

(5). CHECK : Enusre all values in a column satisfy a specific condition.

CREATE  TABLE  Person (

Salary   NUMERIC CHECK (Salary >= 0)

);

(6). DEFAULT : The DEFAULT constraint is used to assign a default value to a column when no

                             value is explicity provided during an INSERT.

CREATE TABLE users (

    id SERIAL PRIMARY KEY,

    name TEXT NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);