Postgresql More than onw row returned by a subquery in a insert to script-CodePudding

I need to create entries based on the id's of a SELECT query, but i cant manage to do this in anyway, tried UNION ALL but im not experto on SQL.

this is my current code, the SELECT works fine alone, but with INSERT INTO it gives me the error "more than one row returned by a subquery used as an expression"

INSERT INTO payment_consent_state (
    id,
    consent_id, 
    status, 
    created_at
)
SELECT uuid_in(overlay(overlay(md5(random()::text || ':' || random()::text) placing '4' from 13) placing to_hex(floor(random()*(11-8 1)   8)::int)::text from 17)::cstring) as id,
(SELECT consent_id FROM payment_consent_state
    WHERE  created_at < current_timestamp - interval '185 minutes'
    GROUP BY consent_id
    HAVING COUNT(consent_id) = 1)
,
'REJECTED' as status,
now() as data

CodePudding user response：

The error message you're seeing is being caused by the fact that the subquery in the SELECT clause of your INSERT INTO statement is returning more than one row. The INSERT INTO statement is expecting a single value for each column, but the subquery is returning multiple rows.

You can try to fix this by using the IN operator instead of = in the HAVING clause of the subquery. This will match multiple values instead of just one. For example:

HAVING COUNT(consent_id) IN (1,2,3)

Another solution is to use a join instead of a subquery, this way you can insert multiple rows at once using the results of the SELECT statement.

INSERT INTO payment_consent_state (
    id,
    consent_id, 
    status, 
    created_at
)
SELECT uuid_in(overlay(overlay(md5(random()::text || ':' || random()::text) placing '4' from 13) placing to_hex(floor(random()*(11-8 1)   8)::int)::text from 17)::cstring) as id,
pcs.consent_id, 'REJECTED' as status, now() as data
FROM payment_consent_state pcs
WHERE  pcs.created_at < current_timestamp - interval '185 minutes'
GROUP BY pcs.consent_id
HAVING COUNT(pcs.consent_id) = 1

Keep in mind that this is just an example and you should adjust it to your specific needs, also, you might need to add an ORDER BY clause to make sure you are selecting the correct rows to be inserted

CodePudding user response：

Is this helpful :

INSERT INTO payment_consent_state (
    id,
    consent_id, 
    status, 
    created_at
)
SELECT uuid_in(overlay(overlay(md5(random()::text || ':' || random()::text) placing '4' from 13) placing to_hex(floor(random()*(11-8 1)   8)::int)::text from 17)::cstring) as id,
consent_id,
'REJECTED' as status,
now() as data
FROM payment_consent_state
WHERE  created_at < current_timestamp - interval '185 minutes'
GROUP BY consent_id
HAVING COUNT(consent_id) = 1)

This considered as multi rows itself

(SELECT consent_id FROM payment_consent_state
    WHERE  created_at < current_timestamp - interval '185 minutes'
    GROUP BY consent_id
    HAVING COUNT(consent_id) = 1)