# A new fiddle for dba.se

SQLFiddle has been an essential tool and a great complement to dba.se for a very long time, but it has gone downhill a bit in the last year or two.

I have also wondered for some time what a 'fiddle' designed specifically with dba.se and markdown in mind might look like, and over the last few days I've put together a prototype of how I think it should be.

Please have a play with the site and let me know here if you have suggestions for improvements or design changes.

The current list of supported databases is:

• MySQL 8.0
• Oracle 11.2
• Postgres 8.4, 9.4, 9.5, 9.6, 10 and 11
• SQL Server 2012, 2014, 2016, 2017 and 2017 Linux
• SQLite 3.8 and 3.16

Here's an example (the image links to the fiddle):

I've edited some of my answers (e.g. this one) to use db<>fiddle.

As of Apr 2018 the site uses ssl by default — there are some benefits even for a site like db<>fiddle and it's free now so why not?

• +1, +10, +1000000000000000000 – ypercubeᵀᴹ Feb 16 '17 at 18:28
• ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ – MDCCL Feb 16 '17 at 21:14
• +2 also. It would be a welcome addition. As wishes are cheap: having several versions (i.e.: PostgreSQL 9.4, 9.5, 9.6...) of the same DB would be a plus. Sometimes you don't know when your problem is just version specific... and you'd better upgrate quickly. – joanolo Feb 19 '17 at 6:38
• @joanolo "As wishes are cheap" not at all, thanks for the suggestion. I've added 9.5 and 9.4, and will add others at some point! – Jack Douglas Feb 19 '17 at 18:16
• As sqlfiddle is an established site, and you seem to have already invested time and effort in to it according to the about page, couldn't SE just adopt it and make sql fiddle great again, by pushing some resources towards it's development? – Tanner Feb 21 '17 at 10:34
• @Tanner when I saw the writing on the wall a couple of years ago, I asked that question, but although help was offered, it wasn't the sort that Jake needed. – Jack Douglas Feb 21 '17 at 10:37
• @JackDouglas fair enough, just thought I'd ask... you know, reinventing the wheel n all. good luck, would be interested to see how it develops. – Tanner Feb 21 '17 at 10:54
• @Tanner thanks, feel free to post feature requests here if you have ideas. – Jack Douglas Feb 21 '17 at 11:10
• Forgive my ignorance, but what are SQLfiddle/DBFiddle? I followed the links but they didn't have any explanatory text. I also couldn't find any reference on DBA.SE about them. – BradC Feb 23 '17 at 16:37
• @BradC Try this one: dbfiddle.uk/… the point is that you can make a reproducible test case demonstrating a question or problem, and then others can tinker with it (try changing the sql in the link in this comment) – Jack Douglas Feb 23 '17 at 17:12
• @BradC also, there is a bit of documentation on the home page for each RDBMS: dbfiddle.uk/?rdbms=sqlserver_next – Jack Douglas Feb 23 '17 at 17:13
• @JackDouglas: Others who don't have the target dbms installed can tinker. That's the big deal for me. I'd usually rather see the SQL in the question, myself. There's no reason people can't include SQL in the question and in a fiddle, but that's not as common as I would like it to be. – Mike Sherrill 'Cat Recall' Feb 28 '17 at 16:49
• @Mike we can't force that (except by editing posts), but yes, all the SQL should go in the Q&A as well as the fiddle. I've tried to make this as easy as possible with the 'markdown' link next to the 'run' button — I only added this yesterday so you may not have seen it? – Jack Douglas Feb 28 '17 at 16:53
• The site has evolved. By now, in all modesty, I'd like to say it's absolutely marvellous. – Erwin Brandstetter Apr 10 '17 at 1:18
• @lad2025 thanks for spotting that bug, now fixed. Regarding db support, pg11 will be added soon, but Oracle 12c probably never as there is no XE version. 18c XE is thought to be coming up (possibly in July) and if that materializes I will add it. – Jack Douglas May 28 at 8:36

Thank you for this. I have been using rextester for this, but it doesn't work properly for Oracle.

I don't like the tiny input field to start with. I would prefer it to be presented as a much wider (and higher) text field so that it's immediately clear that you can input more then just a few characters.

It also doesn't auto-expand for subsequent statements (using Firefox on Windows 10). After pressing the + the input field stays at the initial size. You can input larger statements but you can't see them properly.

I know parsing SQL is hard, but having to click the + button for each new statement is a bit cumbersome.

Also, if anyone wants to talk me through installing SQL Server 2016 on Debian without a GUI, I'm all ears

