ll alias (ls -l) is not in ubuntu

If you come from a red-hat based distro and are using ubuntu for the first time (just like me) one thing you may have noticed is that ll alias is not set by default, to do that just edit your ~/.bashrc and uncomment or add the line "alias ll='ls -l'" now the next time you start a shell window that alias will be loaded.


# some more ls aliases
alias ll='ls -l'
#alias la='ls -A'
#alias l='ls -CF'



Porting mysql group_concat to postgres

There's a very useful aggregate function in mysql that allows you to concatenate several row values into one single row value. Here is an example:



mysql> create table t1 (
-> t1_id numeric,
-> col2 varchar(50)
-> );
Query OK, 0 rows affected (0.18 sec)

mysql> create table t2 (
-> t2_id numeric,
-> t1_id numeric,
-> col2 varchar(50)
-> );
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t1 values (1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into t2 values (1,1,'d'),(2,1,'f'),(3,2,'g'),(4,3,'f'),(5,3,'h'),(6,3,'i'),(7,3,'j');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+-------+------+
| t1_id | col2 |
+-------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+-------+------+
3 rows in set (0.01 sec)

mysql> select * from t2;
+-------+-------+------+
| t2_id | t1_id | col2 |
+-------+-------+------+
| 1 | 1 | d |
| 2 | 1 | f |
| 3 | 2 | g |
| 4 | 3 | f |
| 5 | 3 | h |
| 6 | 3 | i |
| 7 | 3 | j |
+-------+-------+------+
7 rows in set (0.00 sec)

mysql> select *,(select group_concat(col2) from t2 where t1_id = t.t1_id) from t1 as t;
+-------+------+-----------------------------------------------------------+
| t1_id | col2 | (select group_concat(col2) from t2 where t1_id = t.t1_id) |
+-------+------+-----------------------------------------------------------+
| 1 | a | d,f |
| 2 | b | g |
| 3 | c | f,h,i,j |
+-------+------+-----------------------------------------------------------+
3 rows in set (0.17 sec)


Postgres as you may have noticed doesn't have a function like this, so I created my own approach:

Remember that you should have enabled pspgsql for your database previously:

In the shell you must type :

createlang plpgsql -Upostgres -d template1



template1=# CREATE FUNCTION concat (text, text) RETURNS text AS '
template1'# DECLARE
template1'# result text;
template1'# BEGIN
template1'# IF $1 is not null THEN
template1'# result := $1 || $2;
template1'# END IF;
template1'#
template1'# RETURN result;
template1'# END;
template1'# ' LANGUAGE plpgsql;
CREATE FUNCTION

template1=# CREATE AGGREGATE row_concat(
template1(# sfunc = concat,
template1(# basetype = text,
template1(# stype = text,
template1(# initcond = ''
template1(# );
CREATE AGGREGATE


template1=# create table t1 (
template1(# t1_id numeric,
template1(# col2 varchar(50)
template1(# );
CREATE TABLE
template1=#
template1=# create table t2 (
template1(# t2_id numeric,
template1(# t1_id numeric,
template1(# col2 varchar(50)
template1(# );
CREATE TABLE

template1=# insert into t1 values (1,'a');
INSERT 0 1
template1=# insert into t1 values (2,'b');
INSERT 0 1
template1=# insert into t1 values (3,'c');
INSERT 0 1

INSERT 0 1
template1=# insert into t2 values (1,1,'d');
INSERT 0 1
template1=# insert into t2 values (2,1,'f');
INSERT 0 1
template1=# insert into t2 values (3,2,'g');
INSERT 0 1
template1=# insert into t2 values (4,3,'f');
INSERT 0 1
template1=# insert into t2 values (5,3,'h');
INSERT 0 1
template1=# insert into t2 values (6,3,'i');
INSERT 0 1
template1=# insert into t2 values (7,3,'j');
INSERT 0 1

template1=# select *,(select row_concat(col2) from t2 where t1_id = t.t1_id) from t1 as t;
t1_id | col2 | ?column?
-------+------+----------
1 | a | df
2 | b | g
3 | c | fhij
(3 rows)




I didn't want to set a comma as the default separator so the user can customize easily the string. Another important thing is that if there is a null value in any row then the result will be null, like most of the aggregate functions.


template1=# select *,(select row_concat(coalesce(col2,'') || ' , ') from t2 where t1_id = t.t1_id) from t1 as t;
t1_id | col2 | ?column?
-------+------+------------------
1 | a | d , f ,
2 | b | g ,
3 | c | f , h , i , j ,
(3 rows)