PostgreSQL - Left join with the last item of an array

Question

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?

side note : Postgre is wrong either Postgres or PostgreSQL — Vivek S.
– Vivek S., Commented Apr 28, 2015 at 4:34

lante · Accepted Answer · 2015-04-27 21:13:36Z

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;

answered Apr 27, 2015 at 21:13

lante

7,3924 gold badges39 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erwin Brandstetter Over a year ago

Yes, that's the way to go. Alternatively consider normalizing your schema, so you can have referential integrity, unique constraints etc.: stackoverflow.com/questions/8016776/…

Collectives™ on Stack Overflow

PostgreSQL - Left join with the last item of an array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related