How do I install pgcrypto in postgresql 9.1 on Windows?

Question

The web page for Postgresql says that pgcrypto is included in the download for Postgresql 9.1. There is no pgcrypto.sql file, however. If I look in the share\extension directory there are 3 files:

pgcrypto--1.0.sql pgcrypto--unpackaged--1.0.sql pgcrypto.control

If I try to install with

\i pgcrypto--1.0.sql

I get a bunch of errors like this:

psql:pgcrypto--1.0.sql:194: ERROR:  could not access file "MODULE_PATHNAME": No such file or directory

Maybe the files in share\extension were meant to be called by the share\contrib\pgcrypto.sql file (which doesn't exist).

On linux on Postgresql 8.4 I have to install the contrib package to get pgcrypto.sql. Is there another package I have to install on Windows for Postgresql 9.1?

Thanks.

score 20 · Accepted Answer · answered Nov 03 '11 at 20:19

20

In v9.1 the way to install extra modules was changed, those are now called EXTENSIONS and are installed with a special SQL statement CREATE EXTENSION.

answered Nov 03 '11 at 20:19

Milen A. Radev

60,241
22
105
110

I entered "CREATE EXTENSION", "CREATE EXTENSION pgcrypto", "CREATE EXTENSION pgcrypto--1.0" and "CREATE EXTENSION pgcrypto--1.0.sql" and nothing happened. Nothing new shows up in Extensions in pgAdmin. What else do I need to do? – Dean Schulze Nov 03 '11 at 21:35
11

`create extension pgcrypto` works for me. Did you commit your statement? What does `select * from pg_available_extensions` show you right after you ran `CREATE EXTENSION`? – Nov 03 '11 at 22:05
Running "create extension pgcrypto" from the query tool in pgadmin worked. psql has some problems running in cygwin. I must have had an issue with the psql session in cygwin. – Dean Schulze Nov 04 '11 at 02:20

score 8 · Answer 2 · answered Sep 05 '17 at 02:54

8

1.add the extensions: create extension pgcrypto

2.check the extensions: select * from pg_available_extensions

3.use the extensions: select '{SHA}'||encode(digest('test', 'sha1'), 'base64');

answered Sep 05 '17 at 02:54

liyuhui

1,210
12
17

score 1 · Answer 3 · answered Dec 05 '14 at 17:25

If you need use some extension, the way is for example for pgcrypto: "CREATE EXTENSION pgcrypto" from a window query, but is very important to said that this script must be executed in the DB that you need to use this extension, after having finished the script to verify that it is installed, check in pgAdmin over your DB the extensions seccion.

I hope this help.

score 0 · Answer 4 · edited Nov 15 '11 at 09:36

I was trying to convert a MySQL script that contained their SHA1 function. After finally doing the "create extension pgcrypto" command, then the example in the PostgreSQL documentation worked perfectly (at least all of the values I've tried so far).

Here is the SHA1 function:

CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
  SELECT encode(digest($1, 'sha1'), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;

It should be noted that I did all of this on PostgreSQL 9.1 with the PgAdminIII tool and on 64-bit Windows 7.

How do I install pgcrypto in postgresql 9.1 on Windows?

4 Answers4