Postgres Pro
Downloads
Home   >   mailing lists

Re: SELECT FOR UPDATE returns zero rows - Mailing list pgsql-novice

From amul sul
Subject Re: SELECT FOR UPDATE returns zero rows
Date
Msg-id CAAJ_b94Jqz50heX=KL+ro15RL_DS2juTkSZu-wrQxDQTMe+NTQ@mail.gmail.com
Whole thread Raw
In response to Re: SELECT FOR UPDATE returns zero rows  (Dima Pavlov <imyfess@gmail.com>)
Responses Re: SELECT FOR UPDATE returns zero rows  (Dima Pavlov <imyfess@gmail.com>)
List pgsql-novice
Tree view
On my environment it working fine, see below:

postgres=# WITH 
    cte1 AS (
        SELECT t2c2 FROM t2 WHERE t2c1 = 'string_value_2'
    ),
    cte2 AS (
        SELECT * FROM t1
        WHERE 
             t1c1 = 123456789 
            AND t1c2 = (SELECT t2c2 FROM cte1)
        FOR UPDATE
    ) 
SELECT * FROM cte2;

 _pk |   t1c1    | t1c2 |      t1c3      
-----+-----------+------+----------------
   1 | 123456789 |  100 | string_value_1
(1 row)


Are you sure, user executing these SQLs have appropriate permissions on respective table? Is there any error or warnings (check server log too)?


Regards,
Amul

On Sun, Sep 18, 2016 at 7:20 PM, Dima Pavlov <imyfess@gmail.com> wrote:
1. 1 row. 
    t2c2: 100


2. 1 row. 
_pk:1
t1c1: 123456789
t1c2: 100
t1c3: "string_value_1"

Example:

CREATE TABLE t1 (_pk serial, t1c1 integer, t1c2 integer, t1c3 text);
CREATE TABLE t2 (_pk serial, t2c1 text, t2c2 integer);
insert into t1 (t1c1, t1c2, t1c3) values(123456789, 100, 'string_value_1');
insert into t2 (t2c1, t2c2) values('string_value_2', 100);

On Sun, Sep 18, 2016 at 9:41 AM, amul sul <sulamul@gmail.com> wrote:
What is output of following queries:

1. SELECT t2c2 FROM t2 WHERE t2c1 = "string_value_2"
2. SELECT * FROM t1 WHERE t1c1 = 123456789 AND t1c2 IN (SELECT t2c2
FROM t2 WHERE t2c1 = "string_value_2")

Regards,
Amul


pgsql-novice by date:

Previous
From: Dima Pavlov
Date:
Subject: Re: SELECT FOR UPDATE returns zero rows
Next
From: Dima Pavlov
Date:
Subject: Re: SELECT FOR UPDATE returns zero rows