column reference not found: if you want to skip this validation, set 'strict_order_by' to false

[G1tQuality]

Version

1.28.0

What happened?

I have read some issues related to this but I can't fix it while using the latest version. There is this column in my pgSQL server and the following query was successfully run.

Relevant log output

sql\queries\feeds.sql:17:1: column reference "last_fetched_at" not found: if you want to skip this validation, set 'strict_order_by' to false

Database schema

-- +goose Up
CREATE TABLE feeds(
    id UUID PRIMARY KEY,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    name TEXT NOT NULL,
    url TEXT UNIQUE NOT NULL,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE
    );
-- +goose Down
DROP TABLE feeds;
-- +goose up
ALTER TABLE feeds ADD COLUMN last_fetched_at TIMESTAMP;

-- +goose down
ALTER TABLE feeds DROP COLUMN last_fetched_at;

SQL queries

-- name: CreateFeed :one
INSERT INTO feeds (id, created_at, updated_at, name, url, user_id)
VALUES (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6
)
RETURNING *;

-- name: GetFeeds :many
SELECT * FROM feeds;

-- name: GetNextFeedsToFetch :many
SELECT * FROM feeds
ORDER BY last_fetched_at ASC NULLS FIRST
LIMIT $1;

Configuration

version: "2"
sql:
  - schema: "sql/schema"
    queries: "sql/queries"
    engine: "postgresql"
    gen:
      go:
        out: "internal/database"

Playground URL

No response

What operating system are you using?

Windows

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[snowtoslow]

Does the migration part related to adding/dropping last_fetched_at is in the same file? Also, maybe the --- +goose Up/Down somehow differ from --- +goose up/down? Try to maybe split these in two different files, and ensure that goose comments are written in the same way, maybe it's gonna help 🙈

[iSpot24]

@G1tQuality Yep, it's like @snowtoslow said. I had exactly the same problem. It's about the typos in goose up & down tags. Your last migration tags are lower case, it should be like "+goose Up".

Postgres can handle multiple migrations in a same file, that's not a problem. I think only sqlite doesn't support that. This will work:

-- +goose Up ALTER TABLE feeds ADD COLUMN last_fetched_at TIMESTAMP;

-- +goose Down ALTER TABLE feeds DROP COLUMN last_fetched_at;