PostgreSQL automatically create column from existing table

Question

I am using postgreSQL and I would like to automatically insert the value of username from table users in the profiles table as handle when i create a new user.

Is this possible by means of an SQL statement?

For clearifing:

First i add a new user:

insert into users (email, user_name, first_name, last_name, active, created_at) values ('[email protected]', 'johndoe', 'John', 'Doe', true, '2018-03-16T17:39:54Z');

After that i create a profile for this user:

insert into profiles (user_id, handle, name, city) VALUES (2, 'johndoe', 'John Doe', 'Tawala');

With this step, the value of user_name should be automatically written to the handle column in the profiles table without doing so manually. Are there solutions like a function in SQL?

Here are my tables:

CREATE TABLE IF NOT EXISTS users (
  id serial UNIQUE NOT NULL,
  email varchar(255) NOT NULL,
  user_name varchar(255) NOT NULL,
  first_name varchar(255) NOT NULL,
  last_name varchar(255) NOT NULL,
  active bool DEFAULT TRUE,
  created_at DATE
);

CREATE TABLE IF NOT EXISTS profiles (
  id serial UNIQUE NOT NULL,
  user_id serial NOT NULL,
  handle varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  city varchar(255) NULL,
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON 
  UPDATE CASCADE
);

Do you want a new row in profiles to be created whenever there is an INSERT on users? — Laurenz Albe
– Laurenz Albe, Commented Feb 28, 2019 at 11:33
@LaurenzAlbe The row handle exists in profiles when i create the new user. — pkberlin
– pkberlin, Commented Feb 28, 2019 at 11:35
Then I don't understand your question. Can you clarify that. — Laurenz Albe
– Laurenz Albe, Commented Feb 28, 2019 at 11:47

Kaushik Nayak · Accepted Answer · 2019-02-28 17:30:17Z

1

Yes, you may combine them using a WITH clause INSERT

with ins1
AS
(
  insert into users (email, user_name, first_name, last_name, active, created_at) 
   values ('[email protected]', 'johndoe', 'John', 'Doe', true, '2018-03-16T17:39:54Z')
       returning id as ret_user_id, user_name as ret_user_name 
 )      
  insert into profiles (user_id, handle, name, city) 
     select ret_user_id, ret_user_name ,'John Doe', 'Tawala' 
from ins1; --takes from the returning output.

Demo

answered Feb 28, 2019 at 17:30

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pkberlin Over a year ago

Thanks, that helps me doing what i want ;)

Collectives™ on Stack Overflow

PostgreSQL automatically create column from existing table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related