Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE TABLE IF NOT EXISTS my_schema.table_a (
    id serial PRIMARY KEY,
    table_b_ids BIGINT[] NOT NULL,
    table_a_name VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS my_schema.table_b (
    id serial PRIMARY KEY,
    tag_value BIGINT UNIQUE NOT NULL
);

CREATE SCHEMA

CREATE TABLE

CREATE TABLE

INSERT INTO my_schema.table_b VALUES
    (1, 12345),
    (2, 12346);

INSERT INTO my_schema.table_a VALUES
    (1, '{1}', 'Michael');

INSERT 0 2

INSERT 0 1

UPDATE my_schema.table_a
SET table_a_name = 'John'
WHERE 12345 = ANY(table_b_ids)
RETURNING *

id
table_b_ids
table_a_name

UPDATE 0