• "It also doesn't auto-expand for subsequent statements" thanks, fixed! I've tinkered with the home page a bit which may make it a bit clearer (please tell me if you think it doesn't). I don't want to parse the SQL at all if possible — I've toyed with the option of allowing a separator or magic character to be specified as a delimiter — maybe with a 'paste script' button. – Jack Douglas Feb 18 '17 at 19:05
• I think you can install on Debian (perhaps 'testing', that's fine) — I did find half a HOWTO but it wasn't detailed enough. That link seems to be about Docker in particular which isn't what I'm after. – Jack Douglas Feb 18 '17 at 19:14
• @JackDouglas: there is an installation procedure for Ubuntu: docs.microsoft.com/en-gb/sql/linux/… – a_horse_with_no_name Feb 19 '17 at 20:00
• That's much more like it :) chat.stackexchange.com/transcript/message/35541418#35541418 – Jack Douglas Feb 19 '17 at 20:23
• "having to click the + button for each new statement is a bit cumbersome" you no longer have to do this if you are pasting in a whole script into an empty field — DBFiddle will split on ';' (or GO for SQL Server). – Jack Douglas Feb 20 '17 at 22:22
• NB: Bug in the last output value: dbfiddle.uk/… (query to answer: stackoverflow.com/a/53324597/361842) – JohnLBevan Nov 15 at 17:40

Just as a suggestion:

For me syntax coloring would be appreciated.

And some short-key to execute instead of click on RUN button.

And I don't know if it is a vNext feature, but dates fields are shown using this long format:

 Jan  1 2017 12:00:00:000AM

• Thank you, great suggestions. No idea about the dates except that it's probably British defaults? – Jack Douglas Feb 22 '17 at 21:47
• @JackDouglas It is similar to style=9 of CONVERT command. dbfiddle.uk/… – McNets Feb 22 '17 at 21:50
• "And some short-key to execute instead of click on RUN button." 'r' now has the "accesskey" attribute so you can press it with ALT+r (or whatever — it depends on your browser and platform). Unfortunately it doesn't work while editing a textarea — you need to lose the focus first. in practice that means press TAB then hit ALT+r which works OK. – Jack Douglas Feb 24 '17 at 9:49
• Ok @JackDouglas Thanks. – McNets Feb 24 '17 at 9:51
• I've used Ace for syntax highlighting — can you let me know if you like it? Not sure whether to keep the line numbering or not. – Jack Douglas Feb 24 '17 at 16:49
• @JackDouglas it's fantastic! good job. Line numbering is useful if the error message may report the line causing the error. – McNets Feb 24 '17 at 16:53
• Good point about line numbering, it's useful here: dbfiddle.uk/… – Jack Douglas Feb 24 '17 at 20:25
• Could you tell me why this is failing? The SQL couldn't be simpler - I just get 'invalid character' for some reason? I thought it might have been a copy and paste artefact, but not so - I typed it in manually. Puzzling! – Vérace Nov 5 '17 at 11:46
• @Vérace try it on this way: dbfiddle.uk/… – McNets Nov 6 '17 at 7:44
• Is that not a bug? I mean, to have to individually put in each record? – Vérace Nov 6 '17 at 11:45
• @Vérace I think so (for Oracle), but you should ask to Jack Douglas – McNets Nov 6 '17 at 11:46
• @Vérace It's difficult to make Oracle work the way some other DBs work in this scenario, because the Oracle driver doesn't do the work for me, meaning I would have to parse the SQL (something I am very keen to avoid doing). There are workarounds though, such as this: dbfiddle.uk/… – Jack Douglas Apr 18 at 13:41
• also you can click on the 'split' link to spilt a field on the default delimiter, which is a shortcut to this quickly: dbfiddle.uk/… – Jack Douglas Apr 18 at 13:42
• Thanks for that - I'll take another tour - I've been using a rival site with a name not too different from yours :-) I'll keep you posted! – Vérace Apr 18 at 14:36

Excellent work, but I don't care much for the logo. I'm not an artist, but here's a stab at something reminiscent of what you have. If you don't like it, that's fine, but please find something else.

• Thanks very much Leigh — I'd been thinking about changing the logo to <> (as in "db<>fiddle"), but maybe this instead. – Jack Douglas Feb 28 '17 at 16:44
• Maybe I don't get it. What are you trying to say with db<>fiddle? "Database not equal Fiddle" or something like "Don't fiddle with the database"? Or is it just a symbol to separate db from fiddle? – Leigh Riffel Feb 28 '17 at 16:49
• "is it just a symbol to separate db from fiddle" yes, it's just a database-related symbol to separate db from fiddle. Also a (subtle?) joke about programmers v DBAs. – Jack Douglas Feb 28 '17 at 16:56
• Then maybe you just want a stylized <>. I have one, but the image upload tool doesn't seem to accept it right now. – Leigh Riffel Feb 28 '17 at 21:11
• That's probably due to the Amazon S3 outage: theregister.co.uk/2017/03/01/aws_s3_outage – Jack Douglas Mar 1 '17 at 7:51
• @JackDouglas Indeed, works now. – Leigh Riffel Mar 1 '17 at 13:46
• thanks for that too :) – Jack Douglas Mar 1 '17 at 13:55

A few comments after superficial testing:

### 0.

Thank you for the site. It's generally great!

### 1.

Are you ok with links posted on other SO sites or anywhere in the web? Example on SO, where I just updated an existing answer with it:

Not sure how you would stop people from doing that, but you can stop me.

### 2.

I have used the generic text "SQL Fiddle" to link to (mostly) sqlfiddle.com in the past. Do you think it's appropriate to use the same text for links to your site or do you prefer "dbfiddle" like you have it in the markup. Maybe more useful for the general public, to see where the link is going right away?

### 3.

There might be an option to remove an additional SQL window (except for the first one). Currently we can only add more, but there is no way back. The Plus + might flip to -. Also there might be a clear per SQL window (additionally), not just for the whole page.

### 4.

I like the spartan, no-nonsense design.

• Result fields might have a tiny bit more spacing. That's what I see in Firefox 51.0.1:

### 5.

I like that we can have any number of queries with results.

• It might be useful to have an option to (re-)execute only one of them. i.e. a button like run this below each SQL window in addition to the general run at the end. Might require more webdesign smarts to update only the selected section (I am no expert with this.).

While the next SQL window can depend on results from the previous one, that does not have to be the case (like in my example above).

Alternatively (additionally), run so far could be useful.

### 6.

