PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – What is the difference between CURRENT_TIMESTAMP::DATE and CURRENT_DATE?

I created an immutable function as below:

CREATE OR REPLACE FUNCTION GetCurrentDate()
  RETURNS DATE
AS $p$
BEGIN
    RETURN  CURRENT_TIMESTAMP::DATE;
END;
$p$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER;

And I use this in a UNIQUE INDEX’s WHERE clause like:

CREATE UNIQUE INDEX PersonPhoness_UK_PhoneNo_CountryID 
    ON person_phones (phone_no, country_id)
    WHERE   GetCurrentDate() 
        BETWEEN effective_from_date AND effective_till_date;

This unique index sometimes does not catch duplicate when another row is added.

The effective_from_dt for the newly added row is always CURRENT_DATE. However, it works if I change the function to:

CREATE OR REPLACE FUNCTION GetCurrentDate()
RETURNS DATE
AS $p$
BEGIN
    RETURN  CURRENT_DATE;
END
$p$;

So I want to understand the difference.

Tags:

3

Answers


  1. 0

    First version is little bit slower:

    1. you execute cast timestamp -> date
    2. you switch execution context from current user to owner of function. The context has not any effect on CURRENT_TIMESTAMP pseudo function, so it is useless. But although it is useless, it has an overhead. Maybe if the owner of function is same user as an application user, then the context is not changed. But this is bad configuration from security reasons.

    So both functions returns same result, but first does some useless operations, and then it should be little bit slower.

    When you want to "rename" build in functions, then the best is using SQL language for functions instead. It is zero overhead. Starting PL/pgSQL runtime for any execution of any PL/pgSQL function has some overhead:

    so

    CREATE OR REPLACE FUNCTION GetCurrentDate()
RETURNS DATE
AS $$ SELECT CURRENT_DATE $$
LANGUAGE SQL STABLE;

    is better. It is inlined in query (not executed), so overhead of this is zero.

    Login or Signup to reply.
  2. 0

    In PL/pgsql we CAST data two way.

    1. Using Cast() Function: CAST(value as cast_data_type)
    2. Using Cast Operator (::) : value::cast_data_type

    So, when you write anything like: CURRENT_TIMESTAMP::DATE
    It means, cast(CURRENT_TIMESTAMP as DATE)

    Now we know default CURRENT_TIMESTAMP format is YYYY-MM-DD hh:mm:ss.nnnnnn+/-tz and CURRENT_DATE format is YYYY-MM-DD.

    So, you tell me what need more time ? @JitendraLoyal

    For reference:
    enter image description here

    Pl/pgSQL TypeCast

    Login or Signup to reply.
  3. 0

    Answer to question in the tile

    CURRENT_TIMESTAMP::DATE and CURRENT_DATE do effectively exactly the same. CURRENT_TIMESTAMP::DATE is a bit slower from first getting timestamptz and then coercing it to date – a pointless detour. Either expression is only STABLE, not IMMUTABLE, because either depends on the timezone setting of the executing session.

    About your index

    The whole approach cannot work. CURRENT_DATE (or any function returning a point in time for that matter) makes no sense in an index definition. The way you have it, a row would enter into the partial index on one day, but not on another. Arbitrary consequences …

    Obviously, Postgres cannot allow that. The manual:

    All functions and operators used in an index definition must be
    “immutable”, that is, their results must depend only on their
    arguments and never on any outside influence (such as the contents of
    another table or the current time)    . This restriction ensures that the
    behavior of the index is well-defined. To use a user-defined function
    in an index expression or WHERE clause, remember to mark the
    function immutable when you create it.

    Bold emphasis mine. When trying to violate that rule, CREATE INDEX answers with:

    ERROR:  functions in index predicate must be marked IMMUTABLE

    You forced the broken concept with an IMMUTABLE function wrapper. Only do that when it’s actually true. Or when you know exactly what you are doing with a white lie.

    Your lie isn’t white. Whenever a session operates with a different timezone setting, you (can) get a different date for the same point in time and arbitrary things happen. This can go for a long time before you actually realize the broken functionality.

    So you have two levels of breakage. Luckily, it surfaced before too long. It’s pure coincidence that you saw errors with one function definition and not with the other. Either is bonkers. Pardon my French.

    Start by defining your requirements exactly in plain English (or French, if you will). Then look for the proper tool(s) to implement …

    Asides

    • SECURITY DEFINER is completely useless for this. Only adds cost and possible security implications without any gain.

    • Avoid mixed-case identifiers if at all possible. See:

    • The second function definition in your question is incomplete without language declaration. And without volatility label IMMUTABLE it wouldn’t be accepted by CREATE INDEX to begin with.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search