QUERY_STRING_NULL_HANDLING

Also known as

query_null_handling, query_string_null, query_null (with or without @ prefix)

Controls how clients can pass NULL values to PostgreSQL function parameters via query string.

Since query strings can only contain text values, there's no native way to represent SQL NULL. This annotation defines what query string value should be interpreted as NULL.

Syntax

@query_null <mode>
@query_string_null_handling <mode>

Values

Value	Alias	Result
empty	empty_string	Empty query string value (?param=) is interpreted as NULL
null	null_literal	Literal string "null" (?param=null) is interpreted as NULL
ignore		No special NULL handling - values are passed as-is (default)

Behavior Explained

Ignore Mode (Default)

By default (ignore), no special NULL handling is applied. Query string values are passed as-is to the function:

GET /api/func/?t=         →   _t = '' (empty string)
GET /api/func/?t=null     →   _t = 'null' (literal string "null")
GET /api/func/?t=hello    →   _t = 'hello'

If the parameter is not provided at all, the function receives NULL:

GET /api/func/            →   _t = NULL (parameter not provided)

EmptyString Mode

When set to empty_string (or empty), an empty query string value is interpreted as SQL NULL:

GET /api/func/?t=         →   _t = NULL
GET /api/func/?t=null     →   _t = 'null' (literal string)
GET /api/func/?t=hello    →   _t = 'hello'

This allows clients to explicitly pass NULL by providing an empty value.

NullLiteral Mode

When set to null_literal (or null), the literal string "null" (case-insensitive) is interpreted as SQL NULL:

GET /api/func/?t=null     →   _t = NULL
GET /api/func/?t=NULL     →   _t = NULL
GET /api/func/?t=         →   _t = '' (empty string)
GET /api/func/?t=hello    →   _t = 'hello'

This allows clients to explicitly pass NULL by providing the string "null".

Examples

Using Empty String for NULL

create function get_nullable_param(_t text)
returns text language sql as 'select _t';

comment on function get_nullable_param(text) is '
query_string_null_handling empty_string
';

Request	Parameter Value
GET /api/get-nullable-param/?t=	_t = NULL
GET /api/get-nullable-param/?t=hello	_t = 'hello'

Using "null" String for NULL

create function get_data(_filter text)
returns text language sql as 'select _filter';

comment on function get_data(text) is '
query_string_null_handling null_literal
';

Request	Parameter Value
GET /api/get-data/?filter=null	_filter = NULL
GET /api/get-data/?filter=	_filter = '' (empty string)
GET /api/get-data/?filter=active	_filter = 'active'

Default Behavior (No Special Handling)

create function search(_query text)
returns text language sql as 'select _query';

comment on function search(text) is '
query_string_null_handling ignore
';

Request	Parameter Value
GET /api/search/?query=	_query = '' (empty string)
GET /api/search/?query=null	_query = 'null' (literal string)
GET /api/search/	_query = NULL (parameter omitted)

Configuration Default

You can set the default behavior for all endpoints in appsettings.json:

{
  "NpgsqlRest": {
    "QueryStringNullHandling": "EmptyString"
  }
}

Available values: Ignore (default), EmptyString, NullLiteral.

This sets the default for all endpoints, which can then be overridden per-endpoint using comment annotations.

Related

• NpgsqlRest Options configuration - Configure default null handling
• Comment Annotations Guide - How annotations work
• Configuration Guide - How configuration works

Related Annotations

• REQUEST_PARAM_TYPE - Control parameter source
• RESPONSE_NULL_HANDLING - NULL in responses