I like that we have Postgres versions 9.4, 9.5 and 9.6 at our disposal. SQL Fiddle didn't keep up.

### 7.

I like the markdown feature, which produces this (example from link above):

-- empty string equals any string of spaces when cast to char(n)
SELECT ''::char(5) = ''::char(5)     AS eq1
,''::char(5) = '  '::char(5)   AS eq2
,''::char(5) = '    '::char(5) AS eq3;

/*
|eq1|eq2|eq3|
|:--|:--|:--|
|t  |t  |t  |
*/


eq1 | eq2 | eq3
----+-----+----
t   | t   | t


Less noise, more spacing. It's the default format in psql, with "quoted code" markdown. (Meta has no syntax highlighting, effects are better viewed on the main site.)

### 8.

On-site disclosure / statements on site owner, intended use, license, update policy, expected lifetime, public visibility, privacy?

I know, it's still beta. Just mentioning it.

• Thanks for the encouragement Erwin — If you find dbfiddle useful then I must be getting something right. ① anywhere in the web is fine :) ② I'd prefer "dbfiddle" — though you could use the generic "fiddle". "SQL Fiddle" is (It think) Jake's trademark so should probably only be used for his site. ④ you mean the output tables or something else? A screengrab might help with this. ⑤ iiuc that won't be possible — the whole script is run as a unit and in general a SELECT is going to depend on the INSERT before it. There is a keyboard shortcut for the "run" button though. … – Jack Douglas Mar 6 '17 at 17:32
• ⑦ I like your alternative format, and the markdown could be adjusted to look a bit more like that, but while there is still some hope that we will eventually get markdown tables on dba.se, I think it would be good to use valid markdown. ⑧ yes to all of that, and also all the source code is going up on GitHub at some point. Top of my todo list is getting some more backends added (SQL Server 2014, 2012, MySQL whatever), but the rest isn't too far down… – Jack Douglas Mar 6 '17 at 17:36
• @JackDouglas: I updated ④ & ⑤, and added the missing ③. – Erwin Brandstetter Mar 6 '17 at 18:15
• I wondered about ③ :) definitely a good suggestion, I've been lazy so far because there is a secret way to get rid of fields: just leave them blank. Then they are removed when you press "run" — hardly intuitive though. ④ I've increased the left and right padding by 1px, and made the table borders grey instead of black, have I gone far enough with the padding do you think? ⑤ OK I get what you mean — but at the moment I'm unsure if that would be worth the extra complexity. – Jack Douglas Mar 6 '17 at 18:56
• ④ works great for me now. Generally: all just ideas. The site is already a win. Most importantly, it works, and pretty quickly, too, so far. – Erwin Brandstetter Mar 6 '17 at 22:43
• ③ I've added 'clear' and 'remove' links to each field, does that seem like a good solution? – Jack Douglas Mar 13 '17 at 17:49
• Awesome. A flipped button [-] might be ambiguous as to which field is removed. Your implementation avoids the problem. – Erwin Brandstetter Mar 13 '17 at 18:04
• ⑦ I've tweaked the markdown output to add spaces left and right — as per the Markdown Extra spec: michelf.ca/projects/php-markdown/extra/#table. By default it now only adds leading and trailing pipes for 1-column tables. I think the extra spacing is much nicer, but I'm not sure whether removing the pipes is an improvement, can you let me know your opinion? dbfiddle.uk/… – Jack Douglas Mar 14 '17 at 14:44
• @JackDouglas: I like both (more spacing, less unnecessary pipes) better than before. But I like the psql style still better, yet. – Erwin Brandstetter Mar 16 '17 at 2:19
• I'm glad you think that's an improvement — it now exactly matches the SO documentation table markdown so I think I'd better stick with it. Several people have asked about the comments — the purpose is purely so the tables don't get syntax highlighting. I could get a similar effect with <!-- language: none --> and <!-- language: lang-sql --> before/after the result but not without splitting the code block. What do you think is best? – Jack Douglas Mar 16 '17 at 8:17
• tangentially related to ⑧, the PHP source for the postgres backends is now up on GitHub: github.com/jackdouglas/dbfiddle/blob/master/backend/… – Jack Douglas Mar 31 '17 at 17:50

The only thing I need is the Text To DDL function from sqlfiddle:

Of course I can create the DDL in sqlfiddle and copy to dbfiddle. But would be nice having that function included.

I tried the Msft SQL Server vNext option by copying this SQL Statements to create tables and data into the first field and a working solution into the second field. You can see the result in my dbafiddle.uk.

It it unclear to me what i would have to do next, to see a result? Some error message or some indication that the tables are created would be helpfull.

### Update after comment that it runs now

The html structure is currently this sequence of html tags <input><textarea /><textarea />. It seems that the first textarea is used for the user to input data and the second is used to output a result for instance a ✓.

• It would be helpful to see the result next to each first textarea as either a statement (tables created) or a preview of the table strcuture (see example below). In my dbfiddle above only that the second group of (<input><texta /><texta />) had an output

• I noticed that the output of my first dbfiddle was a little off (to many white space). So I forked the above dbfiddle to confirm that the cause was the RoomName [nchar](120) inside CREATE TABLE [dbo].[Rooms]... This made me aware that a fork button and a field to comment what was changed in the fork could be useful. Linking the forked fiddles could be useful as well. Another option would be to stripe (remove whitespace) of the output columns (although this might be undesired in some cases).

Example: Add a table definition output for each create table-statement next to the matching textarea field like this.

