Set default value in PostrgreSQL table column to a value from a query

Question

I want to do the following

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

but it keeps giving me syntax error. How could I do this guys? Any help is highly appreciated. ;)

Answer 1

1. Create a function to get the id from the table users with email as an arg.

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

2. And alter the table

   ALTER TABLE runs ADD COLUMN userId bigint NOT NULL DEFAULT     
   id_in_users('[email protected]');
   

---

SQL FIDDLE(DEMO)