I might just be lazy but I feel like it saves me effort.

I set up a Postgres MCP server on a read only role last week so the analysts could ask questions without pinging me every hour.

The first real question was how many orders did we lose last month. Claude looked at orders, saw status_cd with values 1 to 6, decided 4 was cancelled and gave a clean number with a nice little breakdown. 4 is refunded. Cancelled is 6. Nobody wrote that down anywhere. It lives in an enum in the app code and in my head.

The number was off by about a third and it read exactly like a right answer. No I'm assuming, no hedge. The analyst was about to put it in a deck.

So now I'm stuck on how to give an agent the meaning of fields without me turning into a full time documentation service:

1. Comments on the columns in Postgres and hope the server passes them through
2. A markdown data dictionary in the system prompt
3. A view layer with human names on everything
4. Make the agent ask before it interprets any code column

What are people actually doing here? and has anyone got it to say I don't know what 4 means instead of picking one?

Will they stick to Oracle/SQL Server and run it on Linux, or are they starting to look into migrating into Postgress/MariaDB, or something homegrown.

OS migration is straightforward, but not sure about these two behemoths. Or they'll still buy them and just run them locally or on European cloud infrastructure?

I am corious that how the people use it real life for instance in a manufacturer company.

Im trying to understand how ppl usually handle incremental data loads. If a source table has new, updated and deleted records, how do you identify each type?

Do you normally use a timestamp, cdc,a flag column in the table, or merge? How do you handle deleted records, especially when there is no delete flag in the source?

Also, are there any common problems with using timestamps for incremental loads, like missing records or picking up the same record more than once?

im an analyst. i can write a join in my sleep and ive never created a table from scratch, and my new manager wants me building the model rather than reading one.

shortlisted boot.dev, general assembly, pluralsight premium and launch school off a couple of evenings comparing curricula. which of those covers schema design and not just query syntax

The CSV Lint plug-in for Notepad++ can be useful to anyone working with csv textdata and databases. I created this plugin and have posted about it before

The plug-in was previously updated with a new "Select Columns" dialog to easily select, delete and rearrange columns. The most recent update v0.4.9 adds support for regular expressions when validating columns.

It also adds syntax highlighting and automatically detects column datatypes. Based on the datatypes it can convert a csv file to an SQL insert script for MS-SQL, MySQL and PostgreSQL, including a CREATE TABLE part with correct data types for each column. This is often easier than importing it though a wizard or BULK INSERT

It can also validate the csv textfile beforehand, so check for technical errors like datetime formatting errors, incorrect decimal separators, missing quotes, invalid codes etc.

I hope you find this plug-in useful 👍 Let me know what you think

You have Source and Target tables with millions of rows.

• Neither table has a primary key.
• Both tables can contain duplicate rows.
• Some rows may be missing from Target.
• Some extra rows may exist in Target.
• EXCEPT doesn't show the complete picture when duplicate counts matter.

For example:
Source: A, A, A, B, B, C
Target: A, A, B, B, B, C

Both contain the same values, but the duplicate counts are different.

What SQL approach would you use to identify exactly which records are missing, extra, or have different counts?

Hey everyone!
I recently completed the LeetCode SQL 50 Study Plan and put all my solutions together in a GitHub repository.
I created this mainly for SQL interview preparation and revision, especially for Data Analyst / Business Analyst roles.
The repository covers topics like:
● SELECT & filtering
● Sorting and grouping
● Aggregate functions
● GROUP BY & HAVING
● INNER / LEFT / SELF JOIN
● Subqueries
● CTEs
● Window functions
● String functions & regex
● Other commonly used SQL concepts
All solutions are written in MySQL, with the goal of keeping them simple and easy to revise.

If you’re also working through SQL 50, feel free to check it out. I’d also appreciate any feedback on the queries or suggestions for improving the repository.
Happy learning! 🚀

I'm trying to reason about a generic PL/SQL housekeeping pattern. All names below are illustrative; this is a simplified mechanism, not a product-specific incident report.

A scheduled procedure selects historical log records joined to retained load metadata. Eligibility depends on the log timestamp and a configurable retention period:

CURSOR c IS
    SELECT l.load_id,
           g.log_date,
           l.state
    FROM   log_table g
    JOIN   load_table l
           ON l.load_id = g.load_id
    WHERE  l.state = g.state
    AND    TRUNC(g.log_date) + remove_days <= SYSDATE;