| column name | type        |
|-------------+-------------|
| id          | PRIMARY KEY |
| start_time  | TIMESTAMP   |
| name        | VARCHAR     |
|-------------+-------------|

• I don't know why, but if you separate the INSERT statements from the CREATE TABLE statements things look more healthy: dbfiddle.uk/… – Jack Douglas Feb 20 '17 at 20:59
• It seems execute(@PivotBase) is still ignored. Was my feedback usefull? – threeFourOneSixOneThree Feb 21 '17 at 9:53
• Yes, useful feedback, thanks! what should execute(@PivotBase) return? My SQL Server knowledge is somewhat limited :) – Jack Douglas Feb 21 '17 at 10:28
• @PivotBase contains an sql statement and execute is used to executes this sql statement. – threeFourOneSixOneThree Feb 21 '17 at 15:43
• I needed to widen the permissions — it runs now, thanks – Jack Douglas Feb 21 '17 at 16:05
• I updated my answer to suggest a preview for each table created next to the matching textarea field. – threeFourOneSixOneThree Feb 23 '17 at 4:09
• "…either a statement (tables created) or…" in this case I prefer the former — we do need to show the 'table created' (or 'x rows inserted' or whatever) notice. That's next on my list after SQL Server 2016 – Jack Douglas Feb 23 '17 at 8:10
• re forking, every press of the 'run' button creates a fork, but as you say, there is no way of linking forks to the previous fiddle. I'm not sure that's something I'd be looking to add as it probably belongs in the write-up (blog or post on dba.se or whatever) – Jack Douglas Feb 23 '17 at 8:10

Thanks for making this tool! I tried to break the site. Hopefully some of this is useful feedback.

1.

Any chance of getting Oracle 12c? 11g is pretty old at this point although I think Oracle recently extended support for it.

2.

It looks like SQL Server 2014 is pretty close to RTM. If possible can you patch it, at least to SP2?

Testing on SQL Server 2016:

3.

Can we get a bit more info in the erorr messages? Here's what I get in SSMS for the following query:

SELECT 1 / 0


Msg 8134, Level 16, State 1, Line 1

Divide by zero error encountered.

Here is what I get on the site:

Line 1: Divide by zero error encountered.

The following queries all return the "22P02ERROR: invalid input syntax for type json DETAIL: The input string ended unexpectedly. CONTEXT: JSON data, line 1:" error message:

5.

Any query that selects a VARBINARY value such as this one:

SELECT CAST('A' AS VARBINARY(1));


6.

This code takes a really long time to execute before failing (I would expect a timeout but if it generates too much data before doing so that's fine):

DECLARE @i INT = 0;
WHILE @i < 100
SELECT REPLICATE('Z', 8000);


7.

use tempdb;


8.

use msdb;


9.

use master;


10.

DECLARE @str VARCHAR(MAX) = REPLICATE('Z', 8001);

SELECT
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str;


11.

Is there an easy way to make this one display more than just seconds? It is precise to more than just seconds (as opposed to GETDATE()):

SELECT SYSDATETIME();


Oracle - DBMS_OUTPUT.PUT_LINE does not print anything and SET SERVEROUTPUT ON is not working.

It would be really useful to test PL/SQL programs. Is there any other way to see this output, other than creating a function and seeing output from a select?

• I've been meaning to add this for a while, thanks for prompting me. How about like this: dbfiddle.uk/… – Jack Douglas May 26 at 12:42
• @JackDouglas : Wow! You fixed it so quick.. Thanks a lot. That greatly helps. – Kaushik Nayak May 26 at 12:49
• glad to help — please consider following the oracle tags on dba.se I'm sure you could answer some of the questions here! – Jack Douglas May 26 at 12:59

Small Screens Hide Vital Controls (eg "Run")

The images above are from a Samsung S5 using Chrome, not the largest phone screen, but also not the smallest. In landscape mode one can "just" access the Run button, but this is not possible in portrait orientation at all. While I accept that writing SQL on a phone is tedious in the extreme (well I find it is) many people do read Q/A information on such screens.

"Split" is not an intuitive feature:

It took me a few attempts to understand what "Split" does (divides a block of DDL/DML/SQL into individual parts on screen). This is unique to your user interface as far I can tell and certainly not a normal part of SQL. It also appears to be essential to your UI.

I didn't find this initiative at all. Personally it seems of equal importance - and a prerequisite - to "Run" so why isn't it just embedded into the run logic? (and/or placed to the left of run in the top menu?)

Finding the URL

It took me a little while to realize that I needed to use Markdown to locate a URL which would allow me to return to the fiddle.

Removing the 9 unwanted "boxes" to produce "My Fiddle"

If I open https://dbfiddle.uk by default you display multiple "splits" which are aimed at helping "me-the-dumb-user" to understand how it works. Perhaps I'm wrong, but if I want a markdown output that is exclusively related to my own fiddle, I first need to remove these "help splits". That's 9 or so remove clicks to reach a "clean start".

While help is useful for the first-time user, it's tedious for an experienced user. Why not just provide a Help link in the toolbar which displays "help"? The default screen would in that case be almost blank and ready for a new fiddle to be created with no need to hit remove multiple times.

Off-topic content

While faith may be an important topic to some, does it really warrant promotion from a technical help site?

• Just wanted to say thank you for providing an a place for sql related fiddles. Perhaps you might also consider DB2 amongst your db list? (it works on Debian it seems: en.wikipedia.org/wiki/IBM_DB2_Express-C) – used_by_already Jun 13 '17 at 6:52
• Mmmm just found that in the top menu I can "Clear" all the unwanted "splits" with a single click which is good. I would still prefer to work the other way, have the default screen "Clear" and add "Help" if requested. – used_by_already Jun 13 '17 at 7:02

Not sure if it is correct place to put bug report. But let's give a try:

When I run(SQL Server 2012-2017) I can't see any result set.

SELECT i, COUNT(*) cnt
FROM tab
WHERE status = 1
GROUP BY ALL i;  -- ALL is deprecated feature


Output:

Warning: Null value is eliminated by an aggregate or other SET operation.

Temporary workaround:

-- workaround materializng to table
SELECT i, COUNT(*) cnt
INTO tab2
FROM tab
WHERE status = 1
GROUP BY ALL i;

SELECT * FROM tab2;


DBFiddle Demo

• Thanks for spotting this — I think it is now fixed. Please let me know if you find any cases where results with informational messages don't look right. – Jack Douglas Aug 27 at 14:34
• @JackDouglas Thanks for quick response. I love that site. Is there opportunity to ugrade SQLite up to 3.25.0 when it will be GA? Many new things were added. – lad2025 Aug 27 at 14:37
• I know it's not what you asked for, but I have added 3.16. My current plan is to add whatever versions come packaged with each release of Debian (so I get security updates easily), so as long as 3.25 is released by Jan 2019 it'll appear with Buster's release later in 2019. – Jack Douglas Aug 27 at 19:13
• @JackDouglas Thanks for adding upgraded version plus info. For all available free SQL playgrounds(db-fiddle.com/rextester.com/sqlfiddle.com) your version is truly the best. I am huge fan of showing actual working code rather than query/description only. Many times I had to prove that SQL can do things that others thought were virtually unreachable. Keep up the good work! – lad2025 Aug 27 at 19:44
• Thanks very much for the kind words, and the encouragement to keep working on improving and updating the site. Please do feel free to post your suggestions here, and you may like to know I'm currently working on a side-by-side compare feature. You currently need to run the code against both databases individually then edit the url manually to get the result but there will eventually be a UI to do this :) – Jack Douglas Aug 28 at 10:10
• I already like this side-by-side feature and looking forward for release :) – lad2025 Aug 28 at 14:36
• @JackDouglas BTW Could you provide some statistics? Something like DB version - number of scripts created for last year. I am really curious which RDBMS is most popular. – lad2025 Aug 28 at 14:45
• You can probably get the info you are curious about from the 'secret' stats tables on the Postgres 9.6 database: dbfiddle.uk/… – Jack Douglas Aug 28 at 15:01
• @JackDouglas SEDE(Stack Exchange Data Explorer) for DBFiddle.uk :) Nice – lad2025 Aug 28 at 15:01

