Dynamic unpivoting: SQL Server said, Postgres said

SQL Server version:

declare @tx table(
    id int identity(1, 1) not null,
    data varchar(100),
    column1 int,
    column2 int,
    column3 int
);

insert into
    @tx(data, column1, column2, column3)
values
    ('data1', 1, 2, 3),
    ('data2', 4, 5, 6),
    ('data3', 7, 8, null);

select
    a.id,
    a.data,
    c.item,
    c.value 
from
    @tx a
    cross apply (
        values
            (
                cast(
                    (
                        select
                            a.* for xml raw
                    ) as xml
                )
            )
    ) as b(xmldata)
    cross apply (
        select
            item  = xattr.value('local-name(.)', 'varchar(100)'),
            value = xattr.value('.', 'int')
        from
            b.xmldata.nodes('//@*') as xnode(xattr)
        where
            xnode.xattr.value('local-name(.)', 'varchar(100)') not in 
                ('id', 'data', 'other-columns', 'to-exclude')
    ) c

SQL Server can't include null values though.

Postgres version:

Note: Should run this first before being able to use hstore functionality: create extension hstore

create temporary table tx(
    id int generated by default as identity primary key,
    data text,
    column1 int,
    column2 int,
    column3 int
) on commit drop;

insert into
    tx(data, column1, column2, column3)
values
    ('data1', 1, 2, 3),
    ('data2', 4, 5, 6),
    ('data3', 7, 8, null);
    
with a as (
    select
        id,
        data,
        each(hstore(tx.*) - 'id'::text - 'data'::text) as h
    from
        tx
)
select
    id,
    data,
    (h).key as item,
    (h).value::int as value
from
    a 
-- this would work too:
-- where (h).key not in ('id', 'data', 'other-columns', 'to-exclude')

No problem with Postgres, it include nulls


If the number of columns to unpivot is not so dynamic, can do this in Postgres:

select tx.id, tx.data, x.*
from tx
join lateral (
 values
     ('column1', column1), 
     ('column2', column2), 
     ('column3', column3)
) as x(item, value) on true

Equivalent to SQL Server:

select tx.id, tx.data, x.*
from @tx tx
cross apply (  
    values
     ('column1', column1), 
     ('column2', column2), 
     ('column3', column3)    
) as x(item, value);

Both Postgres and SQL Server include nulls in result

https://stackoverflow.com/questions/55731155/combined-semi-transpose-of-a-data/55731461

Add column with default value and nullity

create table something
(
p int not null
);

insert into something(p) values(1);


alter table something
add q int default 6 not null;


alter table something
add r int default 8 null;


select * from something;

Postgresql output:

p q r
1 6 8

Sql Server output:

p q r
1 6 null

When DEFAULT doesn't default

I almost gave a wrong code review to a colleague:

It's better to use NOT NULL:

ALTER TABLE dbo.blah
ADD IsTransferred BIT NULL DEFAULT (0);

Is there a business or technical reason why IsTransferred should be nullable?

On the above DDL for IsTransferred, nullable or not nullable, IsTransferred defaults to 0, hence this is a superfluous code:

-- initialize new column, i.e., NULL columns, to "not transferred"
UPDATE dbo.blah
SET IsTransferred = 0
WHERE IsTransferred IS NULL ;


Further read: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx

The code could be shortened and optimized but I'm not inclined to advise it; this query is just a one-time thing, no need to micro-optimize. The code is good enough

Spotted the wrong thing on the above advice? The above advice is correct if we are using PostgreSQL. However we are using SQL Server, the newly-added IsTransferred field will default to null if the field is nullable regardless of the above DDL indicating a default value of false(0).


So the UPDATE above is not a superfluous code, at least on SQL Server, the UPDATE is still needed despite the DEFAULT 0 clause. PostgreSQL (and other RDBMSes for that matter) is intuitive in this regard, it honors the DEFAULT clause regardless of the field is nullable or not.


Further read: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx


PostgreSQL-related news: https://twitter.com/spolsky/status/433323487961178113



Happy Coding! ツ

Use IMMUTABLE on PostgreSQL function to avoid optimization fence

I thought this is the PostgreSQL equivalent of SQL Server's efficient inline table-valued function:

create or replace function get_orgs() returns table
(
ID int,
Favorite int
)
as
$$ 
    select p.person_id, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
$$ language sql;

Using that function on this expression..

select p.*, o.* 
from person p
join get_orgs() o on p.person_id = o.ID
where p.person_id < 4

..produces a divide-by-zero error on PostgreSQL, while SQL Server does not materializes the rows that are greater than or equal to 4, hence this doesn't produce an error on SQL Server:

ERROR:  division by zero
CONTEXT:  SQL function "get_orgs" statement 1

********** Error **********

ERROR: division by zero
SQL state: 22012
Context: SQL function "get_orgs" statement 1

To achieve the same optimized execution plan of SQL Server on PostgreSQL, must add IMMUTABLE on the function:

create or replace function get_orgs() returns table
(
ID int,
Favorite int
)
as
$$ 
    select person_id, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
$$ language sql IMMUTABLE;

When that function is used on the the query above, it doesn't yield divide-by-zero anymore, it's now efficient, it doesn't eager load the function's result, it's like the function is inlined on the query itself, the query that joins to the IMMUTABLE function behaves like a table-deriving query, very efficient, no divide-by-zero will occur, i.e., the RDBMS just expand the function's query to other query, divide-by-zero error won't happen, to wit:

