👋 Hey there, I am Waylon Walker
I am a Husband, Father of two beautiful children, Senior Python Developer currently working in the Data Engineering platform space. I am a continuous learner, and sha
Not being able to use windowed values (rank, lead lag etc) to filter in the base query, but the query engine is quite happy when you wrap the query in a WITH CTE AS (...) and then filter on windowed field when you SELECT * FROM CTE.
👋 Hey there, I am Waylon Walker
I am a Husband, Father of two beautiful children, Senior Python Developer currently working in the Data Engineering platform space. I am a continuous learner, and sha
In retrospect I could have just said "date handling" and walked away from the subsequent vortex of possible discussion. At my workplace I keep a wiki page with stock clippings of date literals, date constants and date expressions for the different dialects I use. I barely read/notice them now, I just trust my decades younger self to have ensured each was right and copy-paste them into place. Younger me never worked out INTERVAL syntax, so none of them have that.
The only thing worse than cascade delete is ending up with orphaned records. If no-one specifies the foreign key relationship in the database you can end up with order lines with no order address, or orders with no customer. I agree that RI (referential integrity) can be frustrating and perhaps engines could be smarter about checking the scope of the transaction but I'd rather have the delete fail than result in corrupted data.
It's pronounced Diane. I do data architecture, operations, and backend development. In my spare time I maintain Massive.js, a data mapper for Node.js and PostgreSQL.
Migra looks very promising, at least at smaller scales: make changes locally, diff against production, tweak as needed, and ship a single migration script instead of keeping old/intermediary states around forever.
Some of the things in no particular order:
🍜 diddo sql flavors
Not being able to use windowed values (rank, lead lag etc) to filter in the base query, but the query engine is quite happy when you wrap the query in a WITH CTE AS (...) and then filter on windowed field when you SELECT * FROM CTE.
I'd agree with all of those too :)
SQLLines has a free tool that I find myself coming back to when I need to translate between flavours - sqlines.com/online
Sqlines is a good one.
Sounds like a cool tool, doesnt seem to work with my simple example. I will definitely keep it bookmarked to try when I have a real example.
postgresql
Should convert to
Oracle
I used it when migrating from SQL Server to Postgres and found it handy in that particular use-case.
Dates were a a real drag:
SQL Server
Postgres
The AWS Schema Conversion Tool is a slicker, although paid, version of the same if you are using their services.
In retrospect I could have just said "date handling" and walked away from the subsequent vortex of possible discussion. At my workplace I keep a wiki page with stock clippings of date literals, date constants and date expressions for the different dialects I use. I barely read/notice them now, I just trust my decades younger self to have ensured each was right and copy-paste them into place. Younger me never worked out INTERVAL syntax, so none of them have that.
The only thing worse than cascade delete is ending up with orphaned records. If no-one specifies the foreign key relationship in the database you can end up with order lines with no order address, or orders with no customer. I agree that RI (referential integrity) can be frustrating and perhaps engines could be smarter about checking the scope of the transaction but I'd rather have the delete fail than result in corrupted data.
Desires state can generate unexpected/unwelcome scripts, while script based approaches can quickly become painful to manage.
Migra looks very promising, at least at smaller scales: make changes locally, diff against production, tweak as needed, and ship a single migration script instead of keeping old/intermediary states around forever.
Good one.