Cannot run simple WHERE query in node-postgres

Question

This is my table:

-- Table Definition ----------------------------------------------

CREATE TABLE "Friendships" (
    id integer DEFAULT nextval('"Friendships_id_seq"'::regclass) PRIMARY KEY,
    "createdAt" timestamp with time zone NOT NULL,
    "updatedAt" timestamp with time zone NOT NULL,
    "fromUserId" integer NOT NULL REFERENCES "Users"(id),
    "toUserId" integer NOT NULL REFERENCES "Users"(id)
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX "Friendships_pkey" ON "Friendships"(id int4_ops);

This is my query:

const { Client } = require('pg')
const pgClient = new Client({ user: PG_USERNAME, host: PG_HOST, database: PG_DB, password: PG_PASSWORD, port: 5432 })
pgClient.connect()

pgClient.query({
            name: 'fetch-friendships',
            text: 'SELECT * FROM Friendships WHERE fromUserId = $1',
            values: [1]
        }, (err, res) => {
            console.log(err)
            console.log(res)
        })

Error: relation "friendships" does not exist

cosmos=> \dt;
            List of relations
 Schema |      Name       | Type  | Owner 
--------+-----------------+-------+-------
 public | AuthCodes       | table | pangu
 public | BirthRecords    | table | pangu
 public | Births          | table | pangu
 public | Friendships     | table | pangu
 public | InvitationCodes | table | pangu
 public | SequelizeMeta   | table | pangu
 public | Users           | table | pangu
(7 rows)

cosmos=> SELECT * FROM Friendships;
ERROR:  relation "friendships" does not exist
LINE 1: SELECT * FROM Friendships;

Try SELECT * FROM "Friendships". In Oracle, if you double quote a table or column name during creation, you are making it case-sensitive. But that would force you tom make case-sensitive queries as well... — Glenn
– Glenn, Commented Sep 17, 2019 at 20:45

Juan Carlos Oropeza · Accepted Answer · 2019-09-17 20:50:48Z

1

In postgres you need use double quotes if create the table with upper cases.

Without double quotes

Friendships -> friendships

As you can see for the error:

ERROR: relation "friendships" does not exist

but you create

CREATE TABLE "Friendships" (

So you need use "Friendships"

Same goes for Tables, Field names or Function names.

edited Sep 17, 2019 at 20:50

answered Sep 17, 2019 at 20:45

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

3 Comments

TIMEX Over a year ago

Thanks! How can I use AS alias in my query? SELECT * FROM "Friendships" AS "F" where "F.fromUserId" = 1 throws error: ` Perhaps you meant to reference the column "f.fromUserId".`

Juan Carlos Oropeza Over a year ago

You need use "F"."fromUserId" double quote every item, table, alias or field

Juan Carlos Oropeza Over a year ago

my suggestion use dash and lower case to avoid use double quotes. so use friendship and from_user_id instead

Collectives™ on Stack Overflow

Cannot run simple WHERE query in node-postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related