PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Can I remove a table from a schema without physically deleting it and then add it back to a schema?

I have an application that utilizes Advantage Database Server, and I want to migrate it to PostgreSQL.
To remove a table from the database dictionary, I use the following SQL query:

DROP TABLE table_name FROM DATABASE NO_DELETE;

The NO_DELETE keyword instructs the server not to delete the table files from the disk.
After that, I can re-add the deleted table to the database dictionary using the following procedure:

sp_AddTableToDatabase(
  TableName,CHARACTER,200,
  TablePath,CHARACTER,515,
  TableType,SHORTINT,
  CharType,SHORTINT,
  IndexFiles,MEMO,
  Comment,MEMO )

For example:

EXECUTE PROCEDURE sp_AddTableToDatabase('table_name', 'table_name.adt', 3, 1, '', '');

Is there such a possibility in PostgreSQL?

Tags:

2

Answers


  1. 0

    PostgreSQL doesn’t allow "re-linking" an external file as a table directly, you must Export then re-create the table.
    Search about & try Foreign Data Wrappers (FDW)
    Hope it helps

    Login or Signup to reply.
  2. 0

    Move it to another schema

    If you need to temporarily "hide" it, you can just move it to a different, non-default schema/namespace:
    demo at db<>fiddle

    create schema some_other_schema;
alter table table_name set schema some_other_schema;

    By default things are created and searched in schema public or whatever schema Postgres sees first in your search_path which means that once you move it elsewhere, it’s no longer accessible by anyone unless they explicitly reference it by a fully schema-qualified name.

    That should be enough to hide it but to make it truly inaccessible even to those that discover its new location, you can also revoke access to it.

    To bring back the table, you can move it back to public:

    alter table some_other_schema.table_name set schema public;

    And if you also revoked access, you can grant it back.

    If you wanted all referential constraints to be broken in the process, possibly to cascade the drop to other objects before bringing this one back, this won’t do that. Changing the namespace doesn’t affect referential integrity so all foreign keys still point at this table.

    Changing schema is a metadata-only operation and will not cause any sort of actual deletion or re-write of the table’s pages on disk.

    Hide it behind a view, then drop the view

    Dropping a view will break all referential constraints and let you cascade it while the actual table and data in it remain intact. It’s also a metadata-only operation.

    Detach it

    If you make it a partitioned table, you can alter table..detach the partition(s), then drop the table. All referential constraints will be broken and the drop will be able to cascade (or set null/set default).

    Afterwards, you can re-define the table and re-attach the partition(s). All of this is also metdata-only and (except for the cascaded part) doesn’t physically delete any rows from table’s pages on disk.

    Drop as a foreign object

    As mentioned by @Adel Alaa, you can set up the table as a foreign table. In that case, dropping it means just removing the link to the data, rather than actually removing the data from wherever it actually resides. If you set it up on a Postgres db, you can use postgres_fdw to link it. There’s file_fdw for things like CSV, and extensions that let you link objects from other RDBMS.

    It’s worth pointing out that some other RDBMS refer to databases as schemas and namespaces or vice versa. In PostgreSQL, you can have multiple schemas inside a single database and different objects can have the same identifier/name as long as they are in a different schema.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search