Postgre – is a Database.
SQL – structured query language, for fetching the records from the database.
Procedural Languages Support
PostgreSQL supports four standard procedural languages, which allows the users to write their own code in any of the languages and it can be executed by PostgreSQL database server. These procedural languages are – PL/pgSQL, PL/Tcl, PL/Perl and PL/Python. Besides, other non-standard procedural languages like PL/PHP, PL/V8, PL/Ruby, PL/Java, etc., are also supported.
PostgreSQL – Data Type
Data types are most of like MYSQL datatypes.
PostgreSQL – Schema
A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions. Schemas are analogous to directories at the operating system level, except that schemas cannot be nested. PostgreSQL statement CREATE SCHEMA creates a schema.
PostgreSQL – Expressions
An expression is a combination of one or more values, operators, and PostgresSQL functions that evaluate to a value.
PostgreSQL EXPRESSIONS are like formulas and they are written in query language. You can also use to query the database for specific set of data.
testdb=# SELECT * FROM COMPANY WHERE SALARY = 10000;
PostgreSQL – WITH Clause
In PostgreSQL, the WITH query provides a way to write auxiliary statements for use in a larger query. It helps in breaking down complicated and large queries into simpler forms, which are easily readable. These statements often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.
The WITH query being CTE query, is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables. It computes the aggregation once and allows us to reference it by its name (may be multiple times) in the queries.
The WITH clause must be defined before it is used in the query.
With CTE AS
(Select ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY )
Select * From CTE;
PostgreSQL – CONSTRAINTS
Constraints are the rules enforced on data columns on table. These are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database.
Defining a data type for a column is a constraint in itself.
- NOT NULL Constraint
- UNIQUE Constraint
- PRIMARY Key
- FOREIGN Key
- CHECK Constraint
- EXCLUSION Constraint
PostgreSQL – TRIGGERS
PostgreSQL Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs.
GET THE EXAMPLES.
PostgreSQL – LOCKS
Locks or Exclusive Locks or Write Locks prevent users from modifying a row or an entire table. Rows modified by UPDATE and DELETE are then exclusively locked automatically for the duration of the transaction.
PostgreSQL – Sub Queries
A subquery or Inner query or Nested query is a query within another PostgreSQL query and embedded within the WHERE clause. Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, etc.
PostgreSQL – AUTO INCREMENT
PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases.
PostgreSQL – Functions
PostgreSQL functions, also known as Stored Procedures, allow you to carry out operations that would normally take several queries and round trips in a single function within the database. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code.
Functions can be created in a language of your choice like SQL, PL/pgSQL, C, Python, etc.