Last active
June 26, 2023 09:52
-
-
Save caspartse/5779fe2a1fbf6883f842d7d6caa3b8fa to your computer and use it in GitHub Desktop.
Chat history import for Quivr(v0.0.17)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Add a new column `note` to the `chat_history` table in order to distinguish which records were manually imported. | |
| ALTER TABLE chat_history ADD COLUMN note varchar(255); | |
| -- Import historical records from the `chats` table. | |
| WITH tmp AS ( | |
| SELECT | |
| dialog_number, | |
| chat_id, | |
| MAX(content) FILTER (WHERE role = 'user') AS user_message, | |
| MAX(content) FILTER (WHERE role = 'assistant') AS assistant, | |
| creation_time | |
| FROM ( | |
| SELECT | |
| chat_id, | |
| (ROW_NUMBER() OVER () + 1) / 2 AS dialog_number, | |
| col ->> 0 AS role, | |
| col ->> 1 AS content, | |
| creation_time | |
| FROM chats, jsonb_array_elements(history) AS col | |
| WHERE history IS NOT NULL | |
| ) t1 | |
| GROUP BY dialog_number, chat_id, creation_time | |
| ORDER BY dialog_number, chat_id | |
| ) | |
| INSERT INTO chat_history (chat_id, user_message, assistant, message_time, note) | |
| SELECT | |
| chat_id, | |
| user_message, | |
| assistant, | |
| creation_time + (dialog_number * INTERVAL '1 millisecond') AS message_time, -- message_time is fake. | |
| 'import_from_chats' AS note -- flag of historical record. | |
| FROM tmp; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment