Skip to content

Insert statement with WITH clause used as SELECT field #2886

Open

Description

@kampde

Version

1.22.0

What happened?

This is similar to #2350.

I've tried 2 variations of the same query using WITH (v AS ...) and referencing that v as one of the SELECTed fields. While both variations produce the expected results using psql, they fail with different errors when compiling them using sqlc.

  1. Using `INSERT INTO ... WITH ... SELECT ...
-- name: GenerateNewHistoricalData :exec
INSERT INTO historical_data(id, default_value, version)
  WITH v AS (SELECT nextval('historical_data_version_seq'))
SELECT id, default_value, (table v)
FROM data;

Error: query.sql:18:1: relation "v" does not exist

  1. Using WITH ... INSERT INTO ... SELECT ...:
-- name: GenerateNewHistoricalDataTakeTwo :exec
WITH v AS (SELECT nextval('historical_data_version_seq'))
INSERT INTO historical_data(id, default_value, version)
SELECT id, default_value, (table v)
FROM data;

Error: query.sql:24:1: edit start location is out of bounds

Relevant log output

No response

Database schema

CREATE TABLE data (
  id   TEXT NOT NULL,
  value TEXT NOT NULL,
  CONSTRAINT data_pkey PRIMARY KEY(id)
);

CREATE TABLE historical_data (
  id TEXT NOT NULL,
  value TEXT NOT NULL,
  version BIGINT NOT NULL,
  CONSTRAINT historical_data_pkey PRIMARY KEY(id, version)
);

CREATE SEQUENCE historical_data_version_seq;

SQL queries

No response

Configuration

No response

Playground URL

https://play.sqlc.dev/p/4c877cf26c2bb27c217303995af1afbef3bf9c84a25e682f01de5f0745c259cc

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

Activity

  1. added
    bugSomething isn't working
    triageNew issues that hasn't been reviewed
    on Oct 19, 2023
  2. kyleconroy commented on Oct 19, 2023

    @kyleconroy
    Collaborator

    The first error will be fixed by #2805. You can fix the second error by explicitly selecting the column in the subselect.

    -- name: GenerateNewHistoricalDataTakeTwo :exec
    WITH v AS (SELECT nextval('historical_data_version_seq') as nv)
    INSERT INTO historical_data(id, value, version)
    SELECT id, value, (select nv from v)
    FROM data;

    https://play.sqlc.dev/p/af2fda7ab8fcff8040cf19be8dd85ce78fec9a51fd4fdbab187ffa6b682939b1

  3. kampde commented on Oct 20, 2023

    @kampde
    Author

    Thanks, that works wonderfully :D

    If you prefer you can close the issue now or maybe you want to keep it opened for tracking or whatever.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions