CODEWITHSUNDEEP
sqlpostgresqlpsql
flutterbug98
flutterbug98

Reputation: 121

PostgreSQL Commands Providing No Result

In my database, there is a table that provides no result whenever I issue a query through the command line.

For example, if I type:

select * from <table>

Nothing happens. The terminal stops responding until I press ^C to cancel the request.

All the other tables work fine, I am not sure what is causing this error. It just started happening out of nowhere.

Upvotes: 1

Views: 518

Answers (2)

Tanuj0610
Tanuj0610

Reputation: 11

There is a simpler way

Basically 3 points to consider when planning to user PostGreSQL from cmd prompt.

  1. Semi-colon
  2. Set Search path
  3. Handle case sensitivity and naming conventions

First answer is to add a semi-colon after the statement.

Second, if you are using postgre from command line , and you have created you tables in public schema, so every time instead of writing

SELECT * FROM public."Users";

we can set the search path Set Search Path Temporarily in That Particular Session:

psql -U your_username -d your_database
SET search_path TO public;
SELECT * FROM Users;

Permanently Setting the Search Path for a Role:

psql -U your_username -d your_database
ALTER ROLE your_username SET search_path TO public;
SELECT * FROM Users;

Lastly, very important case-sensitivity. If you create tables using PgAdmin then they will be created as "Users" so if you will run statement from command prompt as

SELECT * FROM Users;
OR 
SELECT * FROM users;

that wont work, you will have to write

SELECT * FROM "Users";

So its better to create table in lowercase and alter existing tables as

ALTER TABLE "Users" RENAME TO users;

Hope this helps

Upvotes: 0

flutterbug98
flutterbug98

Reputation: 121

Okay, I found a way to solve it.

First run:

select * from pg_locks where not granted;

Then, from that table, find the relation id. It should be in the column under relation.

Then run this command:

select * from pg_locks where relation = <relationid>;

After that, determine the pid of the relation that has granted equal to true. There should be a granted column with boolean t and f values. Determine that relation's pid.

Finally, run:

select pg_terminate_backend(<pid>);

Then, you should now be able to access the table.

Upvotes: 1

Related Questions