CODEWITHSUNDEEP
sqlpostgresql
xCloudx8
xCloudx8

Reputation: 721

Multiple columns in one SQL

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?

Upvotes: 0

Views: 53

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

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.

Upvotes: 1

Gary Olsson
Gary Olsson

Reputation: 1217

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

Upvotes: 0

Related Questions