StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You would write like this in postgres.<br> <code>IGNORE</code> is irrelevant here, as the table has just been recreated and is guaranteed to be empty. And <code>UNION</code> guarantees there are no duplicate rows inserted.</p> <pre><code>DROP TABLE IF EXISTS tmp_table; CREATE TEMP TABLE tmp_table(id int4 primary key); INSERT INTO tmp_table SELECT user2role.userid::int4 AS id FROM user2role JOIN users ON users.id = user2role.userid JOIN role ON role.roleid = user2role.roleid WHERE role.parentrole like 'H1::H2::H3::H4::H5::%' UNION SELECT groupid::int4 FROM groups WHERE groupid in (2,3,4); </code></pre> <p>If duplicates in the SELECT cannot occur, you might consider the faster <code>UNION ALL</code> instead of <code>UNION</code>. Otherwise you need <code>UNION</code> to eliminate possible dupes. Read <a href="http://www.postgresql.org/docs/current/static/sql-select.html#SQL-UNION" rel="nofollow noreferrer">here</a>.<br> If your dataset is large you might consider creating the primary key <em>after</em> the INSERT. That's faster. </p> <p>Read the <a href="http://dev.mysql.com/doc/refman/5.6/en/insert.html" rel="nofollow noreferrer">mySQL docs</a> on effects of <code>IGNORE</code>.</p> <hr> <p>On revisiting the page I realized you mention <code>IF NOT EXISTS</code> in the original code. You don't say so, but that only makes sense if the original code created the table only if it didn't exist already, which introduces the possibility of it being <strong>not empty</strong> before the INSERT. In this case <code>IGNORE</code> is relevant and needs an equivalent in PostgreSQL.</p> <p>So here is <b><em>alternative answer</em></b> for that interpretation of your question. </p> <p><code>CREATE TEMP TABLE IF NOT EXISTS</code> has been <a href="http://www.postgresql.org/docs/current/static/sql-createtable.html" rel="nofollow noreferrer">implemented in PostgreSQL <strong>9.1</strong></a>.<br> For older version I posted a <a href="https://stackoverflow.com/questions/1766046/postgresql-create-table-if-not-exists">solution</a> on SO recently.</p> <pre><code>CREATE TEMP TABLE IF NOT EXISTS tmp_table(id int4 primary key); INSERT INTO tmp_table SELECT x.id FROM ( SELECT user2role.userid::int4 AS id FROM user2role JOIN users ON users.id = user2role.userid JOIN role ON role.roleid = user2role.roleid WHERE role.parentrole like 'H1::H2::H3::H4::H5::%' UNION SELECT groupid::int4 FROM groups WHERE groupid in (2,3,4) ) x LEFT JOIN tmp_table t USING (id) WHERE t.id IS NULL; </code></pre> <p><code>LEFT JOIN ... WHERE t.id IS NULL</code> excludes any <code>id</code> that might already be present in <code>tmp_table</code>. <code>UNION</code> goes into a sub-select, so that clause needs only be applied once. Should be fastest.<br> <a href="http://www.postgresql.org/docs/current/interactive/sql-select.html" rel="nofollow noreferrer">More on LEFT JOIN here</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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