Pearl DB Service

Roles:                   PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.                   Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects.                  Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role. Role Attributes: CREATEDB         NOCREATEDB CREATEROLE NOCREATEROLE LOGIN           NOLOGIN SUPERUSER          NOSUPERUSER INHERIT NOINHERIT BYPASSRLS         NOBYPASSRLS REPLICATION NOREPLICATION PASSWORD ENCRYPTED PASSWORD CONNECTION LIMIT VALID UNTIL Examples: postgres=# \du+                                           List of roles  Role name |                         Attributes           

INTERNAL LAYOUT: a tuple or an item is a synonym for a row a relation is a synonym for a table a filenode is an id which represent a reference to a table or an index. a block and page are equals and they represent a 8kb segment information the file storing the table. a heap refer to heap file . Heap files are lists of unordered records of variable size. Although sharing a similar name, heap files are different from heap data structure. CTID represent the physical location of the row version within its table. CTID is also a special column available for every tables but not visible unless specifically mentioned. It consists of a page number and the index of an item identifier. OID stands for Object Identifier. database cluster , we call a database cluster the storage area on disk. A database cluster is a collection of databases that is managed by a single instance of a running database server. Page:            Inside the data file (heap table and index, as well as the free space ma

POSTGRESQL SCHEMA: A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions. There are several reasons why one might want to use schemas:     To allow many users to use one database without interfering with each other.     To organize database objects into logical groups to make them more manageable.     Third-party applications can be put into separate schemas so they do not collide with the names of other objects. Default Schema: public Examples: Current schema list of table: pearl=# \dt+                     List of relations  Schema | Name  | Type  |  Owner   | Size  | Description  --------+-------+-------+----------+-------+-------------  public | muthu | table | postgres | 16 kB |  (1 row) Current database list of schemas: pearl=# \dn+                           List of schemas   Name  |  Owner   |  Access privileges   |      Description        --------+----------+----------------------+------------------

PostgreSQL Installation 1.Community PostgreSQL (Open Source) 1.Binary(Packages) 2.Source 3.RPM(Redhat Package Management) 2.Enterprisedb 1.Run File 1.GUI Mode 2.Text Mode 2.Repository(yum Installation) 1.Community PostgreSQL Installation: Binary Installation:(Online Installation - Internet Must) Step 1: goto https://www.postgresql.org/ and click Download Choose Packages on Top Left Cornor Step 2: Select Your Operating System. Choose Linux and select your Linux distribution(Redhat/centos). Step 3: Select PostgreSQL Version. Example Choose 12 Step 4: Select Linux Platform. Example select Red Hat enterprise, CentOS, Scientific or Oracle version 7 Step 5: Select OS architecture Type. Example Select x86_64 Step 6: Copy the Script and paste your terminal Example: # Install the repository RPM: yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg

Physical Structure: base Subdirectory containing per-database subdirectories global Subdirectory containing cluster-wide tables, such as pg_database pg_commit_ts   Subdirectory containing transaction commit timestamp data pg_dynshmem   Subdirectory containing files used by the dynamic shared memory subsystem pg_logical   Subdirectory containing status data for logical decoding pg_multixact   Subdirectory containing multitransaction status data (used for shared row locks) pg_notify   Subdirectory containing LISTEN/NOTIFY status data pg_replslot   Subdirectory containing replication slot data pg_serial   Subdirectory containing information about committed serializable transactions pg_snapshots   Subdirectory containing exported snapshots pg_stat   Subdirectory containing permanent files for the statistics subsystem pg_stat_tmp   Subdirectory containing temporary files for the statistics subsystem pg_subtrans   Subdirectory containing

Sequence: It is used to automatically generate the number(unique).I generate serial list of unique number for numerical column of database tables. Sequence Options: TEMPORARY or TEMP //If specified, the sequence object is created only for this session and is automatically dropped on session exit. IF NOT EXISTS //Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing relation is anything like the sequence that would have been created - it might not even be a sequence. NAME //The name of the sequence to be created. INCREMENT  //The optional clause INCREMENT BY increment specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1. MINVALUE NO MINVALUE //The optional clause MINVALUE minvalue determines the minimum value a sequence c

Subquery:               A Subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause. Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, etc. Types of Subqueries Single Row Sub Query:  Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions. Example: pearl=# select name from siva where age = (select age from siva where age=21);  name -------  muthu (1 row) Multiple row sub query:  Sub query returning multiple row output. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries returning multiple columns also. Usage of Multiple Row operators     [> ALL] More than the highest value returned by the subquery     [< ALL] Less than the lowest value