Query Data

Select

Using select * will affect performance for the appliocation and network. Avoid using this

Concatenation

Select statement support concatenation, this is by using ||. For example:

select first_name || ' ' || last_name, email from customer;

This will result into something like this

       ?column?        |                  email
-----------------------+------------------------------------------
 Jared Ely             | [email protected]
 Mary Smith            | [email protected]
 Patricia Johnson      | [email protected]
 Linda Williams        | [email protected]
 Barbara Jones         | [email protected]
 Elizabeth Brown       | [email protected]
 Jennifer Davis        | [email protected]

The ?column? is the default if the column has no name. To fix this, we can use expression as colum_name statement the assign a name

select first_name || ' ' || last_name as customer_name, email from customer;

The as keyword is optional so without it still work (this also apply for column alias as well)

select first_name || ' ' || last_name customer_name, email from customer;

     customer_name     |                  email
-----------------------+------------------------------------------
 Jared Ely             | [email protected]
 Mary Smith            | [email protected]
 Patricia Johnson      | [email protected]
 Linda Williams        | [email protected]
 Barbara Jones         | [email protected]
 Elizabeth Brown       | [email protected]
 Jennifer Davis        | [email protected]

Retrieve function result

The from is optional. You can use select to get a function result

select now();

This will return the current date.

Select distict

When using distinct with multiple columns, it will use the combination of columns to evaluate duplicate.

select distinct * from table_name;

This will use all the column for evaluation. But watch out the ID column

Distinct On

Distinct on (column_1) column_1, column_2 will make sure that only 1 value of the same (column_1) is kept. For example:

If we select distinct, we might have multiple red in bcolor.

postgres@localhost:dvdrental> select distinct bcolor, fcolor from colors;
+--------+--------+
| bcolor | fcolor |
|--------+--------|
| <null> | <null> |
| red    | <null> |
| blue   | red    |
| red    | red    |
| green  | green  |
| blue   | blue   |
+--------+--------+

postgres@localhost:dvdrental> select distinct on (bcolor) bcolor, fcolor from colors;
+--------+--------+
| bcolor | fcolor |
|--------+--------|
| blue   | blue   |
| green  | green  |
| red    | red    |
| <null> | <null> |
+--------+--------+
SELECT 4
Time: 0.007s

Doing distinct on bcolor will make sure that the bcolor is unique

Order by

Order by can be desc and asc.

ORDER BY expression [ASC|DESC] [NULLS FIRST | NULLS LAST]

Order by another field

Or we can order by another column

select first_name, length(first_name) len from customer order by len desc;

Filter nulls value first

create table num_order(num int);

insert into num_order(num) values (1),(2),(null);

select num from num_order order by num nulls first

by default order by will place null after other values. The default of psql display null as empty string as well. To make it clearer we can set it to a different value

\pset null null

dvdrental=# \pset null null
Null display is "null".
dvdrental=# select num from sort_demo order by num nulls first;
 num
------
 null
    1
    3
   10