PostgreSQL - 创建表并设置特定的日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21019674/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - Create table and set specific date format
提问by D-Lef
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"
...
)
回答by Erwin Brandstetter
I suggest a different approach: Neverstore date / time as character type(text, varchar(), ...) to begin with. Use an appropriate type, probably datein your case.
我建议采用不同的方法：一开始不要将日期/时间存储为字符类型( text, varchar(), ...) 。使用适当的 type，可能date在您的情况下。
Also, neveruse reserved wordsas identifier. useris just not possible to begin with, you would have to double-quote, which I would discourage.
Could look like this:
另外，永远不要使用保留字作为标识符。user刚开始是不可能的，你必须双引号，我会劝阻。看起来像这样：
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 commenthas more on that.
The manual has all the details.
现在，各种输入格式都是可能的，只要它们是明确的。@DrColossos 在他的评论中链接的相关问题有更多相关内容。
该手册包含所有详细信息。
To enforce a particular inputformat, you could run the text literal through the to_date()function. Example:
要强制执行特定的输入格式，您可以通过to_date()函数运行文本文字。例子：
INSERT INTO usr (usr, expiration_date)
VALUES ('flippin_user', to_date($my_date_literal, ' YYYY/MM');
Note: if you include the leading blankin the pattern, it is expected from the input!
注意：如果您在模式中包含前导空格，则应从输入中输入！
Finally, you can format your date any way you like with to_char()on output:
最后，您可以to_char()在输出中以任何您喜欢的方式格式化您的日期：
SELECT usr, to_char(expiration_date, ' YYYY/MM') AS formatted_exp_date
WHERE usr_id = 1;
