CODEWITHSUNDEEP
postgresql
Nulik
Nulik

Reputation: 7360

How do I set default value when creating table from a composite TYPE?

I want to set default values for fields of a table which is created from a composite type, but I am getting an error:

PLSQL $ cat ../animal2.sql 
DROP TYPE IF EXISTS zoo_t CASCADE;
DROP TYPE IF EXISTS animal_t;
CREATE TYPE animal_t AS (
    animal_id           integer,
    animal_color        varchar,
    animal_name         varchar
);
CREATE TYPE zoo_t AS (
    wolf_object         animal_t,
    bear_object         animal_t    
);
CREATE TABLE zoopark OF zoo_t (
    (wolf_object).animal_color  WITH OPTIONS DEFAULT 'black'
);
 PLSQL $ psql dev < ../animal2.sql 
DROP TYPE
DROP TYPE
CREATE TYPE
CREATE TYPE
ERROR:  syntax error at or near "("
LINE 2:  (wolf_object).animal_color  WITH OPTIONS DEFAULT 'black'
         ^
PLSQL $

What is the problem here?

Upvotes: 2

Views: 732

Answers (1)

Roman Tkachuk
Roman Tkachuk

Reputation: 3266

You can not set field directly by DEFAULT. But you can set whole field:

CREATE TABLE zoopark OF zoo_t (
    wolf_object  WITH OPTIONS DEFAULT (NULL, 'black', NULL)
);

Or you can create TRIGGER and set animal_color if it's unset but field wolf_object is not null, or create whole field if it null

Upvotes: 3

Related Questions