Postgresql – Formatting Timestamp in Default Constraint

date formatnumber formattingpostgresqlpostgresql-10timestamp

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

Best Answer

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.

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Best Answer

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

Postgresql – In PostgreSQL, is there a type-safe first() aggregate function

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`