I don't think we should strive to remove non-idempotent cases. If something is not idempotent does not mean it is bad. It just means that request should be handled differently.

In your example (and I ask this as I remained confused after also reading SO):

Let's say that you need the client to provide the ID in the request body.

In this case, how is using PUT when creating a new resource idempotent if the ID should be unique and you have a constraint on the DB level for example?

What happens when the second call will be made with the same ID?

If I execute the following sequence:

Call 1: PUT resource + request.body {id: 1} => a new record is created so the state of the system changes

Call 2: PUT resource + request.body {id: 1} => no record is created, maybe the existing one is updated

IMO this is not idempotent nor should it be. Creating a new resource is not an idempotent operation.

I also don't like that depending on the state of the DB two requests with the same attributes will have different impacts on the system.

In my mind as a consumer of an API it is simpler: POST it creates a new record so I know what to expect, PUT updates an existing one.