Let's say I create the following function:

create or replace function my_new_function(
    p_something text default null

But if I go to check the function on PostgreSQL I see this instead:

CREATE OR REPLACE FUNCTION my_new_function(p_something text default NULL::text)

Why does it do that?


In short: you stumbled across an internal detail, of how postgres handles datatypes in default values. You likely just want to keep it.

As described in Why do I need to cast NULL to column type?, the default type of NULL is unknown in most cases, so the type info is appended when you set a default. (The same can happen to column defaults also)

When postgres displays the function, it likely does not remember how you created it, so it displays null::text. For column defaults, postgres sometimes recognizes that the user would find the typecast irrelevant and removes it, but sometimes it does not (not sure when exactly). So if you state default null that can be seen as default NULL::varchar(30) for instance. For tables, this can be "fixed" with ... drop default, but I think this would make the function parameter required.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.