PostgreSQL如何找到最近n分钟内的任何更改 [英] PostgreSQL how to find any changes in the last n-minutes

问题描述

我正在编写一个同步PostgreSQL和MS SQL服务器数据库的程序(并在此过渡中添加了一些更改).对于数百万条记录，这将花费很长时间，并且使用select *将服务器加载得非常糟糕.它还需要更多的资源来解析未更改的记录并针对MS SQL Server对其进行验证.

I am writing a program that syncs PostgreSQL and MS SQL server databases (and adds some changes in this transition). With multi million records, it takes a long time, and loads server pretty bad with select *; it also takes more resources to parse unchanged records and validate them against MS SQL server.

PostgreSQL中是否有任何日志可以解析以找出最近n分钟内发生的更改?这将使我仅选择我需要处理的那些记录；提高性能.

Are there any logs in PostgreSQL that I can parse to find out the changes that took place in the last n-minutes? This would allow me to select only those records that I need to work; improving performance.

推荐答案

PostgreSQL，在最近n分钟内查找更改:

Postgresql不会自动存储添加/更新/删除行的时间(如果您不希望这样做，postgresql确实会降低处理此类时间戳的速度).

Postgresql does not automatically store the time that rows were added/updated/deleted (it would really slow things down for postgresql to handle timestamps like this if you didn't want it to).

您必须自己做:在表中添加一个timestamp列.当您在表中插入一行时，让它将时间戳列更新为current_timestamp.选择行时，请使用select语句对时间戳大于N分钟的位置进行过滤，如下所示:

You'll have to do it yourself: Add a timestamp column to the table. When you insert a row into the table, have it update the timestamp column to the current_timestamp. When you are selecting the row, use a select statement that filters down where timestamp is greater than N minutes ago as follows:

获取时间戳大于日期的行:

SELECT * from yourtable 
WHERE your_timestamp_field > to_date('05 Dec 2000', 'DD Mon YYYY');

获取最近n分钟内已更改的行:

SELECT * from yourtable 
WHERE your_timestamp_field > current_timestamp - interval '5 minutes'

这篇关于PostgreSQL如何找到最近n分钟内的任何更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！