Things I wished more developers knew about databases

Re:Things I wished more developers knew about databases by rspeele on Thursday April 23, 2020 @01:56PM (#22958783)

Single-row inserts are super common in both application code and in interactive usage of SQL, so I think that it is worth it to have a syntax for them that reduces this common error. Especially when a table has many columns of the same type (like booleans). E.g.

insert into Permissions(UserId, Create, Read, Update, Delete, Share, ForceUnlock, LaunchNukes) values (12345, 1, 1, 0, 1, 0, 0, 1);

When I wrote my (now unmaintained) statically typed SQL dialect for F#, which compiles to underlying SQLite/Postgres/MSSQL, I added a single-row insert with Field=Value because it's nice to have and took no more than 30 minutes to do. It's only a tweak to the parser after all -- you just parse it to the same AST used to represent the `INSERT ... VALUES` clause and all later stages of the compilation do not need to know about it.

https://rspeele.gitbooks.io/rezoom-sql/doc/Language/InsertSt...

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by magicalhippo on Friday April 24, 2020 @09:55AM (#22967952)

The DB we use at work (SQLAnywhere) supports inserting from select, and using auto column name matching, so for a table with columns (name, en1, en2) you could have

    INSERT INTO tbl WITH AUTO NAME
    SELECT 'foo' AS name, 1 AS en1, 0 AS en2;

Not nearly as neat as the direct key=value syntax but...

[ Parent | on HN ]