Formatting Timestamp in Default Constraint Can you format a TIMESTAMP WITH TIME ZONE column in a CREATE TABLE default constraint in Postgres v10.x?

Example Code:

create_date TIMESTAMP WITH TIME ZONE
    DEFAULT to_char(current_timestamp, 'DD-MON-YYYY HH24:MI:SS')
    NOT NULL


When running this, I get the following error:


  ERROR:  column "create_date" is of type timestamp with time zone but default expression is of type text
  HINT:  You will need to rewrite or cast the expression.
  SQL state: 42804


However, according to the Postgres Docs this type of formatting should be possible. 

I've also tried casting this with the to_timestamp() method, however, I get the following error:


  ERROR:  function to_timestamp(timestamp with time zone, unknown) does not exist
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  SQL state: 42883
Can you format a TIMESTAMP WITH TIME ZONE column in a CREATE TABLE default constraint in Postgres v10.x?


No because that doesn't make any sense. Timestamps are stored as timestamps. It's a special type. You format things for display. Any point in time is stored the same way regardless. Formatting time should arguable not be done in the database at all.

Think of it like this, you may need Roman Numeral representation for some columns, but even if you do the database will stored integers in their binary representation.

Now if you want to change the clients default output formatting for timestamps, you can certainly do that.  From the docs


  The date/time style can be selected by the user using the SET datestyle command, the DateStyle parameter in the postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client.