import record of fraction of seconds into postgreSQL database-CodePudding

There is one csv file & in one column datetime column value is in nano second (i.e. 21/11/2021 01:00:05.120972944). I need to insert csv file data into postgreSQL database. When i used datetime column datatype timestamp(6) then throwing error invalid syntax of datetime column. What would be correct datatype of datetime column in potgreSQL database.

CodePudding user response：

The maximum precision for a timestamp is 6. You're providing data with a precision of 9.

laetitia=# select now()::timestamp(9);
WARNING:  TIMESTAMP(9) precision reduced to maximum allowed, 6
LINE 1: select now()::timestamp(9);
                      ^
            now             
----------------------------
 2022-10-05 11:41:02.107602
(1 row)

So my suggestion is to add the data into a temporary table with this column as text and then transform it into a timestamp to insert it into your regular table. (Actually, when loading data from CSV files, I always suggest loading everything in a temporary table and then transforming it with SQL).

For example:

laetitia=# select col::timestamp(9)
from (values ('01/11/2021 01:00:05.120972944')) as test(col);
WARNING:  TIMESTAMP(9) precision reduced to maximum allowed, 6
LINE 1: select col::timestamp(9)
                    ^
            col             
----------------------------
 2021-01-11 01:00:05.120973
(1 row)

I guess the warning is acceptable in that case, or you can craft another query to avoid that warning too.

Oh, I almost forgot, make sure your date time default format is the right one because if Postgres wants MM/DD/YYYY, then the 21/11/2022 is out of range!