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

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.

CodePudding user response：

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)

CodePudding user response：

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.