Timestamp resolution
I need to set the timestamp type as the primary key (in each case it must be unique, some instructions can insert k records at a time) with the default value "current_timestamp". This is a kind of log file. For this, I have to improve the timestamp resolution per microsec (I don't think pg can write a million records per second). This precision is possible for postgres. See Here It is something like this:
CREATE TABLE upsistema
(
t timestamp(6) without time zone NOT NULL DEFAULT current_timestamp(6) without time zone,
blabla bigint,
foo bigint,
CONSTRAINT "XIDS3w" PRIMARY KEY (t)
)
But that won't work. When I check, with pgAdmin3, it is logged with millisecond precision. And of course, you can write more records in one millisecond. So, I needed to set some mystical variable or something to keep it accurate to microsections?
source to share
As you saw in the documentation here , the permission can be up microseconds
to using the type timestamp
.
Here's an example to show that microseconds exist:
CREATE TABLE table_test
(
column1 timestamp(6) without time zone,
column2 timestamp(6) without time zone
);
insert into table_test (column1,column2) values (current_timestamp, current_timestamp + interval '100 MICROSECONDS');
select extract (MICROSECONDS from column1 - column2 ) from table_test;
Result:
date_part
-----------
-100
(1 ligne)
source to share
By default, PostgreSQL stores timestamps as accurately as possible.
If I was at your best, I could use a different default for the timestamp. Current_timestamp is the start time of the current transaction. It does not change during the execution of a transaction, no matter how many rows you insert or how long it takes. This means that INSERT statements can fail due to duplicate primary key values.
create table upsistema (
t timestamp without time zone primary key
default current_timestamp,
foo bigint
);
insert into upsistema (foo) values (42), (43), (44);
ERROR: duplicate key value violates unique constraint "upsistema_pkey"
Try using clock_timestamp () as default. This does not guarantee success, but it makes it more likely.
create table upsistema (
t timestamp without time zone primary key
default clock_timestamp(),
foo bigint
);
insert into upsistema (foo) values (42), (43), (44);
select * from upsistema;
t foo - 2014-11-19 19: 17: 23.369432 42 2014-11-19 19: 17: 23.36958 43 2014-11-19 19: 17: 23.369587 44
Change the primary key constraint or drop it entirely to ensure success. Whether or not it matters depends on the application, but I wouldn't be surprised if you find multiple clients logging data in the same microsecond.
Your table uses timestamp without time zone
but current_timestamp and clock_timestamp () return timestamp with time zone
. It might be a good idea to change the UTC timezone for your session before running the SQL queries.
set time zone 'UTC';
select ... ;
and
set time zone 'UTC';
insert ... ;
If it makes sense to set the server to use UTC by default, you can set the time zone parameter in postgresql.conf to "UTC". Then you don't need to set the timezone in every session.
source to share