Так тонко, что даже толсто)

А не вариант добавить отдельный столбец с serial и проверять уникальность по двум столбцам? Это как-то... естественнее выглядит что ли.

Уникальность должна быть в имени) я ее потом вывожу на фронтенд, а не для выборки с таблицы)

Если вы при каждой последующей записи дописываете только новые появившиеся комментарии, то, очевидно даже для хлебушка, что вы не имеете понятия какие комментарии у вас удалили. Вы же это не пишете. Либо нужно сразу при очередной записи выбирать все комментарии, которые существовали на момент предыдущей записи, и каждый из них проверять на предмет их существования на данный момент, отмечая те, что удалили. Можно банально отдельную колонку со статусом добавить. Либо каждый раз писать полностью весь список комментариев и сравнивать в конце дня два последних списка.

Если это чисто для вывода, а выборки с where name=name{$id} не планируются, то можно же просто выбирать  concat(name, id) name.

Не, можно, конечно и триггеры, но мне лично вариант с колонкой кажется проще и нагляднее.

Ну или так, спс

Подключенные таблицы, далеко-далеко под капотом, являются обычными клиентскими запросами в оракл. Постгрес, грубо говоря, в итоге просто конструирует селекты и делает запросы в оракл. Совершенно так же, как это делает любой абстрактный код, обращающийся в базу данных.  Нет никакого понимания того, что в любой отдельный момент лежит в таблицах оракла. Никто не присылает вам  оповещения о изменениях в данных этих таблиц. Соответственно нет никакой технической возможности построить индекс. Да и незачем абсолютно.

Привет! Подскажите, есть у меня табличка типа id | state | date
Надо мне достать отсюда все записи с уникальным state (причём самый последний), а потом отсортировать по date.

select distinct on (state) * from x order by state, date desc;

Вернёт мне записи, отсортированные по state. А мне надо их как-то пересортировать по date ещё (также по desc). Грузить всё это в приложение и там сортировать вообще кажется не правильным. Интернет даёт какие-то странные предложения(

Мне надо, условно, такое (конечно, pgsql не скушает):

select distinct on (state) * from x order by date desc, state;

Subquery делать тоже очень не хочется, ведь тогда все индексы отъедут :(

row_number() over (partition by state order by date desc) as rnum

а потом select ... where rnum = 1

Вот не надо таких советов)

Я чувствовал, что оконные функции тут могут помочь, но вот придумать что-то не мог. Спасибо большое! Насколько это будет медленнее, чем distinct?

У нас PG хорошо на Debian 9/10.

Да и не завелось :(
[42703] ERROR: column "rnum" does not exist

Как уже писалось - все фломастеры разные. Я вот замечательно живу не на дебианах/шляпоосях. Советовать другому человеку дистрибутив - неблагодарное занятие, могут и послать.

select distinct on (state) * from x order by date desc, state;

Хмм... а чем то, что нужно, отличается от:

WITH x (id, state, date) AS (
VALUES
(1, 'state1', '2019-01-01'),
(2, 'state1', '2019-01-02'),
(3, 'state1', '2019-01-03'),
(4, 'state2', '2019-01-04'),
(5, 'state2', '2019-01-05'),
(6, 'state3', '2019-01-06')
)
SELECT DISTINCT ON (state) *
  FROM x
 ORDER BY state, date DESC;

?

Почему Вы так думаете, кстати?
Вот как раз с DISTINCT ON / ROW_NUMBER эффективного использования индексов Вам не видать, если что.

WITH x (id, state, date) AS (
VALUES
(1, 'state1', '2019-01-01'),
(2, 'state1', '2019-01-02'),
(3, 'state1', '2019-01-03'),
(4, 'state2', '2019-01-04'),
(5, 'state2', '2019-01-05'),
(6, 'state3', '2019-01-06')
)
SELECT DISTINCT ON (state) *
  FROM x
 ORDER BY state, date DESC;

Потому что в этом случае у меня всё будет отсортировано по state. А мне надо, чтобы было отсортировано по date