I created a three-batch script on SQL Server 2017 (Windows):

### Batch 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

CREATE TABLE dbo.T (n integer NULL);


### Batch 2

CREATE TRIGGER trg ON dbo.T
AFTER INSERT AS
BEGIN
SELECT * INTO #i FROM Inserted
CREATE INDEX i ON #i (n);
END;


### Batch 3

SET IMPLICIT_TRANSACTIONS ON;

INSERT dbo.T (n) VALUES (1);

SET IMPLICIT_TRANSACTIONS OFF;


This produces no output at all when run is clicked (I expected an error about snapshot isolation not being enabled). There is no indication that db<>fiddle has tried to execute the script at all.

Please enable snapshot isolation for db<>fiddle:

ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;

• Thanks Paul. I think the bug might be a recent regression — and I think it is now fixed: dbfiddle.uk/… – Jack Douglas Aug 30 at 21:18
• I've enabled snaphot isolation on 2017 linux only because running the same script still gives a (different) error there: dbfiddle.uk/…. If you tell me that new error is expected, I'll enable it on the others too tomorrow. – Jack Douglas Aug 30 at 21:26
• That error is expected. It was the point of the demo actually. – Paul White Aug 30 at 21:30
• @JackDouglas The only remaining issue is that the error message is quite long and there doesn't seem to be a way to see all of it. – Paul White Aug 31 at 6:33
• Thanks, I've applied ALLOW_SNAPSHOT_ISOLATION to all the other versions and fixed the error message truncation :) – Jack Douglas Aug 31 at 13:16

Is there a chance we get Oracle 18c version?

Oracle Database 18c XE now available!

• I've already downloaded it and hope to have it installed in the next few days :) – Jack Douglas Oct 22 at 15:39
• it's looks like it's working, so please give it a whirl: dbfiddle.uk/… – Jack Douglas Oct 23 at 14:51
• @JackDouglas Wow, it is marvelous! Thank you so much. Now I could provide more demos on SO :) – lad2025 Oct 23 at 14:53
• @JackDouglas Polymorphic Table Function demo This is magnificent 👍 – lad2025 Oct 23 at 15:26

Here is an advanced attempt, trying to create and use a function:

http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=0c843d449e3fb7b22975c57b3594a79b

42883ERROR:  function f_tmp_groups() does not exist
LINE 1: SELECT * FROM  f_tmp_groups();
^


Not sure why this happens. A simple SQL function works:

http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=af6b4ee3972343aaeeed4a197b5407e7

And a basic set-returning plpgsql function works, too:

http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=28aae0139d521a01c26e748234fe178a

After separating CREATE TABLE and INSERT statements, the first example works:

http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=a64f32b548c342189762e746c16a0eaa

I am confused at this point.

Aside: format of error msg ...

