Postgres Gotchas - the "pg_dump" and the "null" Value Fiasco

PostgreSQL has been my staple since the year starting at U.E. 1262304000. Back then, the cutting edge version was 8.3.x. Today, it is 12.x.x. Whatever. 

I learnt multiple lessons. I really loved the jump from platform-specific installed client to the web-based client. There was hiccups but, nowadays, we even dockerize the pg_admin client. What a breakthrough. 

Long story short, I did the following:

SELECT L.A, R.B

FROM TABLE_A AS A

LEFT JOIN TABLE_B AS B

USING (KEY_1, KEY_2, KEY_3);

-- note that KEY_3 is all null in both tables

I ended up with R.B is always null ! How come !?

The answer is the way postgres is handling null value. In short, null = null evaluates to false. 

Let's move on to lesson number 2. It is pg_dump ignores -n (or --schema) parameter for data. 

According to the RTFM, -n is to specify the schema. However, it works only for the DDL of the dumps. The data portion, when there are two tables with the same name, it dumps the table in the schema it finds first. 

What a weird gotchas! 

Logged on Doughnut I/O. U.E. 1584685653