Postgres "Default" (column data) behaviour

 I've started working a lot with Postgres lately and one of the interesting things I've noticed is how the DEFAULT value is treated when doing an insert into a table.

For example, let's take the following test table;

DROP TABLE IF EXISTS ap_test;

CREATE TABLE ap_test(

    id SERIAL NOT NULL,

    stuff TEXT DEFAULT 'ok',

    CONSTRAINT ap_test_pk PRIMARY KEY (id)

);

So now let's look at the necessary inserts;

INSERT INTO ap_test(stuff) VALUES (null);

INSERT INTO ap_test(stuff) VALUES ('test');

INSERT INTO ap_test(stuff) VALUES (DEFAULT);

SELECT * FROM ap_test;

So basically we inserting three values into the table; NULL, 'test', and DEFAULT.

You get the following values in the table;

So "NULL" is not triggering the columns default value (as "NULL" is a value in itself). 

The problem comes when you're working with this table outside of SQL and using code. It's fairly typical to structure your code using a parametised string. For example;

insertSQL := 'INSERT INTO ap_test(stuff) VALUES (%)'

The problem is how do you handle the difference between NULL and DEFAULT. You need to include the logic in your code which ensures that if you mean NULL then it writes null, but if you mean "use the default" it writes DEFAULT.

I just found this interesting (coming from Oracle) and an environment where default values were handled very differently.