• The first field had two statements in it, this works: dbfiddle.uk/… — obviously the error messages are a big fail at the moment! – Jack Douglas Mar 7 '17 at 16:40
• @JackDouglas:That's what I found, too (last link). So it's strictly 1 statement per SQL window? – Erwin Brandstetter Mar 7 '17 at 16:44
• sorry — I obviously didn't read right to the end. Yes, it's currently 1 statement per field — and I thought it had to be that (without me parsing the SQL). Oracle is definitely one statement per field, and SQL Server allow multiple (it's one batch per field), I assumed pg would be the same as Oracle, but… – Jack Douglas Mar 7 '17 at 16:48
• …looking at the api docs I may have misunderstood that and might be able to change it so you can have multiple statements/results per field — I'll look into it! – Jack Douglas Mar 7 '17 at 16:49
• 1 statement per field is fine with me. Just didn't know, yet. (Avoids complications.) Not sure how you implemented the execution. You probably only get the results of the last statement. – Erwin Brandstetter Mar 7 '17 at 16:53
• Thanks for reporting this. I've no way of enforcing 1 statement per field so I've changed the backend to handle multiple statements — it's a bit quirky though — only the first result set is returned (and I can't change that), so I expect that users will typically stick to one statement per field, but the important thing is that it now behaves as sane as it can and gives more sensible messages. Your original link now works just fine. – Jack Douglas Mar 7 '17 at 19:27

Awesome work!

Looks like it would be handy for some of the answers I've been posting to https://codegolf.stackexchange.com/, and what would be super helpful would be to have a byte counter somewhere underneath each code box, since most of the contests are scored by size.

I'll put this in as an "answer" and maybe consolidate my "bugreports" here.

I was answering this question, so I used your tool to construct the fiddle. It's a question about SQLite.

Here is the fiddle that answered the first part of the question - an UPDATE.

To answer part two, I tried this. As you can see, it fails. I'm quite persistent, so I tried db-fiddle (your arch-rival? :-) ) here and it works nicely.

Any thoughts?

• I think you need 'BEGIN;' (or 'BEGIN TRANSACTION;') not 'BEGIN' for SQLite (tutorialspoint.com/sqlite/sqlite_transactions.htm) this seems to work: dbfiddle.uk/… – Jack Douglas May 10 at 15:24
• and I notice you are actually using 'BEGIN TRANSACTION;' on (the other) db-fiddle – Jack Douglas May 10 at 15:25
• Fraid not :-) Your transaction fails silently - I did try both options! At the bottom, SELECT * FROM master should be sell, sell, not buy, buy and there's no sign of SELECT * FROM scheduled. Also, the db-fiddle works with both BEGIN and BEGIN TRANSACTION. – Vérace May 10 at 16:03
• ah, OK, I will look into that! – Jack Douglas May 10 at 16:15
• The SQLlite driver doesn't allow multiple statements in each block, even if they are a single transaction, if you split them out does this do what you'd expect dbfiddle.uk/…? I think this fiddle shows a transaction working as expected: dbfiddle.uk/… – Jack Douglas May 14 at 13:11

No CREATE TYPE privilege under Oracle. It would have been greatly helpful if included, especially to test/ demonstrate queries and PL/SQL code containing collections of records and TABLE functions.

• "It would have been greatly helpful if included, especially to test/ demonstrate queries and PL/SQL code containing collections of records and TABLE functions" very true, fixed! dbfiddle.uk/… – Jack Douglas May 16 at 18:42

Bug: If you [remove] all sections, then you get just the separator line, and no way to get an empty section. Clicking [+] just gives more separators.

Oracle collection and COLLECT is not showing any result.

Sample scenario:

CREATE TABLE tab(NR NVARCHAR2(100), GRD NVARCHAR2(100), DT DATE);

INSERT INTO tab(NR, GRD, DT)
SELECT '00000000000000000001' AS NR,N'06' AS GRD,DATE '2013-01-01' AS DT
FROM dual UNION ALL
SELECT '00000000000000000001',N'06',DATE '2013-01-01'  FROM dual UNION ALL
SELECT '00000000000000000001',N'21',DATE '2013-01-01' FROM dual UNION ALL
SELECT '00000000000000000002',N'06',DATE '2013-01-01' FROM dual UNION ALL
SELECT '00000000000000000002',N'21',DATE '2013-01-01' FROM dual UNION ALL
SELECT '00000000000000000004',N'01',DATE '2013-03-31' FROM dual;

CREATE TYPE tbl_array AS TABLE OF NVARCHAR2(4000);


And query:

select nr, CAST(collect(grd) AS tbl_array) grds
from tab
group by nr;


Should return:

┌──────────────────────┬────────────────────────────────┬
│          NR          │              GRDS              │
├──────────────────────┼────────────────────────────────┤
│ 00000000000000000001 │ HR.TBL_ARRAY('06', '21', '06') │
│ 00000000000000000002 │ HR.TBL_ARRAY('06', '21')       │
│ 00000000000000000004 │ HR.TBL_ARRAY('01')             │
└──────────────────────┴────────────────────────────────┴


But there is no result at all.

DBFiddle Demo

Note: Similiar scenario with PostgreSQL is working fine.

DBFiddle Demo - PostgreSQL

