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?

  • 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
    Commented Mar 25, 2011 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
    Commented Mar 25, 2011 at 15:00

3 Answers 3


For newer version of PG, check out the answer below by Dustin Kirkland

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
  • When I run sudo apt-get install postgres-contrib I get E: Unable to locate package postgres-contrib
    – Jonas
    Commented Mar 25, 2011 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
    Commented Mar 25, 2011 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
    Commented Mar 25, 2011 at 17:19
  • 1
    Ah, the command was \i /usr/share/postgresql/8.4/contrib/pgcypto.sql
    – Jonas
    Commented May 24, 2011 at 16:24
  • 9
    Under postgresql 9.1 you type in command line: CREATE EXTENSION pgcrypto
    – user5423
    Commented Dec 30, 2011 at 14:08

PostgreSQL 9.1+

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
  • I really like this answer, though you should have self-answered a question for 9.1+ (when this feature was added) As the OP clearly asked for 8.4 and his version doesn't support CREATE EXTENSION (which makes the other answer strictly speaking correct. Commented Jan 9, 2017 at 4:32

For the latest version, there is no file path end with pgcrypto.sql .

Create an extension pgcrypto under the required user.

$ psql -U <username> -d mydb

psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

mydb=> CREATE EXTENSION pgcrypto;


If in case, the user does not have permission to create an extension, give superuser permission by login as postgres(default) user and try again.

$ psql --u postgres

psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# ALTER USER <username> WITH SUPERUSER;


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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