PostgreSQL: moving rows to an archive table and passwordless psql

Move rows to an archive table in one query

A DELETE ... RETURNING inside a WITH query passes the deleted rows straight to an INSERT:

WITH moved_rows AS (
    DELETE FROM box_magldno.serialno
    WHERE tms::DATE <= '2018-12-31'
    RETURNING *
)
INSERT INTO box_magldno.serialno_2018
SELECT * FROM moved_rows;

Documentation: WITH Queries

Table partitioning

Besides declarative partitioning, PostgreSQL supports partitioning with table inheritance:

• Table partitioning
• Inheritance

psql without typing the password

Store the credentials in ~/.pgpass (format host:port:database:user:password):

$ cat ~/.pgpass
localhost:5432:*:postgres:pass

psql ignores the file unless its permissions are 0600 (chmod 600 ~/.pgpass).

Set the default host in ~/.zshrc:

export PGHOST=localhost

• Password file
• Environment variables

To run PostgreSQL itself in a container, see Podman: PostgreSQL in a container.