Postgres: how does a default value get populated?

Question

To be clear, I want to know what is the mechanism that is used to populate the default value not the SQL syntax needed to create the default value constraint on the table.

Does Postgres use some kind of trigger that updates the default value if it is missing or something else?. I couldn't find an explanation on the official website.

Unless this is just curiosity about PG internals, I think you will get a better response stating why you want to know. — Andrew Lazarus
– Andrew Lazarus, Commented Jan 30, 2019 at 19:15
Yeah just curious to get a high level understanding of when and how that is done. — shaffooo
– shaffooo, Commented Jan 30, 2019 at 19:23
You could browse the source code. (remember: PG is open source) — wildplasser
– wildplasser, Commented Jan 30, 2019 at 20:33
That is exactly what I wanted to avoid- don't know much of C. I thought somebody would know and willing to help, hence posted it on stackoverflow. Not interested in too much detail just a high level understanding which google failed to help with. — shaffooo
– shaffooo, Commented Jan 30, 2019 at 20:39
The "high level" picture is that whenever you ask the server to execute a statement, it does it by running some code, and when you execute an INSERT statement, some of that code will fill in the missing columns with defaults. There are no triggers involved, or anything else that you might call a "mechanism"; it's just written that way. — Nick Barnes
– Nick Barnes, Commented Jan 30, 2019 at 23:16

Laurenz Albe · Accepted Answer · 2019-01-31 11:17:47Z

2

This happens in the rewriteTargetListIU function in src/backend/rewrite/rewriteHandler.c. The comment says it all:

/*
 * rewriteTargetListIU - rewrite INSERT/UPDATE targetlist into standard form
 *
 * This has the following responsibilities:
 *
 * 1. For an INSERT, add tlist entries to compute default values for any
 * attributes that have defaults and are not assigned to in the given tlist.
 * (We do not insert anything for default-less attributes, however.  The
 * planner will later insert NULLs for them, but there's no reason to slow
 * down rewriter processing with extra tlist nodes.)  Also, for both INSERT
 * and UPDATE, replace explicit DEFAULT specifications with column default
 * expressions.

So this happens during query rewrite, which is the step between parsing the SQL string and optimizing it.

answered Jan 31, 2019 at 11:17

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: how does a default value get populated?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related