How to show null ,empty string,space in postgresql?

Question

Execute the below statements to create database and create table then insert null ,empty string and space and string x.

CREATE DATABASE test;
\c test;
CREATE TABLE test (
    id numeric(3,0) PRIMARY KEY,
    content varchar(255)
);
INSERT INTO test (id, content) VALUES (1, NULL);
INSERT INTO test (id, content) VALUES (2, '');
INSERT INTO test (id, content) VALUES (3, ' ');
INSERT INTO test (id, content) VALUES (4, 'x');

All data-- null ,empty string,space shown as same blank when selecting them.

How can show them properly?

test=#  \pset null 'Unknown'
Null display is "Unknown".
test=# select * from test;
 id | content 
----+---------
  1 | Unknown
  2 | 
  3 |  
  4 | x
(4 rows)

Remained issue:how to distinct empty string '' from blank ' '?Set something to better display empty string '' and blank ' ' in psql shell.

That's an issue with the SQL client. In this case it's the included PostgreSQL command-line shell. Other clients may display data in a different way. — The Impaler
– The Impaler, Commented Feb 4, 2022 at 0:00

Laurenz Albe · Accepted Answer · 2022-02-04 05:15:09Z

5

This is how I would do it in psql:

test=> \pset null '(null)'
Null display is "(null)".
test=> SELECT id, '"' || content || '"' FROM test;
 id │ ?column? 
════╪══════════
  1 │ (null)
  2 │ ""
  3 │ " "
  4 │ "x"
(4 rows)

answered Feb 4, 2022 at 5:15

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

showkey Over a year ago

Make a little progress.

showkey Over a year ago

SELECT id, '"' || content || '"' as content FROM test;

Jonathan Hartley Over a year ago

Some people might like \pset null '∅', which (in my font) sort of visually stands out a little amongst a table full of regular text values. Consider putting it in your ~/.psqlrc.

Fernando Basso Over a year ago

On Linux with Gtk, one can type Ctrl+Shift+u followed by 2205 (possibly followed by space) to insert the EMPTY SET Unicde char ∅. On Vim, on insert mode, Ctrl+v u 2205, and on Emacs, Ctrl+x = RET 2205 RET. Remember that \e on psql launches your editor so a query can be composed from the comfort of your editor.

Adrian Klaver · Accepted Answer · 2022-02-04 01:04:38Z

0

From here psql:

\pset  null 'NULL'

--Or whatever string you want to represent NULL.

select null;
 ?column? 
----------
 NULL
(1 row)

See in psql under Files how you can persist the setting in .psqlrc.

AFAIK, there is no way to do this for empty strings and spaces.

edited Feb 4, 2022 at 1:04

answered Feb 3, 2022 at 23:58

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

4 Comments

showkey Over a year ago

It is null instead of 'NULL' in setting.

Adrian Klaver Over a year ago

Yeah, I left out the null part. Answer corrected.

showkey Over a year ago

How about the remained issue?

Adrian Klaver Over a year ago

As I said in my answer I know of no way to do that. You just have to figure that anything that does not show up as the null string is empty spaces.

Collectives™ on Stack Overflow

How to show null ,empty string,space in postgresql?

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related