On 7/13/22 07:58, André Hänsel wrote:
Jeremy Smith wrote:
CREATE TABLE t (
id serial PRIMARY KEY,
name text NOT NULL UNIQUE,
address text NOT NULL
);
This will yield “8”, showing that new sequence numbers have been
generated for each attempt.
If running out of id's
On Wed, Jul 13, 2022 at 7:58 AM André Hänsel wrote:
>
> SELECT last_value FROM t_id_seq;
>
>
>
> This will yield “8”, showing that new sequence numbers have been generated
> for each attempt.
>
Yep, an entire able-to-be-inserted tuple is formed every time. That
requires evaluating defaults so t
=?utf-8?Q?Andr=C3=A9_H=C3=A4nsel?= writes:
> This will yield “8”, showing that new sequence numbers have been generated
> for each attempt.
Well, yeah, because the INSERT has to be attempted first, and
that includes forming the whole candidate row including the
nextval() result. If you're expec
Jeremy Smith wrote:
It sounds like you aren't adding a WHERE clause to prevent the duplicate rows
from being updated. It would help if you could share your query, but in
general this could look like this:
INSERT INTO my_table (col1, col2)
SELECT col1, col2 FROM other_table
ON CONFLICT
On Wednesday, July 13, 2022, André Hänsel wrote:
> The wiki entry https://wiki.postgresql.org/wiki/UPSERT kind of suggests
> that with the introduction of ON CONFLICT DO UPDATE the problem of upserts
> is solved. But is it?
>
>
>
> A common use case for upserts is to keep a table up to date from
- New versions are created for all rows, even if the data is identical.
> This quickly fills up the WAL and puts unnecessary load on the tablespace
> drives.
>
> - If the conflict target is not the serial column, the sequence backing
> the serial column gets incremented for every row. This quickly
The wiki entry https://wiki.postgresql.org/wiki/UPSERT kind of suggests that
with the introduction of ON CONFLICT DO UPDATE the problem of upserts is
solved. But is it?
A common use case for upserts is to keep a table up to date from an external
data source. So you might have a cron job that ru
