Some pgSQL
by Bartłomiej Wójtowicz
Grouping
Grouping
Grouping: group by
select id, user_id, score, max_score, published_at from user_result group by user_id
- it worked in MySQL & SQLite
in PG:
ERROR: column "user_result.id" must appear in the GROUP BY clause or be used in an aggregate function
Grouping: group by
Fix "group by" error by putting all columns in group by - NO!
WRONG RESULT:
Grouping: aggregation
2nd approach: aggregate results
select
last_element(id),
user_id,
last_element(score),
last_element(max_score),
last_element(published_at)
from user_result
group by user_id
Grouping: aggregation
select
last_element(id order by published_at),
user_id,
last_element(score order by published_at),
last_element(max_score order by published_at),
last_element(published_at order by published_at)
from user_result
group by user_id
Grouping: aggregation
select
last_element(q.id),
q.user_id,
last_element(q.score),
last_element(q.max_score),
last_element(q.published_at)
from
(select id, user_id, score, max_score, published_at from user_result order by user_id, published_at) q
group by q.user_id
Grouping: aggregation
CREATE FUNCTION last_element_fn(anyelement, anyelement) RETURNS anyelement AS
$$
SELECT $2;
$$
LANGUAGE sql;
CREATE AGGREGATE last_element(anyelement) (
SFUNC=last_element_fn,
STYPE=anyelement
);
"Grouping": CTE + Window functions
with latest_result as (
select
id, user_id, score, max_score, published_at,
row_number() over w as _row_index
from user_result
window w as (
partition by user_id
order by user_id, published_at desc
)
)
select * from latest_result where _row_index = 1
"Grouping": CTE + Window functions
Result:
"Grouping": CTE + Window functions
with latest_result as (
...
)
select * from latest_result -- where _row_index = 1
Utils
Ternary operator
SELECT
CASE
WHEN a = 1 THEN
true
ELSE
false
...
in MySQL:
SELECT
IF(a = 1, true, false) ...
Ternary operator - helper fn
CREATE FUNCTION _IF(boolean,anyelement, anyelement)
RETURNS anyelement AS $$
SELECT
CASE
WHEN $1 THEN
$2
ELSE
$3
END;
$$
LANGUAGE SQL;
SELECT _IF(a = 1, true, false) ...
Ternary operator - possible problem
select _IF(score > 0, score, 0.0) from user_result
ERROR: function _if(boolean, double precision, numeric) does not exist - why?
score is double precision
0.0 is numeric
so what is the final column type???
_if(score > 0, score, 0.0::double precision)
_if(score > 0, score::numeric, 0.0)
Ruby equivalent: Time.now.utc
CREATE FUNCTION now_utc()
RETURNS timestamp without time zone AS
$$
BEGIN
RETURN CAST(now() at time zone 'utc' AS timestamp);
END;
$$
LANGUAGE plpgsql;
--
select now_utc();
Time.now.utc
CREATE FUNCTION update_doc_when_notes_change() RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
-- if not global note then update country when note is deleted
IF OLD.country_id IS NOT NULL THEN
UPDATE countries SET updated_at = now_utc() WHERE countries.id = OLD.country_id;
RETURN OLD;
END IF;
ELSIF TG_OP = 'INSERT' THEN
-- if not global note then update country when note is created
IF NEW.country_id IS NOT NULL THEN
UPDATE countries SET updated_at = NEW.updated_at WHERE countries.id = NEW.country_id;
RETURN NEW;
END IF;
ELSIF TG_OP = 'UPDATE' THEN
...