select p.*, o.* 
from person p
join
(
    select p.person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
)
o on p.person_id = o.ID
where p.person_id < 4;

Produces this output, has no divide by zero error:

 person_id | lastname  |  firstname   | nickname | favorite_number | id | favorite 
-----------+-----------+--------------+----------+-----------------+----+----------
         2 | mccartney | james paul   | paul     |                 |  2 |        7
         3 | harrison  | george       |          |                 |  3 |        7
         1 | lemon     | john winston | john     |                 |  1 |        7
(3 rows)

However, PostgreSQL, unlike SQL Server, has an optimization fence on its CTE, i.e., PostgreSQL eagerly loads the result of the CTE. SQL Server's CTE is more efficient than PostgreSQL's CTE on this regard:

with o as
(
    select p.person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
)
select p.*, o.* 
from person p
join o on p.person_id = o.ID
where p.person_id < 4;

That CTE yields the following error on Postgresql, a proof that Postgresql eagerly load the result of the CTE; while on SQL Server it has no error, SQL Server doesn't eagerly load the result of CTE, a proof that SQL Server sees CTE as subject to execution plan rather than an eager-loading mechanism to fetch the results. Eagerly-loaded results has no chance to be optimized, as it looks like a blackbox from the caller, the caller can't optimize what's inside

ERROR:  division by zero

********** Error **********

ERROR: division by zero
SQL state: 22012

If we will follow C language principle design, I wishes PostgreSQL to use an existing keyword to indicate we don't want an optimization fence on a CTE:

with o immutable as
(
    select p.person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then p.person_id / 0 else 7 end
    ) eorg(ounits) on true
)
select p.*, o.* 
from person p
join o on p.person_id = o.ID
where p.person_id < 4;

If a CTE's query can be re-used, the query is better be made as view instead, a query inside a view don't have optimization fence too, i.e., it also behaves as table-deriving query, view's query just get expanded to other query, this won't produce divide-by-zero error:

create or replace view vw_get_orgs as
    select person_id as ID, eorg.ounits
    from person p
    join lateral
    (
        select case when p.person_id = 4 then person_id / 0 else 7 end
    ) eorg(ounits) on true;


select p.*, o.* 
from person p
join vw_get_orgs o on p.person_id = o.ID
where p.person_id < 4;

Happy Coding!

SQL Server Said, PostgreSQL Said. APPLY and LATERAL

SQL Server CROSS APPLY:

select m.*, elder.*
from Member m
cross apply
(
    select top 1 ElderBirthDate = x.BirthDate
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
) as elder
order by m.BirthDate

Equivalent on PostgreSQL:

select m.*, elder.*
from Member m
join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
 limit 1
) as elder on true -- Though PostgreSQL's LATERAL offers more flexibility than SQL Server's APPLY, I haven't yet found a use case to use a condition on LATERAL, hence the hardcoded true
order by m.BirthDate

Note, a JOIN LATERAL(explicitly INNER JOIN LATERAL) with a condition of always true, is essentially a cross join. We can rewrite the PostgreSQL code above as follows:

select m.*, elder.*
from Member m
cross join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
    limit 1
) as elder -- we don't need to put any condition here anymore since this is a cross join
order by m.BirthDate

SQL Server OUTER APPLY:

select m.*, elder.*
from Member m
outer apply
(
    select top 1 ElderBirthDate = x.BirthDate, ElderFirstname = x.Firstname
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
) as elder
order by m.BirthDate

Equivalent on PostgreSQL:

select m.*, elder.*
from Member m
left join lateral 
(
    select x.BirthDate as ElderBirthDate, x.FirstName as ElderFirstName
    from Member x 
    where x.BirthDate < m.BirthDate 
    order by x.BirthDate desc
    limit 1
) as elder on true -- Though PostgreSQL's LATERAL offers more flexibility than SQL Server's APPLY, I haven't yet found a use case to use a condition on LATERAL, hence the hardcoded true
order by m.BirthDate

Related: http://www.ienablemuch.com/2012/04/outer-apply-walkthrough.html

Example: http://www.sqlfiddle.com/#!17/50ee1/8

Happy Coding! ツ

Postgresql exception-catching rocks!

Gotta love Postgres. It always yield back the control to you when an exception occur.

Given this:

create table z
(
i int not null primary key,
zzz int not null
);

Try both(one at a time) alter table z drop column aaa; and alter table z add column zzz int;, your code can detect the DDL exceptions

do $$


begin

    -- alter table z drop column aaa;
    alter table z add column zzz int;


exception when others then 

    raise notice 'The transaction is in an uncommittable state. '
                     'Transaction was rolled back';

    raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
end;


$$ language 'plpgsql';

Here are the errors, both kind of errors are catchable:

Contrast that with Sql Server, try both(one at a time) alter table z drop column aaa; and
alter table z add zzz int;

begin try

    begin transaction

    -- alter table z drop column aaa;
    alter table z add zzz int;

    commit tran;

end try
begin catch 

    print 'hello';
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

end catch

print 'reached';

Here are the errors for SQL Server:

Catchable error:


Uncatchable error:

Sql Server won't let you catch the error on alter table z add column zzz int;