Select query doesn't work with specific column condition in postgresql

Question

Background: PostgreSQL service faced some corruption after the server power outage. And I used the pg_resetwal command to fix that issue. As suggested here

After the service successfully starts I'm facing this weird issue.

When I query with the id column, it doesn't result anything even the queried data is there and column type matches

# SELECT id, email FROM users WHERE id=1;
 id | email
----+-------
(0 rows)

But if I query with other columns (in this example, email column), it results

# SELECT id, email FROM users WHERE email='[email protected]';
 id |          email
----+--------------------------
 1 | [email protected]
(1 row)

Any suggestions?

Postgresql version: 12.7

Is this id column numeric or varchar? Can it contain spaces? You could try something like SELECT id, email FROM users WHERE id LIKE '%1%' to check this. If this doesn't help, could you please provide the create table command of this table? — Jonas Metzler
– Jonas Metzler, Commented May 10, 2022 at 12:18
@JonasMetzler, the id column is an integer type. So the LIKE operator is not working. — Odko
– Odko, Commented May 11, 2022 at 8:37

Richard Huxton · Accepted Answer · 2022-05-10 12:34:09Z

1

OK, so after you have managed to get your PostgreSQL instance running what you should have done is:

Take an immediate backup of all your databases
Audit them + check for any damage
Drop the existing dbs and restore from the audited backups
Identify the mis-configuration in your server that resulted in the data corruption.

I assume you haven't done these things and have a corrupted index.

Your hardware is lying to PostgreSQL about persisting data to disk. It isn't safe to trust the existing data - anything that was being updated (not just directly updated by you, but vacuum processess too) is suspect.

answered May 10, 2022 at 12:34

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Select query doesn't work with specific column condition in postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related