Take the 2-minute tour ×
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
add comment

2 Answers 2

up vote 11 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

Or,

$ 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
1  
Ah, the command was \i /usr/share/postgresql/8.4/contrib/pgcypto.sql –  Jonas May 24 '11 at 16:24
4  
Under postgresql 9.1 you type in command line: CREATE EXTENSION pgcrypto –  Lukasz Dec 30 '11 at 14:08
show 1 more comment

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;
CREATE EXTENSION

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

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

test=# insert into test (value) VALUES ('scoobydoo');
INSERT 0 1
test=# select * from test;
                                id                                |   value   
------------------------------------------------------------------+-----------
 76dd5bd0120d3df797f932fbcb4f8aa5088e215ee2b920dddbff59c8595fbac7 | scoobydoo
share|improve this answer
add comment

Your Answer

 
discard

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.