The posts about PostgreSQL that were written for this blog can be found below. For quick reference only.
VIEWS vs Materialized Views in SQL
Here are listed the key distinctions between Views and Materialized Views. This material has been gathered throughout time from a variety of sources and books. A Materialized View is always stored on disk, whereas a View is never. A Materialized view is a physical replica of a base table, whereas a View is a virtual…
Sub-Queries in SQL Server
A subquery is a SQL query that runs within another query. They are nested inquiries that offer data to the query that comes before it. Individual values or a list of records can be returned via subqueries. In SQL, a subquery is a select expression surrounded by parentheses and used as a nested query block…
TRUNCATE TABLE in SQL
TRUNCATE TABLE is a Data Definition Language operation in SQL that marks the extent of a table for deallocation. This operation removes all data from a table quickly, typically bypassing a number of integrity-enforcing mechanisms. The syntax is the same in all the SQL products such as MySQL, PostgreSQL, SQL Server, etc. Syntax: TRUNCATE TABLE…
Naming Conventions in SQL
Why is a naming convention required in SQL? The short answer is READABILITY. A naming convention is a set of rules that you decide on before you begin modelling your database. These rules will be applied when naming anything within the database, including tables, columns, primary, and foreign keys, stored procedures, user-defined functions, views, etc.…
List tables in PostgreSQL
Unlike MySQL, Apache Hive, and Apache Impala, we can’t acquire a list of tables in PostgreSQL using the SHOW TABLES command. Use the below command instead. This will avoid system tables as well. SELECT * FROM pg_catalog.pg_tables WHERE schemaname != ‘pg_catalog’ AND schemaname != ‘information_schema’; Or you can use the below if SELECT table_schema ||…
PostgreSQL – Grant Permission on Database
After a long time, when I tried to create a user and grant him all permissions on a certain database using- GRANT ALL PRIVILEGES ON DATABASE myDB TO newUser; the command succeeded but the user received the permission refused error. GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schemaName TO newUser; is another operation that…
UPSERT in SQL
The UPSERT statement combines the INSERT and UPDATE statements into one statement. If another row with the identical set of primary key values already exists for each row processed by the UPSERT statement, the other columns are modified to match the values from the record being UPSERTed. The row is produced if no other row…
SQL-92 and SQL-89 Joins
SQL-92 and SQL-89 joins are supported by most RDBMSs, including Apache Impala. This article will explain how the syntax is written in both ways and which is the better of the two. A JOIN clause is used to join rows from two or more tables together based on a related column. The tables that need…
Apache Hive Aggregate Functions – Part-2
This is continuation part of “Apache Hive Aggregate Functions” Function: VARIANCE & VAR_POP Returns the population variance of the total number of records present in the specified column. Syntax: variance(col), var_pop(col) Example: SELECT VARIANCE(amount) FROM tbSalesData; The result is 14618.639 As per the calculation, the variance supposed to be 15836.858 however in Apache Hive 1.1.0,…
Creating Linked Server for PostgreSQL in MSSQL
Creating a linked server in Microsoft SQL Server to connect PostgreSQL 1) Download the PostgreSQL Unicode (x64) driver to support ODBC. 2) Go to ODBC Data Source Administrator in your machine and create the system DSN. 3) Go to SQL Server Management Studio and execute the below command in a new query window. EXEC master.dbo.sp_addlinkedserver @server…
Automation – PostgreSQL Restore Data
There are three methods to backing up PostgreSQL databases SQL dump File System Level backup Continuous archiving SQL Dump command used to take a full database backup. The backup file consists of SQL commands which will create the database when it is gets restored. The below command is used to take a backup through the…
Reason to convert datetime to bigint – What is epoch time
In my previous article, I had discussed the conversion of bigint value to date time in both PostgreSQL and SQL Server. It raises several questions 1) Why it was converted into bigint in the first place? 2) How the bigint value is related to the date-time? 3) How to convert a date into bigint value?…
PostgreSQL BigInt Value – Conversion to UTC and Local Times
Most of the ticketing systems by default use either MySQL or PostgreSQL and often stores the date-time of the DML events in Bigint format. When such tables data moved to SQL Server, the bigint value should be converted into date and time for readability and analysis purposes. This is not new, however, this article will…
Big Data & SQL 