Multiple columns in one SQL

Question

Can i insert multiple values from different columns in one?

i have:

ref | alt | couple_refalt
--------------------------
 A     C       AC ( i want)
 A     G       AG         Etc...

Is there a simple way?

I tried with:

INSERT INTO refalt(couple_refalt)
SELECT ref||alt
FROM refalt
WHERE ref='A';

Is it correct?

But it gives me the error:

null value in column violates not-null constraint

Postgres want a value for each colum, why can't i update or insert into specific column?

You're on the wrong track... Never, ever store data as comma separated items. It will only cause you lots of trouble! — jarlh
– jarlh, Commented Aug 11, 2016 at 10:35
Ok Thanks i'll chage the way i want to store it, but the SQL is it correct? — xCloudx8
– xCloudx8, Commented Aug 11, 2016 at 10:41
To achieve what you want you may replace (ref,alt) by ref || alt. But jarlh is right - it is bad design. — serges_newj
– serges_newj, Commented Aug 11, 2016 at 10:41
The correct syntax is insert into t1 (c1, c2, ...) select ca, cb, ... from t2 ... — jarlh
– jarlh, Commented Aug 11, 2016 at 10:48
Seems correct but here's a error: null value in column violates not-null constraint Error row contains: (null, null, AG ) — xCloudx8
– xCloudx8, Commented Aug 11, 2016 at 10:48

Gordon Linoff · Accepted Answer · 2016-08-11 10:41:39Z

1

Storing comma separated value is not the SQLish way to store values. What you seem to want is a computed column. Postgres does not support that directly. One method is to declare a view:

create view v_refault
    select r.*, ref || ',' || alt
    from refault;

Other possibilities are:

Define a trigger to maintain the value.
Concatenate the values at the application level.
Use a function-based method to emulate a computed column.

answered Aug 11, 2016 at 10:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Gary Olsson · Accepted Answer · 2016-08-11 10:40:58Z

0

In order to insert two values into one column, you need to concatenate them. In postgresql the syntax is the following.

SELECT ref::text || ', ' || alt::text FROM refalt

If you want more details, here is the string documentation

answered Aug 11, 2016 at 10:40

Gary Olsson

1,21722 silver badges34 bronze badges

Collectives™ on Stack Overflow

Multiple columns in one SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related