Formatting Timestamp in Default Constraint

Question

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

Answer 1

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.