What does it mean when pg_stat_activity.datname is NULL?

postgresql

PeterVandivierPeterVandivier

UNION ALL + LIMIT - unintuitive behaviour with subselect

postgresql

PeterVandivierPeterVandivier

3The docs are a bit vague about this. In the ["Combining Queries"](https://www.postgresql.org/docs/13/queries-union.html) section (which discusses `UNION`/`INTERSECT`/`EXCEPT`) they state:

Jack Douglas

3Without explicit parentheses, the second `LIMIT` is scoped to the batch, not the statement. Consider the `EXPLAIN`

PeterVandivier

Reverse string by parts with strict ordering

postgresql

PeterVandivierPeterVandivier

5If 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

4I would probably use a scalar sub-select:

Anonymous 1633

How to connect SQLeo to an online database?

postgresql

DavidDavid

5With some [pointers from the SQLeo developer](https://sourceforge.net/p/sqleo/discussion/general/thread/622c79e069/#76c7), I managed to sort this. The more generic form of my problem is **using an SSH tunnel to connect securely to a remote database**.

David

How do I remove quotes from string in PostgreSQL COPY command8

postgresql-11

khakha

5One option might be to tell psql to make the `QUOTE` character a space `' '`, although you should be aware of the risk of creating a malformed csv.

PeterVandivier

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

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