How to set a Postgresql default value datestamp like YYYYMM

Question

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

User · Answer

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  lt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[datetime] How to set a Postgresql default value datestamp like 'YYYYMM'?

Examples related to datetime

Examples related to postgresql

Examples related to default-value