Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create schema my_schema;
create table my_schema.user(id int primary key);
insert into my_schema.user select 1;
CREATE TABLE IF NOT EXISTS my_schema.user_profile (
    id SERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES my_schema.user (id) UNIQUE NOT NULL,
    media_url VARCHAR(50) NOT NULL,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'utc'),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'utc')
);
insert into my_schema.user_profile select 1, 1,'example.com/pic.jpg' returning *;

CREATE SCHEMA



CREATE TABLE



INSERT 0 1



CREATE TABLE




id
user_id
media_url
created_at
updated_at


1
1
example.com/pic.jpg
2024-02-03 02:28:39.566576
2024-02-03 02:28:39.566576



INSERT 0 1


prepare insert_s(bigint,text) as
INSERT INTO my_schema.user_profile
        (user_id, media_url)
    VALUES
        ($1, $2)
    ON CONFLICT (user_id)
        DO UPDATE SET (
            updated_at,
            media_url
        ) = (
            CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC',
            excluded.media_url
        )
returning *;

PREPARE


execute insert_s(1,'example.com/a_completely_new_pic.jpg');


id
user_id
media_url
created_at
updated_at


1
1
example.com/a_completely_new_pic.jpg
2024-02-03 02:28:39.566576
2024-02-03 02:28:40



INSERT 0 1