PostgreSQL left join lateral output in all cases

Question

CREATE TABLE public.temp (
    a text COLLATE pg_catalog."default",
    b text COLLATE pg_catalog."default",
    c text COLLATE pg_catalog."default",
    d text COLLATE pg_catalog."default",
    e numeric,
    pkey bigint NOT NULL,
    CONSTRAINT pkey_temp PRIMARY KEY (pkey) )

INSERT INTO public.temp(a, b, c, d, e, pkey)    VALUES
  ('xwlnormingoohlzr','some_value','tebwdlajnzypccgk','nnakygtgpvqxuayg',3276,1),
  ('bnlmykbfdexbrcwj','some_value','zxjlszfazsxpllcp','onlaqqddbsxnogyh',3360,2),
  ('hvjvlsyacstdlvog','some_value','xsznhgrjzhlxvspt','vosoulnvdxbfffer',62,3),
  ('zmgriuziltpbwfys','some_value','nzgnseflbvxcdqev','jefyxrdowtnwznve',1833,4),
  ('ziwhqxbcmbwjduji','some_value','gjiazbxnvkccusxe','wlgmphvqvapvflzi',3936,5),
  ('ldojloaothuwhsky','some_value','onpgbjkjwrjvdisw','ajpmkoshzcvqdsxp',3416,6),
  ('edtgbaqmpvfxqadz','other_value','ostuffegobykyiaf','gommhuppohcypppr',2754,7),
  ('vometxuiataxjdrd','other_value','zvwcularbgyiyrar','hdnamfmjkicgufxk',462,8),
  ('cqbpxyyiklhdvxcd','other_value','qrlvjmkijqohuvnb','hphrmykukcaoqmjy',63,9),
  ('mqwwnemddwynulwy','other_value','plbgdjniyqkzwxwm','waszvkkpinnjofet',59,10),
  ('zehtxhonbrfiiksw','other_value','oaowphudhtaupisp','ezgwzyiolgtehpou',920,11),
  ('ylhzfkyyxvdkftdk','other_value','kknyczaiihxacqjd','tzafbxojawhznmir',2528,12),
  ('dhlnmexaovbivudl','other_value','xbwqogpxaqssqjee','qecclksfpnbtugli',224,13);

I want to duplicate some rows that match some conditions as per the below :

select
    b,
    the_value
from public.temp
    left join lateral 
        unnest(array[-e,e]) as the_value
        on b = 'some_value'

For b = 'some_value', I would like to duplicate rows with one outputting e, the other -e; and for b = "other_value", want to output e.

The query above gives null when b is not 'some_value'. I understand that it is the expected behaviour but how do I output something in all cases ?

Currently I get :

"some_value"    "-3276"
"some_value"    "3276"
"some_value"    "-3360"
"some_value"    "3360"
"some_value"    "-62"
"some_value"    "62"
"some_value"    "-1833"
"some_value"    "1833"
"some_value"    "-3936"
"some_value"    "3936"
"some_value"    "-3416"
"some_value"    "3416"
"other_value"   
"other_value"   
"other_value"   
"other_value"   
"other_value"   
"other_value"   
"other_value"

And I would like to see :

"some_value"    "-3276"
"some_value"    "3276"
"some_value"    "-3360"
"some_value"    "3360"
"some_value"    "-62"
"some_value"    "62"
"some_value"    "-1833"
"some_value"    "1833"
"some_value"    "-3936"
"some_value"    "3936"
"some_value"    "-3416"
"some_value"    "3416"
"other_value"   "2754"
"other_value"   "462"
"other_value"   "63"
"other_value"   "59"
"other_value"   "920"
"other_value"   "2528"
"other_value"   "224"

The CREATE TABLE and INSERT are fine, but we need to see the actual result that you expect from that data. — user330315
– user330315, Commented May 9, 2019 at 5:50

Rémy Baron · Accepted Answer · 2019-05-09 07:10:18Z

1

you can simply do that :

  select b,unnest(CASE WHEN b='some_value' then array[-e,e] else array[e] end)  from public.temp

That's return the expected result

answered May 9, 2019 at 7:10

Rémy Baron

1,4099 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL left join lateral output in all cases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related