Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto first. But I don't know how to install it.

There is no pgcrypto if I try to install it using apt-get install pgcrypto and I don't find any files starting with pgcrypto in my system (I tried find / -name "pgcrypto*").

How do I install pgcrypto so I can use the digest('word-to-hash','sha1') function in my database queries?

Update: I'm struggling to install pgcrypto on another Ubuntu machine. After installing the package using sudo apt-get install postgresql-contrib-8.4 how do I install it to my current PostgreSQL database?

share|improve this question
Hi @Jonas, I'm not sure if you're having issues installing pgcrypto or if you're having issues getting it to work with your db install. It sounds like you have a repo issue. Verify this file exists on this path /usr/local/pgsql/share/contrib/pgcrypto.sql and let us know. –  jcolebrand Mar 25 '11 at 14:46
@jcolebrand: No I don't have a /usr/local/pgsql directory. The only place where I have *.sql files are in /usr/share/postgresql/8.4/ but no crypto-related. –  Jonas Mar 25 '11 at 15:00

2 Answers 2

up vote 13 down vote accepted

It's an external module for Postgres. You should install the postgresql-contrib-8.4 (or your pg version) package via apt:

apt-get install postgresql-contrib-8.4

Then you find the sql install file somewhere in the /usr/share/postgresql folder, and you'll need to run pgcryto.sql on the database.

psql -d <database> -f /usr/share/postgresql/8.4/contrib/pgcrypto.sql


$ cd /usr/share/postgresql/8.4/contrib
$ psql -d <database>
    psql (8.4.8)
    Type "help" for help.

    database=# \i pgcrypto.sql
share|improve this answer
When I run sudo apt-get install postgres-contrib I get E: Unable to locate package postgres-contrib –  Jonas Mar 25 '11 at 14:54
I googled and now I have installed it using sudo apt-get install postgresql-contrib-8.4 and then run \i <path-to-pgcrypto> using psql. –  Jonas Mar 25 '11 at 15:59
That's the way to do it. I slightly updated the answer to include the note on the version for the package –  DrColossos Mar 25 '11 at 17:19
Ah, the command was \i /usr/share/postgresql/8.4/contrib/pgcypto.sql –  Jonas May 24 '11 at 16:24
Under postgresql 9.1 you type in command line: CREATE EXTENSION pgcrypto –  Danubian Sailor Dec 30 '11 at 14:08

Note that I'm working on Ubuntu 12.04, which uses postgresql 9.1.

There, I needed to:

sudo apt-get install postgresql-contrib

And then in my database:

postgres@ztrustee:~$ psql test
psql (9.1.3)
Type "help" for help.
test=# CREATE EXTENSION pgcrypto;

And now I can use pgcrypto functionality, gen_random_bytes():

test=# create table test ( 
    not null 
    default encode( gen_random_bytes( 32 ), 'hex' ) 
    primary key, 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
test=# \d test
                            Table "public.test"
 Column | Type |                         Modifiers                          
 id     | text | not null default encode(gen_random_bytes(32), 'hex'::text)
 value  | text | 
    "test_pkey" PRIMARY KEY, btree (id)

test=# insert into test (value) VALUES ('scoobydoo');
test=# select * from test;
                                id                                |   value   
 76dd5bd0120d3df797f932fbcb4f8aa5088e215ee2b920dddbff59c8595fbac7 | scoobydoo
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.