SQL aggregation function to choose the only value

You can get only the "unique" natural ids using:

select technical_id, max(natural_id), sum(val)from tgroup by technical_idhaving min(natural_id) = max(natural_id);

If you want the query to actually fail, that is a little hard to guarantee. Here is a hacky way to do it:

select technical_id, max(natural_id), sum(val)from tgroup by technical_idhaving (case when min(natural_id) = max(natural_id) then 0 else 1 / (count(*) - count(*)) end) = 0;

And a db<>fiddle illustrating this.

Seems I've finally found solution based on single-row cardinality of correlated subquery in select clause:

select technical_id,       (select v from unnest(array_agg(distinct natural_id)) as u(v)) as natural_id,       sum(val)from tgroup by technical_id;

This is the simplest solution for my situation at this moment so I'll resort to self-accept. Anyway if some disadvantages show, I will describe them here and reaccept to other answer. I appreciate all other proposals and believe they will be valuable for anybody too.

You can use

SELECT technical_id, max(natural_id), count(natural_id)...GROUP BY technical_id;

and throw an error whenever the count is not 1.

If you want to guarantee the constraint with the database, you could do one of these:

1. Do away with the artificial primary key.

2. Do something complicated like this:

   CREATE TABLE id_map (   technical_id bigint UNIQUE NOT NULL,   natural_id text UNIQUE NOT NULL,   PRIMARY KEY (technical_id, natural_id));ALTER TABLE t   ADD FOREIGN KEY (technical_id, natural_id) REFERENCES id_map;