Обсуждение: Populate a calendar table
'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 ---------------------------------------------------------------
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/
"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
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/
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 ---------------------------------------------------------------