Error: invalid input syntax for integer: ""

Question

I have this table tbl_buku:

id_buku  judul_buku   tahun_buku
1          Bioogi          2010
2          Fisika          2010
3          Informatika     2012
4          Kimia           2012

I use query like this, but I am getting an error:

select case when t1.tahun_buku=t2.tahun_buku then ''
            else t1.tahun_buku end tahun_buku,t1.judul_buku
from tbl_buku t1 left join tbl_buku t2
on t1.id_buku-1=t2.id_buku;

I want to show table like this:

tahun_buku     judul_buku
2010             Biologi
                 Fisika
2012             Informatika
                 Kimia

How to achieve this?

You may want to use `lag()` window function. Details here: http://www.postgresql.org/docs/current/static/functions-window.html — Ihor Romanchenko, May 28 '14 at 12:46
SQL is the wrong tool for suppressing repeated values in a column. That's what report writers are for. (You have a presentation-level problem, not a database problem.) — Mike Sherrill 'Cat Recall', May 28 '14 at 12:54

score 7 · Accepted Answer · answered May 28 '14 at 12:56

I think the problem in your query is that tahun_buku is of datatype int and you are trying to select an empty string ('').

You have to workarounds:

Change tahun_buku to be varchar (2010,2012..will be consider as strings I dont know if it is ok)

Set:

select case when t1.tahun_buku=t2.tahun_buku then null else t1.tahun_buku end tahun_buku,t1.judul_buku
from tbl_buku t1 left join tbl_buku t2
on t1.id_buku-1=t2.id_buku;

score 3 · Answer 2 · edited May 23 '17 at 12:17

Use the window function lag() to get values from the previous row.
Use NULL for "empty" values in a numeric column - like @apomene already suggested.
The empty string '' can only be used in character types like text or varchar.
Use NULLIF() in this particular case to substitute the NULL value.

SELECT NULLIF(lag(tahun_buku) OVER (ORDER BY tahun_buku, judul_buku)
            , tahun_buku) AS tahun_buku
     , judul_buku
FROM   tbl_buku
ORDER  BY tahun_buku, judul_buku;

Error: invalid input syntax for integer: ""

2 Answers2

Linked