SQL helper

Programming Mar 13 ,2020

This post acts as helper page for sql queries that are useful but i keep forgetting them.

Read from VALUES keyword. This is helpful when not querying from database but feeding the value directly. Use cases include : bulk insert and update.

SELECT X.name FROM (
	values ('a', 1, 'M'), ('b', 2, 'F'), ('c', 3, 'M'), ('d', 45, 'F'), ('e', 5, 'M') 
	) 
AS X("name","age", "gender")

Example :  UPDATE

Updating the table as shown below is more efficient than looping the dataset and updating them row by row. Time for execution is less when doing bulk update.

UPDATE user as u
    SET
    name = c.entity_id,
    age = c.discovery_confidence,
    gender = c.discovery_message::jsonb,
    from (values
    ('a', 1, 'M'), ('b', 2, 'F'), ('c', 3, 'M'), ('d', 45, 'F'), ('e', 5, 'M')
    ) as c(name, age, gender)
where u.name=c.name

 

Use of With statement

WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE.

Example 1 : insert statement

WITH missing_rs as (
	SELECT o.id from organizations o 
	LEFT JOIN resource_settings r on o.id =r.resource_id
	AND r.resource_type='ORGANIZATION' and r.setting_group='global_settings'
	WHERE r.setting_group is null
),
defal as (
	SELECT config_value config from configurations c where config_type='DefaultSettings'
)
INSERT INTO resource_settings (resource_type, resource_id, setting_group, settings, description)
SELECT 'ORGANIZATION', m.id, 'global_settings', d.config, 'Global settings for organization'
FROM missing_rs m cross join defal d;

 

Example 2 : select statement

with m_id as (
	select migration_planner_id from migration_planner
), m_sprint_composition as(
	select distinct(resource_name) as resource_name from migration_sprint_composition 
) select  m.migration_planner_id, 'Relocate',msc.resource_name,
case 
	when msc.resource_name = 'customer_resource' then 0
	when msc.resource_name = 'loe_per_server' then 2
	else 1
end
from m_id m cross join m_sprint_composition msc;

 

With Queries

 With queries are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECTINSERTUPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECTINSERTUPDATE, or DELETE.

with x("vmidentifier","vcenter", "disk_gb", "guest_os") as (
	values ('machine12','vcenter',0.5703125,'Other Linux (64-bit)'),
		('machine0111','vcenter',0.5,'Other Linux (64-bit)'),
		(test_machine','vcenter',0.5703125,'Other Linux (64-bit)'),
		('Powfu','vcenter',0.5,'Other Linux (64-bit)')
        )
update some_table ev 
set 
	col1 = x.disk_gb * 1024*1024*1024::int8,
	col2 = x.guest_os
from x inner join entity e on (e.some_col1 = x.vmidentifier and e.sole_col2 = x.vcenter)
where e.entity_id = ev.entity_id 
and e.organization_id = 'asdasd';