Pq Bind message errors

[hackertron]

Hello I am getting pq bind message errors like the following below

2025/04/06 09:52:07 Error fetching jobs: pq: bind message supplies 6 parameters, but prepared statement "" requires 1

 Error fetching user jobs for b3249379-e39a-4fcb-a003-a766121762ca: pq: bind message has 17 result formats but query has 14 columns

I can't seem to find the issue. the sql code is generated by sqlc generate. below are my sql

I tried select * too but I was getting same errors, that's why I have explicitly mentioned the column names.

jobs.sql

-- name: CreateJob :one
-- Creates a new job record, initially in 'pending_payment' status by default schema definition.
INSERT INTO jobs (
    id,
    title,
    description,
    company,
    location,
    salary_range,
    company_id,
    category,
    tags,
    plan_id, -- Added plan_id association
    created_at,
    updated_at
    -- status defaults to 'pending_payment', expires_at is NULL initially
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
RETURNING *;

-- name: UpdateJobPaymentOrder :exec
-- Updates the job with the Razorpay Order ID after it's created.
UPDATE jobs
SET razorpay_order_id = $2,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- name: ActivateJob :exec
-- Activates a job after successful payment verification. Sets status to 'active', stores payment ID, and calculates expiry.
UPDATE jobs
SET status = 'active',
    razorpay_payment_id = $2,
    expires_at = CURRENT_TIMESTAMP + ($3::integer * interval '1 day'), -- Calculate expiry based on plan's validity_days
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1 AND status = 'pending_payment'; -- Only activate jobs awaiting payment

-- name: GetJobForActivation :one
-- Fetches minimal job details needed before activating, ensuring it's pending payment.
SELECT id, plan_id, razorpay_order_id FROM jobs WHERE id = $1 AND status = 'pending_payment';

-- name: GetJobsWithPagination :many
-- Fetches active and non-expired jobs for public listing, with pagination and filters.
SELECT j.id, j.title, j.description, j.company, j.location, j.salary_range, j.category, j.tags, j.created_at, j.updated_at, j.company_id, j.search_vector, u.username as company_name
FROM jobs j
LEFT JOIN users u ON j.company_id = u.id
WHERE j.status = 'active' AND (j.expires_at IS NULL OR j.expires_at > CURRENT_TIMESTAMP) -- Filter for active & not expired
  AND ((COALESCE($1, '') = '' OR j.category = $1))
  AND ($2::text[] IS NULL OR j.tags && $2)
  AND ($3::varchar IS NULL OR j.location ILIKE '%' || $3 || '%')
  AND ($4::varchar IS NULL OR j.salary_range ILIKE '%' || $4 || '%')
ORDER BY j.created_at DESC
LIMIT $5 OFFSET $6;

-- name: SearchJobs :many
-- Searches active and non-expired jobs using full-text search and filters.
SELECT j.id, j.title, j.description, j.company, j.location, j.salary_range, j.category, j.tags, j.created_at, j.updated_at, j.company_id, j.search_vector, u.username as company_name,
       ts_rank(j.search_vector, websearch_to_tsquery('english', $1)) as rank
FROM jobs j
JOIN users u ON j.company_id = u.id
WHERE j.status = 'active' AND (j.expires_at IS NULL OR j.expires_at > CURRENT_TIMESTAMP) -- Filter for active & not expired
  AND j.search_vector @@ websearch_to_tsquery('english', $1)
  AND ($2::varchar IS NULL OR j.category = $2)
  AND ($3::text[] IS NULL OR j.tags && $3)
  AND ($4::varchar IS NULL OR j.location ILIKE '%' || $4 || '%')
  AND ($5::varchar IS NULL OR j.salary_range ILIKE '%' || $5 || '%')
ORDER BY rank DESC, j.created_at DESC
LIMIT $6 OFFSET $7;

-- name: GetCompanyJobs :many
-- Fetches all jobs for a specific company, regardless of status (for dashboard view).
SELECT id, title, description, company, location, salary_range, category, tags, created_at, updated_at, company_id, search_vector, status, plan_id, expires_at, razorpay_order_id, razorpay_payment_id
FROM jobs
WHERE company_id = $1
ORDER BY created_at DESC;

-- name: GetJobByID :one
-- Fetches a single job by ID, including company username. Shows job regardless of status/expiry.
SELECT j.*, u.username as company_name
FROM jobs j
JOIN users u ON j.company_id = u.id
WHERE j.id = $1;

-- name: GetJobs :many
-- Fetches ALL jobs regardless of status/expiry (potentially for admin use or internal checks). Consider removing if not needed.
SELECT * FROM jobs ORDER BY created_at DESC;

-- name: UpdateJob :one
-- Updates job details. Typically used before payment or for active jobs by owner/admin.
-- Does not change payment status or expiry here.
UPDATE jobs
SET
    title = $2,
    description = $3,
    company = $4,
    location = $5,
    salary_range = $6,
    category = $7,
    tags = $8,
    -- plan_id = ?, -- Potentially allow plan change only before payment? Add if needed.
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1 AND company_id = $9 -- Ensure ownership check
RETURNING *;

-- name: DeleteJob :exec
-- Deletes a job. Typically allowed for owners/admins, maybe only for 'pending' or 'draft' jobs unless intentional deletion of active ones is required.
DELETE FROM jobs
WHERE id = $1
AND (company_id = $2 OR $3 = true); -- $3 is admin override flag

-- name: DeleteJobSuccess :one
-- Checks if a job still exists (useful after a delete operation for confirmation).
SELECT EXISTS (SELECT 1 FROM jobs WHERE id = $1) AS success;

-- name: GetJobCategories :many
-- Fetches distinct categories from ACTIVE jobs only.
SELECT DISTINCT category FROM jobs WHERE category IS NOT NULL AND status = 'active' AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP) ORDER BY category;

