Lesser-known Postgres features

  id int SERIAL

  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

https://hakibenita.com/postgresql-unknown-features#prevent-s...

You might think this is trivial -- but SERIAL creates an "owned" (by a certain user) sequence behind the scenes, and so you run into massive headaches if you try to move things around.

Identity columns don't, and avoid the issue altogether.

https://www.2ndquadrant.com/en/blog/postgresql-10-identity-c...

An attempt to find a suffix like that will not be able to use an index, whereas creating a functional index on the reverse and looking for the reversed suffix as a prefix will be:

  # create table users (id int primary key, email text);
  CREATE TABLE
  # create unique index on users(lower(email));
  CREATE INDEX
  # set enable_seqscan to false;
  SET
  
  # insert into users values (1, 'foo@gmail.com'), (2, 'bar@gmail.com'), (3, 'foo@yahoo.com');
  INSERT 0 3
  # explain select * from users where email ~* '@(gmail.com|yahoo.com)$';
                                  QUERY PLAN
  --------------------------------------------------------------------------
   Seq Scan on users  (cost=10000000000.00..10000000025.88 rows=1 width=36)
     Filter: (email ~* '@(gmail.com|yahoo.com)$'::text)

  # create index on users(reverse(lower(email)) collate "C"); -- collate C explicitly to enable prefix lookups
  CREATE INDEX

  # explain select * from users where reverse(lower(email)) ~ '^(moc.liamg|moc.oohay)';
                                               QUERY PLAN
  ----------------------------------------------------------------------------------------------------
   Bitmap Heap Scan on users  (cost=4.21..13.71 rows=1 width=36)
     Filter: (reverse(lower(email)) ~ '^(moc.liamg|moc.oohay)'::text)
     ->  Bitmap Index Scan on users_reverse_idx  (cost=0.00..4.21 rows=6 width=0)
           Index Cond: ((reverse(lower(email)) >= 'm'::text) AND (reverse(lower(email)) < 'n'::text))

Here is another way I have used to do pivot tables / crosstab in postgres where you have a variable number of columns in the output:

https://gist.github.com/ryanguill/101a19fb6ae6dfb26a01396c53...

You can try it out here: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=5dbbf7eadf0ed...

For example create a table with a trigger on insert "NOTIFY new_data". Then on query do

    LISTEN new_data;
    SELECT ...;

They achieve the same thing, but [.] avoids the problem of your host language "helpfully" interpolating \. into . before sending the query to Postgres.

- generate_series(): While not the best to make _realistic_ test data for proper load testing, at least it's easy to make a lot of data. If you don't have a few million rows in your tables when you're developing, you probably don't know how things behave, because a full table/seq scan will be fast anyway - and you'll not spot the missing indexes (on e.g. reverse foreign keys, I see missing often enough)

- `EXPLAIN` and `EXPLAIN ANALYZE`. Don't save minutes of looking at your query plans during development by spending hours fixing performance problems in production. EXPLAIN all the things.

A significant percentage of production issues I've seen (and caused) are easily mitigated by those two.

By learning how to read and understand execution plans and how and why they change over time, you'll learn a lot more about databases too.

(CTEs/WITH-expressions are life changing too)

[0]: https://www.postgresql.org/docs/current/rangetypes.html

[1]: https://www.postgresql.org/docs/current/functions-range.html

I posted to pgsql-hackers about this problem here: https://www.postgresql.org/message-id/DE57F14C-DB96-4F17-925...

It's nice to see one that's full of good advice.

Would i use them in production? no. Are they fun to play around with? Yes!

Or in words: There is an overlap if meeting A starts before meeting B ends and meeting A ends after meeting B starts.

So the scenario described looks way more complex as it actual seems to be.

It's not a huge deal in most cases, is a bigger issue if you are running any sort of scheduled ETL to update (or insert new) things.

    SELECT *
    FROM users
    WHERE email ~ ANY('{@gmail\.com$|@yahoo\.com$}')

    with connection.cursor() as cursor:
        cursor.execute('''
            SELECT *
            FROM users
            WHERE email ~ ANY(ARRAY%(patterns)s)
        ''' % {
            'patterns': [
                '@gmail\.com$',
                '@yahoo\.com$',
            ],
        })

  SELECT user_id, ARRAY_AGG(STRUCT(post_id, text, timestamp) ORDER BY timestamp DESC LIMIT 1)[SAFE_OFFSET(0)].*
  FROM posts
  GROUP BY user_id

I would hesitate to call this a useful feature. This error message doesn’t tell you about the actual problem at all.

How about something like:

Permission denied, cannot select other than (id, name) from table users

    SELECT *
    INTO TEMP copy
    FROM foobar;
    \copy "copy" to 'foobar.csv' with csv headers

Though I wish there was an easy way to figure out how many times "CONFLICT" actually occured (i.e how many times did we insert vs update).

Is that feature considered well-known? Or is it so obscure this author didn't know about it?

They're the ones that will be hardest to migrate to a different database, most likely to be deprecated, and least likely to be understood by the next engineer to fill your shoes.

While many of these are neat, good engineering practice is to make the simplest thing to get the job done.

db=# COMMENT ON TABLE sale IS 'Sales made in the system'; COMMENT

Enjoyed and bookmarked.

This feature might be great for OLAP systems, but it's awful for OLTP systems. The last thing I want is debug permission errors bubbling from my database into the application layer. It may not be as efficient, but you should always manage your ACL rules in the application layer if you're building an OLTP app.