= SQL {c} {wiki} = SQL example {c} {parent=SQL} We have some runnable examples with under the `sequelize/raw` directory. These examples are written in the library using raw queries. Sequelize is used minimally, just to feed raw queries in transparently to any underlying database, and get minimally parsed results out for us, which we then assert with standard . The queries themselves are all written by hand. By default the examples run on . Just like the examples from , you can set the database at runtime as: * `./index.js` or `./index.js l`: * `./index.js p`: . You must manually create a database called `tmp` and ensure that peer authentication works for it Here we list only examples which we believe are standard SQL, and should therefore work across different SQL implementations: * \a[nodejs/sequelize/raw/index.js]: basic hello world to demonstrate the setup and very simple functionality * \a[nodejs/sequelize/raw/many_to_many.js]: illustrates with . Contains: * examples: * \a[nodejs/sequelize/raw/commit_error.js]: https://stackoverflow.com/questions/27245101/why-should-we-use-rollback-in-sql-explicitly/27245234#27245234 and https://stackoverflow.com/questions/48277519/how-to-use-commit-and-rollback-in-a-postgresql-function/48277708#48277708 suggest that on , once something fails inside a transaction, all queries in the current transaction are ignored, and `COMMIT` simply does a `ROLLBACK`. Let's check. Yup, true for Postgres, but false for , SQLite just happily runs anything it can, you really need `ROLLBACK` for it. * {child} * and : * \a[nodejs/sequelize/raw/group_by_extra_column.js]: let's see if it blows up or not on different DB systems, allows it: * https://github.com/sequelize/sequelize/issues/5481#issuecomment-964387232 * https://dba.stackexchange.com/questions/141594/how-select-column-does-not-list-in-group-by-clause/141600 says that it was allowed in when there are no ambiguities due to constraints, e.g. when grouping by unique columns * https://github.com/postgres/postgres/blob/REL_13_5/src/test/regress/sql/functional_deps.sql#L27 shows that wants it to work for `UNIQUE NOT NULL`, but they just haven't implemented it as of 13.5, where it only works if you group by `PRIMARY KEY` * https://dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b also says that `UNIQUE NOT NULL` doesn't work. Dan Lenski then points to rationale mailing list thread: * \a[nodejs/sequelize/raw/group_by_max_full_row.js]: here we try to get the full row of each group at which a given column reaches the max of the group * : has `SELECT DISCINTCT ON` which works perfectly if you only want one row in case of multiple rows attaining the max. `ON` is an extension to the standard unfortunately: https://www.postgresql.org/docs/9.3/sql-select.html#SQL-DISTINCT[] Docs specify that it always respects `ORDER BY` when selecting the row. * https://stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column asks it without the multiple matches use case * https://stackoverflow.com/questions/586781/postgresql-fetch-the-rows-which-have-the-max-value-for-a-column-in-each-group/587209#587209 also present in simpler form at https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of/123481#123481 gives a very nice only solution! Incredible, very elegant. * https://dba.stackexchange.com/questions/171938/get-only-rows-with-max-group-value asks specifically the case of multiple matches to the max * : * https://stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite * https://stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/48328243#48328243 teaches us that in SQLite min and max are magic and guarantee that the matching row is returned * https://stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/72996649#72996649 uses the magic of `ROW_NUMBER` * https://stackoverflow.com/questions/17277152/sqlite-select-distinct-of-one-column-and-get-the-others/71924314#71924314 get any full row without specifying which, we teach how to specify * https://code.djangoproject.com/ticket/22696 WONTFIXed `DISTINCT ON` * https://stackoverflow.com/questions/50846722/what-is-the-difference-between-postgres-distinct-vs-distinct-on/72997494#72997494 `DISTINCT` vs `DISTINCT ON`, somewhat related question * https://stackoverflow.com/questions/5803032/group-by-to-return-entire-row asks how to take the top N with distinct after order limit. I don't know how to do it in Postgres * \a[nodejs/sequelize/raw/most_frequent.js]: illustrates a few variants of findind the , including across GROUP * https://stackoverflow.com/questions/12235595/find-most-frequent-value-in-sql-column/72979899#72979899 * \a[nodejs/sequelize/raw/group_by_max_n.js]: get the top N in each group * PostgreSQL * https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group * https://stackoverflow.com/questions/7613785/postgresql-top-n-entries-per-item-in-same-table * https://dba.stackexchange.com/questions/247275/rank-used-in-where-returns-invalid-column-but-exists-in-results-set * SQLite https://stackoverflow.com/questions/28119176/select-top-n-record-from-each-group-sqlite * MySQL https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group * order results in the same order as `IN`: * MysQL: https://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause * PostgreSQL: * https://stackoverflow.com/questions/866465/order-by-the-in-value-list * https://dba.stackexchange.com/questions/59394/order-of-returned-rows-with-in-statement * LIMIT by a running total: TODO links = SQL implementation {c} {parent=SQL} {wiki} = IBM Db2 {c} {parent=SQL implementation} {wiki} = MySQL {c} {parent=SQL implementation} {wiki} Login without password: https://askubuntu.com/questions/915585/how-to-login-mysql-shell-when-mysql-have-no-password `` sudo mysql `` works on 20.10. Create user for further logins without `sudo` https://askubuntu.com/questions/915585/how-to-login-mysql-shell-when-mysql-have-no-password/1325689#1325689[]: `` sudo mysql -e "CREATE USER $USER" `` Run command from CLI https://stackoverflow.com/questions/1602904/how-do-you-run-a-single-query-through-mysql-from-the-command-line `` mysql -e 'SHOW DATABASES' `` Create test user with password: `` sudo mysql -e 'CREATE USER user0 IDENTIFIED WITH mysql_native_password BY "a"' sudo mysql -e 'GRANT ALL PRIVILEGES ON database_name.* TO "user0"' `` and login as that user: `` mysql -u user0 -p `` Login with password given on the command line: `` mysql -u user0 -pmypassword `` The `IDENTIFIED WITH mysql_native_password` part is to overcome "Client does not support authentication protocol requested by server" when connecting from . List users: `` sudo mysql -e 'SELECT * FROM mysql.user' `` View permissions for each user on each DB: https://serverfault.com/questions/263868/how-to-know-all-the-users-that-can-access-a-database-mysql `` sudo mysql -e 'SELECT * FROM mysql.db' `` List databases: `` sudo mysql -e 'SHOW DATABASES' `` Create database: `` sudo mysql -e 'CREATE DATABASE mydb0' `` Destroy database: `` sudo mysql -e 'DROP DATABASE mydb0' `` Show tables in database: `` sudo mysql -e 'SHOW TABLES' mydb0 `` or: `` sudo mysql -e 'SHOW TABLES FROM mydb0' `` = mysqldump {c} {parent=MySQL} https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html = mysqldump to CSV {parent=mysqldump} Related: https://stackoverflow.com/questions/32026398/transform-sql-insert-script-into-csv-format = MariaDB {c} {parent=MySQL} {wiki} Dude's a legend. Sells company for a few million. Then forks the open source project next year. Love it. = PostgreSQL {c} {parent=SQL implementation} {wiki} = Postgres {c} {synonym} {title2} PostgreSQL feels good. Its feature set is insanely large! Just look at stuff like: https://stackoverflow.com/questions/1986491/sql-split-string-by-space-into-table-in-postgresql/1993058#1993058 Had a look at the source tree, and also felt good. If is the of database, Postgres is the , and (or more precisely ) is the (i.e. the one that got delayed by legal issues). Except that their were accidentally swapped. The only problem with Postgres is its name. PostgreSQL is so unpronounceable and so untypeable that you should just call it "Postgres" like everyone else. = PostgreSQL getting started {c} {parent=PostgreSQL} On Ubuntu 20.10 PostgreSQL 12.6, login with `psql` on my default username without fails with: https://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist This is the one that worked on : https://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist/38444152#38444152 `` sudo -u postgres createuser -s $(whoami) createdb $(whoami) `` Explanation: * `sudo -u postgres` uses the `postgres` user via * `-s` in `createuser -s`: make it a superuser * `createdb`: TODO why do we have to create a table with the same name as the user? Otherwise login fails. You can now run `psql` without any password. This works without password due to peer authentication, `` sudo cat /etc/postgresql/12/main/pg_hba.conf `` shows that peer authentication is available to all users apparently: `` local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer `` List users: `` psql -c '\du' `` output: `` List of roles Role name | Attributes | Member of -------------+------------------------------------------------------------+----------- ciro | Superuser, Create role, Create DB | {} owning_user | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} `` Delete user later on: `` psql -c 'DROP USER username;' `` Create a database: `` createdb testdb0 `` Help toplevel: `` help `` Get help for Postgres commands such as `\h` and so on: `` \? `` List supported SQL commands: `` \h `` Show syntax for one type of command: `` \h SELECT `` List all databases: `` psql -c '\l' `` which shows: `` Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+----------+----------+-------------+-------------+----------------------- ciro | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres testdb0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | (6 rows) `` Delete a database: `` psql -c 'DROP DATABASE "testdb0";' `` If you didn't give a database from the command line e.g.: `` psql `` you can do that afterwards with: `` \c testdb0 `` Let's create a table and test that it is working: `` psql testdb0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));' `` List tables, no special tables: `` psql testdb0 -c '\dt' `` gives: `` List of relations Schema | Name | Type | Owner --------+--------+-------+------- public | table0 | table | ciro (1 row) `` View table schema: https://stackoverflow.com/questions/109325/postgresql-describe-table `` psql testdb0 -c '\d+ table0' `` output: `` Table "public.table0" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------------+-----------+----------+---------+----------+--------------+------------- int0 | integer | | | | plain | | char0 | character(16) | | | | extended | | `` Insert some data into it and get the data out: `` psql testdb0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');" psql testdb0 -c 'SELECT * FROM table0;' `` output: `` int0 | char0 ------+------------------ 2 | two 3 | three 5 | five 7 | seven (4 rows) `` Delete the table: `` psql testdb0 -c 'DROP TABLE table0;' `` = PostgreSQL tips {c} {parent=PostgreSQL getting started} * output one column per line: https://stackoverflow.com/questions/9604723/alternate-output-format-for-psql-showing-one-column-per-line-with-column-name * PostgreSQL does not automatically index foreign keys! https://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys = Create a test user in PostgreSQL {parent=PostgreSQL} In order to create a test user with password instead of , let's create test user: `` createuser -P user0 createdb user0 `` `-P` makes it prompt for the users password. Alternatively, to create the password non-interactively https://stackoverflow.com/questions/42419559/postgres-createuser-with-password-from-terminal[]: `` psql -c "create role NewRole with login password 'secret'" `` Can't find a way using the `createuser` helper. We can then login with that password with: `` psql -U user0 -h localhost `` which asks for the password we've just set, because the `-h` option turns off peer authentication, and turns off password authentication. The password can be given non-interactively as shown at https://stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively with the `PGPASSWORD` : `` PGPASSWORD=a psql -U user0 -h localhost `` Now let's create a test database which `user0` can access with an existing superuser account: `` createdb user0db0 psql -c 'GRANT ALL PRIVILEGES ON DATABASE user0db0 TO user0' `` We can check this permission with: `` psql -c '\l' `` which now contains: `` List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- user0db0 | ciro | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/ciro + | | | | | ciro=CTc/ciro + | | | | | user0=CTc/ciro `` The permission letters are explained at: * https://www.postgresql.org/docs/13/ddl-priv.html * https://stackoverflow.com/questions/25691037/postgresql-permissions-explained/25691587 `user0` can now do the usual table operations on that table: `` PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));' PGPASSWORD=a psql -U user0 -h localhost user0db0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');" PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'SELECT * FROM table0;' `` = Peer authentication {parent=PostgreSQL} https://www.postgresql.org/docs/13/auth-peer.html Uses the name of the current user to login without a . = PostgreSQL logging {parent=PostgreSQL} https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries has a certain default level of logging by default to: `` /var/log/postgresql/postgresql-13-main.log `` but it does not log everything, only/mostly errors it seems. Setting: `` log_statement = 'all' `` under: `` /etc/postgresql/13/main/postgresql.conf `` and then restarting the server: `` sudo service restart postgresql `` just works. Realtime monitoring for long queries instead: https://stackoverflow.com/questions/8597516/app-to-monitor-postgresql-queries-in-real-time = PostgreSQL serialization failure {parent=PostgreSQL} When using and , concurrent transactions may fail with a serialization failure, and then you might need to retry them. You server code or your ORM must always account for that. A good way to explore when it happens is to use the example Related questions: * https://stackoverflow.com/questions/7705273/what-are-the-conditions-for-encountering-a-serialization-failure * https://stackoverflow.com/questions/59351109/error-could-not-serialize-access-due-to-concurrent-update * https://stackoverflow.com/questions/50797097/postgres-could-not-serialize-access-due-to-concurrent-update/51932824 = Microsoft SQL Server {c} {parent=SQL implementation} {wiki} = Oracle Database {c} {parent=SQL implementation} {wiki} = SQL Server {c} {synonym} Often known simply as SQL Server, a terrible thing that makes it impossible to find portable SQL answers on ! You just have to Google by specific unfortunately to find anything about the open source ones. = SQLite {c} {parent=SQL implementation} {wiki} The minimalism, serverlessness/lack of temporary caches/lack of permission management, Hipp's religious obsession with efficiency, the use of their own pure Fossil https://sqlite.org/whynotgit.html{ref}. Wait, scrap that last one. Pure beauty! Official mirror: https://github.com/sqlite/sqlite Create a table `` sqlite3 db.sqlite3 " CREATE TABLE 'IntegerNames' (int0 INT, char0 CHAR(16)); INSERT INTO 'IntegerNames' (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven'); " `` List tables: `` sqlite3 db.sqlite3 '.tables' `` output: `` IntegerNames `` Show schema of a table: `` sqlite3 db.sqlite3 '.schema IntegerNames' `` outputs the query that would generate that table: `` CREATE TABLE IF NOT EXISTS 'IntegerNames' (int0 INT, char0 CHAR(16)); `` Show all data in a table: `` sqlite3 db.sqlite3 'SELECT * FROM IntegerNames' `` output: `` 2|two 3|three 5|five 7|seven `` = SQLite import CSV {c} {parent=SQLite} = SQLite import CSV from stdin {c} {parent=SQLite import CSV} = SQLite benchmark {c} {parent=SQLite} {wiki} Python sequence test data generation: https://stackoverflow.com/questions/18219779/bulk-insert-huge-data-into-sqlite-using-python/76659706#76659706 = SQLite C extension {c} {parent=SQLite} {wiki} Example: \a[sqlite/ip.c], adapted from https://www.sqlite.org/loadext.html[], also mentioned explained at: https://stackoverflow.com/questions/7638238/sqlite-ip-address-storage/76520885#76520885[]. Sample usage in the test program: \a[sqlite/test.sh]. Docs: https://www.sqlite.org/loadext.html = SQLite isolation levels {c} {parent=SQLite} {wiki} https://www.sqlite.org/pragma.html#pragma_read_uncommitted mentions: ____ The default isolation level for SQLite is SERIALIZABLE ____ It does not appear possible to achieve the other two levels besides SERIALIZABLE and READ UNCOMMITED https://www.sqlite.org/isolation.html = Node.js SQLite bindings {parent=SQLite} = `sqlite3` Node.js package {parent=Node.js SQLite bindings} * https://github.com/mapbox/node-sqlite3 * https://www.npmjs.com/package/sqlite3 Includes its own copy of sqlite3, you don't use the system one, which is good to ensure compatibility. The version is shown at: https://github.com/mapbox/node-sqlite3/blob/918052b538b0effe6c4a44c74a16b2749c08a0d2/deps/common-sqlite.gypi#L3 source is tracked compressed in-tree: https://github.com/mapbox/node-sqlite3/blob/918052b538b0effe6c4a44c74a16b2749c08a0d2/deps/sqlite-autoconf-3360000.tar.gz horrendous. This explains why it takes forever to clone that repository. People who don't believe in git submodules, there's even an official Git mirror at: https://github.com/sqlite/sqlite It appears to spawn its own via its extension (since and and is not -based), which allows for parallel queries using multiple threads: https://github.com/mapbox/node-sqlite3/blob/v5.0.2/src/threading.h Hello world example: \a[nodejs/node-sqlite3/index.js]. As of 2021, this had slumped back a bit, as maintainers got tired. Unmerged pull requests started piling more, and started pulling ahead a little. * https://github.com/mapbox/node-sqlite3/issues/1381 `FATAL ERROR: Error::ThrowAsJavaScriptException napi_throw` with vs https://github.com/JoshuaWise/better-sqlite3/issues/237 = `better-sqlite3` Node.js package {parent=Node.js SQLite bindings} As claimed on their README, their operation truly appears to be 10x faster than the node-sqlite package!! It is insane!! How can that other package still exist at all? The only big problem was the lack of , but people are looking into that by adding it to : * https://github.com/JoshuaWise/better-sqlite3/issues/23 * https://github.com/sequelize/sequelize/issues/11400 = SQL function {c} {parent=SQL} = SQL set returning function {c} {parent=SQL function} : https://www.postgresql.org/docs/current/functions-srf.html = SQL `genenerate_series` {c} {parent=SQL set returning function} = SQL aggregate function {c} {parent=SQL function} = SQL `COUNT` function {c} {parent=SQL aggregate function} Have a look at some interesting examples under \a[nodejs/sequelize/raw/many_to_many.js]. = SQL keyword {parent=SQL} = SQL CASCADE {c} {parent=SQL keyword} {wiki} https://stackoverflow.com/questions/59297/when-why-to-use-cascading-in-sql-server = DELETE {c} {disambiguate=SQL} {parent=SQL keyword} = Delete all duplicate rows in SQL {parent=DELETE (SQL)} * with `rowid`: https://stackoverflow.com/questions/8190541/deleting-duplicate-rows-from-sqlite-database * is `ctid`: https://stackoverflow.com/questions/14626481/rowid-equivalent-in-postgres-9-2 * has crazy "CTEs" change backing table extension: https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server = GROUP BY {disambiguate=SQL} {c} {parent=SQL keyword} {wiki=Group_by_(SQL)} = HAVING {c} {disambiguate=SQL} {parent=GROUP BY (SQL)} = INSERT {disambiguate=SQL} {c} {parent=SQL keyword} {wiki=Insert_(SQL)} = Upsert {c} {parent=INSERT (SQL)} = ON CONFLICT UPDATE {c} {synonym} {title2} `UPSERT` is extremely handy, and reduces the number of find, check on server, update loops. But `RETURNING` is a fundamental part of that (to get the updated/existing) ID. Can't believe SQL hasn't standardized it yet as of 2022. But both and support it with similar syntax thankfully. \a[nodejs/sequelize/raw/upsert.js] * https://www.postgresql.org/docs/14/sql-insert.html#SQL-ON-CONFLICT * https://www.sqlite.org/lang_returning.html = Upsert with `NOT NULL` column {parent=UPSERT} Attempt at \a[nodejs/sequelize/raw/upsert.js]: * https://stackoverflow.com/questions/48816629/on-conflict-do-nothing-in-postgres-with-a-not-null-constraint OP unable to provide a minimal exampe, but it is likely the problem * https://dba.stackexchange.com/questions/292428/postgresql-upsert-issue-with-not-null-columns Related on more complex constraints: * https://dba.stackexchange.com/questions/175182/on-conflict-on-two-columns-where-one-can-be-null * https://dba.stackexchange.com/questions/292428/postgresql-upsert-issue-with-not-null-columns = JOIN {disambiguate=SQL} {c} {parent=SQL keyword} {wiki=Join_(SQL)} = SQL prefix column names with the table they came from {c} {parent=JOIN (SQL)} It is mind blowing that this is not possible... the only way to avoid ambiguity in JOINs with column name conflicts is to give aliases to each column... * https://stackoverflow.com/questions/329931/sql-select-join-is-it-possible-to-prefix-all-columns-as-prefix * https://stackoverflow.com/questions/13153344/in-a-join-how-to-prefix-all-column-names-with-the-table-it-came-from = INNER JOIN {c} {parent=JOIN (SQL)} = OUTER JOIN {c} {parent=JOIN (SQL)} = SELECT {disambiguate=SQL} {c} {parent=SQL keyword} {wiki=Select_(SQL)} = SELECT FOR UPDATE {c} {parent=SELECT (SQL)} An example where `SELECT FOR UPDATE` is a good solution to an use case can be seen at: {file}. `SELECT FOR UPDATE` vs/together with the is commented at: https://stackoverflow.com/questions/10935850/when-to-use-select-for-update[]. = SQL stored procedure {c} {parent=SQL keyword} {tag=Stored procedure} = SQL FUNCTION keyword {c} {parent=SQL stored procedure} = SQL PROCEDURE {c} {parent=SQL stored procedure} = SQL TRIGGER {c} {parent=SQL keyword} {tag=Database trigger} {wiki} 's implementation of . This feature is really cool, as it allows you to keep caches up to date! In particular, everything that happens in a trigger happens as if it were in a transaction. This way, you can do less explicit transactions when you use triggers. It is a bit like the advantages of . : * : * https://stackoverflow.com/questions/24870416/counter-cache-column-in-postgresql * * https://stackoverflow.com/questions/35255304/sqlite-create-trigger-for-insert-or-update : * : = ISO SQL TRIGGER syntax {c} {parent=SQL keyword} {tag=Database trigger} {tag=SQL standard} TODO what is the standard compliant syntax? requires you to define a : https://stackoverflow.com/questions/28149494/is-it-possible-to-create-trigger-without-execute-procedure-in-postgresql Their syntax may be standard compliant, not sure about the `EXECUTE` part. Their docs: https://www.postgresql.org/docs/current/sql-createtrigger.html does not support at all, so maybe that's why they can't be standard compliant here: https://stackoverflow.com/questions/3335162/creating-stored-procedure-in-sqlite 11.38 covers "Trigger definition". The starts with the `CREATE TRIGGER` and ends in: `` ::= `` This is defined at 13.5 "SQL procedure statement", but that is humongous and I'm not sure what it is at all. = nodejs/sequelize/raw/trigger_count.js {file} {parent=ISO SQL TRIGGER syntax} In this example we cache track the number of posts per user on a cache column. = UNION {c} {disambiguate=SQL} {parent=SQL keyword} {{wiki=Set_operations_(SQL)#UNION_operator}} Basic example tested on 3.40.1, : `` sqlite3 :memory: 'select 1 union select 2' `` output: `` 1 2 `` Two columns two rows: `` sqlite3 :memory: < is an option, but it fails if you have a `NOT NULL` column: Bibliography: * https://stackoverflow.com/questions/11563869/update-multiple-rows-with-different-values-in-a-single-sql-query * https://dba.stackexchange.com/questions/69269/updating-multiple-rows-with-different-values-in-one-query = UPDATE with JOIN {c} {disambiguate=SQL} {parent=UPDATE (SQL)} Dumping examples under \a[nodejs/sequelize/raw/many_to_many.js]. Not possible without subqueries in the standard syntax, a huge shame: https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server The `UPDATE` + `FROM` extension exists in a few s: * : https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql * : https://stackoverflow.com/questions/19270259/update-with-join-in-sqlite/69831549#69831549 : * : = DELETE with JOIN {c} {disambiguate=SQL} {parent=UPDATE with JOIN (SQL)} {tag=DELETE (SQL)} Demo under: \a[nodejs/sequelize/raw/many_to_many.js]. NO way in the apparently, but you'd hope that implementation status would be similar to , but not even! * : possible with `DELETE FROM USING`: https://stackoverflow.com/questions/11753904/postgresql-delete-with-inner-join * : not possible without subqueries as of 3.35 far: https://stackoverflow.com/questions/24511153/how-delete-table-inner-join-with-other-table-in-sqlite[], Does not appear to have any relevant features at: https://www.sqlite.org/lang_delete.html * : no support of course: https://stackoverflow.com/questions/40890131/sequelize-destroy-record-with-join = SQL standard {c} {parent=SQL} {wiki} A quick look at 's compliance notes: https://www.postgresql.org/docs/13/features.html shows the complete utter mess that this standard is. Multiple compliance levels that no one fully implements and optional features everywhere. = SQL standard version {c} {parent=SQL standard} = SQL:1999 {c} {parent=SQL standard version} = SQL application {c} {parent=SQL} = SQL histogram {c} {parent=SQL application} OK, there's a billion questions: * * https://stackoverflow.com/questions/485409/generating-a-histogram-from-column-values-in-a-database OP did not know the difference between count and histogram :-) But it's the number one Google result. * https://stackoverflow.com/questions/19103991/create-range-bins-from-sql-server-table-for-histograms has a minor extra group by twist, but otherwise fine * https://stackoverflow.com/questions/16268441/generate-histogram-in-sql-server * * https://stackoverflow.com/questions/67514208/how-to-optimise-creating-histogram-bins-in-sqlite perf only, benchmarking would be needed. . * https://stackoverflow.com/questions/32155449/create-a-histogram-with-a-dynamic-number-of-partitions-in-sqlite variable bin size, same number of entries per bin * https://stackoverflow.com/questions/60348109/histogram-for-time-periods-using-sqlite-regular-buckets-1h-wide time * : https://stackoverflow.com/questions/1764881/getting-data-for-histogram-plot MySQL appears to extend `ROUND` to also round by integers: `ROUND(numeric_value, -2)`, but this is not widely portable which is a shame * https://stackoverflow.com/questions/72367652/populating-empty-bins-in-a-histogram-generated-using-sql specifically asks about empty bins, which is amazing. dialect unfortunately, but answer provided works widely, and Redshift was forked from , so there's hope. Those newb server focused projects that don't use ! Let's try it on 3.40.1, . Data setup: `` sqlite3 tmp.sqlite 'create table t(x integer)' sqlite3 tmp.sqlite < + as per https://stackoverflow.com/questions/72367652/populating-empty-bins-in-a-histogram-generated-using-sql[]: `` sqlite3 tmp.sqlite < 3.40.1, . Data setup: `` sqlite3 tmp.sqlite 'create table t(x integer, y integer)' sqlite3 tmp.sqlite < + as per https://stackoverflow.com/questions/72367652/populating-empty-bins-in-a-histogram-generated-using-sql[]: `` sqlite3 tmp.sqlite < * https://stackoverflow.com/questions/67848017/simple-recursive-sql-query * https://stackoverflow.com/questions/28688264/how-to-traverse-a-hierarchical-tree-structure-structure-backwards-using-recursiv * https://stackoverflow.com/questions/51822070/how-can-postgres-represent-a-tree-of-row-ids * depth first * uspecified depth first variant * https://stackoverflow.com/questions/50098759/postgres-nested-records-in-a-recursive-query-in-depth-first-manner * https://stackoverflow.com/questions/59463176/how-to-perform-depth-first-search-in-postgresql * https://stackoverflow.com/questions/30336265/postgresql-recursive-cte-results-ordering * * https://dba.stackexchange.com/questions/63153/how-do-i-sort-the-results-of-a-recursive-query-in-an-expanded-tree-like-fashion * https://stackoverflow.com/questions/65247873/preorder-tree-traversal-using-recursive-ctes-in-sql/77276675#77276675 * https://stackoverflow.com/questions/3709292/select-rows-from-table-using-tree-order * * https://stackoverflow.com/questions/8252323/mysql-closure-table-hierarchical-database-how-to-pull-information-out-in-the-c asks how to use a specific order () with * * https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example = Closure table {parent=SQL tree traversal} {wiki} = Nested set model in SQL {parent=SQL tree traversal} {wiki} = Nested set index {title2} {synonym} How to implement in : * https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/42781302#42781302 contains the correct left/size representation and update queries, which makes it much easier to maintain the tree without having to worry about the sizes of siblings which are constant * https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/194031#194031 amazing ASCII art representations of the structure. Unfortunatly uses a wonky left/right representation, rather than the much more natural left/size representation from the other post = SQL feature {c} {parent=SQL} = SQL RECURSIVE query {c} {parent=SQL feature} Minimal example: \a[nodejs/sequelize/raw/recursive.js] More advanced examples: \a[nodejs/sequelize/raw/tree.js] docs: https://www.postgresql.org/docs/16/queries-with.html#QUERIES-WITH-RECURSIVE = SQL RECURSIVE prevent infinite recursion {c} {parent=SQL RECURSIVE query} Example under: \a[nodejs/sequelize/raw/tree.js] * https://stackoverflow.com/questions/31739150/to-find-infinite-recursive-loop-in-cte Canon. * : * https://stackoverflow.com/questions/58326847/sql-recursion-cte-infinite-loop * https://dba.stackexchange.com/questions/215426/simple-recursive-cte-stuck-in-infinite-loop * https://stackoverflow.com/questions/13718168/infinite-loop-in-cte-when-parsing-self-referencing-table * * https://stackoverflow.com/questions/26671612/prevent-and-or-detect-cycles-in-postgres?noredirect=1&lq=1 * https://stackoverflow.com/questions/39555616/detect-cycles-using-sql#:~:text=A%20cycle%20is%20detected%20when,are%20added%20to%20the%20entry. = SQL spatial index {c} {parent=SQL feature} = PostgreSQL spatial index {c} {parent=SQL spatial index} = PostgreSQL GIST {c} {parent=PostgreSQL spatial index} * https://www.postgresql.org/docs/15/gist.html * https://www.postgresql.org/docs/15/datatype-geometric.html * https://medium.com/postgres-professional/indexes-in-postgresql-5-gist-86e19781b5db the only example on the net! The highly underdocumented built-in module, that supports and a lot more. Quite horrendous as it only seems to work on geometric types and not existing columns. But why. And it uses custom operatores, where standard operators would have been just fine for points... Minimal runnable example with points: `` set -x time psql -c 'drop table if exists t' time psql -c 'create table t(p point)' time psql -c "insert into t select (point ('(' || generate_series || ',' || generate_series || ')')) from generate_series(1, 10000000)" time psql -c 'create index on t using gist(p)' time psql -c "select count(*) from t where p <@ box '(1000000,1000000),(9000000,2000000)'" `` The index creation unfortunately took 100s, so it will not scale to 1B points very well whic his a shame. Some sources about it: * https://stackoverflow.com/questions/28292198/how-to-port-simple-spatial-index-using-sqlite-r-trees-to-postgres = PostGIS {c} {parent=PostgreSQL spatial index} http://postgis.net/ The third part module, which clutters up any serches you make for the built-in one. = SQL subquery {parent=SQL feature} {title2=CTE} = Common Table Expression {parent=SQL subquery} {title2=CTE} Similar to , but with some differences: https://stackoverflow.com/questions/706972/difference-between-cte-and-subquery `` rm -f tmp.sqlite sqlite3 tmp.sqlite 'create table t(i integer)' sqlite3 tmp.sqlite 'insert into t values (1), (2)' sqlite3 tmp.sqlite 'with mycte as ( select * from t ) delete from mycte where i = 1' sqlite3 tmp.sqlite 'select * from t' `` = CTE insert values {parent=Common Table Expression} Useful for testing: https://stackoverflow.com/questions/21819183/how-to-use-ctes-with-update-delete-on-sqlite `` sqlite3 :memory: 'WITH t (i, j) AS (VALUES (1, -1), (2, -2)) SELECT * FROM t' `` = SQL transaction {c} {parent=SQL feature} = SQL transaction isolation level {c} {parent=SQL transaction} = SQL isolation level {synonym} Each transaction isolation level specifies what can or cannot happen when two queries are being run in parallel, i.e.: the of the system. Remember that queries can affects thousands of rows, and database systems like can run multiple such queries at the same time. Good summary on the page: https://www.postgresql.org/docs/14/transaction-iso.html Implementation specifics: * {child} = SQL READ UNCOMMITTED isolation level {c} {parent=SQL transaction isolation level} = SQL READ COMMITTED isolation level {c} {parent=SQL transaction isolation level} Example where this level is sufficient: {file}. = SQL REPEATABLE READ isolation level {c} {parent=SQL transaction isolation level} Vs on : https://dba.stackexchange.com/questions/284744/postgres-repeatable-read-vs-serializable {file} is an example which experimentally seems to be solved by `REAPEATABLE READ`, although we are not sure that this is truly the case and why. What is clear is that that example is not solved by the . In , this is the first isolation level which can lead to , this does not happen to in that . You then have to retry the transaction. = SQL SERIALIZABLE isolation level {c} {parent=SQL transaction isolation level} = SQL isolation level example {c} {parent=SQL transaction isolation level} = SQL parallel update example {c} {parent=SQL isolation level example} = nodejs/sequelize/raw/parallel_update_async.js {file} {parent=SQL parallel update example} \a[nodejs/sequelize/raw/parallel_update_worker_threads.js] contains a base example that can be used to test what can happen when queries are being run in parallel. But it is broken due to a bug: https://github.com/mapbox/node-sqlite3/issues/1381[]... \a[nodejs/sequelize/raw/parallel_update_async.js] is an version of it. It should be just parallel enough to allow observing the same effects. This is an example of a transaction where the if sufficient. These examples run queries of type: `` UPDATE "MyInt" SET i = i + 1 `` Sample execution: `` node --unhandled-rejections=strict ./parallel_update_async.js p 10 100 `` which does: * , see other databases options at * 10 threads * 100 increments on each thread The fear then is that of a classic failure. But as https://www.postgresql.org/docs/14/transaction-iso.html page makes very clear, including with an explicit example of type `UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;`, that the default isolation level, , already prevents any problems with this, as the update always re-reads selected rows in case they were previously modified. \Q[If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row] Since in "Read uncommitted" appears to be effectively the same as "Read committed", we won't be able to observe any failures on that database system for this example. {file} contains an example where things can actually blow up in read committed. = nodejs/sequelize/raw/parallel_select_and_update.js {file} {parent=SQL parallel update example} This example is similar to {file}, but now we are doing a separate SELECT, later followed by an update: * `SELECT FROM` to get i * update on Js code `newI = i + 1` * `UPDATE SET` the `newI` Although this specific example is useless in itself, as we could just use `UPDATE "MyInt" SET i = i + 1` as in {file}, which automatically solves any concurrency issue, this kind of code could be required for example if the update was a complex function not suitably implemented in SQL, or if the update depends on some external data source. Sample execution: `` node --unhandled-rejections=strict ./parallel_select_and_update.js p 2 10 'READ COMMITTED' `` which does: * , see other databases options at * 2 threads * 10 increments on each thread Another one: `` node --unhandled-rejections=strict ./parallel_select_and_update.js p 2 10 'READ COMMITTED' 'FOR UPDATE' `` this will run Observed behaviour under different : * `READ COMMITTED`: fails. Nothing in this case prevents: * thread 1: SELECT, obtains i = 0 * thread 2: SELECT, obtains i = 0 * thread 2: newI = 1 * thread 2: UPDATE i = 1 * thread 1: newI = 1 * thread 1: UPDATE i = 1 * `REPEATABLE READ`: works. the manual mentions that if multiple concurrent updates would happen, only the first commit succeeds, and the following ones fail and rollback and retry, therefore preventing the loss of an update. * `READ COMMITTED` + `SELECT FOR UPDATE`: works. And does not do rollbacks, which probably makes it faster. With `p 10 100`, `REPEATABLE READ` was about 4.2s and `READ COMMITTED` + `SELECT FOR UPDATE` 3.2s on . `SELECT FOR UPDATE` should be enough as mentioned at: https://www.postgresql.org/docs/13/explicit-locking.html#LOCKING-ROWS \Q[FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Section 13.4.] A non-raw version of this example can be seen at: {file}. = nodejs/sequelize/raw/parallel_select_and_update_deterministic.js {file} {parent=SQL parallel update example} This example contains a deterministic demo of when may happen. Tested on 13.5. = nodejs/sequelize/raw/parallel_create_delete_empty_tag.js {file} {parent=SQL parallel update example} In this example, posts have tags. When a post is deleted, we check to see if there are now any empty tags, and now we want to delete any empty tags that the post deletion may have created. If we are creating and deleting posts concurrently, a naive implementation might wrongly delete the tags of a newly created post. This could be due to a concurrency issue of the following types. Failure case 1: * thread 2: delete old post * thread 2: find all tags with 0 posts. Finds `tag0` from the deleted old post which is now empty. * thread 1: create new post, which we want to have tag `tag0` * thread 1: try to create a new tag `tag0`, but don't because it already exists, this is done using 's `INSERT OR IGNORE INTO` or 's `INSERT ... ON CONFLICT DO NOTHING` * thread 1: assign `tag0` to the new post by adding an entry to the join table * thread 2: delete all tags with 0 posts. It still sees from its previous search that `tag0` is empty, and deletes it, which then cascades into the join table which would result in the new post incorrectly not having the `tag0`. Failure case 2: * thread 2: delete old post * thread 2: find all tags with 0 posts * thread 1: create new post * thread 1: try to create a new tag `tag0`, but don't because it already exists * thread 2: delete all tags with 0 posts. It still sees from its previous search that `tag0` is empty, and deletes it * thread 1: assign `tag0` to the new post which leads to a foreign key failure, because the tag does not exist anymore when the assignment happens. Failure case 3: * thread 2: delete old post * thread 1: create new post, which we want to have tag `tag0` * thread 1: try to create a new tag `tag0`, and succeed because it wasn't present * thread 2: find all tags with 0 posts, finds the tag that was just created * thread 2: delete all tags with 0 posts, deleting the new tag * thread 1: assign `tag0` to the new post which leads to a foreign key failure, because the tag does not exist anymore when the assignment happens. Sample executions: * `node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'READ COMMITTED'`: , 9 tags, DELETE/CREATE the `tag0` test tag 1000 times, use `READ COMMITTED` Execution often fails, although not always. The failure is always: `` error: insert or update on table "PostTag" violates foreign key constraint "PostTag_tagId_fkey" `` because the: `` INSERT INTO "PostTag" `` tries to insert a tag that was deleted in the other thread, as it didn't have any corresponding posts, so this is the foreign key failure. TODO: we've never managed to observe the failure case in which `tag0` is deleted. Is it truly possible? And if not, by which guarantee? * `node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'READ COMMITTED' 'FOR UPDATE'`: do a `SELECT ... FOR UPDATE` before trying to `INSERT`. This is likely correct and the fastest correct method according to our quick benchmarking, about 20% faster than `REPEATABLE READ`. We are just now 100% sure it is corret becase we can't find out if the `SELECT` in the `DELETE` subquery could first select some rows, which are then locked by the tag creator, and only then locked by `DELETE` after selection. Or does it re-evaludate the `SELECT` even though it is in a subquery? * `node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'REPEATABLE READ'`: repeatable read We've never observed any failures with this level. This should likely fix the foreign key issue according to the PostgreSQL docs, since: * the `DELETE "Post"` commit cannot start to be seen only in the middle of the thread 1 transaction * and then if DELETE happened, the thread 1 transaction will detect it, ROLLBACK, and re-run. TODO how does it detect the need rollback? Is it because of the foreign key? It is very hard to be sure about this kind of thing, just can't find the information. Related: . * `node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'SERIALIZABLE'`: serializable * `node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'NONE'`: magic value, don't use any transaction. Can blow up of course, since even less restrictions than `READ COMMITTED` All executions use 2 threads. Some theoretical notes: * Failure case 3 is averted by a `READ COMMITTED` transaction, because thread 2 won't see the uncommitted tag that thread 1 created, and therefore won't be able to delete it https://stackoverflow.com/questions/10935850/when-to-use-select-for-update from