pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedexplicit casts
4+ messages / 2 participants
[nested] [flat]
* explicit casts
@ 2011-01-06 00:24 Iuri Sampaio <iuri.sampaio@gmail.com>
2011-01-06 01:22 ` Re: explicit casts Iuri Sampaio <iuri.sampaio@gmail.com>
2011-01-06 02:20 ` Re: explicit casts Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Iuri Sampaio @ 2011-01-06 00:24 UTC (permalink / raw)
To: pgsql-sql
Hi there,
I installed postgresql 8.4 on my box and now i have troubles with the
following query regarding explicit casts.
select to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'),
'fmMonth') as month,
to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'YYYY')
as year,
to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'J') as
first_julian_date_of_month,
to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as
num_days_in_month,
to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'D') as
first_day_of_month,
to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as last_day,
trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1),'Day') as
next_month,
trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),'Day') as
prev_month,
trunc(to_date(:the_date, 'yyyy-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1)),
'DD') as days_in_last_month,
to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1), 'fmMonth')
as next_month_name,
to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),
'fmMonth') as prev_month_name
from dual
the value assigned to the variable :the_date is '2010-01-05'
The error is
Error: Ns_PgExec: result status: 7 message: ERROR: function
to_date(timestamp with time zone, unknown) does not exist
LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'YYYY-...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: select to_date(date_trunc('month',add_months( $1
,1)),'YYYY-MM-DD') - 1
CONTEXT: PL/pgSQL function "last_day" line 6 at SQL statement
how would i apply the following solution
date_trunc('month', p_date_in + interval '1 month')::date - 1
to fix the query above?
cheers,
iuri
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: explicit casts
2011-01-06 00:24 explicit casts Iuri Sampaio <iuri.sampaio@gmail.com>
@ 2011-01-06 01:22 ` Iuri Sampaio <iuri.sampaio@gmail.com>
2011-01-06 02:33 ` Re: explicit casts Adrian Klaver <adrian.klaver@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Iuri Sampaio @ 2011-01-06 01:22 UTC (permalink / raw)
To: pgsql-sql
So far,
I could write the following query
select to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'),
'fmMonth') as month,
to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'YYYY') as
year,
to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'J') as
first_julian_date_of_month,
to_char(last_day('2010-01-02')::date, 'DD') as num_days_in_month,
to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'D') as
first_day_of_month,
to_char(last_day('2010-01-02')::date, 'DD') as last_day,
trunc(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), 1),'Day') as
next_month,
trunc(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), -1),'Day') as
prev_month,
trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months('2010-01-02', -1))::date, 'DD') as
days_in_last_month,
to_char(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), 1), 'fmMonth')
as next_month_name,
to_char(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), -1), 'fmMonth')
as prev_month_name
from dual
But i still miss some lines in order to properly explicit casts in the query
cheers,
iuri
On 01/05/2011 10:24 PM, Iuri Sampaio wrote:
> Hi there,
>
> I installed postgresql 8.4 on my box and now i have troubles with the
> following query regarding explicit casts.
>
> select to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'),
> 'fmMonth') as month,
> to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'YYYY')
> as year,
> to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'J') as
> first_julian_date_of_month,
> to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as
> num_days_in_month,
> to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'D') as
> first_day_of_month,
> to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as
> last_day,
> trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1),'Day') as
> next_month,
> trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),'Day') as
> prev_month,
> trunc(to_date(:the_date, 'yyyy-mm-dd'), 'year') as beginning_of_year,
> to_char(last_day(add_months(to_date(:the_date, 'yyyy-mm-dd'),
> -1)), 'DD') as days_in_last_month,
> to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1),
> 'fmMonth') as next_month_name,
> to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),
> 'fmMonth') as prev_month_name
> from dual
>
> the value assigned to the variable :the_date is '2010-01-05'
>
> The error is
>
> Error: Ns_PgExec: result status: 7 message: ERROR: function
> to_date(timestamp with time zone, unknown) does not exist
> LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'YYYY-...
> ^
> HINT: No function matches the given name and argument types. You
> might need to add explicit type casts.
> QUERY: select to_date(date_trunc('month',add_months( $1
> ,1)),'YYYY-MM-DD') - 1
> CONTEXT: PL/pgSQL function "last_day" line 6 at SQL statement
>
>
> how would i apply the following solution
>
> date_trunc('month', p_date_in + interval '1 month')::date - 1
>
> to fix the query above?
>
> cheers,
> iuri
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: explicit casts
2011-01-06 00:24 explicit casts Iuri Sampaio <iuri.sampaio@gmail.com>
2011-01-06 01:22 ` Re: explicit casts Iuri Sampaio <iuri.sampaio@gmail.com>
@ 2011-01-06 02:33 ` Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2011-01-06 02:33 UTC (permalink / raw)
To: pgsql-sql; +Cc: Iuri Sampaio <iuri.sampaio@gmail.com>
On Wednesday 05 January 2011 5:22:30 pm Iuri Sampaio wrote:
> So far,
> I could write the following query
>
> select to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'),
> 'fmMonth') as month,
> to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'YYYY') as
> year,
> to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'J') as
> first_julian_date_of_month,
> to_char(last_day('2010-01-02')::date, 'DD') as num_days_in_month,
> to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'D') as
> first_day_of_month,
> to_char(last_day('2010-01-02')::date, 'DD') as last_day,
> trunc(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), 1),'Day') as
> next_month,
> trunc(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), -1),'Day') as
> prev_month,
> trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'year') as beginning_of_year,
> to_char(last_day(add_months('2010-01-02', -1))::date, 'DD') as
> days_in_last_month,
> to_char(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), 1), 'fmMonth')
> as next_month_name,
> to_char(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), -1), 'fmMonth')
> as prev_month_name
> from dual
>
>
> But i still miss some lines in order to properly explicit casts in the
> query
>
> cheers,
> iuri
My guess is you upgraded from a version prior to 8.3. In 8.3 many of the implied
casts where removed, so you probably have to go over your code and make the
corrections.
Go here for more detail:
http://www.postgresql.org/docs/8.4/interactive/release-8-3.html
Section
E.21.2.1. General
A temporary solution can be found here:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html
An important tip from the blog-
"The gist was, only restore the casts you need, not all of them."
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: explicit casts
2011-01-06 00:24 explicit casts Iuri Sampaio <iuri.sampaio@gmail.com>
@ 2011-01-06 02:20 ` Adrian Klaver <adrian.klaver@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2011-01-06 02:20 UTC (permalink / raw)
To: pgsql-sql; +Cc: Iuri Sampaio <iuri.sampaio@gmail.com>
On Wednesday 05 January 2011 4:24:34 pm Iuri Sampaio wrote:
> Hi there,
>
> I installed postgresql 8.4 on my box and now i have troubles with the
> following query regarding explicit casts.
>
> select to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'),
> 'fmMonth') as month,
> to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'YYYY')
> as year,
> to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'J') as
> first_julian_date_of_month,
> to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as
> num_days_in_month,
> to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'D') as
> first_day_of_month,
> to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as last_day,
> trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1),'Day') as
> next_month,
> trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),'Day') as
> prev_month,
> trunc(to_date(:the_date, 'yyyy-mm-dd'), 'year') as beginning_of_year,
> to_char(last_day(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1)),
> 'DD') as days_in_last_month,
> to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1), 'fmMonth')
> as next_month_name,
> to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),
> 'fmMonth') as prev_month_name
> from dual
>
> the value assigned to the variable :the_date is '2010-01-05'
>
> The error is
>
> Error: Ns_PgExec: result status: 7 message: ERROR: function
> to_date(timestamp with time zone, unknown) does not exist
> LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'YYYY-...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> QUERY: select to_date(date_trunc('month',add_months( $1
> ,1)),'YYYY-MM-DD') - 1
> CONTEXT: PL/pgSQL function "last_day" line 6 at SQL statement
>
>
> how would i apply the following solution
>
> date_trunc('month', p_date_in + interval '1 month')::date - 1
>
> to fix the query above?
>
> cheers,
> iuri
If I am following this right the problem is in the last_day function and in
particular the return value of the add_months function used in the
date_trunc(). To be sure we would need to see those functions. As a shot in the
dark:
select to_date(date_trunc('month',add_months( $1 ,1)::date)...
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-01-06 02:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-06 00:24 explicit casts Iuri Sampaio <iuri.sampaio@gmail.com>
2011-01-06 01:22 ` Iuri Sampaio <iuri.sampaio@gmail.com>
2011-01-06 02:33 ` Adrian Klaver <adrian.klaver@gmail.com>
2011-01-06 02:20 ` Adrian Klaver <adrian.klaver@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox