Postgres Pro
Downloads
Home   >   mailing lists

Thread: Populate a calendar table

Populate a calendar table

From
Raymond O'Donnell
Date:
'Lo all,

I've created a calendar table based on an article I found on the web,
but I can't figure out what's wrong with the query I've written to
populate it. Here's the table -

CREATE TABLE aux_dates
(
   the_date date NOT NULL,
   the_year smallint NOT NULL,
   the_month smallint NOT NULL,
   the_day smallint NOT NULL,
   month_name character varying(12),
   day_name character varying(12),
   CONSTRAINT aux_dates_pkey PRIMARY KEY (the_date)
)

- and here's what I've come up with to populate it -

insert into aux_dates
select * from (
select
     d.dates as the_date,
     extract (year from d.dates) as the_year,
     extract (month from d.dates) as the_month,
     extract (day from d.dates) as the_day,
     to_char(extract (month from d.dates), 'FMmonth') as month_name,
     to_char(extract (day from d.dates), 'FMday') as day_name
   from
   (
     select ('2007-01-01'::date + s.a) as dates
     from generate_series(0, 14) as s(a)
   ) d
) dd;

The error I get is:

     ERROR: "." is not a number
     SQL state: 22P02

Any help will be appreciated!

Thanks,

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: Populate a calendar table

From
Peter Eisentraut
Date:
Raymond O'Donnell wrote:
>      to_char(extract (month from d.dates), 'FMmonth') as month_name,
>      to_char(extract (day from d.dates), 'FMday') as day_name

These formatting patterns are invalid. Check the documentation for the
real ones.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: Populate a calendar table

From
Tom Lane
Date:
"Raymond O'Donnell" <rod@iol.ie> writes:
>      to_char(extract (month from d.dates), 'FMmonth') as month_name,
>      to_char(extract (day from d.dates), 'FMday') as day_name

I think you want just to_char(d.dates, 'FMmonth') and so on.
What you're invoking above is to_char(numeric) which has entirely
different format codes...

            regards, tom lane

Re: Populate a calendar table

From
Osvaldo Rosario Kussama
Date:
Raymond O'Donnell escreveu:
> 'Lo all,
>
> I've created a calendar table based on an article I found on the web,
> but I can't figure out what's wrong with the query I've written to
> populate it. Here's the table -
>
> CREATE TABLE aux_dates
> (
>   the_date date NOT NULL,
>   the_year smallint NOT NULL,
>   the_month smallint NOT NULL,
>   the_day smallint NOT NULL,
>   month_name character varying(12),
>   day_name character varying(12),
>   CONSTRAINT aux_dates_pkey PRIMARY KEY (the_date)
> )
>
> - and here's what I've come up with to populate it -
>
> insert into aux_dates
> select * from (
> select
>     d.dates as the_date,
>     extract (year from d.dates) as the_year,
>     extract (month from d.dates) as the_month,
>     extract (day from d.dates) as the_day,
>     to_char(extract (month from d.dates), 'FMmonth') as month_name,
>     to_char(extract (day from d.dates), 'FMday') as day_name
>   from
>   (
>     select ('2007-01-01'::date + s.a) as dates
>     from generate_series(0, 14) as s(a)
>   ) d
> ) dd;
>
> The error I get is:
>
>     ERROR: "." is not a number
>     SQL state: 22P02
>
> Any help will be appreciated!
>



Try:
      to_char(d.dates, 'FMmonth') as month_name,
      to_char(d.dates, 'FMday') as day_name

[]s
Osvaldo


_______________________________________________________
Yahoo! Mail - Sempre a melhor opção para você!
Experimente já e veja as novidades.
http://br.yahoo.com/mailbeta/tudonovo/

Re: Populate a calendar table

From
Raymond O'Donnell
Date:
On 27/03/2007 17:00, Tom Lane wrote:

> I think you want just to_char(d.dates, 'FMmonth') and so on.
> What you're invoking above is to_char(numeric) which has entirely
> different format codes...

Duh! Of course.....I didn't spot that.

Thanks to all who replied.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------