![]() ![]() In addition to the extra work in function type resolution this adds an extra cast to timestamptz - which not only adds more cost, it can also introduce problems with DST leading to unexpected results in rare cases. If only one candidate remains, use it else continue (of the input data type's type category) at the most positions where Run through all candidates and keep those that accept preferred types Would be ambiguous, but timestamptz is "preferred" among "date/time types". There is an implicit cast from date to timestamp as well as one from date to timestamptz. But that requires more work from Postgres. Thanks to function type resolution we can still pass date. The omitted time part defaults to 00:00 with ISO format. The call with timestamp arguments resolves to the best variant directly without descending into function type resolution rules and without additional cast for the input. An explicit cast is needed to return date. ![]() There is no variant taking or returning date. ( numeric variants were added with Postgres 9.5.) The relevant ones are the last two in bold taking and returning timestamp / timestamptz. Generate_series(timestamp with time zone,timestamp with time zone,interval) | timestamp with time zone Generate_series(timestamp without time zone,timestamp without time zone,interval) | timestamp without time zone Generate_series(numeric,numeric) | numeric Generate_series(numeric,numeric,numeric) | numeric Generate_series(bigint,bigint,bigint) | bigint Generate_series(integer,integer) | integer Generate_series(integer,integer,integer) | integer Currently (Postgres 11): SELECT oid::regprocedure AS function_signature There are a number of overloaded variants of generate_series(). (That aside, the last variant is typically fastest by a tiny margin.) Why timestamp ? What is the expected behaviour for multiple set-returning functions in SELECT clause?.And I would not advise that variant before Postgres 10 - at least not with more than one set-returning function in the same SELECT list: The AS keyword is required in the last variant, Postgres would misinterpret the column alias day otherwise. Or with the set-returning function in the SELECT list: SELECT generate_series(timestamp '', '', '1 day')::date AS day ![]() See below.Įquivalent, less explicit short syntax: SELECT day::dateįROM generate_series(timestamp '', '', '1 day') day ![]() And you do not needlessly involve DST (daylight saving time) rules coupled with the conversion from date to timestamp with time zone and back. The advantage in performance is small, but there is no reason not to take it. Au contraire, timestamp is the best choice. The cast to date ( day::date) does that implicitly.īut there is also no point in casting date literals to date as input parameter. To generate a series of dates this is the optimal way: SELECT t.day::dateĪdditional date_trunc() is not needed. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |