PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Preserve the column names when selecting part of the columns?

I have a table

CREATE TABLE policyholder(policyholder_id text, fname text, lname text, risk_score text, high_risk text);
INSERT INTO policyholder VALUES
('PH3068','ADAM','OCHSENBEIN','88','1'),
('PH3069','MALINDA','MEHSERLE','42','0'),
('PH3070','SANDRA','KUHTA','20','0');

and I want to get a table AS

CREATE TABLE mod(id text, others text);

INSERT INTO mod
SELECT policyholder_id, row_to_json(row(fname, lname, risk_score, high_risk)) from policyholder;

i.e., one column as id, and others as json.

However, we have

 policyholder_id |                     row_to_json                     
-----------------+-----------------------------------------------------
 PH3068          | {"f1":"ADAM","f2":"OCHSENBEIN","f3":"88","f4":"1"}
 PH3069          | {"f1":"MALINDA","f2":"MEHSERLE","f3":"42","f4":"0"}
 PH3070          | {"f1":"SANDRA","f2":"KUHTA","f3":"20","f4":"0"}

bcuz the ROW() drops column name.

I don’t want to create a new type because there is too much of this function.

I notice a similar question How to create a row without losing the column names, for input of row_to_json?

However, I failed to figure out how to apply it to this situation…

Tags:

2

Answers


  1. 0

    The first version below is a partial solution.

    The second version matches your desired result.

    =# SELECT policyholder_id, row_to_json((p.*)) from policyholder AS p;
 policyholder_id |                                             row_to_json                                             
-----------------+-----------------------------------------------------------------------------------------------------
 PH3068          | {"policyholder_id":"PH3068","fname":"ADAM","lname":"OCHSENBEIN","risk_score":"88","high_risk":"1"}
 PH3069          | {"policyholder_id":"PH3069","fname":"MALINDA","lname":"MEHSERLE","risk_score":"42","high_risk":"0"}
 PH3070          | {"policyholder_id":"PH3070","fname":"SANDRA","lname":"KUHTA","risk_score":"20","high_risk":"0"}
(3 rows)

    Here is a version where I remove the policyholder_id value from the JSON. This seems to require casting as jsonb:

    =# SELECT policyholder_id, row_to_json((p.*))::jsonb - 'policyholder_id' AS json FROM policyholder AS p;
 policyholder_id |                                      json                                       
-----------------+---------------------------------------------------------------------------------
 PH3068          | {"fname": "ADAM", "lname": "OCHSENBEIN", "high_risk": "1", "risk_score": "88"}
 PH3069          | {"fname": "MALINDA", "lname": "MEHSERLE", "high_risk": "0", "risk_score": "42"}
 PH3070          | {"fname": "SANDRA", "lname": "KUHTA", "high_risk": "0", "risk_score": "20"}
(3 rows)
    Login or Signup to reply.
  2. 0

    Insert Into using Select does not change the column name.

    CREATE TABLE mod(id text, others text);

INSERT INTO mod
SELECT policyholder_id, row_to_json(row(fname, lname, risk_score, high_risk)) 
FROM policyholder;
Select * From mod;

    The above command is ok. If you want to change the definition of a table use DDL commands (Create, Alter …). Insert, Select, Update will never change the table difinition.

    However, if you run just the Select command…

    SELECT policyholder_id, row_to_json(row(fname, lname, risk_score, high_risk)) 
FROM policyholder;

    … then the second column will have label row_to_json because you didn’t name the column… Use alias like below

    SELECT policyholder_id, 
       row_to_json(row(fname, lname, risk_score, high_risk))  as others  -- alias is others
FROM policyholder;

    See the fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search