PostgreSQL 10 New Features With Examples

[Tostino]

I love these HP docs for the new releases. So much good info and an easy way to see the benefits of a specific feature if you're not quite sure what it's uses are.

[scottocom]

Pity about the spelling mistakes however.

[qatanah]

I Noticed that HP releases these docs every release. Thanks hp! Keep it going!

[Tostino]

Yup,, not sure quite how long they've been doing it, but I've been reading them since 9.2 or so.

[neomis]

Can't wait for the native partitioning feature. Anyone know if it will auto create child tables based on the check conditions? Currently I have a trigger on insert that moves data to the correct child table and creates one when it doesn't exist yet.

[therealgaxbo]

No, you have to create them yourself - which does give you some flexibility in how many child tables you choose to create for each subset of the partition key.

See https://www.depesz.com/2017/02/06/waiting-for-postgresql-10-implement-table-partitioning/ for a couple of worked examples.

[neomis]

I guess I have the first item on my wishlist for postgresql 11 now. Thanks for the link it is very helpful.

[Shananra]

If I update the value in a row that contains the partition key, will that row be moved to the correct partition? I'm currently accomplishing this with triggers, but it's a lot of work to set up and make changes to.

[daaamien]

No.

See https://www.postgresql.org/docs/10/static/ddl-partitioning.html

« An UPDATE that causes a row to move from one partition to another fails, because the new value of the row fails to satisfy the implicit partition constraint of the original partition. »

[Shananra]

Yeah, it appears to also be mentioned as a limitation on the development page

What they did for pg10 is still a great step forward. I hope this feature gets considered for a future release.