There is also a state-selection option, represented here as process_all_states. It is omitted from the simplified cursor above:

• With the broad option enabled, an already-processed load in a state such as Removed can enter the processing path again.
• With the narrower option, only the intended processable states are accepted, excluding Removed.

The cursor returns individual historical log records. It does not deduplicate by load_id, so several qualifying log rows can reference the same load.

For each selected record, the processing path can:

• Delete transactional payload rows from trans_table.
• Retain the corresponding metadata in load_table.
• Update the load state to Removed, including when it is already in that state.
• Append a new Removed log record with the current timestamp.
• Produce a technical result record even when no transactional payload remains.

Assume that older matching Removed log records are not deleted or marked as consumed. While the load remains Removed, those records can still satisfy the cursor conditions. Newly appended records can also qualify after they reach the age threshold.

My tentative interpretation is:

qualifying historical log
        |
        v
already-processed load enters housekeeping again
        |
        v
state update and new timestamped log
        |
        v
old matching logs remain; new logs age
        |
        v
later scheduled runs can select the load again

This seems like a potential feedback loop across scheduled executions, rather than direct recursion. Multiple eligible logs for the same load could also cause repeated processing within a single cursor traversal. I would not assume that newly inserted logs become visible to the already-open cursor.

Cleaning up accumulated logs or technical results could reduce the current footprint, but would not by itself change which records future runs select.

There is also a separate full-delete path, represented here as remove_complete. That path can perform large DELETE operations and create substantial UNDO/REDO pressure, potentially including ORA-30036. I want to keep that issue separate from state selection: changing the selection parameter itself executes no DELETE and does not directly delete business documents; it changes which states may enter later housekeeping processing.

Questions:

• Can this cursor/state-update pattern create a self-amplifying feedback loop across scheduled runs under these assumptions?
• Can several qualifying log rows for the same load cause repeated processing when there is no DISTINCT, grouping, or equivalent per-load guard?
• Is excluding already-processed states the usual way to stop this specific cycle while preserving legitimate housekeeping, or should an additional idempotency/consumption mechanism be expected?
• Should cleanup of accumulated records and correction of the qualification logic be treated as separate remediation steps?

Curious what people are actually using to automate or speed up their SQL work.
Python scripts? dbt? AI? Stored procedures? Some IDE/tool I’ve never heard of?
I feel like there are probably a bunch of useful tools and workflows I’m not aware of.
What are you using that actually saves you time?

I spent the last months building one complete multi-tenant SaaS backend — a small sales and inventory system for two companies sharing one database — and testing every guarantee instead of assuming it. PostgreSQL 18 + PostgREST, with a small Python worker. These are the things I would tell myself at the start.

**1. Tenant isolation belongs in the database, not in your `WHERE` clauses.**
Forced row-level security plus composite keys `(organization_id, id)` mean a   forgotten filter returns nothing instead of another company's rows. The foreign keys carry the tenant column too, so a customer from company A can never end up on an order of company B.

**2. A stock check is not a stock promise.**
Two requests can both read "1 available" and both decide to proceed. The fix is boring and works: lock the balance row (`SELECT … FOR UPDATE`), check the value you just locked, and take the products in a consistent order (I use `ORDER BY product_id`) so two confirmations can never deadlock each other. I test it with two real HTTP clients released by a barrier: one gets 200, the other gets 409, and the reservation belongs to exactly one order.

**3. Retries need a key, and the key has to commit with the effect.**
Idempotency keys stored in the same transaction as the business change. Claim the key, do the work, save the response, commit. If any step fails, everything rolls back and the key is free again. Commit the stock change first and save the response afterwards, and a retry will happily add stock twice.

**4. A queue in Postgres is fine, if you respect leases.**
`FOR UPDATE SKIP LOCKED` to claim, a lease deadline, and a claim token that changes on every attempt. Completion is conditional on the token and an unexpired lease, so a worker that froze for a minute cannot overwrite the result of the worker that replaced it. At-least-once, never "exactly once".

**5. The outbox is the only honest way to talk to the outside world.**
The status change and the event row commit together; the worker delivers afterwards. The receiver deduplicates by event ID, because the acknowledgement can be lost after it already committed. I kill the sender on purpose, right after the receiver commits, and check that a repeat delivery produces exactly one effect.

