PostgreSQL, select rows if unique

PostgreSQL, select rows if unique

I'm trying to do a two part query and return a text array of semicolon delimited column values.

I'm trying to do a two part query and return a text array of semicolon delimited column values.

First, select only rows that are unique based on values of three columns (i.e. if the three tuple of values exists more than once, it is not unique). Of the remaining rows, perform another filer based on a fourth column.

This is how I think about it, but maybe there's a better solution.

I've tried this a few different ways. My current attempt is using CTE:

with uniqe as (
    select distinct on (
        col1,
        col2,
        col3
    ) *
    from MyTable
)
select concat(col::text, ';', col2::text, ';', col3)
    as key
    from uniqe
    where upper(dateRange) <= (now() - interval '1 days')
    order by key;

The issue I'm running into is that SELECT DISTINCT ON (col1, col2, col3) ... seems to pick at least 1 row of the others that I'm not considering "unique".

Just to be clear, here's a sample table:

 id  | col1 | col2 | col3 |                       dateRange
-----+------+------+------+-------------------------------------------------------
  1  |   1  |   1  |  A   |   ["2018-12-31 16:01:40-08","2018-12-31 16:03:20-08")
  2  |   1  |   1  |  A   |   ["2018-12-31 16:01:40-08","2018-12-31 16:03:20-08")
  3  |   1  |   1  |  B   |   ["2018-12-31 16:01:40-08","2018-12-31 16:03:20-08")
  4  |   1  |   2  |  A   |   ["2018-12-31 16:01:40-08","2018-12-31 16:03:20-08")
  5  |   2  |   1  |  A   |   ["2018-12-31 16:01:40-08","2018-12-31 16:03:20-08")
  6  |   2  |   1  |  A   |   ["2018-12-31 16:01:40-08","2018-12-31 16:03:20-08")
  7  |   1  |   2  |  B   |   ["2018-12-31 16:01:40-08","2018-12-31 16:03:20-08")
  8  |   1  |   2  |  B   |   ["2018-12-31 16:01:40-08","2018-12-31 16:03:20-08")

I consider rows 3 & 4 to be the only unique rows.

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

PostgreSQL Tutorial - Learn PostgreSQL Database from Scratch

PostgreSQL Tutorial - Learn PostgreSQL Database from Scratch. Learn how to use PostgreSQL in this full course. PostgreSQL is a general purpose and object-relational database management system. It is the most advanced open source database system widely used to build back-end systems. What is a Database? What is SQL And Relational Database? What is PostreSQL AKA Postrgres? PostgreSQL Installation. How to Create Database.

Learn & Use PostgreSQL - Getting Started with Sequelize and PostgreSQL

In this tutorial, we will learn about NodeJS ORM called Sequelize interfacing with PostgreSQL Database. We are going to learn and use PostgreSQL. Sequelize is the NodeJS module which provides common functionalities and utilities to manipulate SQL based databases. Technically, it is an ORM ( Object relational mapping ) module for NodeJS. It supports the following databases: MySQL, PostgreSQL, MariaDB, SQLite, MSSQL

PostgreSQL with Nodejs

Most of the applications need at some point to persist data. It can be through files, local storage, cloud services or often databases