How to print postgres table output so it can distinguish between empty string and null

Question

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.

user330315 · Accepted Answer · 2020-04-07 10:51:23Z

26

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

answered Apr 7, 2020 at 10:51

user330315

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

Comments

Gordon Linoff · Accepted Answer · 2020-04-07 10:48:10Z

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.

answered Apr 7, 2020 at 10:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Kristijan Over a year ago

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.

Collectives™ on Stack Overflow

How to print postgres table output so it can distinguish between empty string and null

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related