How to declare a variable and use it in an if statement in Postgres

Question

I'm wanting to create a variable based on a query result and then use it in an IF statement. I have this so far:

with variable as (select "Id" from public.tableName where "OtherId" = 24)

if variable notnull then
    insert into public.tableName ("OtherName", "OtherPhoneNumber", "OtherAddress", "Id")
    overriding system values
    values ('foo', '205-123-4567', '123 Sample Ln', variable)
end if

Postgres gives me an error that reads:

SQL Error [42601]: ERROR: syntax error at or near "if"

What am I doing wrong?

****edit: Id is not the primary key, as there is a different primary key named TableNameId****

I don't get the first CTE. If you expect Id = 24, then wouldn't you also know what the value of Id would be returned from the select? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 23, 2019 at 16:38
@TimBiegeleisen I guess it's supposed to be an IF EXISTS(…) — Bergi
– Bergi, Commented Dec 23, 2019 at 16:40
Unrelated to your question, but: you should really avoid those dreaded quoted identifiers. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Dec 23, 2019 at 17:13
Is "Id" the primary or a unique key? If yes, you can simply use insert ... on conflict do nothing instead — user330315
– user330315, Commented Dec 23, 2019 at 17:15
if Id is not unique then the whole IF doesn't make sense as the "variable" would only be able to contain a single value, not multiple. And to which value should variable be set anyway? The Name? If yes, then it seems rather strange that you want to store "a name" into an integer column. — user330315
– user330315, Commented Dec 23, 2019 at 17:23

GMB · Accepted Answer · 2019-12-23 16:40:47Z

1

If you want to check if a given id exists in one table and insert into another table if there is a match, you can use the insert ... select syntax as follows:

insert into public.tableName2 ("OtherName", "OtherPhoneNumber", "OtherAddress", "Id")
select 'foo', '205-123-4567', '123 Sample Ln', "Id"
from public.tableName1
where "Id" = 24

edited Dec 23, 2019 at 16:40

answered Dec 23, 2019 at 16:38

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

Colby Willoughby Over a year ago

I'm wanting this to take place in one table. I want to check for if a certain value exists, then add another value. If not, then don't do anything.

Bergi Over a year ago

@ColbyWilloughby It works with the same table instead of two separate tables just as well. However, then it looks like your Id column is not unique, which would be rather weird.

Collectives™ on Stack Overflow

How to declare a variable and use it in an if statement in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related