A plain ALTER TABLE ... RENAME COLUMN is instant, but it breaks any running old version of the app during a rolling deploy. The usual workaround is to never have a moment where only one name exists. Here’s the sequence, with Postgres commands. Test it on a scratch database first.

1. Expand: add the new column

ALTER TABLE orders ADD COLUMN customer_ref text;

A nullable column with no default is a metadata-only change, so it’s fast.

2. Deploy the app writing both columns, still reading the old one.
The app sets customer_id_old and customer_ref on every insert and update. (A trigger can do this instead, but then the logic lives in the database. Decide which you prefer.)

3. Backfill in batches

UPDATE orders
SET customer_ref = customer_id_old
WHERE id BETWEEN 1 AND 10000
  AND customer_ref IS DISTINCT FROM customer_id_old;

Repeat for the next id range. Small batches avoid long locks and big bursts of WAL and replication lag. Run VACUUM as needed afterwards, since each update leaves dead rows.

4. Verify

SELECT count(*)
FROM orders
WHERE customer_ref IS DISTINCT FROM customer_id_old;

IS DISTINCT FROM treats NULLs properly, so you expect 0.

5. Deploy the app reading the new column (still writing both).

6. Deploy the app that no longer writes the old column.

7. Contract: drop the old column in a separate, later migration

ALTER TABLE orders DROP COLUMN customer_id_old;

This is also quick, but it’s irreversible without a backup, so check first that nothing reads it: views, functions, reports, other services, replicas used for analytics.

If the new column needs a constraint or index:

• Use CREATE INDEX CONCURRENTLY so you don’t block writes.
• For NOT NULL, adding a CHECK (customer_ref IS NOT NULL) NOT VALID and then VALIDATE CONSTRAINT avoids a long lock. Check the docs for your version, since behavior has changed across releases.

When I’d skip all this: a table with no other readers, where a short maintenance window is acceptable. Then a plain rename is simpler.

What do you do differently, for example triggers versus dual writes in the app?

Guys can you suggest me a repo in GitHub or any idea about sql projects that will add weight in my resume?? I need your help. I'm a final year BTech student looking forward to building a career in data analytics.

Hi r/SQL, I'm David, one of the two developers of Oxynote. My friend Simon and I also maintain ttlcache, a Go caching library with about 1,300 GitHub stars, so this isn't our first open-source project.

I've spent 8 years writing software, 2 of them as an SRE and 3 as a team lead. Every team I've been on had the same documentation problem. The useful stuff lived in someone's head or a private Slack thread, and the wiki was full of stale docs nobody could find.

So we set out to solve this problem by building Oxynote, a self-hosted wiki for engineering teams. A chart on a page runs a SQL query against your database whenever someone opens the page, so the number and the notes explaining what it means sit together. Think "signups per day" or "failed payments by provider" on the same page as the explanation of how it's counted and what to do when it looks wrong.

It works with PostgreSQL, MySQL, and MariaDB. A wiki that queries production should never be able to write to it, so Oxynote accepts only read-only connections. A user with write access fails the connection test with "has write access", and Oxynote asks for a read-only one instead. Queries support Grafana-style macros like `$__timeFilter` and `$__timeGroup`, so one query follows whatever time range the reader picks. On pages you mark as reviewable, changes to a chart's query go through a draft and a diff that a teammate approves, like a pull request.

The name comes from oxygen + note: docs that breathe. Live data gives people a reason to open the wiki every day, and docs people open every day stay up to date.

It's Apache-2.0 and runs as a single Docker image: https://github.com/oxynote/oxynote

We started it about a year ago, and we made it public about 3 months ago. If you have any questions, feedback, or suggestions, feel free to reach out. I'm happy to answer any questions.

I see this one trip people up all the time, including in interviews, so here's a quick write-up.

Say you want customers who have never referred anyone:

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (SELECT referred_by FROM customers);

Looks right. Returns **zero rows**.

**Why:** most customers weren't referred, so `referred_by` is NULL for them. `x NOT IN (1, 2, NULL)` expands to `x <> 1 AND x <> 2 AND x <> NULL`. Comparing anything with NULL gives "unknown", not true, so the whole condition is never true and every row is filtered out.

**Fix 1: NOT EXISTS** (handles NULLs safely):

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM customers r
WHERE r.referred_by = c.customer_id
);

**Fix 2:** keep NOT IN but filter the NULLs out: `... WHERE referred_by IS NOT NULL`.

Plain IN doesn't have this problem, because `x = 1 OR x = 2 OR x = NULL` can still be true.

In an interview, mentioning the NULL issue before you write the query is an easy way to show you've worked with real data. Happy to answer questions!

Almost every SQL DBMS I can think of accept != as the "not equal operator". However, I realized that W3Schools only shows <>.

I was under the impression that <> is "the good-ol ANSI standard one" and every implementation out there was allowing the use of !=. But somebody was correcting me and saying that it has been on-spec for a while. I don't own the ANSI (a bit pricey for a language-lawyer curiosity!). Was I wrong?

Edit: After seeing quite a concensus on these answers, I talked with John (the one correcting me before). We checked the 2023 standard and his confusion came from the use of != as an alternative not equals operator for SQL/JSON lexical elements. That means that inside SQL/JSON blocks you may use != but on regular SQL (e.g. in the plain WHERE) using != is not covered by the standard (even if it is widely accepted for modern engines). It was a "coffee discussion", not something relevant IRL, but we both wanted to know the truth behind it.

