How to create a Datum for database NULL in PostgreSQL a server side C function?

Question

I was just wondering how to create a Datum for the database NULL value in PostgreSQL?

I known there is PG_RETURN_NULL() for returning a database NULL in a LANGUAGE C function. But I just want to create a Datum for NULL, e.g. to be stored in an array (or record). Should I just use (Datum) 0?

(This is with PostgreSQL 12)

Laurenz Albe · Accepted Answer · 2020-04-17 14:19:42Z

2

0 is a valid value for many data types, so that wouldn't work.

Wherever you have a nullable Datum in PostgreSQL server code, you also have a bool flag (typically called isnull) that indicates if the Datum is NULL or not.

Compare the following definition from src/include/postgres.h:

/*
 * A NullableDatum is used in places where both a Datum and its nullness needs
 * to be stored. This can be more efficient than storing datums and nullness
 * in separate arrays, due to better spatial locality, even if more space may
 * be wasted due to padding.
 */
typedef struct NullableDatum
{
#define FIELDNO_NULLABLE_DATUM_DATUM 0
    Datum       value;
#define FIELDNO_NULLABLE_DATUM_ISNULL 1
    bool        isnull;
    /* due to alignment padding this could be used for flags for free */
} NullableDatum;

answered Apr 17, 2020 at 14:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

thor Over a year ago

Thanks! So if I want to return a tuple (NULL, 1.0), how to use NullableDatum?

Laurenz Albe Over a year ago

That depends on the code - how and where exactly do you want to use it? If it is just your own code, and you are not interacting with core code, you can do it however you want. Otherwise the answer depends on where and how you want to use the NULL.

thor Over a year ago

I think I understand your answer now. I was looking at this line elem = PG_ARGISNULL(1) ? (Datum) 0 : PG_GETARG_DATUM(1); in postgres code for array_agg_transfn, and was confused about the (Datum) 0 part. Thanks for the help.

Laurenz Albe Over a year ago

That code means: if the first argument IS NULL, set elem to 0, otherwise set it to the value of the first argument.

Collectives™ on Stack Overflow

How to create a Datum for database NULL in PostgreSQL a server side C function?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related