How to create a view in PostgreSQL containing ARRAY?

Question

I have a problem, I’m currently working with a Postgres database and what I want to do is to create a View from two tables. The problem is that one of the tables contains a column that is of the type “varchar ARRAY”. This column contains some sort of ID that I can use to get information from the second table.

I have the following:

Table 1: Person 

primary_name : varchar
known_for_titles : varchar ARRAY (array of t_const)  Table 2:

Table 2: Movie

t_const : varchar
primary_title : varchar

I want to create a view (actorView) that looks like this: -primary_name : varchar -famous_for : varchar ARRAY (array of titles)

I have this SQL that does not work properly, I manages to create the view but I can’t use the select statement on its own and I can’t ask a query on the view.

CREATE or REPLACE VIEW actorView AS
    SELECT p.primary_name,
           array_agg(t.primary_title) as famous_titles
FROM person p JOIN title t
                   ON t.t_const = any(p.known_for_titles)
    group by primary_name

When I try the select part of the SQL statement, it's just ends up searching in an endless loop and I have to terminate the query.

The same thing happens when I use the whole statement. It somehow manages to create the table but I can't search for anything.

See @TAM 's answer. You can run into problems in PostgreSQL if you don't name everything lowercase. See this post for some details. — bfris
– bfris, Commented Oct 6, 2019 at 4:08

TAM · Accepted Answer · 2019-10-04 13:49:34Z

I made the following modifications to your code:

Changed the table names to lower case,
made t_const a primary key, to be allowed to enter data in pgAdmin
added a bigint id as primary key to person.

Also, I had to change the name title to movie to confirm to your table names. (Probably your table descriptions in the posting were not precise :-( )

Then, the phrase

create or replace view actor_view as

SELECT p.primary_name,
           array_agg(t.primary_title) as famous_titles
FROM person p JOIN movie t
                   ON t.t_const = any(p.known_for_titles)
    group by primary_name

runs successfully for me.

The only substantial difference between your and my solution is the primary keys - the rest, I guess, were just typos in your posting. I don't know whether those pks make the difference, but it doesn't hurt to have them anyway.

Collectives™ on Stack Overflow

How to create a view in PostgreSQL containing ARRAY?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related