Feature request: Native nanosecond precision support for temporal types

Hello Hackers,

Postgres temporal types (timestamp, timestamptz, and time) currently lag
behind much of the analytical ecosystem in lacking native nanosecond
precision support.

Modern analytical and financial ecosystems increasingly require the ability
to store and process nanosecond-resolution timestamps regardless of the
precision of the local machine clock.

Nanosecond timestamps are already common across:

* Parquet (TIMESTAMP(NANOS))
<https://parquet.apache.org/docs/file-format/types/logicaltypes/>

* DuckDB (TIMESTAMP_NS)
<https://duckdb.org/docs/stable/sql/data_types/timestamp>

* ClickHouse (DateTime64(9))
<https://clickhouse.com/docs/sql-reference/data-types/datetime64>

* Oracle TIMESTAMP precision up to 9 fractional digits
<https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Data-Types.html>

* SAS datetime values with nanosecond precision
<https://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#p1wj0wt2ebe2a0n1lv4lem9hdc0v.htm>

* Pandas / NumPy (datetime64[ns])
<https://numpy.org/doc/stable/reference/arrays.datetime.html>

In practice, the lack of native PostgreSQL support forces applications into
workarounds such as:

* separate "nanosecond remainder" columns

* bigint epoch-nanosecond encodings

* custom/community temporal extensions e.g.
https://github.com/optiver/timestamp9

Additionally native support would be preferable because it would provide:

* consistent casting and operator semantics

* better support across drivers, ORMs, FDWs, and extensions

* a standardized ecosystem approach rather than fragmented custom types

The lack of native support makes Postgre less suitable for workloads
involving nanosecond-resolution datasets such as market microstructure
research.

Thanks

Alex Malek