Is there an existing function to replace accented characters with unadorned characters in PostgreSQL? Characters like å and ø should become a and o respectively. The closest thing I could find is the translate function, given the example in the comments section found here. Some commonly used accented characters can be searched us...
In PostgreSQL 8.3, let's say I have a table called widgets with the following: id | type | count -------------------- 1 | A | 21 2 | A | 29 3 | C | 4 4 | B | 1 5 | C | 4 6 | C | 3 7 | B | 14 I want to remove duplicates based upon the type column, leaving only those with the...
Hi folks, I tried to update a particular record with same value again and again, but it returns the affected rows value as 1 always. update users set active = 1 where id = 304 1 row(s) affected. again the same query update users set active = 1 where id = 304 1 row(s) affected. but the second update should return 0 row(s) affected...
I'm quite a newbie with PostgreSQL optimization and chosing whatever's appropriate job for it and whatever's not. So, I want to know whenever I'm trying to use PostgreSQL for inappropriate job, or it is suitable for it and I should set everything up properly. Anyway, I have a need for a database with a lot of data that changes frequentl...
this is a model of the view table. class QryDescChar(models.Model): iid_id = models.IntegerField() cid_id = models.IntegerField() cs = models.CharField(max_length=10) cid = models.IntegerField() charname = models.CharField(max_length=50) class Meta: db_table = u'qry_desc_char' this is the SQL i use to create the table CRE...
Running a Ruby on Rails (RoR) app or Ruby code which uses the ActiveRecord framework, you get the error message: Please install the postgresql adapter: gem install activerecord-postgresql-adapter Trying to run: gem install activerecord-postgresql-adapter also fails, leaving you at a loss. ...
I have been tasked with improving the performance of a slow running process which updates some data in a PostGres 8.3 database (running on Solaris, updates are driven by Perl 5.8 scripts through SOAP). About 50% of the time consumed I have very little control over so tuning my 50% is quite important. There are usually about 4,500,000 r...
I'm trying to optimize my PostgreSQL 8.3 DB tables to the best of my ability, and I'm unsure if I need to use varchar_pattern_ops for certain columns where I'm performing a LIKE against the first N characters of a string. According to this documentation, the use of xxx_pattern_ops is only necessary "...when the server does not use the s...
I have a table where I have added a new column, and I want to write a SQL statement to update that column based on existing information. Here are the two tables and the relevant columns 'leagues' => id => league_key => league_id (this is the new column) 'permissions' => id => league_key Now, what I want to do, in plain English, i...
PostgreSQL is about to make me punch small animals. I'm doing the following SQL statement for MySQL to get a listing of city/state/countries that are unique. SELECT DISTINCT city , state , country FROM events WHERE (city > '') AND (number_id = 123) ORDER BY occured...
I would like to access my PostgreSQL 8.3 database with the name of an existing user but, when I try, I get a password authentication failure. I am able to access the database by creating a new Postgres user: postgres createuser -P newusername So I tried createuser with the existing user name but it said that that user already existed...
Interestingly it also works on the shell. [MY code which calls Model.objects.get_or_create(...)] File "/usr/lib/python2.5/site-packages/django/db/models/manager.py", line 123, in get_or_create return self.get_query_set().get_or_create(**kwargs) File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 308, in ge...
Hi i want to use an enum in postgresql as an alternative to making a table, because the values my never change, but i want to be able to retrieve these values for an application that might check just in case they do, is there any way the query it to get the values? ...
I'm starting to get a much better grasp on PostgreSQL indexing, but I've run into an issue with the OR conditional, where I don't know how to go about optimizing my indexes for a faster query. I have 6 conditionals that, when run individually, appear to have a small cost. Here's an example of the trimmed queries, including query plan c...
I'm migrating a db from mysql to postgresql. The mysql db's default collation is UTF8, postgres is also using UTF8, and I'm encoding the data with pg_escape_string(). For whatever reason however, I'm running into some funky errors about bad encoding: pg_query() [function.pg-query]: Query failed: ERROR: invalid byte sequence for encoding...
I need to create an application for the iPhone that will connect to a PostgreSQL 8.4 database using libpq. The problem is I can't get a simple iPhone that links to libpq to compile. I am however able to get the equivalent application that is a regular Mac desktop app to compile and connect to PostgreSQL without any issues. I'm on Xcode ...
Hi all, I have encountered a problem where I need to copy only data from a Postgresql database to Mysql database. I already have the Mysql database with empty tables. By using PGAdmin I got a backup (data only, without database schema). I tried using PSQL tool but it keeps giving segmentation fault which I couldn't fix at the moment. ...
Let's say we are having a table with this definition: range ( id bigint primary key, colourId int references colour(id), smellId int references smell(id), from bigint, to bigint ) This table is actually a reduced view over enormously big table: item ( id bigint primary key, colourId int references colour(id), smellId ...
Alright I need to do a big query, but I only want the latest records. For a single entry I would probably do something like SELECT * FROM table WHERE id = ? ORDER BY date DESC LIMIT 1; But I need to pull the latest records for a large (thousands of entries) number of records, but only the latest entry. Here's what I have but It's no...
I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter "A": SELECT * FROM books WHERE title ILIKE "A%" That's fine for letters, but how do I list all items starting with any number? For what it's worth th...