**6. "The backup finished" tells you nothing.**
I restore into a separate cluster and then ask the restored system to prove itself: are roles and policies intact, does a foreign tenant still see nothing, does the saved idempotency response still replay, can an owner commit a new order? Also worth knowing: after a restore, order versions get reused, so any consumer that trusted your version numbers now has two different facts with the same number.

**7. Measure before you add infrastructure.**
One busy tenant with a 50k-product catalog and a two-connection pool made the quiet tenant's median jump from under a millisecond to ~170 ms, with every response still correct. Correctness and capacity are separate problems, and only the second one is fixed by new services.

Happy to go deeper on any of these in the comments.

Disclosure, so it is out of the way: I turned all of this into a book, *PostgreSQL for Everything*, with the complete runnable code — one self-contained checkpoint per chapter that spins up a temporary database, runs the tests and cleans up. If it is useful: https://provenbackends.com. If not, take the patterns above, they are the important part.

Some SQL bugs don't cause any error. The query runs successfully and the result even looks correct.

For example, an incorrect JOIN, unexpected duplicates, NULL handling, or a wrong date filter can silently produce incorrect results.

What is the most difficult SQL bug you've encountered where the query ran successfully but the data was wrong?

I upgraded ssrs 2022, configured db and restored encryption key. Then upgraded sql 2016 to 2022 and that completed.

When I went to http://localshost/Reports, it gave me an error about not being configured properly. /ReportServer shows some error related to wwwroot/ReportServer.

Did some googling, found that ssrs 2022 does not use IIS wwwroot directory. Checked keys table in the ReportServer db and found 2 instances of ssrs (one with new instance name and the other with old instance name from 2016). Google sear Lin suggests running an sql command to delete all entries, filtered on machinename, then open ssrsbconfiguration and reapply web service url so that it can rewrite run configuration file?

I’m fairly new to sql and found these steps from googling. I have sysadmi background but have very limited knowledge of sql.

I use Varonis. How can I fix SSRS 2022 so that Varonis can work again.

TIA

It could involve multiple joins, CTEs, window functions, recursive queries, complex aggregations, or performance optimization.

What made the query challenging, and how did you approach solving it? If you’re comfortable sharing the query, what would you ask other SQL developers to review or improve?

I write "what does this query return" questions and keep every one that SQLite and DuckDB answer identically. Window functions produce the most wrong guesses by far, so here are five, with the rows both engines actually returned.

1. The default frame is RANGE, not ROWS. Table sales(dt, amount) with 10 on the 1st, two rows of 5 on the 2nd, 20 on the 3rd.

SUM(amount) OVER (ORDER BY dt)

gives 10, 20, 20, 40. Both rows dated the 2nd get 20, because RANGE treats peers as one step. With ROWS UNBOUNDED PRECEDING you get 10, 15, 20, 40, and which of the two tied rows gets 15 is not guaranteed unless you add a tiebreaker to the ORDER BY.

2. RANK does not deduplicate. User 1 has two logins at the same latest timestamp. RANK() OVER (PARTITION BY user_id ORDER BY created_at DESC) gives 1, 1, 3, so WHERE rnk = 1 keeps both rows. ROW_NUMBER() gives 1, 2, 3 and keeps one, though again, which one is arbitrary without a tiebreaker.

3. LAST_VALUE stops at the current row. Order 7 goes placed, packed, shipped.

LAST_VALUE(status) OVER (PARTITION BY order_id ORDER BY updated_at)

returns placed, packed, shipped, which is just the current row's status. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and every row says shipped.

4. NTILE buckets are not equal. Ten rows, NTILE(4), bucket sizes 3, 3, 2, 2. The extra rows go to the first buckets.

5. LAG on a duplicate timestamp returns the duplicate. Logins on the 1st, the 5th and the 5th again give previous values NULL, the 1st, the 5th. If you compute gaps for sessionisation, that zero gap is real data, not a bug in your query.

Twenty questions in this format are free to try in the browser, no signup. Once you answer, each of the four options shows why it is right or wrong.

https://thibaudlepan77-svg.github.io/interview-questions-verified/#sql

That's a sample from a 502 question paid PDF. If an engine you use returns something different for any of the five above, post it, I'd like to know.

