How to detect if field's value changes in UPDATE trigger

use this approach if your field is NOT NULLable:

if NEW.field <> OLD.field then
raise notice 'something changes';
end if;





use this approach if your field is NULLable: # is the XOR of Postgres



false xor false = false
false xor true = true
true xor false = true
true xor true = false


if

( (NEW.field is null)::int # (OLD.field is null)::int ) = 1


OR

NEW.field <> OLD.field

then

raise notice 'something changes';

end if;

Calculate Age or Birthday

CREATE or replace FUNCTION CalculateAge(m_birthday date, m_datetoday date)
RETURNS INTEGER
AS
$$
BEGIN

RETURN

(
select
(
CASE
WHEN dateadd('y', datediff ('y', m_birthday, m_datetoday), m_birthday) > m_datetoday
THEN datediff ('y', m_birthday, m_datetoday) - 1
ELSE datediff ('y', m_birthday, m_datetoday)
END)

);

END;
$$ language 'plpgsql';

-- see my previous post on dateadd and datediff

gapless sequence without using extra table, however, locks the whole table

CREATE OR REPLACE FUNCTION get_tryrecord_lastid()
RETURNS int AS
$$
begin
lock table tryrecord;
return coalesce( (select tryrecord_id from tryrecord order by tryrecord_id desc limit 1) + 1, 1 );
end;
$$
LANGUAGE 'plpgsql' ;





CREATE TABLE tryrecord
(
tryrecord_id integer NOT NULL DEFAULT get_tryrecord_lastid(),
name character varying(20) NOT NULL,
CONSTRAINT pk_tryrecord PRIMARY KEY (tryrecord_id)
);



begin;
insert into tryrecord(name) values('see');
insert into tryrecord(name) values('great');
end;

gapless sequence without locking the whole table

create table therecord_lastid
(
last_id int default 0,
constraint pk_therecord_lastid primary key (last_id)
);
insert into therecord_lastid values(0);



CREATE OR REPLACE FUNCTION get_therecord_lastid()
RETURNS int AS
$$
declare
x int;
begin
update therecord_lastid set last_id = last_id + 1;
select last_id into x from therecord_lastid;
return x;
end;
$$
LANGUAGE 'plpgsql' ;





CREATE TABLE testrecord
(
testrecord_id integer NOT NULL DEFAULT get_therecord_lastid(),
name character varying(20) NOT NULL,
CONSTRAINT pk_testrecord PRIMARY KEY (testrecord_id)
);



begin;
insert into testrecord(name) values('see');
insert into testrecord(name) values('great');
end;

select * from testrecord

if you have many foreign keys, how to check that only one of them must not be null

create table shipment
(
int seq serial bigint not null

client_code,
supplier_code,
testing_company_code,
branch_code,

date_to_ship date,

constraint fk_shipment__client foreign key(client_code)
references client(client_code),


constraint fk_shipment__supplier foreign key(supplier_code)
references supplier(supplier_code),


constraint fk_shipment__testing_company foreign key(testing_company_code)
references testing_company(testing_company_code),

constraint fk_shipment__branch foreign key(branch_code)
references branch(branch_code),



constraint ck_shipment__company check
(
(client_code is not null)::int
+
(supplier_code is not null)::int
+
(testing_company_code is not null)::int
+
(branch_code is not null)::int
) = 1

);



Note: if you only need to test at least one of them is not null, just change = 1 to >= 1.

or better yet, do this:

constraint ck_shipment__company

check(coalesce(client_code, supplier_code, testing_company_code, branch_code) is not null)

Postgres is faster on Windows XP (on AMD only)

(an e-mail i wrote to my peers last july 23, guess it's better to post this on blog. these are all out-of-the-box installation)



this is my latest benchmarkings from fresh installation i performed last saturday, i use the same drive on all installation. Western Digital 160 GB.

Core 2 Duo E6700 2.67 GHz vs AMD64 3400+ 2.0 GHz

select * from technicalsheet_color order by techsheetid, recid limit 100:

c2d 2.67 GHz + ubuntu64 + postgresql = 6 seconds
amd64 2.0 GHz + ubuntu64 + postgresql = 9 seconds

c2d 2.67 GHz + winxp32 + postgresql = 12 seconds
amd64 2.0 GHz + winxp32 + postgresql = 8 seconds


as you can gloss from the results, the speed differences are not symmetrical, it is hard to draw one-sweeping-generalization™ conclusion ;-)

PostgreSQL on laptops doesn't bodes well for Windows XP users, because most of laptops are Core 2 Duo than AMD64/Turion. PostgreSQL runs two times faster on Core 2 Duo if you only use Linux.


upsides:
* PostgreSQL on Ubuntu64 is faster on Core 2 Duo
* PostgreSQL on WinXP32 is faster on AMD64


downsides:
* PostgreSQL on WinXP32 slow down miserably on Core 2 Duo
* on AMD64, PostgreSQL is only marginally faster on WinXP32 than its Ubuntu64 counterpart


it is interesting to note that Dave Cutler (Windows NT,2000,XP,Vista,2003 chief engineer) have work closely/co-developed AMD64 with AMD, this could explain why Windows runs marginally better on AMD64 than Core 2 Duo.
and also, maybe AMD64 or Athlon X2 runs better than Core 2 Duo, i haven't yet pitted AMD's 2.6 GHz against Core 2 Duo 2.67 GHz :-)


