Re: Basics fails; SELECT, UPDATE

On Thu, Jun 27, 2013 at 3:58 AM, Jonas Schwanbom <cygnent(at)gmail(dot)com> wrote:
> Thank you Sergey! But I asure you that I do have the row put in. It's more
> of the other way around; there are rows I want to change or to show a few at
> a time, rows that I put into my database and which I can see when using
> "SELECT * ...".
>
> Obviously you´re right in that PostgreSQL can't see these rows. But it is
> not because they are not there; they are there. Them and the mystery.

Then you probably have a corrupted index on RealTable. The one that
the queries use. Have you had some hardware or OS problems on your
server?

To fix it you can either try to

REINDEX TABLE RealTable

or, to find out which one is corrupted try to

EXPLAIN SELECT * FROM RealTable WHERE RealPrimaryKey = 'realvalue';

and notice what index is used, and then REINDEX INDEX it.

>
>
> Kind regards / Jonas Schwanbom
>
>
> 2013/6/26 Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
>>
>> On Wed, Jun 26, 2013 at 4:00 AM, Jonas Schwanbom <cygnent(at)gmail(dot)com>
>> wrote:
>> > These are the commands that won't work for me:
>> >
>> > SELECT * FROM RealTable WHERE RealPrimaryKey = 'realvalue';
>> > SELECT RealColumn FROM RealTable WHERE RealPrimaryKey = 'realvalue';
>> >
>> > I get a blank sheet. The names of the columns are on top, otherways it's
>> > blank. Why on earth????
>> >
>> > It works without the ”WHERE”-part; I can see the full, filled table if I
>> > want to. But not any specific row or rows.
>> >
>> > Neither will this work, not if there allready is a value in
>> > ”RealColumn”:
>> >
>> > UPDATE RealTable SET RealColumn = 'newrealvalue' WHERE RealPrimaryKey =
>> > 'realvalue';
>> >
>> > Postgre SQL responds: ”Query returned successfully: 0 rows affected, 15
>> > ms
>> > execution time.”
>>
>> It looks like you do not have a row with RealPrimaryKey = 'realvalue'
>> in the table. Try to insert one and let us see.
>>
>>
>> >
>> >
>> > Or other execution time, that differs; if it where allways the same,
>> > something would be wrong.
>> >
>> >
>> >
>> > I can put in
>> >
>> > ”
>> >
>> > GRANT ALL ON RealTable TO public;
>> >
>> > ”
>> >
>> > or not, the result is the same either way; the problems remains.
>> >
>> >
>> >
>> > But in small tables like this, that I make to test things, in them
>> > everything works!
>> >
>> >
>> > CREATE TABLE T (T CHAR(2) PRIMARY KEY, Q CHAR(2));
>> >
>> >
>> > INSERT INTO T (T, Q) VALUES ('A1', 'A2'), ('B1', 'B2');
>> >
>> >
>> > SELECT * FROM T WHERE T = 'A1';
>> >
>> >
>> > ALTER TABLE T ADD COLUMN R CHAR(2);
>> >
>> >
>> > SELECT * FROM T WHERE T = 'A1';
>> >
>> >
>> > UPDATE T SET R = 'C1' WHERE T = 'A1'
>> >
>> >
>> > SELECT * FROM T WHERE T = 'A1';
>> >
>> >
>> > UPDATE T SET Q = 'X' WHERE T = 'A1'
>> >
>> >
>> > SELECT * FROM T WHERE T = 'A1';
>> >
>> >
>> >
>> > Can anyone help me out?
>> >
>> >
>>
>>
>>
>> --
>> Kind regards,
>> Sergey Konoplev
>> PostgreSQL Consultant and DBA
>>
>> Profile: http://www.linkedin.com/in/grayhemp
>> Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
>> Skype: gray-hemp
>> Jabber: gray(dot)ru(at)gmail(dot)com
>
>

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com