Someone asked recently on Slack, whether one should build tsvector data in a field, and index it, or make index on expression.
We talked about it for a while, and I thought I'll reformat my thoughts into blogpost to avoid retyping it next time.
Tag: column
Waiting for PostgreSQL 11 – Fast ALTER TABLE ADD COLUMN with a non-NULL default
On 28th of March 2018, Andrew Dunstan committed patch:
Fast ALTER TABLE ADD COLUMN with a non-NULL default Currently adding a column to a table with a non-NULL default results in a rewrite of the table. For large tables this can be both expensive and disruptive. This patch removes the need for the rewrite as long as the default value is not volatile. The default expression is evaluated at the time of the ALTER TABLE and the result stored in a new column (attmissingval) in pg_attribute, and a new column (atthasmissing) is set to true. Any existing row when fetched will be supplied with the attmissingval. New rows will have the supplied value or the default and so will never need the attmissingval. Any time the table is rewritten all the atthasmissing and attmissingval settings for the attributes are cleared, as they are no longer needed. The most visible code change from this is in heap_attisnull, which acquires a third TupleDesc argument, allowing it to detect a missing value if there is one. In many cases where it is known that there will not be any (e.g. catalog relations) NULL can be passed for this argument. Andrew Dunstan, heavily modified from an original patch from Serge Rielau. Reviewed by Tom Lane, Andres Freund, Tomas Vondra and David Rowley. Discussion: https://postgr.es/m/-7002-4c27-59f5-@2ndQuadrant.com
Continue reading Waiting for PostgreSQL 11 – Fast ALTER TABLE ADD COLUMN with a non-NULL default
Waiting for 9.6 – Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN
On 30th of July, Andrew Dunstan committed patch:
Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN Fabrízio de Royes Mello, reviewed by Payal Singh, Alvaro Herrera and Michael Paquier.
Continue reading Waiting for 9.6 – Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN
Stupid tricks – hiding value of column in select *
One of the most common questions is “how do I get select * from table, but without one of the column".
Short answer is of course – name your columns, instead of using *. Or use a view.
But I decided to take a look at the problem.
Continue reading Stupid tricks – hiding value of column in select *
Waiting for 8.5 – pgsql: DROP IF EXISTS for columns and constraints.
On 205h of July Andrew Dunstan committed patch by Andres Freund :
Log Message: ----------- DROP IF EXISTS FOR COLUMNS AND constraints. Andres Freund.
Continue reading Waiting for 8.5 – pgsql: DROP IF EXISTS for columns and constraints.