SQL

In a vector search, you often want some documents to count more than others. An official page should come before an old PDF, an up-to-date page before a stale archive. The easy way seems to be putting that weight straight into the sort. SELECT id, weight * (1 – (embedding <=> :q)) AS score FROM … Read more

On PostgreSQL, a column whose name contains an uppercase letter must be wrapped in double quotes to be read or modified. Syntax and best practice to avoid this common pitfall.

It is possible, for example in the context of a competition, that we are looking to find out what are the 5 best scores preceding the one I just made. To do it in SQL, although a little tricky, it is not too complicated. The 5 before me SELECT * FROM scores WHERE score > … Read more

When it is necessary to have a predefined order in a query, it is possible to change the order of the items by using a CASE in the ORDER BY. In this example id 12 must appear before id 2. SELECT * FROM channel ORDER BY ( CASE WHEN id = 1 THEN 1 WHEN … Read more