• I've spent a while trawling through the PHP OCI docs and I can't see how to get the text representation of the array type (or indeed any representation). The Oracle backend is processing correctly as you can see here: dbfiddle.uk/…, but oci_fetch_all returns an empty result of type "VARCHAR2Array". – Jack Douglas Sep 1 at 21:15
• @JackDouglas Hmm, that is sad. Could you at least return something like NR - GRDS and second row 00000000000000000001 - (user defined type) and so on. I mean that I would rather get 3 rows with some indicator on GRDS than 0 rows. – lad2025 Sep 2 at 10:28
• It doesn't look like I can get anything back from the PECL OCI driver at all — even when multiple rows should be returned, if a single row has a collection, the result set I get from the driver is empty. I may have better luck with a different driver but options are limited and that would likely break more than it fixes. dbfiddle doesn't parse the sql you enter, but you can work around the issue with dump if you just want to see something: dbfiddle.uk/… – Jack Douglas Sep 2 at 13:04
• @JackDouglas Yes, dump will do the work – lad2025 Sep 2 at 13:05

This is just question/minor bug? When do we get rows count?

For example DBFiddle Demo - PostgreSQL and DBFiddle Demo - SQL Server

The only difference is that I commented WHERE clause.

• row counts kick in at >5 rows, and extra rows hidden behind an ellipsis for >10 rows (you have to click the ellipsis twice for >100 and 3 times for >1000 etc): dbfiddle.uk/… – Jack Douglas Sep 2 at 13:09
• @JackDouglas Thanks for clarification – lad2025 Sep 2 at 13:09

When I try to run:

SELECT COUNT(*)
FROM dual
GROUP BY ()


It works on Oracle 11g but fails on Oracle 18c:

• Thanks, and apologies for the inconvenience — the back-end instance had crashed with an ORA-00822, but the real problem is that I neglected to add the Oracle 18 backend to our monitoring system so I wasn't notified about the crash. That's now rectified, and I may later add more memory to the instance if the crash re-occurs. – Jack Douglas Nov 22 at 23:14
• @JackDouglas Could you check if Oracle18c and MySQL 8.0 are up? I am getting Run failed messages for any query? – lad2025 Dec 2 at 14:38
• both needed restarting, should be OK now… – Jack Douglas Dec 3 at 0:00
• @JackDouglas Looks ok. Thank you once again. – lad2025 Dec 3 at 16:03

There are problems in the display of space / empty string / NULL value:

 VALUES
(1, 'foo')
, (2, '')
, (3, NULL)
, (4, '   ')
, (5, '|   |');

/*
column1 | column2
------: | :------
1 | foo
2 |
3 |
4 |
5 | |   |
*/

VALUES
('foo')
, ('')
, (NULL)
, ('   ')
, ('|   |');

/*
| column1 |
| :------ |
| foo     |
|         |
|         |
|         |
| |   |   |
*/


dbfiddle here

In the online display:

• Note the collapsed row 2 for the empty string in the second SQL field - in the fiddle, not here.

In the markup:

• Some way to distinguish rows 2, 3 and 4 would be useful.

I sharpened the demo to include single quotes and <tag>):

VALUES
('<tag>')
, (NULL)
, ('')      -- empty string
, ('''''')  -- actual single quotes
, ('   ');  -- spaces

/*
| column1 |
| :------ |
| <tag>   |
|         |
|         |
| ''      |
|         |
*/


dbfiddle here

Maybe rendering with a <pre> block would be an improvement? Would also take care of the comments you mentioned.

| column1 |
| :------ |
| <tag>   |
| null    |
| ''      |
| ''      |
|         |


You'd have to escape <. I used &lt; above ...

Included your italic null, added italic single quotes for empty string. May be hard to distinguish from literal single quotes. But still an improvement, because the distinction between empty string and space is more important because it's a more common problem.

All spaces remain to be solved ... Some corner cases are hard to solve. Most clients have a hard time to get it right.

The only change I am convinced is good is the italic null. The rest is just ideas ...

See this related meta-post:

• I can put an italic *null* in row 3 for the markdown too, but I can't think of a good way of distinguishing rows 2 and 4, do you have a suggestion? – Jack Douglas Mar 16 '17 at 8:24
• @JackDouglas: Italic null sounds like a good idea. I added some more above. – Erwin Brandstetter Mar 16 '17 at 12:44
• I found this was much harder than I anticipated due to the way SE handles code blocks and syntax highlighting. The only solutions I found that retained syntax highlighting also separated the SQL and result table into two block visually which I think isn't good. In the end, all I've done is add a plain 'null' for nulls in the markdown (no way of disambiguating the text "null"), and added a &nbsp; where there is an empty string in the tabular output to prevent rows collapsing: dbfiddle.uk/… – Jack Douglas Mar 16 '17 at 17:00
• @JackDouglas: I expected this to be harder than it looks ... Separating code and results does not seem so bad to me. I often do that on purpose in my answers. – Erwin Brandstetter Mar 16 '17 at 17:11
• What do you think of this output? i.stack.imgur.com/GJ0Xu.png – Jack Douglas Mar 16 '17 at 17:22
• I mean just from the POV of the blockquote to separate the statement/result pairs – Jack Douglas Mar 16 '17 at 17:23
• I think that's good. – Erwin Brandstetter Mar 16 '17 at 22:16
• OK, I've implemented that on a trial basis, we'll see if others agree it is an improvement. I think it is, and I'm delighted that the syntax highlighting works, there is no need for the /* */ commenting, and we can have italic null in the markdown output, all at once. Thanks a lot for the input Erwin. – Jack Douglas Mar 17 '17 at 9:54

First, thank you for the site, it's a very useful tool.

Recently MySQL 8.0 introduced JSON_TABLE, however, it's currently not possible to use it in dbfiddle.uk.

See dbfiddle.