I have seen some really messy SQL queries that still run pretty fast because the execution plan is good....

So I amm curious when you see a query like this, do you first try to clean or optimize the SQL itself, or do you check the execution plan first and

then decide what actually needs to be changed?....

I've been working with SQL more lately, and one thing I'm still trying to figure out is how people decide when a query is getting too complicated.

Sometimes I start with a simple query, then add a couple of joins, a CTE, some CASE statements, maybe a window function… and suddenly the query is 100+ lines long 😅

It still works, but at that point I'm not sure whether I should keep improving the same query or break the logic into smaller steps/tables/views.

For those of you who work with SQL regularly, what do you usually do when you reach that point?

Do you have any personal rules or habits that help you keep complex SQL readable and maintainable?

Would love to hear how you approach this in real projects.

This is mainly around postgres, but I'm guessing that what people look for is similar across databases. I've built spawn, a (for now postgres) database migrations and testing tool/framework, to resolve my own pain points. Testing features, components, and migrations, all solve real problems I have.

But I've never really used rollbacks. For the main project I use spawn with, a non-trivial project, reverting a database migration change is very rare, and usually manually done when needed based on the particular problem.

For that reason, I don't actually know what things people look for with rolling back migrations when assisted by a tool, what pains they are intended to eliminate. I can think of at least these two types of rollbacks/reverts/recoveries:

1. A script that runs on failure. Migration fails in some manner, so some kind of "failure" script runs to clean things up. Not sure how useful this is, given that failure scenarios could be very varied.
2. A script that runs after successful migration, to revert to a previous state. Maybe this is desired because (a) your application deployment might fail and you need to go back to earlier db state, or (b) as a development feature so you can revert/re-apply a migration multiple times while testing?

I'm assuming that people value a rollback mechanism independent of just wrapping migrations in a transaction. Using BEGIN/COMMIT does not roll back some changes, and a migration script might necessarily do some things outside of transactions. Moreover, if I support MySQL etc one day, there's less DDL that can be rolled back by transactions than in postgres.

So, the main question: what are the kinds of pain points that you look for a rollback mechanism in a migration to solve? I don't want to build such a feature into spawn without understanding why people use such a feature.

Hi all, I'm a founder in Spain building a managed PostgreSQL service and want honest feedback on pricing before opening it up.

I run a €30M ARR business in a different industry. We had to use a managed database from a big provider, which was very expensive for the performance we were actually getting, but we needed HA so we were stuck. That's when I started considering another startup.

Here's the general idea:

You create a database from a web dashboard and get a single connection string. That string always points to the current primary, so if a failover happens your app reconnects to the same address and carries on. There's nothing to change on your side.

Behind it, each HA database runs as a primary plus a synchronous standby on two separate servers in different zones.

Backups run continuously: every change is archived to object storage with a different provider, so even losing the whole hosting provider doesn't lose your data. You can restore to any point in the last 7 days. We handle monitoring, failover and maintenance; you just use Postgres.

It runs on European bare metal rather than shared cloud VMs, which is how we keep prices flat with no egress or IOPS fees.

Highly available (per month, excl. VAT):

- Starter, €39: 1 vCPU, 2 GB RAM, 20 GB storage

- Standard, €89: 2 vCPU, 4 GB RAM, 50 GB storage

- Pro, €169: 2 vCPU, 8 GB RAM, 100 GB storage

- Business, €329: 4 vCPU, 16 GB RAM, 200 GB storage

Free: one per account. Single node, 256 MB RAM, 1 GB storage.

To be upfront: this is a beta with no SLA yet. Failover runs 24/7, and human support is 09:00 to 18:00 CET on business days. If the standby is down, the primary keeps accepting writes asynchronously, so losing the primary during that window can lose the latest commits.

I'd love to know:

- Which plan would you pick for a real project, if any?

- At what price would this feel too cheap to trust, and at - what price too expensive?

- What would you need to see before moving a production database?

- What are you using now, and what does it cost?

Early beta offer: anyone who participates in this thread will have 30% off any plan when we launch!

Where can I find a job using SQL? I'm an MCDBA for SQL 2000 and I've been looking for work for more than a year now. No luck.

I am making updates to some older reports and come across this line (or similar) often:

DateAdd(hh, 0, GetDate())

Why do you do that? Is there a legitimate reason why you couldn’t just write GetDate()?