[SOLVED] PostgreSQL conditional expressions

PostgreSQL conditional expressions

Is there a way to write two insert statements if a javascript object's value is not an empty string, or one insert statement if it is an empty string in a postgreSQL query using node-postgres?

My database table "job" setup:

CREATE TABLE "job" (
  "id" SERIAL PRIMARY KEY,
  "company" VARCHAR (20) NOT NULL
);

For example, I would like this object to insert two rows:

{
  company: 'Apple',
  company_two: 'Google'
}

And I would like this object to insert one row:

{
  company: 'Facebook',
  company_two: ''
}

If the javascript object has a value for "company_two", I would like to add a second row to the database.

Here is what I have working to insert one row, but this doesn't take the other property into account:

pool.query('INSERT INTO "job" ("company") VALUES $1', [testObject.company]);

Solution

You can "unnest" the JSON to rows, and then use that as the source for an insert:

insert into job (company)
select t.comp
from jsonb_each_text('{
              "company": "Apple",
              "company_two": "Google"
              }'::jsonb) t(k,comp)
where t.comp <> '';

will insert both companies.

And this will only insert one row:

insert into job (company)
select t.comp
from jsonb_each_text('{
              "company": "Facebook",
              "company_two": ""
              }'::jsonb) t(k,comp)
where t.comp <> '';