Re: SERIAL datatype column skipping values.

On 3/11/20 11:15 AM, Prabhat Sahu wrote:
> Hi all,
> Please check the below behavior for the "SERIAL" datatype.
>
> [...]
>
> In this above case, the serial column "c2" is skipping the value "4" in
> select output.
> Is this an expected behavior?

Curious, it seems like DEFAULT expressions of a table are executed an
extra time if a set returning function is used like in your example. And
the SERIAL type is implemented using DEFAULT.

On the other hand if you use "INSERT ... SELECT" the DEFAULT expression
is only executed once per row inserted.

# CREATE FUNCTION test_default() RETURNS int LANGUAGE plpgsql AS $$
BEGIN
RAISE NOTICE 'Ran test_default()';
RETURN 42;
END
$$;
CREATE FUNCTION

# CREATE TABLE t2 (c1 int, c2 int DEFAULT test_default());
CREATE TABLE

# INSERT INTO t2 VALUES (generate_series(1,2));
NOTICE: Ran test_default()
NOTICE: Ran test_default()
NOTICE: Ran test_default()
INSERT 0 2

# INSERT INTO t2 SELECT generate_series(1,2);
NOTICE: Ran test_default()
NOTICE: Ran test_default()
INSERT 0 2

Andreas