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;