SQLite User Forum

Request for change: support item lists with trailing comma
Login
Hierarchical

Request for change: support item lists with trailing comma

(1) By doug (doug9forester) on 2020-12-15 06:56:35 [source]

I am generating SQL as part of C++ code generation for my application. I would like to not mess with trailing commas on lists of items in SQL statements. Specifically, I would like SQLITE to support the following syntax for the insert statement (see trailing comma in lists): 

insert into tbl(a,b,c,) values(1,2,3,);
In version 3.31, SQLite does not support trailing commas in lists. C++ supports them in enum lists which makes life much easier for code generation. I found that SQLite did not support trailing commas thus: 
sqlite> .version
SQLite 3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837bb4d6
zlib version 1.2.11
gcc-5.2.0
sqlite> create table x(a,b,c);
sqlite> insert into x(a,b,c) values(1,2,3);
sqlite> select * from x;
1|2|3
sqlite> insert into x(a,b,c,) values(1,2,3);
Error: near ")": syntax error
sqlite> insert into x(a,b,c) values(1,2,3,);
Error: near ")": syntax error
sqlite>

(2) By Gunter Hick (gunter_hick) on 2020-12-15 08:47:15 in reply to 1 [link] [source]

Is there an SQL Standard or any SQL Engine that supports trailing commas in lists?

(3) By anonymous on 2025-02-13 11:49:24 in reply to 2 [link] [source]

  • The SQLite's sister DuckDB,
  • GoogleSQL (Cloud Spanner, BigQuery),
  • ClickHouse,
  • Snowflake. This is what I found so far. PostgreSQL doesn't (yet) implement it, neither does MySQL, MariaDB, Oracle or Microsoft SQL Server.

(4) By ddevienne on 2025-02-14 09:24:05 in reply to 3 [link] [source]

A propos related post about that yet for PostgreSQL: https://peter.eisentraut.org/blog/2025/02/11/how-about-trailing-commas-in-sql

(5) By Aask (AAsk1902) on 2025-02-14 15:11:14 in reply to 1 [link] [source]

insert into x(a,b,c) values(1,2,3,);

That is asking for four values to be inserted into three columns.

Extending this thinking further, you could equally be asking for the insertion of values into m columns given n values where m and n are unequal (trailing comma in the enumeration of column names).

I think this would be a sloppy feature.

(6) By Chris Locke (chrisjlocke1) on 2025-02-14 16:47:09 in reply to 1 [link] [source]

This just seems a lazy way to create lists. "I can't be bothered to remove the trailing comma so your software has to support it."

That seems like saying, "I always misspell 'select' as 'sleect' so that should be a recognised keyword. And 'select *' should just be '**'."

(7) By Spindrift (spindrift) on 2025-02-14 19:06:30 in reply to 6 [link] [source]

See also:

HTTP Headers:

Referer field

(8.3) By DrkShadow (drkshadow) on 2025-02-16 07:07:06 edited from 8.2 in reply to 1 [link] [source]

Programmatically generating SQL.

That isn't necessarily bad, but...

  1. isn't it faster to use loop over sqlite3_bind_int() than encode the integer into text and then decode it from text again?
  2. I know you're not doing it with text strings that don't need to be converted, because that would be an sql injection
  3. isn't the string concatenation and associated memory allocation bad?
  4. you should probably be using some sort of join() function.

But.

  1. Code generation isn't such a bad thing for small numbers of values, and trailing commas are supported by things like javascript, python, ... (but not JSON)
  2. It's transferring last-comma checking and parsing and processing from the application code to the database code. While this is a little unfair, it amounts to doing it in one tested and vetted place as opposed to yourself, and probably re-coding it every time.
  3. Perhaps it can be allowed only for row-values, and not in just "whatever" SQL (SELECT x, y, z, FROM tbl1, tbl2, WHERE 1=1... LIMIT 10, 32, -- can you have a table named WHERE or GROUP or USING or etc? I don't think so.)

That point 3 might make it valid.

If it's allowed only for row-values, then it's not allowed for lists of column names (INSERT INTO tbl1 (a, b, c) VALUES (1, 2, 3,)). It could probably be a separate feature to do that / not do that.

// just thinking aloud.

(9.2) By Aask (AAsk1902) on 2025-02-16 08:51:55 edited from 9.1 in reply to 8.3 [link] [source]

Programmatically generating SQL.

Indeed. The SQLite CLI (dot command) itself does it. Using any application code, it is easy to generate SQL and equally easy to remove the trailing comma.

If allowed/implemented, no doubt it would be followed by requests for allowing leading superfluous comma.

However, published SQL standards (not that any RDBMS complies with it 100%) do not allow a superfluous trailing comma.

With this request this observation rings true very loudly.

can you have a table named WHERE or GROUP or USING or etc? I don't think so.)

Yes you can, but that is akin to pointing a gun to your head and hoping that your finger does not flinch.

sqlite> -- the reason for using WHERE as a table may be crystal clear but won't stay that way over time
sqlite> -- it will create chaos
sqlite> create table [where](a,b);
sqlite> insert into [where] select 100,'apple';
sqlite> insert into [where] select 200,'orange';
sqlite> select * from [where] where a=100;
a    b
---  -----
100  apple
sqlite>