on Core 2 Duo, WinXP to Ubuntu gains two-fold speed increase, from 12 seconds to 6 seconds. at first, i thought AMD64 WinXP to Ubuntu would gain the same twofold speed increase (8 to 4 seconds), but as i benchmarked it, it wasn't.
AMD64 seems was designed to run Windows well, Microsoft's Dave Cutler and AMD collaboration at its best :-)






just draw your own conclusions :-)

i'll just choose the best combo of processor + os + database




p.s.


these are the results without the ordering:

select * from technicalsheet_color limit 100:

c2d 2.67 GHz + ubuntu64 + postgresql = 11 seconds
amd64 2.0 GHz + ubuntu64 + postgresql = 16 seconds


c2d 2.67 GHz + winxp32 + postgresql = 19 seconds
amd64 2.0 GHz + winxp32 + postgresql = 13 seconds

get the last balance of each account. select the last row of each group

get the last balance of each account

MS SQL-way:

select * from
accountinstallmentfinancingdetailfinal detail

inner join (select invoiceno, pmtstructureno, max (paymentsortorder) as lastrow
group by invoiceno, pmtstructureno) as lastrowofaccount

on lastrowofaccount.invoiceno = detail.invoiceno and lastrowofaccount.pmtstructureno = detail.pmtstructureno and lastrowofaccount.lastrow = detail.paymentsortorder;


PostgreSQL way (note: the above would also work on PostgreSQL):

select distinct on (invoiceno, pmtstructureno) *
from accountinstallmentfinancingdetailfinal
order by invoiceno, pmtstructureno, paymentsortorder desc;

PostgreSQL GROUP_CONCAT

CREATE TABLE produk
(
seq_id serial NOT NULL,
name character varying(100) NOT NULL
) ;


INSERT INTO produk (seq_id, name) VALUES (1, 'beer');
INSERT INTO produk (seq_id, name) VALUES (2, 'in');
INSERT INTO produk (seq_id, name) VALUES (3, 'beer');
INSERT INTO produk (seq_id, name) VALUES (4, 'can');
INSERT INTO produk (seq_id, name) VALUES (6, 'goods');
INSERT INTO produk (seq_id, name) VALUES (7, 'goods');
INSERT INTO produk (seq_id, name) VALUES (8, 'can');
INSERT INTO produk (seq_id, name) VALUES (5, 'goods');


name | id_of_duplicates
----------+------------------
beer | 1,3
can | 4,8
goods | 5,6,7
in | 2



MySQL:

select name, group_concat(cast(seq_id as char)) as id_of_duplicates
from produk
group by name
order by name;


PostgreSQL:

create aggregate array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

CREATE OR REPLACE FUNCTION _group_concat(text, text)
RETURNS text AS $$
SELECT CASE
WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL THEN $2
ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
END
$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat (
BASETYPE = text,
SFUNC = _group_concat,
STYPE = text
);




first approach:


select name, array_accum(seq_id)
from produk
group by name



select name, array_to_string(array_accum(seq_id), ',')
from produk
group by name;



second approach (mysql-compatible approach):

select name, group_concat(seq_id) as id_of_duplicates
from produk
group by name
order by name;


MySQL GROUP_CONCAT with ordering:

select name, group_concat(cast(seq_id as char) order by seq_id) as id_of_duplicates
from produk
group by name
order by name;

PostgreSQL equivalent:

select name, group_concat(distinct seq_id) as id_of_duplicates
from produk
group by name
order by name;

using customized sort:

select name, group_concat(seq_id) as id_of_duplicates
from
(
select name, seq_id
from produk
order by name, seq_id
) as x
group by name


PostgreSQL is a lot more nicer, it allows you to define your own aggregate function. GROUP_CONCAT is not built-in, with user-defined aggregate, you can create one.

return the number of digits after the decimal point

select greatest(length(abs(x - trunc(x))) - 2, 0) from (values (12345.12345::numeric), (1234.1234::numeric), (1234.123::numeric), (-1234.123::numeric), (1::numeric), (10::numeric)) as t(x);



