column does not exists (when an alias)

[gosom]

Version

1.14.0

What happened?

the above query does not compile using sqlc generate.

Relevant log output

# package orm
queries/users.sql:8:5: column "rank_email" does not exist

Database schema

CREATE EXTENSION pg_trgm;
CREATE EXTENSION pgcrypto;

CREATE TABLE users(
    id INT GENERATED ALWAYS AS IDENTITY NOT NULL,
    fname VARCHAR(100) NOT NULL,
    lname VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    enc_passwd TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL default (NOW() AT TIME ZONE 'utc'),
    PRIMARY KEY(id)
);

SQL queries

SELECT 
    users.id,
    users.fname,
    users.lname,
    users.email,
    users.created_at,
    rank_email,
    rank_fname,
    rank_lname,
    similarity
FROM 
    users, 
    to_tsvector(users.email || users.fname || users.lname) document,
    to_tsquery(@search_term::TEXT) query,
    NULLIF(ts_rank(to_tsvector(users.email), query), 0) rank_email,
    NULLIF(ts_rank(to_tsvector(users.fname), query), 0) rank_fname,
    NULLIF(ts_rank(to_tsvector(users.lname), query), 0) rank_lname,
    SIMILARITY(@search_term::TEXT, users.email || users.fname || users.lname) similarity
WHERE query @@ document OR similarity > 0
ORDER BY rank_email, rank_lname, rank_fname, similarity DESC NULLS LAST;

Configuration

version: "2"
sql:
- schema: "migrations"
  queries: "queries"
  engine: "postgresql"
  gen:
    go: 
      package: "orm"
      out: "orm"
      sql_package: "pgx/v4"

Playground URL

https://play.sqlc.dev/p/3935699fa944c07c82d9e0c5cfdd84e95ffd315c62e09a3801bf9209d5f29e81

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[verygoodsoftwarenotvirus]

I am also having this issue. :)

[ovas33710]

Same here but for table alias.
I run the querry in the postgres database and it works without any issue.
When i try to generate Go code for the query using the sqlc generate command i get the following error:
table alias "upd" does not exist

system: Fedora Linux 37 64-bit
go version: go1.19.4 linux/amd64
postgres version: PostgreSQL 15.1

[milosgajdos]

Ouch, not helping here either but I've just hit the same issue as @ovas7 😅

Thanks for otherwise a fantastic project ❤️

[AdrianNigro]

I am running v1.15.0 and have the same problem

[tiklup11]

having the same issue,

this query :

CREATE TABLE "coupons" (
  "id" bigserial PRIMARY KEY,
  "couponCode" varchar NOT NULL,
  "valid_till" timestamptz NOT NULL DEFAULT (now()),
  "discount_percentage" int,
  "max_discount_amount" int NOT NULL
);

-- name: CreateCoupon :one
INSERT INTO coupons(
    couponCode,
    discount_percentage,
    max_discount_amount
) VALUES (
    $1, $2, $3
)
RETURNING *;

returns:

sqlc generate failed.

# package db
query.sql:11:5: column "couponcode" does not exist

my sqlc.yaml:

version: "1"
packages:
  - path: "./db/sqlc"
    name: "db"
    engine: "postgresql"
    schema: "./db/migration/"
    queries: "./db/query/"
    emit_json_tags: true
    emit_prepared_queries: false
    emit_interface: false
    emit_exact_table_names: false

someone please test the same here: https://play.sqlc.dev/p/d24769ff48755a1d32d5972fcac9e8966d475c3e22c4e508d8bfd9568554ba6c

[milosgajdos]

@tiklup11 your issue is unrelated to this one. Please read the description properly.

As for your issue, if you remove the (") double quotes from your CREATE TABLE query you should get the generation working just fine (https://play.sqlc.dev/p/2031703f27cf4cae5b142d8a33fccd90016c43159d3d451fae2968edc7e54b17)

[tiklup11]

@milosgajdos sorry mb, thanks for your help.

[kyleconroy]

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