Deprecated features in PostgreSQL - Past to present

If you have been using PostgreSQL for a long time, or you’re relatively new but have been following old instructions about how to use it, it’s possible that you’re using features that have been deprecated. The reason features disappear tend to be because they have been superseded by better features which cover the same functionality. It’s important to try to avoid using features which are destined to disappear if there’s a newer alternative. Also when planning an upgrade, it’s useful to know if a feature you’re using will suddenly break in the new version. Some of these features still continue to work, but have since been removed from documentation because they’re to be removed in a future release. Others have just been removed completely.

We’ll start off with the absurdly old and work our way to the present:

Version 6.2

timetravel contrib module

Does it still work?: No. This is ooooold and was last supported back in PostgreSQL 6.1.

What’s wrong with it?: This really dragged performance down and took up a huge amount of storage space. But the concept was pretty cool... being able to query data as it was at another time.

What to use instead: You can use triggers to implement a similar mechanism.

Version 6.4

char2/char4/char8/char16 data types

Does it still work?: No. These were removed way back in PostgreSQL 6.4. In fact I shouldn’t bother mentioning these, but you never know... someone *could* still be using them somewhere.

What’s wrong with it?: Not in the SQL standard and they’re no faster than using the ubiquitous char(n).

What to use instead: char(n)

Version 7.0

abstime data type

Does it still work?: This will still work, but it’s no longer documented as of PostgreSQL 7.0 and only intended to be used internally. Despite its name, it supports both date and time.

What’s wrong with it?: The range this data type provides is limited: ‘1901-12-14’ to ‘2038-01-19’. It also only has a resolution down to the second. Its behaviour is unfortunately like that of MySQL’s, in that if you insert an invalid value, it won’t fire an error. Instead you’ll just see ‘invalid’ as the value when you go to query it.

What to use instead: Since abstime supports timezone, the better alternative is using timestamp with time zone (timestamptz). It takes up more space (8 bytes instead of 4), but it has a far wider range: ‘4713 BC’ to ‘294276 AD’ and supports microsecond resolution.

reltime data type

Does it still work?: Yes, still works, but again, no longer documented as of PostgreSQL 7.0 and for internal use only.

What’s wrong with it?: This stores a date/time offset but only +/- 68 years. Again, this doesn’t error with values higher than this limit. Does it put ‘invalid’ in the column like abstime? No. Instead the value wraps around, so entering +70 years would result in a value of around -66 years. Not what you want. It also has a resolution down to the second.

What to use instead: The SQL standard equivalent of this kind of data type is interval, which PostgreSQL has. This does take up more space (12 bytes), but it’s range is absolutely huge: ‘-178000000 years’ to ‘+178000000 years’. This also has microsecond resolution. Interval can also handle relative time units; for example, adding a month to 15th February using interval will give you 15th March, but with abstime it has a fixed notion of a month being 30 days, so will give you 17th March (on a non-leap year). A year is also considered to be 360 days.

timespan data type

Does it still work?: No. This was deprecated back in PostgreSQL 7.0, and totally removed in PostgreSQL 7.3. If for any reason you’re using this, you’re *definitely* overdue an upgrade, and have been for many years.

What’s wrong with it?: It’s not in the SQL standard and was really just an alias for interval.

What to use instead: Just use interval.

psql/pg_dump’s -u option

Does it still work?: No, this was deprecated as far back as PostgreSQL 7.0 and removed in 8.3. You should definitely not be using this.

What’s wrong with it?: This option forced psql and pg_dump to prompt for the username and password before connecting to the database. Since prompting for a username is always optional, but prompting for a password may or may not be required (depending on authentication method), it didn’t make sense to glue both of these together.

What to use instead: It has been replaced by the -U option to specify the username, and the -W option to prompt for the password.

Version 7.1

getpgusername() function

Does it still work?: Yes, but it’s effectively deprecated as of PostgreSQL 7.1!

What’s wrong with it?: It’s no longer documented, and could be removed in a future release since it’s obsolete.

What to use instead: Call current_user instead, since getpgusername() is now just an alias for that.

Version 8.1

autovacuum contrib module

Does it still work?: No, as it was moved into core since PostgreSQL 8.1.

What’s wrong with it?: Nothing. Quite the opposite. It was considered so essential that it became part of the main codebase.

What to use instead: Nothing to worry about. Since it’s now in core, you get it out of the box without having to explicitly include it.

Version 8.2

mSQL-interface and tips contrib modules

Does it still work?: No, these were completely removed in PostgreSQL 8.2.

What’s wrong with it?: These were considered abandoned and unmaintained.

What to use instead: Nothing.

adddepend, dbase, dbmirror, fulltextindex, mac, ora2pg and userlock
contrib modules

Does it still work?: No, again, these were completely removed in PostgreSQL 8.2.

What’s wrong with it?: Most of these were moved to pgFoundry to be maintained separately.

What to use instead: These still exist on pgFoundry if you really want them (except for fulltextindex which has disappeared, and ora2pg which is on its own website), although they’re all now unmaintained (apart from ora2pg).

Version 8.3

automatic casting to text

