How can I cast an array of text
s into an array of UUID
s?
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 UUID
s 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
.
Best Answer
Like has been commented, the column
project_ids
should beuuid[]
, which would preclude the problem. It would also be more efficient.To change (with no illegal data in column like you asserted):
You had
uuid
instead ofuuid[]
by mistake.And this:
.. 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+):... 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 inprojects_ids
from the result. Typically, you'd want to keep those, too: useLEFT [OUTER] JOIN
instead (withusers
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:
db<>fiddle here (loosely based on McNets' fiddle)
Related: