By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,109 Members | 1,007 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,109 IT Pros & Developers. It's quick & easy.

inserting values into array column

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

thnx
Jan 11 '07 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 534
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
Jan 11 '07 #2

P: 38
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
Jan 12 '07 #3

Expert 100+
P: 534
Can you post the table schema, please?
Jan 12 '07 #4

P: 38
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
Jan 13 '07 #5

Expert 100+
P: 534
The first query seems quite simple:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT course_id, student_id 
  3. FROM    school WHERE course_id in 
  4. (
  5.     select course_id from school group by (course_id) 
  6.     having count(course_id) > 5 
  7. );
  8.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. UPDATE courses set student_id = 
  4. (
  5.     SELECT ARRAY (select student_id from school 
  6.         where courses.course_id = school.course_id)
  7. )
  8.  
  9.  
Both topics: subqueries and arrays are well documented in the Postgres manual, you may want to look at the code examples there.
Jan 15 '07 #6

P: 38
The first query seems quite simple:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT course_id, student_id 
  3. FROM    school WHERE course_id in 
  4. (
  5.     select course_id from school group by (course_id) 
  6.     having count(course_id) > 5 
  7. );
  8.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. UPDATE courses set student_id = 
  4. (
  5.     SELECT ARRAY (select student_id from school 
  6.         where courses.course_id = school.course_id)
  7. )
  8.  
  9.  
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
Jan 16 '07 #7

Expert 100+
P: 534
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?
Jan 16 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.