16

How can I cast an array of texts into an array of UUIDs?

I need to do a join between two tables: users and projects.

The users table has an array field named project_ids containing the project IDs as text.

The projects table had a UUID field named id.

My initial idea was a query looks like:

SELECT * FROM projects
JOIN users ON
projects.id = ANY(users.project_ids)

But that does not work since users.project_ids are not UUIDs so I tried:

projects.id = ANY(users.project_ids::uuid[])

and even:

projects.id = ANY(ARRAY[users.project_ids]::uuid[])

but neither one works:

ERROR: invalid input syntax for type uuid: ""

UPDATE

@a_horse_with_no_name is definitely right. The best option should be using an array of UUIDs.

The question now is how can I alter an array of text into an array of uuid?

The users table is currently empty (0 records).

I have tried

ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID USING "project_ids"::uuid[];

which generates

ERROR: result of USING clause for column "product_ids" cannot be cast automatically to type uuid HINT: You might need to add an explicit cast.

ALTER TABLE "users" ALTER COLUMN "product_ids" SET DATA TYPE UUID USING "product_ids"::UUID;

I have also tried

ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID[] USING "project_ids"::uuid[];

which generates

ERROR: default for column "project_ids" cannot be cast automatically to type uuid[]

The column is set to an empty array as default.

I'm running PG version 10.4 and project_ids is currently text[] nullable.

3
  • You apparently have a empty strings in your array and those can't be converted. You would need to remove those array elements before you can cast the complete array.
    – user1822
    Commented Nov 8, 2018 at 8:00
  • 2
    The real question however is: why isn't project_ids defined as uuid[] to begin with?
    – user1822
    Commented Nov 8, 2018 at 8:06
  • Please remember to start any such question by declaring your Postgres version and the actual table definition - is project_ids type text[]? Commented Nov 8, 2018 at 15:29

4 Answers 4

9

Like has been commented, the column project_ids should be uuid[], which would preclude the problem. It would also be more efficient.

To change (with no illegal data in column like you asserted):

ALTER TABLE users ALTER COLUMN project_ids DROP DEFAULT;

ALTER TABLE users
ALTER COLUMN project_ids SET DATA TYPE uuid[] USING project_ids::uuid[];

You had uuid instead of uuid[] by mistake.

And this:

ERROR: default for column "product_ids" cannot be cast automatically to type uuid[]

.. means you have a default value set for the column. That expression cannot be transformed automatically. Remove it before altering the type. You can add a new DEFAULT later.

Fix to original problem

The efficient fix in your original situation is to remove empty strings from the array with array_remove() before the cast (requires Postgres 9.3+):

SELECT *
FROM   users    u
JOIN   projects p ON p.id = ANY(array_remove(u.project_ids, '')::uuid[]);

... after investigating why there can be empty stings in that text[] column.

Related:

Fine points

The [INNER] JOIN in your query removes users without valid projects in projects_ids from the result. Typically, you'd want to keep those, too: use LEFT [OUTER] JOIN instead (with users first).

The JOIN folds duplicate entries either way, which may or may not be as desired. If you want to represent duplicate entries, unnest before the join instead.

And if your aim is simply to resolve the array of IDs to an array of project names, you'll also want to preserve original order of array elements:

SELECT *
FROM   users u
LEFT   JOIN LATERAL (
   SELECT ARRAY(
      SELECT project_name  -- use the actual column(s) of your case
      FROM   unnest (array_remove(u.project_ids, '')::uuid[]) WITH ORDINALITY AS p(id, ord)
      JOIN   projects USING (id)
      ORDER  BY ord
      )
   ) p(projects) ON true;

db<>fiddle here (loosely based on McNets' fiddle)

Related:

5
  • @Sig: I added the requested ALTER TABLE statement. Commented Nov 9, 2018 at 0:46
  • Thanks, I have tried it but it seems not to work. See my update.
    – Sig
    Commented Nov 9, 2018 at 0:49
  • 1
    That's why I strongly suggest to always start questions by disclosing the table definition (CREATE TABLE statement). You obviously have an undisclosed DEFAULT value for the column. Added solution above. Commented Nov 9, 2018 at 0:57
  • Thanks, I didn't create the table directly in SQL. That is the reason I missed a few details. Next time I will try to get the full picture upfront.
    – Sig
    Commented Nov 9, 2018 at 1:03
  • No matter how you created it, you can get the (re-engineered) SQL statement easily with various client tools like pgAdmin3 or pgAdmin4. Or \dt tbl in psql, but a CREATE TABLE script is best since we can use that to test immediately. Commented Nov 9, 2018 at 1:06
3
create table users (user_id int, projects text[]);

insert into users values
(1, (array['a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
           'b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11'])::text[]);

insert into users values
(2, (array['a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
           'b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11',
           'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'])::text[]);

insert into users values
(3, (array['f0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
           '',
           'e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'])::text[]);
create table projects (project_id uuid);

insert into projects values
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid),
('d0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid),
('e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);

As a_horse_with_no_name has pointed out, it fails if some array element has no value. It cannot be converted.

select user_id, project_id
from   projects
join   users
on     project_id = any(projects::uuid[])
ERROR:  invalid input syntax for type uuid: ""

However, you could try to cast project_id as text in this way:

select user_id, project_id
from   projects
join   users
on     project_id::text = any(projects);
user_id | project_id                          
------: | :-----------------------------------
      1 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      2 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      3 | e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

db<>fiddle here

Or you could expand the array (unnest) and avoid empty/null values:

with usr as
(
    select user_id, unnest(projects) as project_id
    from   users
)
select user_id, projects.project_id
from   projects
join   usr
on     coalesce(usr.project_id, '') <> ''
and    usr.project_id::uuid = projects.project_id;
user_id | project_id                          
------: | :-----------------------------------
      2 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      1 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      3 | e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

db<>fiddle here

0
0

How can I cast an array of texts into an array of UUIDs?

In your case I agree with others that project_ids should just be a uuid[] to start with. But for the more general problem, you can define a cast to handle this automatically:

CREATE CAST (text[] AS uuid[]) WITH INOUT AS ASSIGNMENT;

That will automatically convert things for you when you do an INSERT etc. You can also say AS IMPLICIT to cover more cases, although that can cause parse-time ambiguities.

0

In my case I needed to pass an uuid[]

This did it:

 (array['a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
           'b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11',
           'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'])::uuid[]

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.