10

is there a way to configure psql or to pass in some parameter when doing query, to print out different value for null and for empty string? Currently it shows empty for both.

For example, i have a table with this content:

adventure=# select * from account
adventure-# ;
 user_id | username | password | email |         created_on         |         last_login         
---------+----------+----------+-------+----------------------------+----------------------------
       1 |          |          |       | 2020-04-07 10:30:08.836098 | 2020-04-07 10:30:08.836098
(1 row)

With this, I cannot tell if username is empty string or a null. In this case, username is null, but password is empty string.

I tried using \x on, but it's the same thing.

Of course, i could do some coalescing, but that's a bit too much if i need to do it for every column.

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
Kristijan
  • 323
  • 6
  • 11

2 Answers2

20

In psql you can do that with the configuration option null which can be changed using \pset

postgres=# \pset null '<null>'
Null display is "<null>".
postgres=# select null as c1, '' as c2;
   c1   | c2
--------+----
 <null> |

If you want that option permanently, you can put the \pset command into .psqlrc

0

You can use coalesce() to replace the NULL value with something else:

select coalesce(username, '<null user>') as username,
       coalesce(password, '<null password>') as password

Or, if you want to include a third value for empty strings, use case:

select (case when username is null then '<null user>'
             when username = '' then '<empty user>'
             else username
        end)

Of course, if a password were <null password>' then it would look like the NULL value.

Or, you can enclose the values in quotes:

select '"' || username || '"' as username

The || operator returns NULL if the value is NULL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yeah i mentioned that i can do that, but in cases where I have a lot of these, I would like to just do `select *`. Also, i think this would potentially be an issue for non-varchar field types, like a date. – Kristijan Apr 07 '20 at 10:51