Does it still work?: As of PostgreSQL 8.3, non-text data types are no longer implicitly cast to text. This is considered to be one of the major hurdles for some people migrating from earlier versions and the biggest cause of incompatibility.

What’s wrong with it?: Anyone who knows PostgreSQL well will know that it doesn’t like to throw any weirdness or odd behaviour your way. There are cases where implicit casting to text causes undesired results. For example: current_date < 2012-04-02 would result in both sides being automatically cast to text types, even though the date on the right-hand side would first be considered an integer (2012 minus 4 minus 2).

What to use instead: It’s always good practise to be explicit about data types when specifying literals. This will avoid any usual behaviour.

tsearch2 contrib module

Does it still work?: Yes, but it is deprecated as of PostgreSQL 8.3.

What’s wrong with it?: It has been superseded by changes in core with a few functional changes. It’s still kept around for backwards-compatibility.

What to use instead: Use the newer core functionality. There’s information on the tsearch2 contrib module page in the documentation on how to convert to the new functionality.

xml2 contrib module

Does it still work?: Yes, but it is deprecated as of PostgreSQL 8.3.

What’s wrong with it?: Nothing really, and it’s still around for backwards-compatibility, but there is newer XML functionality in core based on the SQL/XML standard.

What to use instead: Use the built-in XML features (xml data type, xml functions, xml parameters).

Version 8.4

pg_dump/pg_dumpall’s -d and -D options

Does it still work?: No, these were removed in PostgreSQL 8.4.

What’s wrong with it?: Such options were often mistaken for a database name parameter, but in fact it caused database dumps to output using insert statements rather than copy statements. This is significantly slower to restore, and cannot be adjusted after the fact.

What to use instead: If someone really did want to use these options intentionally, then the long name options of --inserts and --column-inserts are to be used instead.

Version 9.1

createlang/droplang client applications

Does it still work?: Only up until PostgreSQL 9.1.

What’s wrong with it?: Languages are now treated like extensions as of PostgreSQL 9.1.

What to use instead: Execute CREATE EXTENSION <language name> instead.

CREATE/DROP LANGUAGE

Does it still work?: Yes, but it’s no longer intended to be used by users, only extensions.

What’s wrong with it?: Languages are now considered to be extensions as of PostgreSQL 9.1.

What to use instead: You can install new languages by installing it as an extension with CREATE EXTENSION <language name>. And to remove it, use the DROP equivalent. If you’ve upgraded your cluster to 9.1 or above from a previous version, you will still have the language installed but not as an extension. You can, however, convert it to an extension by using: CREATE EXTENSION <language name> FROM unpackaged. You can then remove it later with DROP EXTENSION.

Version 9.2

=> operator

Does it still work?: Only up to PostgreSQL 9.1, but as of PostgreSQL 9.2, no, at least as far as hstore is concerned. This was actually deprecated in 9.0 and has emitted warnings about using it since then. This is most notably used in the hstore extension. You can still create this operator, but it will return a warning when you do so. Be prepared for this to be completely disallowed in a future release.

What’s wrong with it?: “=>” is reserved in the SQL standard for named function parameters, so it needs to be available for such functionality.

What to use instead: If you’ve been using this in hstore, then it will require changing text=>text to hstore(text,text). If you’ve been using it as a custom operator, you should change it to something else as at some point it will be prohibited.

Literal language name case-sensitivity

Does it still work?: If you're on 9.1 or below, yes, but as of 9.2 you won't get away with this anymore.

What’s wrong with it?: Language names should be treated like an identifier rather than a literal, and in general, literals are case-sensitive. There was special code that case-folded the language name so that the letter casing didn't matter, but this change is the first step in removing string literals as language names altogether.

What to use instead: Just don't use single quotes around language names when writing your functions at all, rather than just lower-casing them. Either use no quotes (as they're not needed for any core language) or use double-quotes as you would with any other identifier.

There are also lots of configuration parameters that have been removed, and here they are:

Parameter	Removed in	Why?
australian_timezones	8.2	Better generalised timezone configuration
preload_libraries	8.2	Renamed to shared_preload_libraries
bgwriter_all_percent	8.3	No longer necessary
bgwriter_all_maxpages	8.3	No longer necessary
bgwriter_lru_percent	8.3	No longer necessary
redirect_stderr	8.3	Renamed to logging_collector
stats_block_level	8.3	Now covered by track_counts
stats_command_string	8.3	Renamed to track_activities
stats_reset_on_server_start	8.3	pg_stat_reset() can be used instead
stats_row_level	8.3	Now covered by track_counts
stats_start_collector	8.3	Now always enabled
explain_pretty_print	8.4	No longer needed
max_fsm_pages	8.4	No longer needed as per-relation free space maps deal with this.
max_fsm_relations	8.4	No longer needed as per-relation free space maps deal with this.
add_missing_from	9.0	Always defaulted to ‘off’ so now permanently off.
regex_flavor	9.0	Always defaulted to ‘advanced’ so now permanently set to this.
custom_variable_classes	9.2	Considered better to remove it as it only causes more maintenance with minimal benefit.
silent_mode	9.2	Not necessary as can be achieved with pg_ctl -l or NOHUP.
wal_sender_delay	9.2	New latch infrastructure has now made this setting redundant.