PostgreSQL values to unique dictionary
Do I have to write SQL for that manually? 


Yes, but it's not that hard:

create table original (id integer, url text);
insert into original 
values
(1,'VeryLongURLText'),
(2,'VeryLongURLText'),
(3,'LoooongURLText'),
(4,'LoooongURLText'),
(5,'LoooongURLText');


create the dictionary 

create table dictionary (id serial, url text);
insert into dictionary (url)
select distinct url
from original;


This creates the table with the following content:

id | data           
---+----------------
 1 | LoooongURLText 
 2 | VeryLongURLText


Now create a new table based on the dictionary:

create table compressed 
as
select o.id, o.some_column, o.other_column, d.id as dictionary_id
from original o
  join dictionary d on o.url = d.url;


As your goal is to reduce the space overhead it's better to create  new table with the dictionary id rather then altering the existing one. This will also be a lot faster then updating all rows from the existing table (with a billion rows this will however still take some time)