[string] PostgreSQL : cast string to date DD/MM/YYYY

I'm trying to cast a CHARACTER VARYING column to a DATE but I need a date format like this : DD/MM/YYYY. I use the following SQL query :

ALTER TABLE test 
ALTER COLUMN date TYPE DATE using to_date(date, 'DD/MM/YYYY');

The result is a date like this : YYYY-MM-DD.

How can I get the DD/MM/YYYYformat ?

Thanks a lot in advance !

Thomas

The documentation says

The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German. The default is the ISO format.

So this particular format can be controlled with postgres date time output, eg:

t=# select now();
              now
-------------------------------
 2017-11-29 09:15:25.348342+00
(1 row)

t=# set datestyle to DMY, SQL;
SET
t=# select now();
              now
-------------------------------
 29/11/2017 09:15:31.28477 UTC
(1 row)

t=# select now()::date;
    now
------------
 29/11/2017
(1 row)

Mind that as @Craig mentioned in his answer, changing datestyle will also (and in first turn) change the way postgres parses date.

In case you need to convert the returned date of a select statement to a specific format you may use the following:

select to_char(DATE (*date_you_want_to_select*)::date, 'DD/MM/YYYY') as "Formated Date"

https://www.postgresql.org/docs/8.4/functions-formatting.html

SELECT to_char(date_field, 'DD/MM/YYYY')
FROM table