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



  • 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



  • 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 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=05d049a8a94de40483edb02ac4429706

    Also, you need an explicit cast to varbit.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2