select length(regexp_replace(x::numeric::text,'^.*\\.',''))
from
(values (12345.12345::numeric), (1234.1234::numeric), (-1234.123::numeric)) as t(x);

updates in PostgreSQL, MS SQL, MySQL

PostgreSQL: OK, MS SQL: OK (just change the "||" to "+"), MySQL: NOT OK

/* very orthogonal, since only one table allowed to be updated, no need to "set product.name = ", "set name = " will do. in fact if you do: "set product.name = ", postgres will disallow it */

update product set name = product.name || ':' || prod.name
from prod
where prod.seq_id = product.seq_id



**********************************

PostgreSQL: OK (good for re-using existing query, must alias the source table if the source table conflicts with to-be-updated-table name), MS SQL: NOT OK, MySQL: NOT OK

update product set name = product.name || ':' || prod.name
from product x
inner join prod on prod.seq_id = x.seq_id
where x.seq_id = product.seq_id /* don't forget to filter the to-be-updated-table to queried rows */



**********************************

PostgreSQL: NOT OK, MS SQL: NOT OK, MySQL: OK

/* note: even only one possible table can be updated, requires the fully-qualified name for the fields to be set. i.e. set product.name */

update product,prod set product.name = concat(product.name, ':', prod.name)
where prod.seq_id = product.seq_id;



**********************************

PostgreSQL: NOT OK, MS SQL: OK (good for re-using existing query), MySQL: NOT OK

update product set name = product.name + ':' + prod.name
from product /* limitation: cannot alias this, must be same name as the table to-be-updated */
inner join prod on prod.seq_id = product.seq_id

***********************************


PostgreSQL: NOT OK, MS SQL: NOT OK, MySQL: OK


update product join prod on prod.seq_id = product.seq_id

set product.name = concat(product.name, ':', prod.name);


/* nice update syntax, not ambiguous as MS SQL. similar to MS Access update join syntax, though not as good/flexible as PostgreSQL, wherein you can just drop-in your previous query to the update statement */


UPDATE April 17, 2019 Anonymous is correct. It's this simple in Postgres: UPDATE product set name = product.name || ':' || prod.name FROM prod WHERE prod.seq_id = product.seq_id

delete duplicate rows (and leaving one copy) in PostgreSQL, MS SQL, MySQL

MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK


delete from product where
seq_id not in (select min(seq_id) from product group by name)

to make this work with MySQL:

delete from product where
seq_id not in (select x from (select min(seq_id) as x from product group by name))




MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK

delete from product where
exists(select * from product x where x.name = product.name and product.seq_id > x.seq_id)



MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK

/* advantage: can re-use existing query's logic for finding the first record of each duplicate */

delete from product where
exists(
select x.name, min(x.seq_id)
from product x
where x.name = product.name
group by x.name
having product.seq_id > min(x.seq_id)
);


MS SQL: OK, PostgreSQL: NOT OK, MySQL: OK

delete product from product
inner join product b
on b.name = product.name and product.seq_id > b.seq_id;

MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK


delete from product where
seq_id in
(select a.seq_id from product a inner join product b on a.name = b.name and a.seq_id > b.seq_id);

to make this work with MySQL:

delete from product where
seq_id in
(select x from (select a.seq_id as x from product a inner join product b on a.name = b.name and a.seq_id > b.seq_id));

/* MySQL is pretty lame in this last case */

PostgreSQL DATEDIFF

version 8.0 and higher

CREATE OR REPLACE FUNCTION datediff(p_Interval "varchar", p_DateFrom date, p_DateTo date)
RETURNS int4 AS
$BODY$




BEGIN
if p_Interval = 'm' then
return ((date_part('y',p_DateTo) * 12) + date_part('month',p_DateTo))
- ((date_part('y',p_DateFrom) * 12) + date_part('month',p_DateFrom));
elseif p_Interval = 'y' then
return date_part('y',p_DateTo) - date_part('y',p_DateFrom);
else
raise exception 'Datediff: Interval not supported';
return 0;
end if;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;





before version 8.0



CREATE OR REPLACE FUNCTION datediff("varchar", date, date)
RETURNS int4 AS
$BODY$

DECLARE
p_Interval ALIAS FOR $1;
p_DateFrom ALIAS FOR $2;
p_DateTo ALIAS FOR $3;




BEGIN
if p_Interval = 'm' then
return ((date_part('y',p_DateTo) * 12) + date_part('month',p_DateTo))
- ((date_part('y',p_DateFrom) * 12) + date_part('month',p_DateFrom));
elseif p_Interval = 'y' then
return date_part('y',p_DateTo) - date_part('y',p_DateFrom);
else
raise exception 'Datediff: Interval not supported';
return 0;
end if;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;