CODEWITHSUNDEEP
sqlpostgresql
Mahir Zukic
Mahir Zukic

Reputation: 532

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

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. ;)

Upvotes: 25

Views: 13298

Answers (2)

Vivek S.
Vivek S.

Reputation: 21945

  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)

Upvotes: 37

JCalcines
JCalcines

Reputation: 1286

You can't do that on DEFAULT. However you could use a trigger before insert checking if there is a NULL value.

You can check the PostgreSQL Trigger Documentation here

Upvotes: 6

Related Questions