如何在选择查询中比较多列和多字段数组？

Question

我在API服务器上创建查询时有些卡住，我只想用修改后的跟踪数据进行响应。我有tracks几列这样的表。

tracks(id, audio_fingerprint, name, creation_date, modified_date)

现在，我只想跟踪在上次获取的时间戳（作为API请求参数传递的音频指纹和上一次获取的时间戳的数组）之后进行更新的轨道。

SELECT * from tracks WHERE (audio_fingerprint, modified_date) IN (Array(audioFingerprint, > lastFetchedTimestamp));

（^^这是无效的查询，仅用于理解）。

谢谢

Answer 1

示例数据：

create table tracks (audio_fingerprint text, modified_date date);
insert into tracks values
    ('a', '2017-01-10'),
    ('b', '2017-01-10'),
    ('a', '2017-02-10'),
    ('b', '2017-02-10'),
    ('c', '2017-02-01');

将参数放在with查询中，并将其与表连接：

with given_values (fingerprint, last_fetched) as (
values
    ('a', '2017-01-01'::date),
    ('b', '2017-02-01')
)

select * 
from tracks t
join given_values v
on t.audio_fingerprint = v.fingerprint
and t.modified_date > v.last_fetched;

 audio_fingerprint | modified_date | fingerprint | last_fetched 
-------------------+---------------+-------------+--------------
 a                 | 2017-01-10    | a           | 2017-01-01
 a                 | 2017-02-10    | a           | 2017-01-01
 b                 | 2017-02-10    | b           | 2017-02-01
(3 rows)

除了CTE，您还可以使用派生表：

select * 
from tracks t
join (
    values
        ('a', '2017-01-01'::date),
        ('b', '2017-02-01')
    ) v(fingerprint, last_fetched)
on t.audio_fingerprint = v.fingerprint
and t.modified_date > v.last_fetched;