The "Six SQL patterns I use to catch transaction fraud" thread here was great, and every one of those patterns is a rule: you know what fraud looks like, you write the WHERE clause. Here's a 7th that doesn't need the pattern up front, and the case where it falls flat.

Toy dataset: 123 transactions, 40 accounts. Each account makes three ordinary payments ($20–$199, daytime, domestic). ACC-33 also makes payments of $2,840, $3,120 and $2,975 at 3am, cross-border. Hidden among the ordinary accounts is a 5-account ring (ACC-11 to ACC-15). Nothing in the table marks them.

Train an anomaly model, no labels

Isolation forest, no target column. It learns what normal rows look like and scores distance from that.

CREATE EXPERIMENT tx_exp AS
  SELECT amount, hour_of_day, cross_border FROM tx
WITH (task_type = 'anomaly_detection',
  algorithms = ['isolation_forest'],
  max_trials = 5, random_seed = 42);

DEPLOY MODEL tx_model FROM EXPERIMENT tx_exp;

Score every row in a SELECT

SELECT account_id, amount, hour_of_day, cross_border,
  ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
FROM tx
ORDER BY score DESC
LIMIT 10;

ACC-33's payments come out on top at 0.8167, 0.8167 and 0.8064. Then a straight drop to 0.5267, with the next normal rows between 0.5100 and 0.5181. Higher means more unusual; it's a ranking, not a probability. It's just an expression, so it works in WHERE, GROUP BY, views.

Features must be passed in training order. Swap two and you get a feature mismatch error instead of quietly wrong scores.

Where it falls flat: the ring

SELECT account_id, amount, hour_of_day,
  ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
FROM tx
WHERE account_id IN ('ACC-11','ACC-12','ACC-13','ACC-14','ACC-15')
ORDER BY score DESC;

15 rows, from 0.4453 to 0.5081. Same as honest customers. The model isn't wrong: each payment is ordinary. A ring isn't a property of any single row.

What gives it away is shared identifiers: ACC-11/12 share a phone, 12/13 a card, 13/14 a phone, 14/15 a card. ACC-13 has a confirmed chargeback. Walking shared devices and cards 2–4 hops from it finds the other four:

In most databases that's a recursive CTE over account–device and account–card link tables. We used a graph query in the same engine:

MATCH p = (s:Account {id: 'ACC-13'})-[*2..4]-(x:Account)
WHERE x.id <> 'ACC-13'
RETURN x.id AS linked_account, [n IN nodes(p) | n.id] AS via
ORDER BY length(p);

An honest household (ACC-21/22 share a tablet) isn't returned. Sharing a device isn't fraud; being connected to a chargeback is.

One review queue, both signals

SELECT s.account_id, ROUND(s.max_anomaly, 2) AS max_anomaly, r.linked_via
FROM (
  SELECT account_id, MAX(score) AS max_anomaly
  FROM (SELECT account_id,
    ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
    FROM tx) t
  GROUP BY account_id
) s
LEFT JOIN ring_members r ON r.account_id = s.account_id
WHERE s.max_anomaly > 0.7 OR r.account_id IS NOT NULL
ORDER BY s.max_anomaly DESC;

Five of six would never cross 0.7. The model found one fraudster; the identifier walk found five.

Takeaways

• Unsupervised scoring is a decent 7th pattern for the lone weird account you didn't write a rule for.
• It's blind to coordinated fraud where every row looks normal.
• Synthetic data built to show the gap. Real scores will be noisier; use them to rank for review, not as verdicts.

Anyone here run anomaly scoring in-database, or do you export to Python and join scores back?

Disclosure: I work on SynapCores, where ANOMALY_SCORE and the graph query come from. The rule-based patterns from the original thread still do most of the work.

Hey all. I'm a solo dev from Korea, so yes, this is self-promo (I checked with the mods first).

I've been working on a desktop SQL client called DBerio. It does MySQL/MariaDB, Postgres and SQLite (plus Redis if that's your thing), on Mac and Windows. I mostly built it because I bounce between a Windows box and a Mac and wanted one tool that works the same on both. I write code all day too, so the UI is basically laid out the way I like to work.

What it does right now:

- the usual GUI client stuff: browse schemas and tables, run queries, edit cells and add/delete rows in the grid (when the result comes from one table with a PK)

- you can mark a connection as production. running UPDATE or DELETE always asks first, and on a production connection things like DB, user and privilege changes ask again. the really destructive ones there make you type the name

- it shows you the SQL before a structure change actually runs

- queries you run all the time can be saved as a view or kept as favorite queries per database

- CSV/TSV export escapes values Excel would treat as formulas

- SSH tunneling for DBs that are locked down from outside access (password, key or encrypted key)

- the UI comes in English, Korean and Japanese

Without paying you get 3 saved connections and 2 open at once, with every feature.

What I'd actually like to hear about:

1. Does it cover your day-to-day work? If you'd have to keep another client open for something, what is it?

2. Bugs. Anything that crashes, shows the wrong thing, or just feels off.

3. Features you'd want added. What's the one thing that would make you actually switch?

Links if you want to poke at it (a test DB is totally fine):

Mac App Store: https://apps.apple.com/app/id6819625601

Microsoft Store: https://apps.microsoft.com/detail/9N53NQGJ3TNG

Thanks, I'll be around in the comments.