What is the most elegant way to store a nanosekum timestamp in postgresql?

Question

Unfortunately, the timestamp postgresql type can store timestamps accurate to microsec, but I also need a nanosek.

PostgreSQL - 8.5. Date / Time Types:

The timestamp and interval take an optional precision value p, which determines the number of fractional digits stored in the seconds field. By default, there is no clear reference to accuracy. The valid range of p is from 0 to 6 for timestamps and interval types.

And I need 7:

0,000,000 001 [billionth] nanosecond [ns]

0,000 001 [millionth] microsecond [μs]

0.001 [thousandth] millisecond [ms]

0.01 [hundredth] centrifugal [cs]

1.0 seconds [s]

Is there an elegant and effective way to solve this problem?

EDIT: Maybe store the timestamp in bigint?

Answer 1

numeric nano. :

create or replace function nanotimestamp_as_text(numeric)
returns text language sql immutable as $$
    select concat(to_timestamp(trunc($1))::timestamp::text, ltrim(($1- trunc($1))::text, '0'))
$$;

, , :

with my_data(nano_timestamp) as (
    select 1508327235.388551234::numeric
)

select 
    to_timestamp(nano_timestamp)::timestamp,
    nanotimestamp_as_text(nano_timestamp)
from my_data;

        to_timestamp        |     nanotimestamp_as_text     
----------------------------+-------------------------------
 2017-10-18 13:47:15.388551 | 2017-10-18 13:47:15.388551234
(1 row)

Answer 2

Bigint . , :

CREATE CAST (timestamp AS bigint)
WITHOUT FUNCTION;

Answer 3

, Postgres . , , - Postgres . timestamp9 Postgres.

bigint UNIX. , . , , 1970 2262 , .

Klin answer is the ideal solution if you do not want the extension to be installed on your system. However, it does not interact well with existing types of timestamps / intervals, and is also less efficient because it uses a numeric type as storage. Having an extension gives you more flexibility.

Disclaimer: I am the author of the extension