Set the default value in the PostrgreSQL table column for the value from the query

Question

I want to do the following

ALTER TABLE runs ADD COLUMN userId bigint NOT NULL DEFAULT (SELECT id FROM users WHERE email = '[e-mail]');

but he continues to give a syntax error. How could I do this? Any help is much appreciated .;)

Answer 1

try it

• Create a function to get idfrom the table usersusing emailas an argument.

  CREATE OR REPLACE FUNCTION id_in_users(iemail varchar) RETURNS int LANGUAGE SQL AS
  $$ SELECT id FROM users WHERE email = iemail; $$;
  

• And change the table

  ALTER TABLE runs ADD COLUMN userId bigint NOT NULL DEFAULT     
  id_in_users('[e-mail]');
  

---

SQL FIDDLE (DEMO)

Answer 2

You cannot do it on DEFAULT. However, you can use the trigger before checking the insert, if there is a value NULL.

You can check out the PostgreSQL Trigger Documentation here.