Open
Description
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
);