Postgresql - Delete N number of rows

DELETE FROM mytablename

WHERE id IN (SELECT id FROM mytablename ORDER BY 1 LIMIT 30000)

Postgresql - Random number generation

update foo set bar = floor(random() * 9 + 1); 

SELECT random(),floor(random() * 9 + 1) LIMIT 10;

select floor(7000000000 + random() * 8999);  

PostgreSQL - How to find and kill a hanging query?

First, check all the processes that are running:

SELECT * FROM pg_stat_activity;

So you can identify the PID of the hanging query you want to terminate, run this:

Option #1 (graceful):

SELECT pg_cancel_backend(PID);

Option #2 (forceful):

SELECT pg_terminate_backend(PID);

SELECT 

    pg_terminate_backend(pid) 

FROM 

    pg_stat_activity 

WHERE 

    -- don't kill my own connection!

    pid <> pg_backend_pid()

    AND state = 'idle'

How do I get the list of primary key(s) of a table from Postgres via pgadmin?

SELECT c.column_name, c.data_type

FROM information_schema.table_constraints tc 

JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) 

JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema

AND tc.table_name = c.table_name AND ccu.column_name = c.column_name

WHERE constraint_type = 'PRIMARY KEY' and tc.table_name = 'mytablename';

SQL Error : Multiple ResultSets were returned by the query

Upon using the custom query in the source object, the mapping fails with the following error:

The select query for read operation failed because of the following error: [org.postgresql.util.PSQLException: Multiple ResultSets were returned by the query​

However, the same query works as expected in PostGresDB.​

If the custom query has more than two query statements, one which is commented and separated by a semicolon, then multiple ResultSets were returned by the query. 

This is expected behavior as the JDBC driver verifies both the statement and returns a null value for the blank query which is a commented line.

Solution:

To resolve the issue, either remove the commented lines from the query or keep the commented lines at the top of the query, so it can be disregarded while running the original query

MOD in postgresql - Useful SQL

 SELECT COUNT(*) FROM abc WHERE MOD(EXTRACT(DAY FROM cast("datecolumn" as timestamp))::int,2) = 1 

UPDATE abc SET datecolumn = 

CASE 

WHEN MOD(EXTRACT(DAY FROM cast("datecolumn" as timestamp))::int,2) = 0 

THEN datecolumn + MAKE_INTERVAL(YEARS := 2022 - EXTRACT(YEAR FROM datecolumn)::INTEGER)

ELSE datecolumn + MAKE_INTERVAL(YEARS := 2021 - EXTRACT(YEAR FROM datecolumn)::INTEGER) END

RDBMS vs NOSQL vs HADOOP

 As the world becomes more information-driven than ever before, a major challenge has become how to deal with the explosion of data. The most common description of Data talks about the four V’s: Volume, Velocity, Variety, and Veracity.

                Volume - Represents the size of data.

                Velocity - refers to the speed with which data is generated.

                Variety - About data in many forms:  structured, semi-structured, and unstructured data.

                Veracity - How accurate or truthful data. Reduce inconsistencies, incompleteness, and ambiguities.

Here is an overview of important technologies to know about how the pieces of information are stored and retrieved.

Traditional RDBMS

                The Relational Databases (RDBMS) (SQL) have dominated the Database market, and they have done a lot of good. So what changed? Web technology started the revolution. Today, many people shop on Amazon. RDBMS was not designed to handle the number of transactions that take place on Amazon every second. The primary constraining factor was the RDBMS’ schema.

NoSQL Database Systems

                NoSQL (commonly referred to as "Not Only SQL") represents a completely different framework of databases that allows for high-performance, agile processing of information at a massive scale. Also offered an alternative by eliminating schemas at the expense of relaxing ACID principles.

Hadoop

                Hadoop is a file system and not a database. Although Hadoop and associates (Hbase, Mapreduce, Hive, Pig, Zookeeper) have turned it into a mighty database, Hadoop is a scalable, inexpensive distributed filesystem with fault tolerance.