PostgreSQL : cast string to date DD/MM/YYYY

Question

A DATE column does not have a format. You cannot specify a format for it.

You can use DateStyle to control how PostgreSQL emits dates, but it’s global and a bit limited.

Instead, you should use to_char to format the date when you query it, or format it in the client application. Like:

SELECT to_char("date", 'DD/MM/YYYY') FROM mytable;

e.g.

regress=> SELECT to_char(DATE '2014-04-01', 'DD/MM/YYYY');
  to_char   
------------
 01/04/2014
(1 row)