Background

I am currently writing the server-side application for an online turn-based in-browser game. I have set up a PostgreSQL database with a variety of tables within the default public schema but the two that matter for the purpose of this question are users and game_requests. These were created with the commands below respectively and populated with test data.

CREATE TABLE IF NOT EXISTS users
(
    user_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 ),
    username varchar(32) NOT NULL,
    email varchar(256) NOT NULL,
    password varchar(64) NOT NULL,
    PRIMARY KEY (user_id),
    CONSTRAINT "user_id_UNIQUE" UNIQUE (user_id),
    CONSTRAINT "username_UNIQUE" UNIQUE (username),
    CONSTRAINT "email_UNIQUE" UNIQUE (email)
);

ALTER TABLE IF EXISTS users
    OWNER to postgres;
    
GRANT ALL ON TABLE users TO postgres;

CREATE TABLE IF NOT EXISTS game_requests
(
    game_request_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    player_id integer NOT NULL,
    date_created varchar(32) NOT NULL,
    time_control integer NOT NULL,
    PRIMARY KEY (game_request_id),
    CONSTRAINT "game_request_id_UNIQUE" UNIQUE (game_request_id),
    CONSTRAINT "game_requests.player_id" FOREIGN KEY (player_id)
        REFERENCES public.users (user_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE SET NULL
        NOT VALID,
    CONSTRAINT "game_requests.time_control" FOREIGN KEY (time_control)
        REFERENCES public.time_controls (time_control_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);

ALTER TABLE IF EXISTS game_requests
    OWNER to postgres;
    
GRANT ALL ON TABLE game_requests TO postgres;

Additionally, I have a C#/ASP.NET Core API that will handle the backend validation and logic for the game. I am using the Npgsql library to connect to the database and execute queries.

The Problem

I am unfortunately, only able to access some tables via my Npgsql connection. Specifically, I can access the users table but not the game_requests table.

When I execute the below code:

NpgsqlConnection connection = new NpgsqlConnection(GlobalConstants.ConnectionString);
string command = "SELECT * FROM users;";
NpgsqlDataReader reader = await new NpgsqlCommand(command, connection).ExecuteReaderAsync();
Console.WriteLine(reader.HasRows);

I correctly get true printed to the console. However, when I change it to select from the game_requests table, i.e.

NpgsqlConnection connection = new NpgsqlConnection(GlobalConstants.ConnectionString);
string command = "SELECT * FROM game_requests;";
NpgsqlDataReader reader = await new NpgsqlCommand(command, connection).ExecuteReaderAsync();
Console.WriteLine(reader.HasRows);

An exception is called on the ExecuteReaderAsync line with the message 42P01: relation "game_requests" does not exist.

It is worth noting that this is not the case within PGAdmin. That is, I can retrieve rows from the game_requests table from within that application so the table definitely does exist.

What I’ve Tried

I found an answer on a similar question on Stack Overflow that mentioned using the below code to ensure the table exists within the public schema.

SELECT * FROM information_schema.tables WHERE table_name='game_requests';

In PGAdmin, it successfully returns a row with information about the table with the table_schema field listed as ‘public’. From within my application, however, there aren’t any rows – signifying that the table doesn’t seem to exist (or, I guess, isn’t accessible).

In the case of the users table, there isn’t such discrepancy. I get a populated response within my application just as within PGAdmin.

I should also note, both applications are accessing the database with the same login credentials so there shouldn’t be any access issues due to that.

I feel like I’m missing something really simple. Why is one of my tables working while the other isn’t? I would appreciate any advice you can provide on the matter.

Thanking you in advance.