PostgreSQL: which columns can be null
Which columns can be null
-- name: ListOrders
SELECT o.id, o.placed_at, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id;
For each column of a query, SqlSource says whether the column can be null or is never null. If orders.id, orders.placed_at and customers.name are all NOT NULL, the columns id and placed_at of the sample are never null, but name can be null. The LEFT JOIN gives a null name to an order with no customer.
The usual reasons that a column can be null are an outer join, a view, or an expression such as initcap(name). A call of a function that the lists below do not name is such an expression. The lists below give every rule.
SqlSource treats a column as one that can be null unless it can show that the column is never null. If it treats a column that is never null as one that can be null, the cost is a null check in your code. It never gives you a wrong value.
SqlSource can tell that a column is never null when the column is one of these:
A
NOT NULLcolumn of a table, when no outer join of the query can fill it with null.count(*), orcount(x)of anything, or a ranking over a window:row_number(),rank(),dense_rank(),percent_rank()orcume_dist(). A count is0for no rows, never null.A constant other than
NULL, such as1or'active'.A test:
IS NULL,IS NOT NULL,IS DISTINCT FROMorIS NOT DISTINCT FROM.Arithmetic or a comparison of values that are never null, in one of these forms:
+,-,*or/of two numbers;=,<>,<,<=,>or>=of two numbers, two texts, two booleans, or two values of one ofdate,timestampandtimestamp with time zone;||of two texts.
One of these functions, over values that are never null:
lowerorupperof a text;lengthof atextor avarchar(not of acharacter(n));date_truncof adate, atimestampor atimestamp with time zone, without a time zone argument.
A cast of a value that is never null, in one of these ways:
- from one number type to another;
- from one text type to another;
- between
date,timestampandtimestamp with time zone.
One of the expressions above over a system column of a table, such as
xmax = 0inRETURNING (xmax = 0) AS inserted. SqlSource treats a system column on its own, as inRETURNING xmax, as one that can be null. The exception is a column of aUNION, anINTERSECTor anEXCEPT, at the end of this list. There a system column of a table counts as never null:SELECT xmin FROM a UNION ALL SELECT xmin FROM bis never null.COALESCE,GREATESTorLEASTwith an argument that is never null, or aCASEwith anELSEwhose every result is never null.- The other rules of this list count such a form as a value that is never null. So arithmetic, a comparison, a function, a cast, an aggregate and a subquery's value over it can count too. For example,
sum(coalesce(x, 0))in a query with aGROUP BYis never null, unless theWHEREsets every column of theGROUP BYto one value. - This holds only where the tool knows the type of the form from its arguments that are never null. A form over arguments of different types, or of a type that the tool does not know, counts only on its own. The form is never null, but no rule of this list counts an expression over it.
- The other rules of this list count such a form as a value that is never null. So arithmetic, a comparison, a function, a cast, an aggregate and a subquery's value over it can count too. For example,
array_agg(...)in a query with aGROUP BYof one column or more, with noFILTERand noOVER. Each group has a row, and the array of a row is never null. SqlSource treats it as one that can be null when theWHEREsets every column of theGROUP BYto one value, asWHERE o.user_id = @userId GROUP BY o.user_iddoes.jsonb_build_object(...)andjson_build_object(...), unless the call is written withVARIADIC. A call written withVARIADICgives null for a null array.A range's constructor (
int4range,int8range,numrange,tsrange,tstzrangeordaterange, with two or three arguments), and the intersection*of two ranges that are never null.COALESCE,GREATESTorLEASTover a column of each side of aFULL JOIN, such ascoalesce(a.id, r.account_id).USINGwrites the merged column in this form. A row of a full join comes from one side at least, so one of the two columns holds a value. The form counts only when all of these are true:- The column of each side is never null.
- Neither side is itself an outer join. An inner join under a side is fine.
- The form is not above the join, and not over a cast of a column.
An aggregate of values that are never null, in a query with a
GROUP BYof one column or more. It counts only when it has noFILTERand noOVER. It is one of these:max,min,sumoravgof numbers;maxorminof texts or ofdate,timestamportimestamp with time zone;string_aggof two texts;bool_and,bool_ororevery.
Each group of such a query has a row. Without
GROUP BY, a query over no rows returns one row whosesumis null. SqlSource treats the aggregate as one that can be null when theWHEREsets every column of theGROUP BYto one value, asWHERE o.user_id = @userId GROUP BY o.user_iddoes.A subquery that is the whole column and returns one row, such as
(SELECT count(*) FROM orders o WHERE o.account_id = a.id). It counts only when all of these are true:- It uses a column of the outer query.
- It has an aggregate, and no
GROUP BY,HAVING,LIMITorOFFSET. - Its value is
count(*), or arithmetic, a function or a cast of the list above over values that are never null. - Its value is of a number type, a text type, or
date,timestamportimestamp with time zone.
SqlSource treats the same subquery inside an expression, as in
(SELECT count(*) ...) + 1, as one that can be null.EXISTS (...)whose subquery uses a column of the outer query, on PostgreSQL 17 and later. PostgreSQL can run such anEXISTSas anINwhen its subquery compares one of its columns with one of the outer query's. Such anEXISTSis never null only when thatINwould be.x IN (SELECT y ...)orx = ANY (SELECT y ...), on PostgreSQL 17 and later. It counts whenxand the subquery'syare never null by the rules above, and the comparison is one of those above.merge_action()in theRETURNINGlist of aMERGE, on PostgreSQL 17 and later.A column of a
UNION, anINTERSECTor anEXCEPTat the top of the query. It counts when every value that it can return there is never null by the rules above. These are the values that it can return:- for a
UNION, the values of every branch; - for an
INTERSECT, the values of either side, since one side is enough; - for an
EXCEPT, the values of the left side.
- for a
count(x), array_agg, the JSON builders and the range constructors and intersections count only when both of these are true:
- No schema of the database but
pg_catalogholds a function or an operator of that name. - No function or operator of that name was added to
pg_catalog.
If another schema holds the name as well, or the name was added to pg_catalog, SqlSource treats the column as one that can be null. The list below says so too.
Each of these counts only where an outer join cannot leave it null:
count(*)and the rankings, andcount(x);array_agg, the JSON builders, and the range constructors and intersections;- constants and tests;
- the arithmetic, comparisons, functions, casts, aggregates and subqueries above;
merge_action();COALESCE,GREATEST,LEASTandCASE.
SqlSource treats a column as one that can be null when one of these is true:
The table column can hold null. The column is not
NOT NULL, or itsNOT NULLwas added asNOT VALIDand was not validated.The table can return null where its column says it cannot. SqlSource then treats every column of the table as one that can be null. A table can do so when one of these is true of it:
- It has or had inheritance children.
- It is partitioned, and a foreign table is one of its partitions.
- It has a
DO INSTEADrule onINSERT,UPDATEorDELETE.
An outer join can fill the column with null. This is a column of the optional side of a
LEFT,RIGHTorFULLjoin.A list after a
NOTor used as a value can make a joined column one that can be null. This is so when all of these are true:- The query holds one of the words
LEFT,RIGHT,FULL,OUTER,ROLLUP,CUBE,GROUPING,MERGE,OLDorNEW. - The query holds a list after a
NOTor used as a value. - The column is of a table that the query joins to another, on either side of the join.
A condition such as
o.id NOT IN @idsor(o.id IN @ids) IS FALSEcan be on the optional side of aLEFT JOIN. Then it drops the rows that the join fills with null, for every list but an empty one. An empty list keeps those rows. So SqlSource treats every joined column of such a query as one that can be null. The column of a query with no join is not affected, and neither is any column of a query that holds none of the words.A list is after a
NOTino.id NOT IN @ids,NOT o.id IN @idsorNOT (o.id IN @ids). A list is used as a value in one of these cases:- Its truth is compared, tested, cast or passed to a function, as in
(o.id IN @ids) IS FALSE,(o.id IN @ids) = falseorCOALESCE(o.id IN @ids, false). - It is in the output of a subquery or a
WITHquery.
A list is not used as a value in these places:
- in a condition that a
WHERE, anON, aHAVINGor aCASE WHENreads, alone or withANDandOR; - in the query's own select list,
ORDER BY,GROUP BY,RETURNINGorSET.
A word counts in any case, anywhere outside a string, a comment or a quoted name. It counts even as the name of a function, such as
left(name, 3)in a query whose joins are all inner. Two quoted names count asOLDandNEW:"old"and"new", in any case. So does a name writtenU&"...".- The query holds one of the words
The column comes from a view or a materialized view. PostgreSQL does not record whether a view's column can be null.
The column comes from a foreign table. PostgreSQL does not enforce
NOT NULLon its remote rows.It is
NULL, or an expression that is not in the list above. For example:initcap(name), or a call of another function;array_agg(...)withoutGROUP BYor withFILTER;upper(during)of a range;price * quantitywhere either column can hold null;sum(total)withoutGROUP BY, or with aGROUP BYwhose every column theWHEREsets to one value;EXISTS (...)before PostgreSQL 17.
It is a function or an operator of a name that another schema of the database holds as well. An example is a
countor a*that an extension installs, in its own schema or inpg_catalog. This holds even for a column that the list above would count as never null. The tool cannot tell which one the query calls.It is a column of a
UNION, anINTERSECTor anEXCEPTwhere a branch can give null. The same holds where SqlSource cannot tell whether a branch gives null.It is an expression computed in one of these places:
- in a subquery in
FROMor aWITHquery, such as acount(*)that aWITHquery returns; - over a
UNION, anINTERSECTor anEXCEPT, such ascount(*)of a subquery that holds one.
SqlSource can treat such a column as one that can be null even when the list above says it is never null.
- in a subquery in
The query has
ROLLUP,CUBEorGROUPING SETS. SqlSource treats every column of such a query as one that can be null.The query names
OLDorNEW, and the column is a subquery, anIN (SELECT ...)or an= ANY (SELECT ...). This holds even for one that the list above says is never null.The search path of the connection puts another schema before
pg_catalog, and the column is one of these. This holds even for one that the list above says is never null:count(*),count(x), a ranking,array_agg, a JSON builder, or a range constructor or intersection;- arithmetic, a comparison, a function, a cast, an aggregate or a subquery;
- an
EXISTS (...)that PostgreSQL runs as anIN. PostgreSQL can do so when the subquery of theEXISTScompares one of its columns with one of the outer query's. The list above says so too; - a
COALESCE,GREATESTorLEASTover both sides of aFULL JOIN.
These are not affected: constants, tests,
COALESCEover columns that are never null, any otherEXISTS (...), andmerge_action(). To avoid this, keeppg_catalogfirst on the search path of the connection that the tool uses. Or leavepg_catalogoff the path: PostgreSQL then searches it first.The query has an outer join,
MERGE,OLDorNEW, and the column comes from one of these places:- through a subquery or a
WITHquery; - from a table that an extension such as Citus or TimescaleDB manages.
SqlSource can treat such a column as one that can be null even when its table column is
NOT NULL.- through a subquery or a
A column that can be null gets a nullable property in the query's output model, such as int? or string?. A column that is never null gets a property that cannot be null, such as int or string. Where you know better than these rules, you decide instead, in one of these ways:
- An alias that ends in
!gives a property that cannot be null. An alias that ends in?gives a property that can be null. - A
-- column:marker says the same, and leaves the name of the column alone.
-- name: GetCustomerLabels
SELECT c.id, initcap(c.name) AS "display_name!"
FROM customers c;
initcap of a value that is not null is never null, but SqlSource treats it as one that can be null. In the sample, "display_name!" gives the property DisplayName the type string, and not string?. Write the alias in quotes, since ! and ? are not part of a plain name. SqlSource leaves the mark out of the name of the property.
A ! is your word, and the build does not check it. If such a column is null after all, a query method throws.
The mark stays in the name of the column, and Dapper matches the name of the column to the name of the property. So with Dapper, write a -- column: marker and no mark:
-- name: GetCustomerLabels
-- column: display_name not null
SELECT c.id, initcap(c.name) AS display_name
FROM customers c;
See which properties can be null and the -- column: marker.
Limits
A property can be null though its column is never null
What you see. A column is never null, but its property in the query's model is nullable. The property costs a null check in your code.
Why. The rules of which columns can be null leave out many columns that a person can see are never null. SqlSource treats each of them as one that can be null. Nothing is wrong when that happens. These are some of the columns that the rules leave out:
EXISTS (...)as a column before PostgreSQL 17;- the rows of
VALUES; - a function that the rules do not list;
- an expression, when the search path puts another schema before
pg_catalog; - a
count(*)or asumthat aWITHquery computes; - a
sumor anarray_aggin a query whoseWHEREsets every column of theGROUP BYto one value; - a
COALESCEover a column of each side of aFULL JOINthat is cast or computed above the join; - a recursive
WITHquery; - the columns of a view.
What to do. Where you know that the column is never null, do one of these:
- End its alias with
!. - When you read the rows with Dapper, write a
-- column:marker that saysnot null, as which columns can be null shows.