StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Oh dear, this could end in tears.</p> <p>Speaking from personal experience only, we moved from MySQL solely because our production system (Heroku) is running PostgreSQL. We had custom-built-for-MySQL queries which were breaking on PostgreSQL. So I guess the morale of the story here is to run on the same DBMS over everything, otherwise you may run into problems.</p> <p>We also sometimes needs to insert records Über-quick-like. For this, we use PostgreSQL's built-in <code>COPY</code> function, used similarly to this in our app:</p> <pre><code>query = "COPY users(email) FROM STDIN WITH CSV" values = users.map! do |user| # Be wary of the types of the objects here, they matter. # For instance if you set the id to a string it will error. %Q{#{user["email"]}} end.join("\n") raw_connection.exec(query) raw_connection.put_copy_data(values) raw_connection.put_copy_end </code></pre> <p>This inserts ~500,000 records into the database in just under two minutes. Around about the same time if we add more fields.</p> <p>Another couple of nice things PostgreSQL has over MySQL:</p> <ul> <li>Full text searching</li> <li>Geographical querying (PostGIS)</li> <li><code>LIKE</code> syntax is like this <code>email ~ 'hotmail|gmail'</code>, <code>NOT LIKE</code> is like <code>email !~ 'hotmail|gmail'</code>. The <code>|</code> indicates an or.</li> </ul> <p>In summary: PostgreSQL is like bricks & mortar, where MySQL is Lego. Go with whatever "feels" right to you. This is only my personal opinion.</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