As title, how can I set a table's column to have the default value the current year and month, in format 'YYYYMM', like 200905 for today?
This question is related to
datetime
postgresql
default-value
Please bear in mind that the formatting of the date is independent of the storage. If it's essential to you that the date is stored in that format you will need to either define a custom data type or store it as a string. Then you can use a combination of extract, typecasting and concatenation to get that format.
However, I suspect that you want to store a date and get the format on output. So, something like this will do the trick for you:
CREATE TABLE my_table
(
id serial PRIMARY KEY not null,
my_date date not null default CURRENT_DATE
);
(CURRENT_DATE is basically a synonym for now() and a cast to date).
(Edited to use to_char).
Then you can get your output like:
SELECT id, to_char(my_date, 'yyyymm') FROM my_table;
Now, if you did really need to store that field as a string and ensure the format you could always do:
CREATE TABLE my_other_table
(
id serial PRIMARY KEY not null,
my_date varchar(6) default to_char(CURRENT_DATE, 'yyyymm')
);
Just in case Milen A. Radev doesn't get around to posting his solution, this is it:
CREATE TABLE foo (
key int PRIMARY KEY,
foo text NOT NULL DEFAULT TO_CHAR(CURRENT_TIMESTAMP,'YYYYMM')
);
Why would you want to do this?
IMHO you should store the date as default type and if needed fetch it transforming to desired format.
You could get away with specifying column's format but with a view. I don't know other methods.
Edited:
Seriously, in my opinion, you should create a view on that a table with date type. I'm talking about something like this:
create table sample_table ( id serial primary key, timestamp date);
and than
create view v_example_table as select id, to_char(date, 'yyyymmmm');
And use v_example_table in your application.
Thanks for everyone who answered, and thanks for those who gave me the function-format idea, i'll really study it for future using.
But for this explicit case, the 'special yyyymm field' is not to be considered as a date field, but just as a tag, o whatever would be used for matching the exactly year-month researched value; there is already another date field, with the full timestamp, but if i need all the rows of january 2008, i think that is faster a select like
SELECT [columns] FROM table WHERE yearmonth = '200801'
instead of
SELECT [columns] FROM table WHERE date BETWEEN DATE('2008-01-01') AND DATE('2008-01-31')
It's a common misconception that you can denormalise like this for performance. Use date_trunc('month', date) for your queries and add an index expression for this if you find it running slow.
Right. Better to use a function:
CREATE OR REPLACE FUNCTION yyyymm() RETURNS text
LANGUAGE 'plpgsql' AS $$
DECLARE
retval text;
m integer;
BEGIN
retval := EXTRACT(year from current_timestamp);
m := EXTRACT(month from current_timestamp);
IF m < 10 THEN retval := retval || '0'; END IF;
RETURN retval || m;
END $$;
SELECT yyyymm();
DROP TABLE foo;
CREATE TABLE foo (
key int PRIMARY KEY,
colname text DEFAULT yyyymm()
);
INSERT INTO foo (key) VALUES (0);
SELECT * FROM FOO;
This gives me
key | colname
-----+---------
0 | 200905
Make sure you run createlang plpgsql from the Unix command line, if necessary.
Source: Stackoverflow.com