-- name: GetJobTags :many
-- Fetches distinct tags from ACTIVE jobs only.
SELECT DISTINCT unnest(tags) as tag FROM jobs WHERE tags IS NOT NULL AND status = 'active' AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP) ORDER BY tag;

applications.sql

-- name: CreateApplication :one
INSERT INTO applications (
    id,
    job_id,
    applicant_id,
    full_name,
    email,
    phone,
    resume_url,
    cover_letter,
    portfolio_url,
    status,
    created_at,
    updated_at
) VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
) RETURNING *;

-- name: GetApplicationByID :one
SELECT * FROM applications WHERE id = $1;

-- name: GetApplicationsByJobID :many
SELECT a.*,
    j.title as job_title,
    j.company as company_name
FROM applications a
JOIN jobs j ON a.job_id = j.id
WHERE a.job_id = $1
ORDER BY a.created_at DESC;

-- name: GetApplicationsByApplicantID :many
SELECT
    a.*,
    j.title as job_title,
    j.company as company_name
FROM applications a
JOIN jobs j ON a.job_id = j.id
WHERE a.applicant_id = $1
ORDER BY a.created_at DESC;

-- name: GetCompanyApplications :many
-- Original: SELECT a.*, j.title as job_title, j.company as company_name
SELECT a.id, a.job_id, a.applicant_id, a.full_name, a.email, 
       a.phone, a.resume_url, a.cover_letter, a.portfolio_url, 
       a.status, a.created_at, a.updated_at,
       j.title as job_title, j.company as company_name
FROM applications a
JOIN jobs j ON a.job_id = j.id
WHERE j.company_id = $1 -- Filter by the company's user ID
ORDER BY a.created_at DESC;

-- name: UpdateApplicationStatus :one
UPDATE applications
SET
    status = $2,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1
RETURNING *;

-- name: DeleteApplication :exec
DELETE FROM applications WHERE id = $1;

-- name: CountApplicationsByJobID :one
SELECT COUNT(*) FROM applications WHERE job_id = $1;

-- name: CheckApplicationExists :one
SELECT EXISTS (
    SELECT 1 FROM applications
    WHERE job_id = $1 AND applicant_id = $2
);