CODEWITHSUNDEEP
sqlpostgresql
pkberlin
pkberlin

Reputation: 852

PostgreSQL automatically create column from existing table

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

Upvotes: 0

Views: 51

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31726

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

Upvotes: 1

Related Questions