Databases - TopAnswers

Truilus

No, Postgres only supports the standard LATERAL. Oracle allows CROSS APPLY as a non-standard alternative to LATERAL.

Andriy M replying to PeterVandivier

Oh, I didn't know Postgres supported `CROSS APPLY`. Though if I knew, I wouldn't have expected there to be a separate set of rules for when to use each.

PeterVandivier replying to Andriy M

pretty sure postgres supports both but i can never remember when `cross apply` is allowed

Andriy M replying to PeterVandivier

You may want to switch to Oracle, I hear it now supports both

PeterVandivier

swear to god i will *never* remember to use `join lateral` instead of `cross apply`

FoggyFinder replying to Truilus

there is `Meta`-type of Qs here as well: https://topanswers.xyz/databases?room=2&search=%7BMeta%7D

Truilus replying to Jack Douglas

Done: https://topanswers.xyz/meta?q=1613 but I can't add a tag. Neither hitting "Enter" when inside the "add tag" input box, nor "Tab" nor clicking on Update will persist my tag.

Jack Douglas replying to Truilus

It [looks like](https://stackoverflow.com/a/59256042/12757754) this is possible (the message in a normal `raise` is [already reported](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=29683f5071e791caee31822bb93b876f) of course). Would you mind posting a meta question tagged db<>fiddle with the feature request?

Truilus

I have a little feature request for db<>fiddle: it would be nice if it displayed messages "printed" using  raise in Postgres: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=182a6e29aee5b0a0a2eca3a17f3801ad  
  
I don't know the technology behind it, but in Java/JDBC these messages are available as "Warnings" on the Statement instance that was used to run the SQL (SQL Server's JDBC driver does the same)

Jack Douglas replying to PeterVandivier

I don't think that caused any issues, though you did prompt me to fix some MySQL issues so thanks :)

PeterVandivier

well it's delightful whatever the reasoning

Andriy M

I'm guessing the choice of scientific notation is to make it brief (1e12 vs e.g. 1,000,000,000,000), and the reason to add the clarification in the first place is to make it unambiguous [which scale](https://en.wikipedia.org/wiki/Long_and_short_scales) trillion is meant, since it's a big difference whether it's the short scale trillion, which is 1e12, or the long scale trillion, which is 1e18.

PeterVandivier

related: I find it endlessly amusing that the sqlite docs put scientific notation in parentheses after numbers in their main docs :laughing:  
  
> There are over 1 trillion (1e12) SQLite databases in active use

PeterVandivier

anyone know who runs the [@sqlite](https://twitter.com/sqlite) twitter handle? or... what's up with it being super private?

PeterVandivier

was running the code from this post w/o thinking https://www.cybertec-postgresql.com/en/postgresql-int4-vs-float4-vs-numeric/

PeterVandivier

<>https://dbfiddle.uk/?rdbms=postgres_13&fiddle=a6d25b0cb9dabf481d801cfb75232003&hide=5  
  
😰 sorry 'bout that @Jack

Jack Douglas replying to PeterVandivier

I'm sure you could tweak Vim to treat .pg.sql the same as .pgsql :)

PeterVandivier

just knocking around the idea of doing two-part-naming this time - e.g. .t.sql, .pg.sql, .my.sql - since it's only for *me*. like - no editor i know of plays nice without aggressive customisation for this use-case. so just let everything be .sql to the IDE and check for the helper prefix in my own scripting :thinking:

PeterVandivier

i used to but then got out of the habit when i was on MSSQL-only for a few years - it's starting to become a thing i think i need to bring back though

PeterVandivier

does anyone else around these parts use custom file name extensions for different sql dialects? e.g. .tsql for MS, .pgsql for postgres, .mysql for MySQL

Andriy M

Morning

Andriy M

Morning

John aka hot2use

Morning

John aka hot2use

Merry Christmas to you all. Hope you have a fantastic day.

John aka hot2use

Anonymous 1750

yes

FoggyFinder replying to Anonymous 1749

MySQL?

Anonymous 1749 replying to FoggyFinder

SQL

Anonymous 1749

sql

FoggyFinder replying to Anonymous 1749

what kind of DB do you use?

Anonymous 1749

Anybody ready to help with Databeses->Select Start and End date?

Andriy M

Sometimes it's not so much a matter of explaining things to someone but more a matter of giving them enough time to talk to themselves

Josh Darnell

Did someone from TeX hack Jack's account?

Jack Douglas

The TA logo should be a rubber duck :)

PeterVandivier

isn't it great how i did a whole little thing there? talked myself right out of an idea all by my lonesome :-P

Jack Douglas

don't mind implementing different options for outputting results though

Jack Douglas replying to PeterVandivier

yes indeed, I think that would be a bridge too far

PeterVandivier

although accessing the subsystem in that way presents certain security considerations that maybe you don't want to invite in just yet :-P

PeterVandivier

the big value add i'm thinking about right now is access to meta-commands in psql & mysql (couldn't use dbfiddle for [this SO answer about `\gexec`](https://stackoverflow.com/a/65356445/4709762) just now :,()

PeterVandivier

i'm thinking you could add something like "shell mode" where instead of using the current drivers to send/receive data, you could save each batch to a file and pipe it into sqlcmd/psql/mysql shells and pipe the results back out into a file   
  
(can't think of a sensible way to connect those utilities in such a way that you'd be able to use the existing formatter so far as i understand it)

PeterVandivier replying to Jack Douglas

@Jack i don't know how the dbfiddle backend stores cached result sets, but does it sound like an onerous amount of work to add support for using a file-as-output rather than datastream-plus-formatting-as-output?

PeterVandivier

thinking about @John's retracted feature request and wondering if there's more to it than i initially gave credit
  
@@@ answer 654

Josh Darnell

Morning.

Josh Darnell replying to Andriy M

Don't worry, it's just pretend.  Elmo is okay.

Andriy M

Morning

Andriy M replying to Josh Darnell

So it wasn't the gif? Because that's what makes it really scary for me!

Josh Darnell replying to PeterVandivier

That's more my style.

PeterVandivier replying to Josh Darnell

[same energy](https://chat.stackexchange.com/transcript/179?m=11826522#11826522)

John aka hot2use

morning

Josh Darnell

I didn't realize how scary until I googled what `\gexec` does.