node-postgres not parsing queries correctly

Question

I am trying to count the number of documents in a table that satisfy a condition.

My code in node:-

const [washingCount, washedCount, dirtyCount] = await Promise.all([
        pool.query("SELECT COUNT(*) FROM clothes WHERE status = 'washing'"),
        pool.query("SELECT COUNT(*) FROM clothes WHERE status = 'washed'"),
        pool.query("SELECT COUNT(*) FROM clothes WHERE status = 'dirty'")
    ])

But I am getting the error saying:

error: column "count" does not exist

And when I copy the same query over to PostgreSQL CLI, those output the desired results.

For full code refer:- https://github.com/js313/clothio/blob/master/index.js

Error stack trace:-

What am I missing here?

Thank you.

This is not reproducible. Please paste the entire contents of the script where you're trying to do the query. May be the error lies somewhere else. — Rahul Sharma
– Rahul Sharma, Commented Oct 6, 2022 at 7:49
@RahulSharma github.com/js313/clothio/blob/master/index.js, please refer to this link for the entire code. Endpoint throwing error is at line 69. — Poseidon Broger
– Poseidon Broger, Commented Oct 6, 2022 at 9:32
Again. Not able to reproduce. Try curl -XGET http://localhost:300/clothes/count after starting the app on your local machine and see what happens. — Rahul Sharma
– Rahul Sharma, Commented Oct 6, 2022 at 10:49
@RahulSharma still the same error, even tried reinstalling 'pg' module. I added the full error stack trace if that helps. — Poseidon Broger
– Poseidon Broger, Commented Oct 6, 2022 at 17:07

JuanDM · Accepted Answer · 2022-10-06 17:09:54Z

2

Why don't use group by?

select status, count(*) from clothes group by status

Edit

tested with a simple script like this and worked

require("dotenv").config({ path: "./.env" });

const Pool = require("pg").Pool;

const pool = new Pool({
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
});

async function main() {
  const result1 = await pool.query("SELECT now()");
  console.log(result1);

  const result = await pool.query(
    "SELECT status, COUNT(*) FROM clothes group by status"
  );

  console.log(result.rows);
}

main()
  .then()
  .catch((e) => console.error(e));

Edit 2

Found the issue in your repo code.

Route registration order matters, so you have this route.

app.get("/clothes/:cloth_id", async (req, res) => {

before the count one, so express is getting into this route and never reaching the count one.

To solve the issue move the /clothes/count route before the clothes/clothe_id one and that should solve the issue

edited Oct 6, 2022 at 17:09

answered Oct 5, 2022 at 19:11

JuanDM

1,33011 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Poseidon Broger Over a year ago

I am new to relational databases, feel stupid now as I know what GROUP BY does. Anyways, this has the same issue, working in the CLI but throwing the same error of "column count does not exist" when using node-postgres.

JuanDM Over a year ago

added an edit, I tested with that script and worked as expected

Poseidon Broger Over a year ago

No still the same error, I added the whole error message to the question, if it helps.

JuanDM Over a year ago

ok, used your repo to check and the issue has nothing to do with node-postgres and it is related to route ordering in express :D, check the new update

Poseidon Broger Over a year ago

I am so stupid! That is express 101, and the unfortunate part being the id named 'count', never thought to check the routes. On the bright side, I now know a practical example of when to use GROUP BY. Thank you :)

Collectives™ on Stack Overflow

node-postgres not parsing queries correctly

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related