inserting values into array column

[artistlikeu]

i have query result (25) rows. can i insert them into an column (array) in a table.

thnx

[michaelb]

Yes, you can...
but at some point you really have to start reading the manual!

http://www.postgresql.org/docs/8.0/static/arrays.html
http://www.postgresql.org/docs/8.0/static/functions-array.html

[artistlikeu]

Yes, you can...
but at some point you really have to start reading the manual!

http://www.postgresql.org/docs/8.0/static/arrays.html
http://www.postgresql.org/docs/8.0/static/functions-array.html

Dear Michael

I m having another problem regarding (1-many relation). If u guide me.
i wrote a query which results to count the multiple values of an entry using count i.e.
select course_id, count(student_id)
from school
GROUP BY course_id
having count(student_id) > 5

this query gives me some results. i am interested to find which student id is against which course....1-many relationship. my result should look like

course_id + student_id
4500 + 1501
4500 + 1508
4500 + 1541
4500 + 1551
4500 + 1532
4500 + 1510
4700 + 1589
4700 + 1518
4700 + 1546
4700 + 1538
4700 + 1522
4700 + 1533
.
.
.
.
and then i want to insert student_id column into a column(array).


thnx
regards
@rtist

[michaelb]

Can you post the table schema, please?

[artistlikeu]

Can you post the table schema, please?

Dear Michael, i do not know how to put schema or tablhe. But my problem is simple. i once again explain it here. If u kindly have a look, i will be grateful 2 u.

i have a table school with two fields
course_id (int4).....
student_id (int4)

1) now many students share same course. i want to write a query that gives me course_id which has more than 5 students in a course.

my result should be like this....

course_id + student_id
4500 + 1501
4500 + 1508
4500 + 1541
4500 + 1551
4500 + 1532
4500 + 1510
4700 + 1589
4700 + 1518
4700 + 1546
4700 + 1538
4700 + 1522
4700 + 1533
4700 + 1564
.
.
.
2) i have an other table which has two field

course_id (int4)
student_id (int4[])..... this field is one dimensional array

and now i want to insert student_id column into this column(array) against course_id. My expected results should be like this

course_id + student_id
4500 + {1501, 1508, 1541, 1551, 1532, 1510}
4700 + {1589, 1518, 1546, 1538, 1522, 1533, 1564}
.
.
.
this problem is urgent... if someone gives me hint plz....

thnx n regards
@rtist

[michaelb]

The first query seems quite simple:

 
SELECT course_id, student_id 
FROM    school WHERE course_id in 
(
    select course_id from school group by (course_id) 
    having count(course_id) > 5 
);
 

From your previous posting I assume that the second table already has
columns with the course_id, but it needs to have the second field populated - the array of student_id for each course.

This query is little more complex... I think something like this
should work for you:

 
 
UPDATE courses set student_id = 
(
    SELECT ARRAY (select student_id from school 
        where courses.course_id = school.course_id)
)
 
 

Both topics: subqueries and arrays are well documented in the Postgres manual, you may want to look at the code examples there.

[artistlikeu]

The first query seems quite simple:

 
SELECT course_id, student_id 
FROM    school WHERE course_id in 
(
    select course_id from school group by (course_id) 
    having count(course_id) > 5 
);
 

From your previous posting I assume that the second table already has
columns with the course_id, but it needs to have the second field populated - the array of student_id for each course.

This query is little more complex... I think something like this
should work for you:

 
 
UPDATE courses set student_id = 
(
    SELECT ARRAY (select student_id from school 
        where courses.course_id = school.course_id)
)
 
 

Both topics: subqueries and arrays are well documented in the Postgres manual, you may want to look at the code examples there.

Dear Michael,

this results all value in one array. i want values respectively as i indicate din my expected result. If u can kindly give hint....
thnx

[michaelb]

I'm not sure I understand.

expected results should be like this

course_id + student_id

4500 + {1501, 1508, 1541, 1551, 1532, 1510}
4700 + {1589, 1518, 1546, 1538, 1522, 1533, 1564}

Isn't this what you're getting with this query?