How to set a Postgresql default value datestamp like 'YYYYMM'?
Solution 1
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')
);
Solution 2
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')
);
Solution 3
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.
Solution 4
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')
Strae
I can accept failure, everyone fails at something - But I can't accept not trying. -- You HAVE to assume your visitor is a maniac serial killer, out to destroy your application. And you have to prevent it. Hire me
Updated on July 09, 2022Comments
-
Strae almost 2 years
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?
-
Strae about 15 yearsBecose i need it. I have a php application that runs and query the db with those value, 'yyyymm' as '200801', '200802', etc.. to retrieve the data's - and i thougt that having it as default value would be better then a SELECT fiels FROM table WHERE column BETWEEN timestamp1 AND timestamp2
-
Strae about 15 yearsWhere the other answer like this one is gone?
-
Marcin Cylke about 15 yearsI'd do this with solution I've added to my reply.
-
araqnid about 15 yearsor do "create language plpgsql" from within psql, which is all createlang does.