PostgreSQL - Create table and set specific date format

Question

I want to create a new table and set a date type with a specific format. Is that possible? For example: ``` CREATE TABLE User ( ... EXPIRATION DATE " YYYY/MM" ... ) ```

Accepted Answer

I suggest a different approach: Never store date / time as character type (`text`, `varchar()`, ...) to begin with. Use an appropriate type, probably `date` in your case.

Also, never use reserved words as identifier. `user` is just not possible to begin with, you would have to double-quote, which I would discourage. Could look like this:

```
CREATE TABLE usr (
  usr_id serial PRIMARY KEY
 ,usr text UNIQUE
 ,expiration_date date
  ... 
);
```

Now, various input formats are possible, as long as they are unambiguous. The related question @DrColossos has linked to in his comment has more on that. The manual has all the details.

To enforce a particular input format, you could run the text literal through the `to_date()` function. Example:

```
INSERT INTO usr (usr, expiration_date)
VALUES ('flippin_user', to_date($my_date_literal, ' YYYY/MM');
```

Note: if you include the leading blank in the pattern, it is expected from the input!

Finally, you can format your date any way you like with `to_char()` on output:

```
SELECT usr, to_char(expiration_date, ' YYYY/MM') AS formatted_exp_date
WHERE  usr_id = 1;
```

PostgreSQL - Create table and set specific date format

Solution

Problem

Related problems