Data Type Mapping PostgreSQL has rich set of data types. Some of the important Data type conversion between Oracle and PostgreSQL is as follow. Oracle…
View More Migration from Oracle to Postgres
Month: October 2021
WITH Queries (Common Table Expressions)
SELECT in a WITH Clause The subqueries, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary…
View More WITH Queries (Common Table Expressions)
How a single PostgreSQL config change improved slow query performance by 50x
At Amplitude, our goal is to provide easy-to-use interactive product analytics, so everyone can find answers to their product questions. In order to provide a…
View More How a single PostgreSQL config change improved slow query performance by 50x
Recursive Queries, CTE (Common Table Expression) or Self Join, Recursive Views
The CTEs are like temporary tables that exist only during the execution of the query. a simple example: Another example using a function Getting โmanager treeโ for…
View More Recursive Queries, CTE (Common Table Expression) or Self Join, Recursive Views
Display big numbers in SQLPLUS
NUMWIDTH setting which is by default 10 and hence you can display only 10 numbers without conversion but anything beyond that becomes exponential format. SHOW…
View More Display big numbers in SQLPLUS
set operators in Postgres
Postgres offers set operators that make it easy to query and filter the results of searches from your database. Set operators are used to join…
View More set operators in Postgres
Left [Outer] Join and Right [Outer] Join
LEFT OUTER JOIN / LEFT JOIN returns all the rows from the LEFT table and the corresponding matching rows from the right table. If right…
View More Left [Outer] Join and Right [Outer] Join
Oracle database Patch on windows platform
Apply Oracle Patch on windows platform NOTE:412160.1 – Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches 1. Set…
View More Oracle database Patch on windows platform
How To Delete Duplicate Rows in PostgreSQL
Preparing sample data First, create a new table named carsthat stores fruits: CREATE TABLE cars( id SERIAL PRIMARY KEY, make VARCHAR(50) NOT NULL, model VARCHAR(50) NOT NULL );…
View More How To Delete Duplicate Rows in PostgreSQL
Indexes in Postgresql
PostgreSQL Basic Index Terminology Before describing the types of indexes in PostgreSQL and their use, letโs take a look at some terminology that any DBA…
View More Indexes in Postgresql