In various PostgreSQL vs. MySQL comparisons I've seen many mentions of problems with data integrity in MySQL. Is this currently still an issue? Particularly for web applications using MySQL, are there tricks to making sure data integrity is maintained? Or with new versions is this true 'out of the box' with no additional configuration r...
i tried using this query: "SELECT * FROM guests WHERE event_id=".$id." GROUP BY member_id;" and I'm getting this error: ERROR: column "guests.id" must appear in the GROUP BY clause or be used in an aggregate function can anyone explain how i can work around this? ...
I use Postgresql + PHP. Say I have this table: Books ( id, title, year ) and this array of titles in PHP: $titles = array ("bible","kafka","Book of Eli"); now I want update all rows where the title is in the $titles array above. So I need a query like this: UPDATE books SET year = '2001-11-11' WHERE title is in $titles; Is is ...
A while ago I posted a message about optimizing a query in MySQL. I have since ported the data and query to PostgreSQL, but now PostgreSQL has the same problem. The solution in MySQL was to force the optimizer to not optimize using STRAIGHT_JOIN. PostgreSQL offers no such option. Update Revised I have isolated the part of the query tha...
I have a named scopes like so... named_scope :gender, lambda { |gender| { :joins => {:survey_session => :profile }, :conditions => { :survey_sessions => { :profiles => { :gender => gender } } } } } and when I call it everything works fine. I also have this average method I call... Answer.average(:rating, :include => {:survey_sessi...
Hi all, Ich have a problem in postgres function: CREATE OR REPLACE FUNCTION linkedRepoObjects(id bigint) RETURNS int AS $$ DECLARE catNumber int DEFAULT 0; DECLARE cat RECORD; BEGIN WITH RECURSIVE children(categoryid,category_fk) AS ( SELECT categoryid, category_fk ...
I am trying to create a website using Symfony and PostgreSQL. I cant find any documentation that shows how to do this. All the documentation assumes the backend db is mySQL. Anyone knows how to do this? ...
I've been searching for about an hour, but could not find any installer for this version... ...
Given an object X; I want this object to have an image. The image must be stored in the database. I can't store the path, the actual image must be in the database. My question can be answered by answering the following subquestions: a). What type of field should I put in the database? (e.g VARCHAR) b) What type of object should I use ...
What is the fastest way to insert 237 million records into a table that has rules (for distributing the data across 84 child tables)? First I tried inserts. No go. Then I tried inserts with BEGIN/COMMIT. Not nearly fast enough. Next, I tried COPY FROM, but then noticed the documentation states that the rules are ignored. (And it was hav...
I'm trying to optimize SQL queries in Akonadi and came across the following problem that is apparently not easy to solve with SQL, at least for me: Assume the following table structure (should work in SQLite, PostgreSQL, MySQL): CREATE TABLE a ( a_id INT PRIMARY KEY ); INSERT INTO a (a_id) VALUES (1), (2), (3), (4); CREATE TABLE b ...
I would like to bundle PostgreSQL into my application. I downloaded the PostgreSQL 9 beta zip without the installer. After extraction, there are 8 directories. I found out that at least the following directories are required to run PostgreSQL properly, at least under my experiments. bin lib share The question is, are the above three d...
I am writing a PL/pgSQL function. The function has input parameters which specify (indirectly), which tables to read filtering information from. The function embeds business logic which allows it to select data from different tables based on the input arguments. The function dynamically builds a subquery which returns filtering data whi...
Hello, this is a part from my database structure: Table: Item Columns: ItemID, Title, Content, Price Table: Tag Columns: TagID, Title Table: ItemTag Columns: ItemID, TagID Table: Image Columns: ImageID, Path, Size, UploadDate Table: ItemImage Columns: ItemID, ImageID The items can have more than one image so i have a extra table "...
(I'm using postgres) Are there any aggregate functions that work on strings? I want to write a query along the lines of select table1.name, join(' - ', unique(table2.horse)) as all_horses from table1 inner join table2 on table1.id = table2.fk group by table1.name Given these 2 tables: | table1 | | table2 ...
Possible Duplicate: Possible to perform cross-database queries with postgres? I don-t know how to use one table from one database and second table from another databse at the same time or copy data from table in one database to table from another databse. I tried following query: select * into NewTable from existingdb.dbo.ex...
Hi, I've installed PL/Python on my postgresql server under postgres privilleges: netherlands=# CREATE PROCEDURAL LANGUAGE plpythonu; CREATE LANGUAGE Now I need to grant permissions so I can use it as a normal user: netherlands=# GRANT ALL ON LANGUAGE plpythonu TO adam; ERROR: language "plpythonu" is not trusted HINT: Only su...
I'm creating a website using ASP.NET MVC 2 and I'm thinking of using the default AccountController and Views to take care of the Users. The only problem is that, for all the rest, I'm using a Postgres database. Is there a way to link The account controller to a User class defined by me? I'm using Nhibernate to connect to the database,...
I'm trying to connect to my PostgreSQL database using Entity Framework. Unfortunately after generating a model using EdmGen (or EdmGen2) in my SSDL file ProviderManifestToken is set to 8.3.7 when in the current version of Npgsql (2.0.9) it is set to 8.1.3. This gives me fallowing exception when trying to use my entity model: System.D...
I am trying to prevent users from being able to irrevocably delete spaces in a Confluence wiki. My first quick thought was to rename the spaces table to allspaces, add a new column deleted, and create a view spaces in place of the old table. The view will only return non-deleted spaces. I have created three rules to allow INSERT, UPDA...