CODEWITHSUNDEEP
sqlformattinggreenplum
Kent Pawar
Kent Pawar

Reputation: 2606

Unexpected behavior when converting double precision to text in PostgreSQL

Came across this weird behavior today.. 

select                date_part('year', CURRENT_DATE);
2017
select        to_char(date_part('year', CURRENT_DATE),'9999');
" 2017"
select length(to_char(date_part('year', CURRENT_DATE),'9999'));
5

Of course I can directly use to_char(CURRENT_DATE,'YYYY'), but just curious why double precision numbers get a blank space in the beginning when converted into text. Thank you!

Notes:

Upvotes: 1

Views: 209

Answers (1)

Oliver Charlesworth
Oliver Charlesworth

Reputation: 272567

This behaviour is documented (see the examples table). It's leaving space for a - sign for negative values.

It seems that you can avoid this with the FM prefix, i.e. 'FM9999'.

Upvotes: 1

Related Questions