CODEWITHSUNDEEP
sqlpostgresqlsql-insert
breakingduck
breakingduck

Reputation: 85

Add rows to SQL table based on a list of values

So I have a table like this and an attribute list - ('attr1', 'attr2', 'attr3').

id | attr_name | attr_value
----------------------------
 1 |   attr1   |  val1 
 1 |   attr2   |  val2
 1 |   attr3   |  val3 
 2 |   attr1   |  val1
 2 |   attr2   |  val4

I want to make a query where this table can be "expanded" to be the following and then make a query using it.

id | attr_name | attr_value
----------------------------
 1 |   attr1   |  val1 
 1 |   attr2   |  val2
 1 |   attr3   |  val3 
 2 |   attr1   |  val1
 2 |   attr2   |  val4
 2 |   attr3   |  null

The attribute list is given to me and dynamic.

Upvotes: 0

Views: 513

Answers (3)

sticky bit
sticky bit

Reputation: 37487

Cross join the IDs and the attribute names and then left join the table to get the attribute values or NULL if no match was found.

SELECT x1.id,
       x2.attr_name,
       t2.attr_value
       FROM (SELECT DISTINCT
                    t1.id
                    FROM elbat t1) x1
            CROSS JOIN (VALUES ('attr1'),
                               ('attr2'),
                               ('attr3')) x2 (attr_name)
            LEFT JOIN elbat t2
                      ON t2.id = x1.id
                         AND t2.attr_name = x2.attr_name
       ORDER BY x1.id,
                x2.attr_name;

Upvotes: 2

Akina
Akina

Reputation: 42739

WITH 
ids AS ( SELECT DISTINCT id
         FROM sourcetable ),
attrs AS ( SELECT DISTINCT attr_name
           FROM sourcetable )
INSERT INTO sourcetable (id, attr_name)
SELECT id, attr_name
FROM ids, attrs
EXCEPT
SELECT id, attr_name
FROM sourcetable

Upvotes: 1

Warsime
Warsime

Reputation: 1

sorry if this seems obvious, but did you try

INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')

?

Then when you query your table it should contain the value

Edit: also you may want to have a unique key

Upvotes: 0

Related Questions