What data type for a column which can have three states: yes, no, unknown I have an application where the user can decide between:


yes
no
unknown


I started to use a boolean column which is nullable.

But things get quite complicated since my framework (django) does not provide a input field for coding a search form out of the box.

In the search form I need four states:


yes
no
unknown
show all (ignore this column in the search)


Related question: https://stackoverflow.com/questions/38307866/search-form-for-nullbooleanfield

An old rule of thumb comes to my mind again: 


  Avoid nullable db columns.


I want to change the data type from a nullable boolean field to something different.

What data type for a column which can have three states: yes, not, unknown?

I use Postgres 9, but AFAIK this should not matter for this question.
You want boolean. 

It can have true (=yes), false (=no) and null (=unknown)

More details in the manual:
https://www.postgresql.org/docs/current/static/datatype-boolean.html



"Avoid nullable columns" is only a recommendation. If you need an "unknown" state, then NULL is exactly what you are looking for. It was specifically created to represent "unknown".

Postgres even support the keyword unknown for boolean expressions: where some_column is unknown is equivalent to where some_column is null if some_column is defined as boolean