Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker <patrickbakerbr(at)gmail(dot)com>
wrote:

>
>>> It's all working, except the LIMIT... if possible can you please give
> me an example of that LIMIT in some of those queries?
>
> ​​
You also should use ORDER BY when using LIMIT and OFFSET; though depending
on the setup it could be omitted. Usually as long as the second execution
cannot select any of the records the first execution touched you can choose
a random quantity. But if you want random then using OFFSET is pointless.

​SELECT *
FROM generate_series(1, 10)
ORDER BY 1
LIMIT 5
OFFSET 3

generate_series
----------------------
4
5
6
7
8
​
You are going to have difficultly finding people willing to help when you
cannot put together a self-contained and syntax error free example (I think
the last one is...) of what you want to do. The PostgreSQL parser is very
good at reading code and telling you what it doesn't like. I'm not
inclined to spend time reading queries that obviously cannot run and point
out those same problems. If you can a particular error you don't
understand I'll be happy to try and explain what it is trying to tell you.

​You probably need to reformulate your update to read:

UPDATE tbl
FROM (
SELECT 50 RECORDS
)​ src
WHERE src = tbl;

​And ensure that the 50 being selected each time through are a different 50.

Writeable CTEs will probably help here.

https://www.postgresql.org/docs/current/static/queries-with.html

​David J.