Just keep the first 32 bits or 64 bits of the MD5 hash. Of course, it voids the main property of md5 (=the probability of collision being infinitesimal) but you’ll still get a wide dispersion of values which presumably is good enough for your problem. SQL functions derived from the other answers: For bigint: create … Read more
pgAdmin has GUI for data import since 1.16. You have to create your table first and then you can import data easily – just right-click on the table name and click on Import.
You shouldn’t use a postgres command to fully delete your database, as you will not have permissions to create a new one. Instead you should use the heroku command to clear out your database: heroku pg:reset DATABASE_URL
the answer to this is a combination of: http://initd.org/psycopg/docs/module.html#psycopg2.connect Any other connection parameter supported by the client library/server can be passed either in the connection string or as keywords. The PostgreSQL documentation contains the complete list of the supported parameters. Also note that the same parameters can be passed to the client library using environment … Read more
Note that PostgreSQL uses the term “clustered index” to use something vaguely similar and yet very different to SQL Server. If a particular index has been nominated as the clustering index for a table, then psql’s \d command will indicate the clustered index, e.g., Indexes: “timezone_description_pkey” PRIMARY KEY, btree (timezone) CLUSTER PostgreSQL does not nominate … Read more
Your pg_hba.conf appears to permit connections from localhost. The easiest way of causing your SSH tunnel connections to appear from localhost is to make them to localhost. The following SSH command connects to remote.example.com as user “user”, and causes your ssh client to listen on localhost, port 1111/tcp. Any connections made to that port will … Read more
Here’s a nice table of reserved words in PostgreSQL: http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html It is probably best to simply avoid using those words as table- or column-names. An alternative, however, is to enclose the identifier in double-quotes, e.g.: CREATE TABLE IF NOT EXISTS apiss ( skey TEXT, time INTEGER, “user” TEXT, ip TEXT); Additionally, Postgres reserves system column … Read more
The best way to reset a sequence to start back with number 1 is to execute the following: ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1 So, for example for the users table it would be: ALTER SEQUENCE users_id_seq RESTART WITH 1
I’ll need to invoke REFRESH MATERIALIZED VIEW on each change to the tables involved, right? Yes, PostgreSQL by itself will never call it automatically, you need to do it some way. How should I go about doing this? Many ways to achieve this. Before giving some examples, keep in mind that REFRESH MATERIALIZED VIEW command … Read more
This is a general PostgreSQL answer, and not specific to Heroku (The simple-stupid answer to this question may be … just restart postgresql. Assuming that’s not desirable or not an option …) Find the PID by running this sql: SELECT pid , query, * from pg_stat_activity WHERE state != ‘idle’ ORDER BY xact_start; (The query … Read more