0

I'm on Postgres 12.8.

I have a table of data points and tags attached to them:

CREATE TABLE data_points (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    date DATE NOT NULL,
    value INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE tags (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    name TEXT NOT NULL,
    data_point_id INT NOT NULL REFERENCES data_points(id),
    PRIMARY KEY (id)
);

We receive this data via CSV, with rows structured like date,value,tag0,tag1,...tagN, and each row can have 0 or more tags.

We would like to mass-upsert, and would like to enforce uniqueness over both the date and the specific set of tags. I.E. if a point with some date and set of tags exists, and we get a new CSV row with the same date and identical (case-insensitive) tags, but a new value, we want to update existing point rather than create a new one. In any other case, we'd insert.

The reason the tags are in a separate table is because the real system in play has further requirements, that would be difficult to deliver if we aggregated the tags into an array or jsonb field on the data points themselves. (It also uses key/value pairs rather than a single list of tags, but I don't think that added complexity is relevant to this question)

I've searched for answers on this, but the only results are related to simple uniqueness or exclusion constraints, and don't have the set-of-joined-rows part of the requirements.

All that said, should I just denormalize anyway here? I'd really like to be able to do this via upsert, just for performance reasons.

There's a better way to handle this, right?

6
  • How about a hash of normalised, sorted, concatenated tags?
    – mustaccio
    Oct 4, 2022 at 22:53
  • I'm not sure what ramifications that would have on the searching / sorting / filtering requirements we have. If I have to make a performance tradeoff somewhere, I'd rather do it on insert. These things get queried all the time.
    – x1a4
    Oct 5, 2022 at 0:07
  • I guess I failed to express myself clearly. The hash wouldn't replace the actual tag values; its addition will help you form the unique constraint you want.
    – mustaccio
    Oct 5, 2022 at 0:38
  • Ah, ok. I mistook normalised in your comment. But yeah, this is the direction i'm leaning at this point. I was mostly just wondering if there was some Postgres Magic™ for this that I wasn't aware of.
    – x1a4
    Oct 5, 2022 at 0:43
  • If the CSV has a variable number of columns, you cannot load it with COPY. You will have to write client code that parses the CSV files and runs the appropriate INSERT statements. Oct 5, 2022 at 2:51

1 Answer 1

1

I ended up just storing a hash code of the sorted & normalized tags on the data point row, as those never have their tags updated (new tags means new data point), then used a pair of partial unique indexes (date WHERE tag_hash IS NULL and (date, tag_hash) WHERE tag_hash IS NOT NULL).

With the version of PG I'm using, this requires the input to be partitioned into two collections, depending on whether the row has segments, and then two separate upserts. That's ok for my use. This is because Postgres only supports a single ON CONFLICT per upsert, but I have two constraints I need to negotiate.

Postgres 15+ (currently in the RC stage at time of this writing) has the ability to create a unique index, with nulls being NOT DISTINCT.

From the release notes

Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut)

Previously NULL entries were always treated as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT.

If I were using Postgres 15, I could solve my problem with a single unique index, and upsert all of the data at once.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.