Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is hard to to in pure SQL. I would resort to a <a href="http://www.postgresql.org/docs/current/interactive/plpgsql.html" rel="nofollow">plpgsql</a> procedure.<br> Say, your table looks like this:<br> <sub>(Next time, be so nice as to post a table definition. Worth more than a thousand words.)</sub></p> <pre><code>create table p ( id serial primary key -- or whatever your primary key is! , company_id int4 NOT NULL , create_time timestamp NOT NULL , for_sale bool NOT NULL ); </code></pre> <p>Use a plpgsql function like this:</p> <pre><code>CREATE OR REPLACE FUNCTION f_p_group() RETURNS void AS $BODY$ DECLARE g_id integer := 1; last_time timestamp; last_company_id integer; r p%ROWTYPE; BEGIN -- If the table is huge, special settings for these parameters will help SET temp_buffers = '100MB'; -- more RAM for temp table, adjust to actual size of p SET work_mem = '100MB'; -- more RAM for sorting -- create temp table just like original. CREATE TEMP TABLE tmp_p ON COMMIT DROP AS SELECT * FROM p LIMIT 0; -- no rows yet -- add group_id. ALTER TABLE tmp_p ADD column group_id integer; -- loop through table, write row + group_id to temp table FOR r IN SELECT * -- get the whole row! FROM p -- WHERE for_sale -- commented out, after it vanished from the question ORDER BY company_id, create_time -- group by company_id first, there could be several groups intertwined LOOP IF r.company_id &lt;&gt; last_company_id OR (r.create_time - last_time) &gt; interval '10 min' THEN g_id := g_id + 1; END IF; INSERT INTO tmp_p SELECT r.*, g_id; last_time := r.create_time; last_company_id := r.company_id; END LOOP; TRUNCATE p; ALTER TABLE p ADD column group_id integer; -- add group_id now INSERT INTO p SELECT * FROM tmp_p; -- ORDER BY something? ANALYZE p; -- table has been rewritten, no VACUUM is needed. END; $BODY$ LANGUAGE plpgsql; </code></pre> <p>Call once, then discard:</p> <pre><code>SELECT f_p_group(); DROP FUNCTION f_p_group(); </code></pre> <p>Now, all members of a group as per your definition share a <code>group_id</code>. </p> <h3>Edit after question edit</h3> <p>I put in a couple more things:</p> <ul> <li>Read the table into a temporary table (ordering in the process), do all the updates there, truncate the original table add group_id and write updated rows from the temp table in one go. Should be much faster and no vacuum needed afterwards. But you need some RAM for that</li> <li><code>for_sale</code> ignored in query after it's not in the question any more.</li> <li>Read about <a href="http://www.postgresql.org/docs/current/interactive/plpgsql-declarations.html" rel="nofollow">%ROWTYPE</a>.</li> <li>Read here about <a href="http://www.postgresql.org/docs/current/interactive/runtime-config-resource.html" rel="nofollow">work_mem and temp_buffers</a>.</li> <li>TRUNCATE, ANALYZE, TEMP TABLE, ALTER TABLE, ... all in <a href="http://www.postgresql.org/docs/current/interactive/index.html" rel="nofollow">the fine manual</a></li> <li>I tested it with pg 9.0. should work in 8.4 - 9.0 and probably older versions too.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POGrouping items that were created within a certain time of each other
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USErwin Brandstetter
    1. USErwin Brandstetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload