0

Suppose I create a table of tasks.

CREATE TABLE todos (
  id UUID DEFAULT gen_random_uuid PRIMARY KEY,
  text STRING(1000)
)

An end-user creates some new todos (which don't yet have an id/UUID) and updates others, and we'd like to be able to update or insert those tasks (update if they have an id, insert otherwise). We could create or update one by one, using the right query.

-- $1 is "some task"
INSERT INTO todos (text)
VALUES ($1)
RETURNING id

-- $1 is a UUID, $2 is "some task"
UPDATE todos
SET text = $2
WHERE id = $1

Can "update or insert" be performed in one query? Or better, in a single query that works with a batch of todos?

UPSERT and INSERT INTO ... ON CONFLICT don't seem to be the right approach, because they try to create first, whereas I'd like to try to update first otherwise insert (to generate id).

I'm using CockroachDB, Go, and the pgx driver.

How would you typically handle this situation?

dgh
  • 8,969
  • 9
  • 38
  • 49

1 Answers1

0

That can never work, because you have no way to identify a task. An automatically generated id is useful as an artificial primary key, that is, it prevents identical copies, but that artificial identifier has no meaning.

If you want to be able to update a task, you have to be able to identify it somehow. Since all you have is the task, all you can do is to have a unique constraint on task to identify when the same task comes a second time. Then you can use task in the ON CONFLICT clause.

However, it seems that task is not so much an identifier as a command line. In that case, you need to change your data model to have something like a task_name that uniquely identifies an individual task. If that task_name is never allowed to change, you might as well make it your primary key and do away with the id.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I chose a poor field name in the example. I've renamed the `task` field to `text` to clarify that in my case a "task" or "todo" is an object with an id and text. A new todo object only has text (until its persisted to the db), an existing todo has both id and text. – dgh Feb 10 '23 at 03:57
  • Your edit is not an improvement, because `text` is a data type in PostgreSQL and there is no data type `string`. Never store a composite object in a single column, as you seem to do here. The name or other identifying tag of the task should be in one column, and attributes of the task in other columns. Then you can put a unique constraint on the identifier, and your problem is solved. – Laurenz Albe Feb 10 '23 at 05:43