Figured this out, here’s a function that does it: CREATE OR REPLACE FUNCTION generate_uid(size INT) RETURNS TEXT AS $$ DECLARE characters TEXT := ‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789’; bytes BYTEA := gen_random_bytes(size); l INT := length(characters); i INT := 0; output TEXT := ”; BEGIN WHILE i < size LOOP output := output || substr(characters, get_byte(bytes, i) % l … Read more
The key is to switch the local timezone to the desired display timezone, for the duration of the transaction: begin; set local timezone to ‘EST5EDT’; select to_char(‘2012-05-29 15:00:00’::timestamp at time zone ‘CDT’, ‘YYYY-MM-DD HH24:MI:SS TZ’); end; The result is: 2012-05-29 16:00:00 EDT Note that with set [local] timezone it is required to use full time … Read more
Options for text search and indexing include: full-text indexing with dictionary based search, including support for prefix-search, eg to_tsvector(mycol) @@ to_tsquery(‘search:*’) text_pattern_ops indexes to support prefix string matches eg LIKE ‘abc%’ but not infix searches like %blah%;. A reverse()d index may be used for suffix searching. pg_tgrm trigram indexes on newer versions as demonstrated in … Read more
There’s a PgFoundry project that contains several example PostgreSQL databases. Most of these haven’t been updated for a while, but will still work with recent PostgreSQL versions. If you need a bigger database, the MusicBrainz music metadata database has full database dumps available for download.
Pavel has it quite right, I’d just like to explain a little. Presuming that you mean a performance impact as compared to floating point, or fixed-point-offset integer (i.e. storing thousandsths of a cent as an integer): Yes, there is very much a performance impact. PostgreSQL, and by the sounds of things MySQL, store DECIMAL / … Read more
PostgreSQL is following the SQL standard and in that case that means that identifiers (table names, column names, etc) are forced to lowercase, except when they are quoted. So when you create a table like this: CREATE TABLE APP_USER … you actually get a table app_user. You apparently did: CREATE TABLE “APP_USER” … and then … Read more
pl/python has access to the all the modules that the normal Python interpreter would have as long as they are in the $PYTHONPATH on the server (and the user that runs the postgres service). Does import lucene work if you run it in the Python interpreter on the server? If your module is installed somewhere … Read more
I found this blog entry which guides you to do these steps: Create a dump of the PostgreSQL database. ssh -C [email protected] pg_dump –data-only –inserts YOUR_DB_NAME > dump.sql Remove/modify the dump. Remove the lines starting with SET Remove the lines starting with SELECT pg_catalog.setval Replace true for ‘t’ Replace false for ‘f’ Add BEGIN; as … Read more
For current versions of PostgreSQL and pgAdmin, the “Guru” dialog warning has a “Fix it!” button or command. Use it. If there’s no “Fix it!” then we can use the Unix command line as follows. This is for PostgreSQL 9.1. Older versions do it differently. PostgresSQL docs are here: download adminpacks 8.4. adminpack functions 9.1. … Read more
By placing the rank() function in the subselect and not specifying a PARTITION BY in the over clause or any predicate in that subselect, your query is asking to produce a rank over the entire url_info table ordered by pub_date. This is likely why it ran so long as to rank over all of url_info, … Read more