PostgreSql 日期插入格式 参数设置

I run the following SQL on my own laptop. Some issues are occured without expectation. 

INSERT INTO department 
(dname, dnumber, mgrssn, mgrstartdate) VALUES
('Administration', 1001, 20915, '02/29/04');

INSERT INTO department 
(dname, dnumber, mgrssn, mgrstartdate) VALUES
('Finance', 1007, 21287, '07/06/05');


INSERT INTO employee 
(fname, lname, ssn, bdate, address, sex, salary, superssn, dno) VALUES 
('Michio',	'Morishima',	20118,	'18/07/1923',	'79 Macpherson St, Turner',	
    'M',	52107,	21286,	1000);
INSERT INTO employee 
(fname, lname, ssn, bdate, address, sex, salary, superssn, dno) VALUES 
('John',	'Backus',	20766,	'03/12/1924',	'25 Burns St, Yarralumla',	
    'M',	46789,	21287,	1007);


Logon the Postgres, and run

\i employeeCreate.sql 

Many records can not be inserted because of the datetype(MDY). Such as 
Errors as below when insert the date formate like '02/29/04'. It's 'MDY' formate.
	postgres=# insert into department values ('Administration', 1001,20915, '29/02/04');
	ERROR:  date/time field value out of range: "29/02/04"
	LINE 1: ... department values ('Administration', 1001,20915, '29/02/04'...

Exuecute show lc_time;  to check the reason. 
	postgres=# show lc_time;
	 lc_time 
	---------
	 C
	(1 row)

It's something I not expected. I check the lc_time paramter in my collage's computer, it show as 'en_AU'.
I set lc_time to be 'en_AU'.
	set lc_time to 'en_AU'


	stgres=# show lc_time;
	 lc_time 
	---------
	 en_AU
	(1 row)

Drop all of the tables, and execute '\i employeeCreate.sql'. 
Errors as below when insert the date formate like '18/07/1923'. It's 'DMY' formate.
	INSERT INTO employee 
	(fname, lname, ssn, bdate, address, sex, salary, superssn, dno) VALUES 
	('Michio',	'Morishima',	20118,	'18/07/1923',	'79 Macpherson St, 	Turner',	
	    'M',	52107,	21286,	1000);

Set the DateStyle to be DMY formate:
SET DateStyle="ISO,DMY";

Execute the following SQL successfully. 
Set back the DateStyle:
SE DateStyle = default;

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值