way (by creating an aggregate function, perhaps)
Perhaps the following is what you need (not tested!):
SELECT
custid
, sum(CASE WHEN vendid = 415 THEN 1 ELSE 0 END) AS c415
, sum(CASE WHEN vendid = 983 THEN 1 ELSE 0 END) AS c983
, sum(CASE WHEN vendid = 1256 THEN 1 ELSE 0 END) AS
null),
(5, 'e', 3),
(6, 'f', null),
(7, 'g', null),
(8, 'h', 4),
(9, 'i', null),
(10, 'j', 5)
) AS t(a,b,c)
)
, grouped_source AS (
SELECT
source.*
,
t.
Also, if you aim on 9.5 (not released yet), it will introduce:
SELECT...
FOR UPDATE
SKIP LOCKED -- this is new
which supports exactly this use-case (i.e. to implement a job queue).
HTH,
Ladislav Lenart
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ut a partial unique index as documented here:
http://www.postgresql.org/docs/9.4/static/indexes-partial.html
I.e.:
CREATE UNIQUE INDEX ON pick (load_id) WHERE picked = 'y';
HTH,
Ladislav Lenart
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To m
read/write dependencies among transactions)?
This is on 9.4.
Thank you in advance,
Ladislav Lenart
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ke of completeness...
If the value (empname in the above example) can be NULL, the compare does not
work, because
SELECT NULL = NULL
returns NULL which is treated as FALSE.
But I am sure you know this :-)
HTH,
Ladislav Lenart
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
t; in the subselect and the sheer size of the tables, even when we could handle
> it
> as an index-only scan.
>
>
> On Apr 29, 2015, at 1:18 PM, Ladislav Lenart wrote:
>
>> I would expect the overall query to return only 60F nad 55F as the most
>> recent
>>
On 29.4.2015 18:54, Jonathan Vanasco wrote:
>
> On Apr 29, 2015, at 12:25 PM, Ladislav Lenart wrote:
>
>> Could you please explain to me the error(s) in my reasoning?
>
> Let me just flip your list in reverse... and add in some elements (marked
> with a *):
>
&
s usage of the CTE is also of a different format that I've
> used in the past.
>
> I think i'll be able to patch together some performance improvements now,
> that will last until the database structure changes.
>
>
> On Apr 29, 2015, at 6:54 AM, Ladislav Lenart
ublish DESC
> LIMIT 10
> ;
I think you can propagate ORDER BY and LIMIT also to the subqueries of the
UNION, i.e.:
select...
from (
(
select...
from posting
where... -- friends
order by timestamp_publish desc
limit 10
) union (
group_id in (
select group_id from memberships where user_id = ?
)
) t
group by t.posting_id
)
select
posting.*,
posting_ids.from_friend,
posting_ids.from_group,
posting_ids.from_friend or posting_ids.from_group as from_any,
posting_ids.from_friend and posting_ids
On 5.12.2013 15:14, Tom Lane wrote:
> Ladislav Lenart writes:
>> What happens if I issue UPDATE SET FROM... but with incomplete/buggy WHERE
>> condition and thus SEVERAL rows from the from_list match ONE row to update?
>
> Any given row will be updated at most once. Howev
Hello.
What happens if I issue UPDATE SET FROM... but with incomplete/buggy WHERE
condition and thus SEVERAL rows from the from_list match ONE row to update?
Thank you in advance,
Ladislav Lenart
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
On 4.10.2013 15:11, David Johnston wrote:
> Ladislav Lenart wrote
>> * Should I alias one of the references to deal?
>> * The above query works the same both with and without an alias, so is it
>> simply
>> a matter of taste / good practice?
>> * Where can I fin
Hello.
I have a noob question about table aliases in SQL.
Suppose the following query:
SELECT *
FROM deal
WHERE
deal.id IN (
SELECT DISTINCT deal.id
FROM
deal
JOIN partner_share ON deal.id = partner_share.deal_id
JOIN partner ONshare.partne
On 16.9.2013 17:30, David Johnston wrote:
> Ladislav Lenart wrote
>> Hello all.
>>
>> I am curious about the following usage of CTEs:
>>
>> Imagine three tables:
>> * item (id, item_type1_id, item_type2_id, ...)
>> * item_type1 (id, ...)
>> * i
On 16.9.2013 17:12, Adrian Klaver wrote:
> On 09/16/2013 07:38 AM, Ladislav Lenart wrote:
>> On 16.9.2013 15:50, Adrian Klaver wrote:
>>> On 09/16/2013 04:57 AM, Ladislav Lenart wrote:
>>>> On 16.9.2013 13:26, Alban Hertroys wrote:
>>>
>
>>>
On 16.9.2013 15:50, Adrian Klaver wrote:
> On 09/16/2013 04:57 AM, Ladislav Lenart wrote:
>> On 16.9.2013 13:26, Alban Hertroys wrote:
>
>>>
>>> Wouldn't it be much easier to define an FK constraint with ON DELETE
>>> CASCADE?
>>> With tha
WHERE item_typeX.id NOT IN (
SELECT item_typeX_id
FROM ok_items
WHERE item_typeX_id IS NOT NULL
)
)
everything works as it should.
Ladislav Lenart
On 16.9.2013 13:57, Ladislav Lenart wrote:
> On 16.9.2013 13:26, Alban Hertroys wrote:
>> On 16 September 2013 11:
On 16.9.2013 13:26, Alban Hertroys wrote:
> On 16 September 2013 11:58, Ladislav Lenart wrote:
>> Hello all.
>>
>> I am curious about the following usage of CTEs:
>>
>> Imagine three tables:
>> * item (id, item_type1_id, item_type2_id, ...)
>>
Thank you in advance,
Ladislav Lenart
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello.
I think this CAN happen in practice when the constraints are DEFERRED, because
as such are checked at COMMIT time.
HTH,
Ladislav Lenart
On 21.6.2013 05:08, Tom Lane wrote:
> Melvin Call writes:
>> I was given a dump of an existing remote schema and database, and the
>>
22 matches
Mail list logo
