Is it possible to reference an inserted column within the same INSERT?

All we need is an easy explanation of the problem, so here it is.

This query gives the error:

create table qwe (a bigint, completed bit varying(100000));
CREATE TABLE

insert into qwe (a, completed) values (1000, repeat('0', a));
ERROR:  column "a" does not exist

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You probably also saw the hint:

HINT: There is a column named "a" in table "qwe", but it cannot be referenced from this part of the query.

You cannot reference a column value that does not exist, yet.

Instead, spell out the value again. Or use a subselect or CTE to provide a only once:

INSERT INTO qwe (a, completed)
VALUES (1000, repeat('0', 1000)::varbit);

INSERT INTO qwe (a, completed)
SELECT a, repeat('0', a)::varbit
FROM  (SELECT 1000) sub(a);

db<>fiddle here

Also, you need an explicit cast to varbit.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply