I am trying to create a PGSQL function that uses HTTP to get some json, format it, and insert it into a table using the donation_id key as a row constraint to prevent duplicates.
I have tried this function:
BEGIN
INSERT INTO donations(
donation_id, amount, avatar_image_url, created_date_utc, display_name, donor_id, event_id, incentive_id, message, participant_id, team_id)
ON CONFLICT (donation_id) DO NOTHING
SELECT elms::jsonb->>'donationID' AS donation_id ,
(elms::jsonb->>'amount')::float8 AS amount ,
elms::jsonb->>'avatarImageURL' AS avatar_image_url ,
(elms::jsonb->>'createdDateUTC')::timestamptz AS created_date_utc ,
elms::jsonb->>'displayName' AS display_name ,
elms::jsonb->>'donorID' AS donor_id ,
(elms::jsonb->>'eventID')::int AS event_id ,
elms::jsonb->>'incentiveID' AS incentive_id ,
elms::jsonb->>'message' AS message ,
(elms::jsonb->>'participantID')::int AS participant_id ,
(elms::jsonb->>'teamID')::int AS team_id
FROM (
select jsonb_array_elements(content::jsonb) AS elms
from http_get('https://extralife.donordrive.com/api/teams/59881/donations/')) as alias;
END;
I'm not quite understanding what I am doing wrong with the ON CONFLICT part of the query, just that it is apparently not valid syntax. I appreciate the insight as I'm not quite grasping the explainer written in docs.
Assuming a test table:
drop table if exists donations;
create table donations (
donation_id text primary key,
amount float8,
avatar_image_url text,
created_date_utc timestamptz,
display_name text,
donor_id text,
event_id int,
incentive_id text,
message text,
participant_id int,
team_id int);
It will work once you move the ON CONFLICT (donation_id) DO NOTHING to the end of the query:
INSERT INTO donations(donation_id, amount, avatar_image_url, created_date_utc,
display_name, donor_id, event_id, incentive_id, message, participant_id,
team_id)
SELECT elms::jsonb->>'donationID' AS donation_id ,
(elms::jsonb->>'amount')::float8 AS amount ,
elms::jsonb->>'avatarImageURL' AS avatar_image_url ,
(elms::jsonb->>'createdDateUTC')::timestamptz AS created_date_utc ,
elms::jsonb->>'displayName' AS display_name ,
elms::jsonb->>'donorID' AS donor_id ,
(elms::jsonb->>'eventID')::int AS event_id ,
elms::jsonb->>'incentiveID' AS incentive_id ,
elms::jsonb->>'message' AS message ,
(elms::jsonb->>'participantID')::int AS participant_id ,
(elms::jsonb->>'teamID')::int AS team_id
FROM ( select jsonb_array_elements('[
{
"displayName": "Christine",
"donorID": "A05C2C1E5DE15CDC",
"links": {
"recipient": "https://assets.yourdomain.com/somelink"
},
"eventID": 552,
"createdDateUTC": "2022-09-18T14:08:35.227+0000",
"recipientName": "Have A Drink Show",
"participantID": 494574,
"amount": 50,
"avatarImageURL": "https://assets.yourdomain.com/asset.gif",
"teamID": 59881,
"donationID": "FDBB61C5C8FFB3AE"
}
]'::jsonb) AS elms) as alias
ON CONFLICT (donation_id) DO NOTHING;