Getting SELECT to return a constant value even if zero rows match - Postgresql

 SELECT COALESCE(status,'not a valid token') AS status,last_sync_data_time FROM

 (

SELECT 

CASE WHEN CURRENT_TIMESTAMP <= license_end_date THEN 'valid' ELSE 'expired' END AS status, last_sync_data_time

FROM adempiere.license_tomato_app

WHERE key = '7EF3D9B8183D9C7AB90242AF5D011ABE'

union all 

select  null, '1900-01-01 00:00:00' last_sync_data_time where not exists (select 1 from adempiere.license_tomato_app WHERE key = '7EF3D9B8183D9C7AB90242AF5D011ABE')

) t

Backup a table in postgresql

 SELECT * INTO abc_bk FROM abc;

Open Source Big Data Databases

Cassandra

CouchDB

FlockDB

HBase

Hibari

MongoDB

Neo4j

OrientDB

Riak

Terrstore

Postgresql: Get all tables count from DB

 SELECT schemaname,relname,n_live_tup 

  FROM pg_stat_user_tables 

  ORDER BY n_live_tup DESC;

Postgresql: Get all primary keys from all the tables in DB

SELECT 

kcu.table_schema, kcu.table_name, tco.constraint_name, kcu.ordinal_position AS position, kcu.column_name AS key_column

FROM information_schema.table_constraints tco

JOIN information_schema.key_column_usage kcu 

    ON kcu.constraint_name = tco.constraint_name

    AND kcu.constraint_schema = tco.constraint_schema

    AND kcu.constraint_name = tco.constraint_name

WHERE tco.constraint_type = 'PRIMARY KEY'

ORDER BY 

kcu.table_schema, kcu.table_name, position;

Columns

table_schema - PK schema name

table_name - PK table name

constraint_name - PK constraint name

position - index of column in table (1, 2, ...). 2 or higher means key is composite (contains more than one column)

key_column - PK column name

Postgresql: Find the 'N' th occurrences in a string

 Sample Table:

create table filter_table (filter_type varchar(500));

Insert Query:

insert into filter_table (filter_type) values ('1111111 > 22222 > 3333333> 4444444> 123456789 > abcdefghi > 987654321 > zyxwvuts > 0000000 >');

Query to find the occurrences:

select substring(filter_type from '^(([^ ]* ){2})')

from filter_table

Postgresql: Important Queries

 --To get the list of tables in Postresql:

SELECT * FROM information_schema.tables WHERE table_name = 'tablename'

--To get the list of columns in the Postgresql tables:

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tablename';