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](https://stackoverflow.com/questions/21796446) for some details. — bfris, Oct 06 '19 at 04:08

score 1 · Answer 1 · answered Oct 04 '19 at 13:49

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.

How to create a view in PostgreSQL containing ARRAY?

1 Answers1