0

According to my reading of the PostgreSQL documentation on ALTER TYPE I should be able to do the following:

CREATE TYPE compfoo AS OBJECT (f1 int, f2 text);
CREATE TYPE compbar AS OBJECT (f1 int, f2 compfoo);
ALTER TYPE compbar DROP ATTRIBUTE f2;
ALTER TYPE compbar ADD ATTRIBUTE f2 varying(1000);
ALTER TYPE compbar ADD ATTRIBUTE f3 compfoo;

However, when I try it, I get the following error:

ERROR: ALTER TYPE ADD/DROP COLUMN statement not supported for object types

What am I missing here? Is there a server configuration that will allow me to do this? Is it because the type in question is a composite OBJECT type, as opposed to a composite of 'basic' types?

UPDATE 2015-02-10 I've updated the title, and the samples to more closely resemble the problem at hand. In short, within Postgres Plus Advanced Server, there's additional syntax for creating types, in the form CREATE TYPE foo AS OBJECT <--- that syntax is the root of the issue. You cannot alter attributes when the composite type was created AS OBJECT.

rbellamy
  • 5,683
  • 6
  • 38
  • 48
  • Looks like a typing error! You are creating `combbar` (in line 2) and then expecting `compbar` to exist (in line 3), which obviously wouldn't! – Robins Tharakan Feb 10 '16 at 08:28
  • It's a typo... I've fixed it. – rbellamy Feb 10 '16 at 16:53
  • I rolled the question back to its previous version. Altering the question to hide the original errors makes the answers look silly instead of the question, IMHO. – wildplasser Feb 11 '16 at 00:07
  • @wildplasser it was NOT hiding the original error - it was updating for clarity. The original error was something else entirely. Seriously, either close the question with the typo flag so I can resubmit with better typing skills, or let me fix it... – rbellamy Feb 11 '16 at 04:34
  • What is the protocol if I have the answer (as in my update)? Just leave it as part of the question, or submit my own answer? – rbellamy Feb 11 '16 at 04:38

1 Answers1

0
CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TYPE combbar AS (f1 int, f2 compfoo);
ALTER TYPE compbar DROP ATTRIBUTE f2;
---------------^ maybe you should write combbar?

To make it clear you are defining a type named "combbar" and then you try to alter a type with the name "compbar".

UPDATE

Besides that typo, the SQL seems to run fine.

Bas Bossink
  • 9,388
  • 4
  • 41
  • 53
devanand
  • 5,116
  • 2
  • 20
  • 19