Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Column does not exist error with jsonb_each in Postgres #1979

Closed
bretwalker opened this issue Dec 2, 2022 · 5 comments
Closed

Column does not exist error with jsonb_each in Postgres #1979

bretwalker opened this issue Dec 2, 2022 · 5 comments

Comments

@bretwalker
Copy link

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

@bretwalker bretwalker added bug Something isn't working triage New issues that hasn't been reviewed labels Dec 2, 2022
@bretwalker
Copy link
Author

Probably a duplicate of #1480

@andrewmbenton andrewmbenton added 📚 postgresql 💻 darwin 💻 linux and removed triage New issues that hasn't been reviewed labels Jun 6, 2023
@andrewmbenton
Copy link
Collaborator

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.

kyleconroy added a commit that referenced this issue Oct 17, 2023
kyleconroy added a commit that referenced this issue Oct 18, 2023
* fix(compiler): Pull in array information from analyzer
Fixes #1532
* test(analyzer): Add testcase for #1574
* test: Added test for #1634
* test: Add test case for #1646
* test: Add test for #1714
* Fixes #1912
* test: Add case for #1916
* test: Add two test cases
#1917
#1545
* test: Add case for #1979
* test: Add case for #1990
@kyleconroy
Copy link
Collaborator

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

@hectorj-thetreep
Copy link

Hi!

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

@kevinvalk
Copy link

kevinvalk commented Feb 28, 2024

@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;

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

No branches or pull requests

5 participants