Column does not exist error with jsonb_each in Postgres

Version

1.15.0

What happened?

When running generate, I get an error about a column not existing even though it should and the select works if I run it directly against a database.

Relevant log output

query.sql:3:8: column "key" does not exist

Database schema

CREATE TABLE my_table (
  data JSONB NOT NULL
);

SQL queries

-- name: GetData :one

SELECT key, value
FROM my_table, jsonb_each(data)
LIMIT 1;

Configuration

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "example"
        out: "example"

Playground URL

https://play.sqlc.dev/p/51d375a16d47e652835b91f5bcebfd41eeb62da8cfc09a01bcbad790862ac9d8

What operating system are you using?

Linux, macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

Probably a duplicate of #1480

This does appear to be a duplicate, but I think we should leave it open just in case the previous issue happens to be specific to pgx/v4.

test: Add case for #1979

fix(compiler): Pull in array information from analyzer (#2864)

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground

Hi!

Does anyone know a workaround for people who aren't ready for the database-backed query analyzer yet?

@hectorj-thetreep

@hectorj-thetreep

Hi!

Does anyone know a workaround for people who aren't ready for the database-backed query analyzer yet?

Yes! In the case of postgresql you can "simply" cast the key to text and it workskey::text!

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

CREATE TABLE my_table (
  data JSONB NOT NULL
);

-- name: GetData :one
SELECT key::text, value::jsonb
FROM my_table, jsonb_each(data)
LIMIT 1;

-- name: GetData2 :one
SELECT data.key::text, data.value::jsonb
FROM my_table, jsonb_each(data) as data
LIMIT 1;

fix(compiler): Pull in array information from analyzer (sqlc-dev#2864)

Navigation Menu

Repository navigation

Column does not exist error with jsonb_each in Postgres #1979

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Column does not exist error with jsonb_each in Postgres #1979

Description

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Activity

bretwalker commented on Dec 2, 2022

andrewmbenton commented on Jun 6, 2023

kyleconroy commented on Oct 24, 2023

hectorj-thetreep commented on Dec 8, 2023

kevinvalk commented on Feb 28, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions