Get a real number by dividing two integers
update table set real_num = (a+0.0)/(b+0.0)
select cast(text as real)...
Extract day from timestamp, interval...
select extract(day from time '2007-01-02 22:11:00') from ...
Failed:update y_question q set answerer_avg_tp =avg(u.total_points),answerer_avg_qr=avg(u.questions_resolved) from y_answer a, y_user u where a.user_id = u.id and a.ques_id = q.id; or update y_question q set answerer_avg_tp,answerer_avg_qr,answerer_avg_ta,answerer_avg_ba,answerer_avg_ms_day,answerer_avg_br,answerer_avg_ar = (select avg(u.total_points) ,avg(u.questions_resolved) ,avg(u.total_answers) ,avg(u.best_answers) ,avg(u.member_since_in_day) ,avg(u.best_ans_ratio) ,avg(u.ratio_ans_ques) from y_answer a, y_user u where a.user_id = u.id and a.ques_id = q.id)
postgres doesn't support update multiple columns at the same time? quite stupid.
workaround: a: stored procedures. read into variables and update b: read into a temproary table and update.
step 1:select a.ques_id, avg(u.total_points) as avg_tp,avg(u.questions_resolved) as avg_qr ,avg(u.total_answers) as avg_ta ,avg(u.best_answers) as avg_ba ,avg(u.member_since_in_day) as avg_ms_in_day ,avg(u.best_ans_ratio) as avg_br ,avg(u.ratio_ans_ques) as avg_ar into tmp from y_answer a, y_user u where a.user_id = u.id group by a.ques_id
step 2:
initdb
create new user
createuser -s -h localhost -p port -U postgres -P username
or: first createuser
then:alter user username password 'password'
createdb -h host -p port -U username db_name
backup/restore
pg_dump -C -f fn -U username -h host -p port db_name
psql dbname < backupfile
allow external access
postgresql.conf: listen_addresses = '*'
pg_hba.conf: host db_name db_user 0.0.0.0/0(or whatever u want) md5
Start/Stop postgres in OS X: sudo su postgres & /Library/PostgreSQL/8.4/bin/pg_ctl start/stop -D data_dir
Last modification: