![]() ![]() Hash Cond: (t.student_id = st.student_id) | If I remove the date match then I get this: QUERY PLAN | Note the discrepancy between rows and actual rows. Index Cond: (st.student_id = t.student_id) | Output: st.program_id, st.organization_id, st.student_id | ![]() Output: t.id, t.student_id, t.student_snapshot_date| Hash Cond: ((t.student_id = snp.student_id) AND (t.student_snapshot_date = snp.created_at)) | I need to join the 3 tables together in a query like the following: select t.*, st.grade_id as current_grade_id, snp.grade_id as snapshot_grade_idĪnd snp.created_at = t.student_snapshot_date The real example uses partitioned tables but this example uses normal ones for simplicity. There isn't an FK back to student because we want to keep the historic data when the student is deleted. There are students and when they take a test we take a snapshot of the student to understand their demographics at the time the test was taken. Student_snapshot_date timestamp not null,įoreign key (student_id, student_snapshot_date) references student_snapshot (student_id, created_at) The query is more complex than this but I can create a minimal reproducible example from the essence of these three tables: create table student ( ![]() The database partitions most tables by a tenant id. I am using Postgres v13 hosted in GCP Cloud SQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |