1

I have the following table in Postgres:

         Column          |            Type             |                   Modifiers                    
-------------------------+-----------------------------+------------------------------------------------
 id                      | uuid                        | not null
 name                    | character varying(255)      | not null
 parent_organization_ids | uuid[]                      | not null default '{}'::uuid[]

The column parent_organization_ids contains an array with all the parent hierarchy for that organization, having first the root, and in the last item the parent of the current row (if exists).

I need to do a query to show the current row information, and then do a left join to show the parent name of the current row.

How can I do that left join?

Is there an alternative to do this?

lante
  • 7,192
  • 4
  • 37
  • 57

1 Answers1

1

Solved doing the following:

SELECT org.name, parent.name
FROM organizations org
LEFT JOIN organizations parent on 
org.parent_organization_ids[array_upper(org.parent_organization_ids, 1)]=parent.id;
lante
  • 7,192
  • 4
  • 37
  • 57
  • 1
    Yes, that's the way to go. Alternatively consider normalizing your schema, so you can have referential integrity, unique constraints etc.: http://stackoverflow.com/questions/8016776/can-postgresql-have-a-uniqueness-constraint-on-array-elements/8017013#8017013 – Erwin Brandstetter Apr 28 '15 at 00:58