News for hackers. Stuff that matters.
Re:PostgreSQL 10 Beta 1 Released by alfalfasprout on Thursday May 18, 2017 @10:29AM (#14367482)
The native table partitioning makes me so happy. I'd been doing this for years with really hacky external modules and tons of triggers. Sadly, even then there were always weird edge cases.

Postgres really has become the most versatile database out there. I cringe whenever I have to work with MySQL again...

Re:PostgreSQL 10 Beta 1 Released by jdubs on Thursday May 18, 2017 @10:58AM (#14367763)
Every time I see a job post mentioning mysql I realize they just haven't discovered postgres, or they have some really gross problem. :/
Re:PostgreSQL 10 Beta 1 Released by lima on Thursday May 18, 2017 @01:09PM (#14368979)
I personally prefer PostgresQL, too, but MySQL does have one massive advantage: operations tooling and clustering options.

There's a whole lot of documentation and an ecosystem of operations tooling (think Percona) and MySQL experts are much more numerous.

MariaDB's Galera cluster is really solid and had years of production use now.

Postgres is catching up, but for now, MySQL/MariaDB win in that regard.

Re:PostgreSQL 10 Beta 1 Released by fictioncircle on Thursday May 18, 2017 @11:59AM (#14368342)
Honestly, MySQL/MariaDB use has more do with features PostgreSQL didn't have until now. (i.e. Logical replication)
Re:PostgreSQL 10 Beta 1 Released by lobster_johnson on Thursday May 18, 2017 @01:03PM (#14368931)
Postgres also has many features that MySQL doesn't.
Re:PostgreSQL 10 Beta 1 Released by fictioncircle on Thursday May 18, 2017 @02:52PM (#14369960)
Never said it didn't.

Logical replication is frequently a requirement which reduces your options to not Postgres until now.

Re:PostgreSQL 10 Beta 1 Released by rimliu on Friday May 19, 2017 @03:16AM (#14373562)
That's very condescending of you. They may be just well informed of their use cases and know which database fits them well. Poor Wikipedia and FB with their MySQL setups, they have no idea how much better of they'd be with postgres…
Re:PostgreSQL 10 Beta 1 Released by matt4077 on Thursday May 18, 2017 @11:15PM (#14372786)
I tried to switch, once. I actually gave up when, after spending entirely too much time trying to find the cli client, I couldn't figure out how to actually send queries (or it may have been the "show database" part–it's been a while)

I tend to think the HN groupthink is strong on this subject, and 90%+ wouldn't ever see an effect beyond placebo from switching. MySQL (or MariaDB, which I actually use these days) has also changed drastically since those 200x-years where most of today's folk wisdom originates.

Re:PostgreSQL 10 Beta 1 Released by ubertaco on Thursday May 18, 2017 @11:19AM (#14367946)
Or they want to allow for case-insensitivity of some data, like for example email addresses on login forms.

As much as postgres is overall better than MySQL in so many ways, it's still ridiculously difficult to set things up such that

    SELECT id FROM users WHERE email='[email protected]' 
returns the same result as

    SELECT id FROM users WHERE email='[email protected]'
Re:PostgreSQL 10 Beta 1 Released by rarrrrrr on Thursday May 18, 2017 @11:32AM (#14368074)
Here's an example of doing that in PostgreSQL:

  create table user_email (
      email text not null 
  );
  -- create a index on the lowercase form 
  -- of the email 
  create unique index user_email_case_idx 
      on user_email (lower(email));
  -- select using the index, with the lowercase form.
  select 1 
    from user_email 
   where lower(email)=lower('[email protected]');
Re:PostgreSQL 10 Beta 1 Released by olavgg on Friday May 19, 2017 @04:31AM (#14373809)
actually it is easier

SELECT 1 FROM user_email WHERE email ILIKE '[email protected]';

Re:PostgreSQL 10 Beta 1 Released by always_good on Friday May 19, 2017 @08:20AM (#14374867)

    WHERE lower(email) = lower('[email protected]')
Is simple and hits an index on lower(email).

I'm not sure ILIKE can hit an index in your example.

Re:PostgreSQL 10 Beta 1 Released by olavgg on Friday May 19, 2017 @03:37PM (#14378765)
It can if you use the pg_trgm extension, a good summary can be read here: https://niallburkley.com/blog/index-columns-for-like-in-post...
Re:PostgreSQL 10 Beta 1 Released by grahamedgecombe on Thursday May 18, 2017 @11:35AM (#14368116)
The citext type automatically does case-insensitive comparisons: https://www.postgresql.org/docs/current/static/citext.html
Re:PostgreSQL 10 Beta 1 Released by synotna on Thursday May 18, 2017 @11:27AM (#14368025)
Re:PostgreSQL 10 Beta 1 Released by ncd on Thursday May 18, 2017 @11:33AM (#14368087)
Postgres also has the citext column type to make this a snap.

https://www.postgresql.org/docs/9.6/static/citext.html

Re:PostgreSQL 10 Beta 1 Released by metalliqaz on Thursday May 18, 2017 @11:58AM (#14368340)
I'm no Postgres master by any means, but I searched it: https://duckduckgo.com/?q=postgres+case+insensitive+query

solution immediately came up at SO:

    SELECT id FROM users WHERE LOWER(email)=LOWER('[email protected]')
Re:PostgreSQL 10 Beta 1 Released by inopinatus on Thursday May 18, 2017 @04:02PM (#14370625)
That's a bad practice. Did you know: email addresses are case-sensitive on the left-hand-side.

It's discouraged by RFC5321 whilst also being defined by it.

Re:PostgreSQL 10 Beta 1 Released by fphilipe on Thursday May 18, 2017 @11:29AM (#14368041)
Does MySQL do that on varchar by default?

Can't you just do this in PostgreSQL?

    SELECT id FROM users WHERE email = lower('[email protected]')
Re:PostgreSQL 10 Beta 1 Released by thejosh on Thursday May 18, 2017 @11:44AM (#14368196)
Because you don't always want to match 'Foo' to 'foo'?
Re:PostgreSQL 10 Beta 1 Released by jakobegger on Thursday May 18, 2017 @01:40PM (#14369310)
That's what per-column collations are for. Ideally you should be able to choose from case sensitive and case insensitive collations. Unfortunately PostgreSQL doesn't support case insensitive collations (for some reason the string comparison routines use memcmp as a tie-breaker when the collation says strings are equal).
Re:PostgreSQL 10 Beta 1 Released by irrational on Thursday May 18, 2017 @02:26PM (#14369741)
If you want to do case-insensitive for all languages you can do this:

1. first install the following (be sure to replace [your schema]:

CREATE EXTENSION pg_trgm with schema extension;

CREATE EXTENSION unaccent with schema extension;

CREATE OR REPLACE FUNCTION insensitive_query(text) RETURNS text AS $func$ SELECT lower([your schema].unaccent('[your schema].unaccent', $1)) $func$ LANGUAGE sql IMMUTABLE;

2. then in your query you can use:

where insensitive_query(my_table.name) LIKE insensitive_query('Bob')

Re:PostgreSQL 10 Beta 1 Released by openasocket on Thursday May 18, 2017 @03:09PM (#14370145)
That will not work for all languages. Look at https://www.w3.org/International/wiki/Case_folding for an explanation of why this problem is nontrivial. The lower function is sufficient: it handles case-folding properly, using the configured locale. Explicitly stripping accents can actually be the wrong choice depending on the locale
Re:PostgreSQL 10 Beta 1 Released by mgkimsal on Thursday May 18, 2017 @12:10PM (#14368429)
not sure why you got downvoted so much.

Everyone's answer is "just lowercase everything".

I'll respond just a bit:

1. You don't always have control over all the queries that have been written against your database.

2. You would probably lose the ability to use ORMs without a moderate amount of customization.

3. If you're migrating from a different database, you may have checksums on your data that would all need to be recalculated if you change case on everything stored.

4. Doing runtime lowercase() on everything adds a bit of overhead, doesn't it?

citext on postgresql seems a decent option - the citext docs even mention drawbacks of some of the other recommended options.

Re:PostgreSQL 10 Beta 1 Released by openasocket on Thursday May 18, 2017 @03:00PM (#14370044)
Is there a single ORM out there that doesn't support the lower() function? I googled "case insensitive search" + a couple ORMs and each of them could implement it as a one-liner.

And doing the runtime lower() on everything will generally not be slower than citext. If you look at the source for the citext comparison (https://github.com/postgres/postgres/blob/aa9eac45ea868e6dda...) you'll see it is internally converting the values to lowercase and comparing them. All it saves you is the overhead of a sql function invocation, and you'd have to do a lot of comparisons to make that difference measurable. But if you're doing a lot of those comparisons, unless you're just running the calculation on the same couple values over and over, the memory and disk latency will dominate performance, not the minimal overhead of the sql function invocation.

I agree you should probably use citext if you need case-insensitive unique or primary key values, but be aware of the drawbacks. https://www.postgresql.org/docs/current/static/citext.html

Re:PostgreSQL 10 Beta 1 Released by solidsnack9000 on Friday May 19, 2017 @12:15AM (#14372981)
> 4. Doing runtime lowercase() on everything adds a bit of overhead, doesn't it?

Maybe MySQL has special sauce for doing this comparison without lowercasing the query string? But there must be some overhead relative to exact search?

Re:PostgreSQL 10 Beta 1 Released by sametmax on Thursday May 18, 2017 @11:30AM (#14368052)
Just lowercase everything. Not that hard.
Re:PostgreSQL 10 Beta 1 Released by irrational on Thursday May 18, 2017 @02:29PM (#14369770)
That only works if you are only dealing with English. I've posted a comment with a solution that works across all languages.
Re:PostgreSQL 10 Beta 1 Released by szatkus on Thursday May 18, 2017 @04:44PM (#14370938)
I work with MySQL in my current job. After years of Postgres it feels like dealing with some parody of database.
Re:PostgreSQL 10 Beta 1 Released by jeltz on Friday May 19, 2017 @05:56AM (#14374150)
Same here and, yeah, it really does. When preparing for upgrading a server recently I took a lvm snapshot of the disk and copied MySQL from it (to test in a VM that the server upgrade would work) assuming it would start just fine after running recovery, but instead it complained some about corruption and then segfaulted. So at least that version of MySQL cannot be trusted with your data in case of a power outage or kernel panic.
Re:PostgreSQL 10 Beta 1 Released by justinclift on Saturday May 20, 2017 @07:26PM (#14384920)
Hmmm, did you ensure the database had flushed outstanding writes + froze the filesystem before snapshotting it?

I don't touch MySQL much, but as a generalisation it's a good idea to flush outstanding writes + freeze the filesystem(s) for pretty much any database before snapshotting.

From previous experience with Oracle (years ago), that specifically would explode dramatically if things weren't flushed first.

Re:PostgreSQL 10 Beta 1 Released by raarts on Thursday May 18, 2017 @11:58AM (#14368334)
Does PostgreSQL offer something comparable to MySQL multi-source replication in combination with auto_increment_offset?
Re:PostgreSQL 10 Beta 1 Released by petereisentraut on Thursday May 18, 2017 @03:01PM (#14370056)
I'm not familiar with MySQL, but this might be along those lines: https://www.2ndquadrant.com/en/resources/bdr/