• Thanks wchiquito, after a bit of digging I found there is a bug fixed in 8.0.13, which is not yet released. See on bugs.mysql.com (last comment in particular) and the 8.0.13 changelog — If you ping me here when 8.0.13 is released I'll update the back-end! – Jack Douglas Sep 5 at 13:44
• I've upgraded to 8.0.13 and it's all working now :) – Jack Douglas Oct 23 at 15:22
• @JackDouglas: Everything working as expected. Thanks for the quick update. – wchiquito Oct 25 at 22:02
• @JackDouglas: It seems that MySQL 8.0 has some problem, it does not execute sentences. Thanks. – wchiquito Nov 9 at 11:50
• apologies it was down for a while — but now working again. – Jack Douglas Nov 19 at 18:12
• @JackDouglas: Excellent, thanks. – wchiquito Nov 24 at 9:16

I really love the db<>fiddle. But sadly I am never able to edit my fiddles with my smartphone (Android, Chrome). There is a very strange behavior with text typing. It sometime overrides the original text when trying to insert something or puts the new text on wrong positions.

The cursor behavior is very strange as well or better: it is hard to handle (finding the right position, move something, ...)

It would be great to use your fiddle on mobile devices as well. Maybe you could check this because at the moment it is really not usable for me.

• I haven't currently got an Android phone to test on, so I've only tested it on iPhone. We'd certainly like to get it working properly on Android, so I'll see if I can get hold of something to test it on. – Jack Douglas Sep 28 at 16:06
• Do you know a good Android emulator for OSX? – Jack Douglas Sep 28 at 16:11
• No I am sorry. I am out of Android development for a few days. Hoping someone can help you. – S-Man Sep 28 at 20:35

I got a problem with MySQL when I wanted to execute more statements in one field.

Please have a look at this working example (Postgres): https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=a0fc52d4e45e4d925b428fabcdee70f5

Now I changed the DBMS into MySQL and tried the same: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a0fc52d4e45e4d925b428fabcdee70f5

That wasn't working. But if I split the statement into one field each, it is working: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b6166ff71140b5957794f1f6c9fb9780

Why does the multi execution work with Postgres but not with MySQL?

• db<>fiddle's design philosophy is against parsing the SQL you enter at all (the one exception being the 'split batch' function, but that is really peripheral to the main working of the site). In other words, this behaviour is inherited from the database itself, or the drivers used to access the DB. There is either something about MySQL or something about the MySQL driver for PHP causing this. It may well be related to this: stackoverflow.com/a/13982705/6854914 but it looks like there are pros and cons. – Jack Douglas Sep 29 at 11:14

I really like to know: When I execute multiple statements in one field (e.g. https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=a0fc52d4e45e4d925b428fabcdee70f5), are they treated as one transaction or are they committed separately?

Bug for MySQL-8.0

SELECT
json_tb.item_id,
json_tb.model_number,
json_tb.quantity
FROM JSON_TABLE
(
'[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
, "$[*]" COLUMNS ( item_id int PATH "$.item_id",
model_number varchar(100) PATH "$.model_number", quantity int PATH "$.quantity"
)
) json_tb


Returns:

SELECT command denied to user 'fiddle_SMYHCBMIQRDQGWHKBNDS'@'localhost' for table 'json_table'


json_table is a function more than a table (ref: JSON_TABLE function manual)

• This was reported earlier and we were waiting for MySQL to release a fix, but thanks for bringing it up again as it prompted me to check back and discover the release was ready. It should all be working now. – Jack Douglas Oct 23 at 15:24

On SO in the PostgreSQL section there are often PostGIS questions. At the moment I don't know any fiddle engine which supports this framework. So as a suggestion: Wouldn't it be great to install the PostGIS frame along with your PostgreSQL instances?

• I've not done this before but it turns out it's reasonably straightforward as the same PG repo for postgres also contains PostGIS. Please try it out on pg11: dbfiddle.uk/… – Jack Douglas Oct 31 at 10:55
• Great, works like charm :) Will you activate it on previous versions too? – S-Man Oct 31 at 12:05

I have to say it once again: db<>fiddle is simply the best.

I have a small request. Could you add traceflag 460 to startup parameters after you patch SQL Server with the newest cumulative update?

Current CU of SQL Server 2017 is CU9.

db<>fiddle demo

SELECT SERVERPROPERTY('ProductUpdateLevel'),@@version;
-- CU9


I would like to be able to use:

Improvement: Optional replacement for "String or binary data would be truncated" message with extended information in SQL Server 2017

Which was previously intended to be SQL Server 2019 feature Truncation error message improved to include table and column names, and truncated value (CTP 2.1)

• We've applied CU12 and added traceflag 460; here is a demo fiddle – James Douglas Dec 3 at 22:21
• @JamesDouglas Once again, huge kudos for all your efforts and help. – lad2025 Dec 4 at 20:19

SQLFormat has an API (and code) to clean up syntax to make it readable.

Would it be possible to show multiple outputs in one SQL field? At the moment if you have two SELECT statements only one will be shown in the results. https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=c17a17e3a448a08adc851b21d0bea5d2

For my Postgres answers I like to do these steps: CREATE TABLE, INSERT data and SELECT * FROM new_table to show how the origin table and its content look like. For multiple tables I have to use two section only for the SELECT part.

It's nothing critical, I know. But it would be nice candy I think!

• I'm afraid I don't think that's an easy thing to do with the postgres PHP driver we are using. It works fine with SQL Server: dbfiddle.uk/…. You can split the batch automatically with one of the icons inside the hamburger on the right but I'm not sure that is what you want. – Jack Douglas Oct 31 at 14:43