How to add a column to typed table without altering type in PostgreSQL?

Question

I am using PostgreSQL 9.5, I have a TYPE which discribes a collection of columns:

CREATE TYPE datastore.record AS
   (recordid bigint,
    ...
    tags text[]);

I have created many tables reliying on this TYPE:

CREATE TABLE datastore.events
OF datastore.record;

Now I would like to add a column to a table which rely on this TYPE without updating the TYPE. I think it is impossible as this, thus I am wondering if there is a way to unbind my table from this TYPE without losing any data or copying the table into a temporary table?

score 6 · Accepted Answer · answered Dec 27 '16 at 09:18

6

There is a special option not of for this purpose. Per the documentation:

NOT OF - This form dissociates a typed table from its type.

So:

alter table my_table not of;
alter table my_table add new_column integer;

answered Dec 27 '16 at 09:18

klin

112,967
15
204
232

Thank you, it worked. I was not aware about the NOT OF Keyword. All my relation are preserved, – jlandercy Dec 27 '16 at 09:19
Awesome. I didn't know about the `not of` – Francisco Puga Dec 27 '16 at 09:22

score 2 · Answer 2 · answered Dec 27 '16 at 10:10

2

If you don't want to break relations:

--drop table if exists t2;
--drop table if exists t1;
--drop type if exists tp_foo;

create type tp_foo as (i int, x int);

create table t1 of tp_foo;
create table t2 (y text) inherits(t1);

alter type tp_foo add attribute z date cascade;

select * from t2;

answered Dec 27 '16 at 10:10

Abelisto

14,826
2
33
41

Thank you for sharing this method. Just tested, it will ease my maintenances. – jlandercy Dec 27 '16 at 11:54

How to add a column to typed table without altering type in PostgreSQL?

2 Answers2