Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to add a custom column with a default value in an sql query?

Tags:

sql

postgresql

So I'm doing a basic join query in SQL (postgres)...

SELECT first_name, last_name 
FROM table1, table2 
WHERE table1.id = table2.customer_id

In the returned result query is it possible to generate an extra column called "default_value" with a string value of "test" in each row returned, and if so how? I'm not trying to permanently alter the tables with new data, just add an extra column in the result set. The use case is to store the sql query in a Heroku dataclip so I can generate a csv report.

like image 598
Nona Avatar asked Oct 23 '14 22:10

Nona


1 Answers

Yes, that's quite easy:

select first_name, 
       last_name,
       'test' as default_value, --<< a "virtual" column containing a character value
       42 as the_answer         --<< another column containing a numeric value
from table1 
  join table2 on table1.id = table2.customer_id;

You should also stop using those outdated implicit joins in the WHERE clause. Use an explicit JOIN operator instead. It makes the queries more robust against accidentally forgotten join conditions.

like image 195
a_horse_with_no_name Avatar answered Oct 16 '22 11:10

a_horse_with_no_name