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

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?

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.

I take your question literally. Because your can't. By all means datestamp at least have "Y","M","D" 3 elements at the same time.

Table 8.10 shows some possible inputs for the date type.
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE.

Example 	Description
1999-01-08 	ISO 8601; January 8 in any mode (recommended format)
January 8, 1999 	unambiguous in any datestyle input mode
1/8/1999 	January 8 in MDY mode; August 1 in DMY mode
1/18/1999 	January 18 in MDY mode; rejected in other modes
01/02/03 	January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode
1999-Jan-08 	January 8 in any mode
Jan-08-1999 	January 8 in any mode
08-Jan-1999 	January 8 in any mode
99-Jan-08 	January 8 in YMD mode, else error
08-Jan-99 	January 8, except error in YMD mode
Jan-08-99 	January 8, except error in YMD mode
19990108 	ISO 8601; January 8, 1999 in any mode
990108 	ISO 8601; January 8, 1999 in any mode
1999.008 	year and day of year
J2451187 	Julian date
January 8, 99 BC 	year 99 BC

Date output:

datestyle Setting 	Input Ordering 	Example Output
SQL, DMY 	day/month/year 	17/12/1997 15:37:16.00 CET
SQL, MDY 	month/day/year 	12/17/1997 07:37:16.00 PST
Postgres, DMY 	day/month/year 	Wed 17 Dec 07:37:16 1997 PST

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.