[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1800
  • Last Modified:

how to set column default in postgresql?

Hello,

I would like to set image1 column default to the value of item_uuid appended by a string.
For example:  image1 = "1ac603c8-3b84-4804-abd1-dcd03d8f62de" || "appended string"
Can I do this without using a trigger?


CREATE TABLE item
(
  itemid integer NOT NULL DEFAULT nextval('item_itemid_seq'::regclass),
  name character varying(150),
  item_uuid uuid DEFAULT uuid_generate_v4(),
  image1 character varying(100)
)

I created the below function:

CREATE OR REPLACE FUNCTION item_image_name(item)
  RETURNS text AS
$BODY$
BEGIN
  select $1.item_uuid::text;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION item_image_name(item)
  OWNER TO postgres;

when I tried to apply the default value as below to image1, I get this error
ERROR: cannot use column references in default expression
SQL state: 42P10

ALTER TABLE item ALTER COLUMN image1 SET DEFAULT item_image_name(item)


Thanks,
0
francisco springer
Asked:
francisco springer
  • 2
1 Solution
 
earth man2Commented:
CREATE TABLE item ( itemid serial,
  name character varying(150),
  item_uuid uuid,
  image1 character varying(100));

CREATE OR REPLACE FUNCTION item_defaults() RETURNS trigger AS $$
BEGIN        
  IF NEW.item_uuid IS NULL THEN
    NEW.item_uuid := uuid_generate_v4();
  END IF;
  IF NEW.image1 IS NULL THEN
    NEW.image1 := NEW.item_uuid || '.jpeg';
  END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER item_001_trg BEFORE INSERT OR UPDATE ON item
 FOR EACH ROW EXECUTE PROCEDURE item_defaults();
insert into item values ( default,default,default,default );
INSERT 0 1
postgres=# insert into item values ( default,default,default );
INSERT 0 1
postgres=# select * from item;
 itemid | name |              item_uuid               |                  image1
--------+------+--------------------------------------+-------------------------------------------
      3 |      | 2f35638d-0c49-4c4d-bc22-2db8c6f112a9 | 2f35638d-0c49-4c4d-bc22-2db8c6f112a9.jpeg

Open in new window

(1 row)
0
 
earth man2Commented:
I don't think you can do this without using a trigger function.
See docs about suppress_redundant_updates_trigger if you need that behaviour.
0
 
francisco springerAuthor Commented:
thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now