What does it mean when pg_stat_activity.datname is NULL?

postgresql

PeterVandivierPeterVandivier

Do multiple updates to the same row cause more table bloat?5

postgresql

Jack DouglasJack Douglas

6**tl;dr**: No. Yes. Multiple updates to the same row within a transaction will add additional dead tuples to the heap. However this is not the case for multiple updates within a single statement.

Jack Douglas

Reverse string by parts with strict ordering

postgresql

PeterVandivierPeterVandivier

4If you want to guarantee ordering (and you should), you can use [with ordinality](https://www.postgresql.org/docs/12/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS) since version 9.4, along with an aggregate with [order by](https://www.postgresql.org/docs/12/sql-expressions.html#SYNTAX-AGGREGATES):

Jack Douglas

Immutable pure-SQL timestamp->epoch conversion

postgresql-12

PeterVandivierPeterVandivier

7Yes, you can: just convert to `timestamp` at UTC. This is not what you might expect.

Jack Douglas

Why does only one update work in this query?

postgresql-12

Jack DouglasJack Douglas

6It [is documented](https://www.postgresql.org/docs/12/queries-with.html#QUERIES-WITH-MODIFYING) that this is not supported for data-modifying statements in `with` clauses:

Jack Douglas

Why does my query not insert any records17

postgresql-12

Anonymous 1199Anonymous 1199

3`SAMPLE` is a different table from `"SAMPLE"`. PostgreSQL lower-cases unquoted table names to conform (its interpretation of) to the SQL standard.

PeterVandivier

How do you insert rows into mutually-referencing tables in an SQL function?28

postgresql-12

Jack DouglasJack Douglas

5I'm not sure this is a sensible general solution as I suspect it may cause concurrency issues, but if you choose a dummy value that will never be present in the table you can insert that, and then update it away again, in a pure `sql` function:

Jack Douglas

Split table based on data with duplicate IDs16

postgresql-11

khakha

4You can use [`EXISTS`](https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS) to get all in a single query:

Erwin Brandstetter

2There are lots of ways to do this. Here is a solution using the windowing function version of `count`:

Jack Douglas

Merge/combine values within a group36

postgresql-9.5

khakha

1You need several steps to do this:

Jack Douglas