How to check in a table if a column has a certain value?

Question

I need help with a query in postgress , i am trying to check if a column has a value or not and to do an insert

I have two databases on local and on is in server and in this table ccpt_data.user_role_permission in column user_role_name I have permission :

Locally I have this query and works :
   INSERT INTO ccpt_data.user_role_permission(user_role_name, permission_name)
    VALUES ('manager','MANAGE_SENSITIVE_DATA');

For the server will be something 
INSERT INTO ccpt_data.user_role_permission(user_role_name, permission_name)
VALUES ('ROLE_PROJECT_MANAGER','MANAGE_SENSITIVE_DATA');

But I can't do it like this so I need if-else condition, to check for example if at ccpt_data.user_role_permission in user_role_name has a manager than I will do this and else the other this, so i am trying on something but as I don't know Postgres I need some help. I am trying :

DO
$do$
BEGIN
IF EXISTS (SELECT  FROM ccpt_data.user_role_permission Where user_role_name = manager ) THEN
INSERT INTO ccpt_data.user_role_permission(user_role_name, permission_name)
VALUES ('ROLE_PROJECT_MANAGER','MANAGE_SENSITIVE_DATA');
ELSE 
   INSERT INTO ccpt_data.user_role_permission(user_role_name, permission_name)
    VALUES ('manager','MANAGE_SENSITIVE_DATA');
END IF;
END
$do$

score 0 · Answer 1 · answered May 16 '19 at 11:28

I don't see what's wrong with what you have, other than missing something between SELECT and FROM and that you need to put manager in quotes. It should work.

But if you want to avoid procedural code you can use a CASE expression returning 'ROLE_PROJECT_MANAGER' if 'manager' exists and 'manager' otherwise.

For example in an INSERT ... SELECT ... (but it should also work to put it in a VALUES clause if you prefer that):

INSERT INTO ccpt_data.user_role_permission
            (user_role_name,
             permission_name)
            SELECT CASE
                     WHEN EXISTS (SELECT *
                                         FROM ccpt_data.user_role_permission
                                         WHERE user_role_name = 'manager') THEN
                       'ROLE_PROJECT_MANAGER'
                     ELSE
                       'manager'
                   END,
                   'MANAGE_SENSITIVE_DATA';

I just need the quotes I fixed already but thank you a lot, you teach me sth thanks — Ilia Tapia, May 16 '19 at 11:30

score 0 · Accepted Answer · answered May 16 '19 at 11:30

What you said is a upsert function, it can be implemented by CTE(with clause) in PostgreSQL.As below:

select * from test;
 id | result 
----+--------
  1 | data1
(1 row)

with upsert as (
update test set result='data10' where id=3 returning *
)
insert into test select 3,'data10' where not exists (select 1 from upsert where id = 3);
INSERT 0 1

select * from test;
 id | result 
----+--------
  1 | data1
  3 | data10
(2 rows)

with upsert as (
update test set result='data22' where id=3 returning *
)
insert into test select 3,'data22' where not exists (select 1 from upsert where id = 3);
INSERT 0 0

select * from test;
 id | result 
----+--------
  1 | data1
  3 | data22

How to check in a table if a column has a certain value?

2 Answers2