IFNULL ERROR 3681: Invalid input syntax for integer

[heather]

Hi,

I would like to display all null values as a "/n". I tried this method but I get an error:

Code: Select all

dbadmin=> \d bad_values;
Did not find any relation.
dbadmin=> select v, ifnull(v, '/n') from bad_vals;
ERROR 3681:  Invalid input syntax for integer: "/n"

Can someone explain why I am getting this error?

Thanks

[scutter]

Column v is declared as an integer? Use ifnull(v::varchar, '/n') instead.

--Sharon

[id10t]

Hi!

1. Change output --- your solution but corrected (use in DECODE function)

Code: Select all

dbadmin=> select * from foo;  -- example of data
     c1     |     c2     
------------+------------
            | a                     -- c1 NULL
 a          |                       -- c2 NULL
(2 rows)

Code: Select all

dbadmin=> select DECODE(c1, NULL, '/n', c1), DECODE(c2, NULL, '/n', c2) from foo;
     c1     |     c2     
------------+------------
 /n         | a         
 a          | /n        
(2 rows)

2. Change output for null in vsql

Code: Select all

dbadmin=> \pset null '/n'
Null display is "/n".

Code: Select all

dbadmin=> select * from foo;
     c1     |     c2     
------------+------------
 /n         | a         
 a          | /n
(2 rows)

3. Change output for null in Shell

Code: Select all

[ ~ ]$ vsql -P null='/n' -c "select * from foo"
     c1     |     c2     
------------+------------
 a          | /n
 /n         | a         
(2 rows)

[heather]

Great explanation and examples! Thanks scutter and skwa!