Transmitindo uma matriz de textos para uma matriz de UUIDs

Como já foi comentado, a coluna project_idsdeveria ser uuid[], o que evitaria o problema. Também seria mais eficiente.

Para alterar (sem dados ilegais na coluna como você afirmou):

ALTER TABLE users ALTER COLUMN project_ids DROP DEFAULT;

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

Você tinha uuidem vez de uuid[]por engano.

E isto:

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

.. significa que você tem um valor padrão definido para a coluna. Essa expressão não pode ser transformada automaticamente. Remova-o antes de alterar o tipo. Você pode adicionar um novo DEFAULTmais tarde.

Corrigir o problema original

A correção eficiente em sua situação original é remover strings vazias do array antes da conversão (requer Postgres 9.3+):array_remove()

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

... depois de investigar por que pode haver picadas vazias nessa text[]coluna.

Relacionado:

• Excluir elemento do array por índice
• A pesquisa de índice seria visivelmente mais rápida com char vs varchar quando todos os valores são 36 caracteres

Pontos finos

• O [INNER] JOINem sua consulta remove usuários sem projetos válidos projects_idsdo resultado. Normalmente, você também deseja mantê - los: use LEFT [OUTER] JOINem vez disso (com usersprimeiro).

• As JOINdobras duplicam as entradas de qualquer maneira, o que pode ou não ser o desejado. Se você quiser representar entradas duplicadas, desvincule antes da junção.

E se o seu objetivo é simplesmente resolver o array de IDs para um array de nomes de projetos, você também vai querer preservar a ordem original dos elementos do array:

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 aqui (vagamente baseado no violino de McNets )

Relacionado:

• Como preservar a ordem original dos elementos em uma matriz não aninhada?

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);

Como a_horse_with_no_name apontou, ele falha se algum elemento do array não tiver valor. Não pode ser convertido.

select user_id, project_id
from   projects
join   users
on     project_id = any(projects::uuid[])

ERRO: sintaxe de entrada inválida para o tipo uuid: ""

No entanto, você pode tentar converter project_idcomo texto desta maneira:

select user_id, project_id
from   projects
join   users
on     project_id::text = any(projects);

user_id | ID_do_projeto                          
------: | :-----------------------------------
      1 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      2 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      3 | e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

db<>fique aqui

Ou você pode expandir o array (unnest) e evitar valores vazios/nulos:

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 | ID_do_projeto                          
------: | :-----------------------------------
      2 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      1 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      3 | e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

db<>fique aqui

Como posso converter uma matriz de textos em uma matriz de UUIDs?

No seu caso, concordo com os outros que project_idsdeveriam ser apenas uuid[]para começar. Mas para o problema mais geral, você pode definir um elenco para lidar com isso automaticamente:

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

Isso converterá automaticamente as coisas para você quando você fizer um INSERTetc. Você também pode dizer AS IMPLICITpara cobrir mais casos, embora isso possa causar ambiguidades no tempo de análise.