0

I'm doing a bulk update statement and trying to cast values to the type of the column they're being set to. I won't have access to the column datatypes in the context where I'm generating the queries, without making a separate call to the database first.

Here's a minimal table:

CREATE TABLE public."test_table" (
    uid uuid primary key,
    other_uid uuid null
);

I can do insert statements without specifying the type and it implicitly casts them to UUID:

INSERT INTO public."test_table" (uid) 
VALUE ('00000000-0000-0000-0000-000000000001'), ('00000000-0000-0000-0000-000000000002');

But when I do a bulk update from a collection of un-nested arrays, it does not implicitly cast to UUID:

UPDATE public."test_table" AS tbl
SET
    other_uid = new_other_uid
FROM (select
    unnest(array['00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000002']) AS new_uid,
    unnest(array['00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000004']) AS new_other_uid
) AS data_table
WHERE uid = data_table.new_uid;

-- ERROR: operator does not exist: uuid = text
-- Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I know that I can explicitly cast both of the values to uuid, but I'm trying to cast to a variable type derived from the column. Something that might look something like this:

UPDATE public."test_table" AS tbl
SET
    other_uid = new_other_uid::pg_typeof(other_uid)
FROM (select
    unnest(array['00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000002']) AS new_uid,
    unnest(array['00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000004']) AS new_other_uid
) AS data_table
WHERE uid = data_table.new_uid::pg_typeof(uid);

-- ERROR: type "pg_typeof" does not exist

I thought maybe I could make a query to information_schema but I can't get this type of attempt to pass even the syntax check:

with column_types as (
    SELECT 
      column_name, 
      data_type 
    FROM information_schema.columns
    WHERE table_name = 'test_table'
)
UPDATE public."test_table" AS tbl
SET
    other_uid = CAST(new_other_uid AS (SELECT data_type FROM column_types WHERE column_name = "other_id"))
FROM (select
    unnest(array['00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000002']) AS new_uid,
    unnest(array['00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000004']) AS new_other_uid
) AS data_table
WHERE uid = cast(data_table.new_uid AS (SELECT data_type FROM column_types WHERE column_name = "other_id"));

--ERROR: syntax error at or near "select"

In the short term I'm going to query the database for the column types and use them to build the SQL query with type casts, but I'm bummed that I can't find a one-transaction way to do this.

Any thoughts, observations or solutions?

1 Answer 1

1

It took awhile to track down, but I found a handful of solutions on this SE post. My answer here is using Erwin Brandstetter's solution #5.

UPDATE public."test_table" AS tbl
SET
    other_uid = val.other_uid
FROM (
    values 
        ((null::public."test_table").uid, (null::public."test_table").other_uid),
        ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000003'),
        ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000004')
) val (uid, other_uid)
where tbl.uid = val.uid;

It works by casting nulls to the type of the row, subsetting them by the columns you want, and coercing this typed value tuple with your value tuples. The null row is ignored because there won't be a record with a null primary key.

NOTE: This doesn't solve the general case of 'How to cast values to a variable type' but it solves my case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.