PostgreSQL – Relevance of ‘SET default_with_oids = true’ in a PostgreSQL Dump

postgresqlpostgresql-8.4

I have an SQL backup from an old database which may have been a PostgreSQL 7 or 8 database I am importing which contains a SET default_with_oids - true statement, into a an existing 8.4 database and wonder what the relevance is and how it might affect the existing database.

I don't think the OIDs were actually used in the original database. Can I simply ignore it?

Best Answer

The documentation states the following:

default_with_oids (boolean)

This controls whether CREATE TABLE and CREATE TABLE AS include an OID column in newly-created tables, if neither WITH OIDS nor WITHOUT OIDS is specified. It also determines whether OIDs will be included in tables created by SELECT INTO. The parameter is off by default; in PostgreSQL 8.0 and earlier, it was on by default.

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.

Note that the feature was deprecated with the coming of 8.0.

This means that if you plan to use the DB as it is, you can safely remove this line before restoring (you have to be really sure that they are not referred to). It will be left set to the default false.

If you have a legacy application working on this DB, you will want to check it, too, if it uses the OIDs in any way. (At the moment I cannot really think of such a use case, but it may be the weakness of my imagination.)

A side note: you may experience some other issues restoring the dump - I remember some problems with changed implicit cast behaviour.

Related Question