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,
francisco springerprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 springerprogrammerAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.