9

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"
... 
)
D-Lef
  • 1,219
  • 5
  • 14
  • 20
  • possible duplicate of [Postgresql date format](http://stackoverflow.com/questions/5467031/postgresql-date-format) – DrColossos Jan 09 '14 at 12:40
  • I think the "duplicated" answer gets to what you want. If this is not the case, ignore the comment above but I think that the linked question will do what you want. – DrColossos Jan 09 '14 at 12:41
  • Dates don't have a "format" –  Jan 09 '14 at 13:13
  • @DrColossos I just wondered if there is a way to set the date's format to a specific format, so that any other input would not be accepted. For example, to set the default format as "YYYY/MM" and if anyone tried to insert data with format "YYYY/MM/dd", he would not succeed. – D-Lef Jan 09 '14 at 17:57

1 Answers1

7

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;
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • You are right for the reversed words, I just used 'user' as an example. My point is that I want to insert data from excel files, so I use the command `COPY usr FROM 'C:\usr.csv' WITH (FORMAT CSV,HEADER);` . Can I use something similar to your advice in that command? – D-Lef Jan 09 '14 at 17:51
  • @D-Lef: This is a new question. [Consider this related answer.](http://stackoverflow.com/questions/8130594/postgresql-how-to-convert-string-date-to-timestamp-without-knowing-the-date-form/8130748#8130748) – Erwin Brandstetter Jan 09 '14 at 18:13
  • Thanks. As I mention in a comment above, I wondered if there is a way to "lock" the date format, when I create the table. – D-Lef Jan 09 '14 at 18:28
  • @D-Lef: Not with a `date` column, when users can INSERT INTO the table directly. Type conversion happens before any other checks. You would have to operate with a `text` column ... brrr. – Erwin Brandstetter Jan 10 '14 at 00:39