Postgresql insert from select query, plus static values

insertpostgresql

Referring to How to insert values into a table from a select query in PostgreSQL?,

I would like to INSERT into a table rows from another, specified by a SELECT DISTINCT, plus some static values, something like:

INSERT INTO new_tbl (column1, column2, column3)
SELECT DISTINCT id FROM -- long where clause --, 
  'a string', 0;

So that every row in the new table will get the same values for column2 and column3 Is this possible?

Best Answer

You can put static values into SELECT clause.

INSERT INTO new_tbl (column1, column2, column3)
    SELECT DISTINCT id, 'a string', 0 FROM -- long where clause --;

Basic `INSERT`

INSERT INTO item_tags (item_id, tag_id)
SELECT p.item_id, t.id
FROM  (SELECT item_id FROM properties WHERE name LIKE 'body') p
    , (SELECT id FROM tags WHERE name ILIKE '%hoax%') t

Where the comma (,) in the FROM clause is short notation for CROSS JOIN.

That's building on your assertion that the second query on tags ...

returns 1 row of tag_id

You get a Cartesian Product from a CROSS JOIN, that's n*m combinations.
If both sides of the join return more than one row, they multiply each other.
If either side does not return a row, the overall result is no row and nothing happens.
If you want neither of those effects, one side must return at least one row, and the other side exactly one row.

For your specific combination you can also use a nested subselect:

INSERT INTO item_tags (item_id, tag_id)
SELECT item_id 
     , (SELECT id FROM tags WHERE name ILIKE '%hoax%') AS tag_id  -- single result!
FROM   properties
WHERE  name LIKE 'body';

The subtle difference: This inserts all rows returned from the outer SELECT, even if the subselect on tags returns no row, which would be converted to NULL for tag_id.

Bulk `INSERT`

To deal with lots of such inserts at once, you would use a largely modified query. But the same rules as for the first query above apply for cardinalities in both joined tables.

INSERT INTO item_tags (item_id, tag_id)
SELECT p.item_id, t.id
FROM  (
   VALUES
      ('body',  'hoax')
    , ('body2', 'fun')
    , ('body3', 'love')
  ) AS it(item, tag)
JOIN   properties p ON p.name LIKE it.item
JOIN   tags t ON t.name ILIKE ('%' || it.tag ||'%');

You see the logic, right? Personally, I call this a "CROSS JOIN by proxy".

Sql-server – Count distinct values of a column and insert them to another table

Try using

SUM(Case WHEN problem Like '%err1%' THEN 1 ELSE 0 END) //for problem count

'%err1%' is a sample error containing letter to rectify which error is this.

Best Answer

Related Solutions

Postgresql – INSERT from a SELECT combined with a single value

Basic INSERT

Bulk INSERT

Sql-server – Count distinct values of a column and insert them to another table

Related Question

Basic `INSERT`

Bulk `INSERT`