Was wondering if someone could help me out a little with this query: SELECT u1.id,count(DISTINCT u2.userstatus) as TEMPCOLUMN FROM users AS u1 JOIN friendssym ON u1.id = friendssym.user_id JOIN (SELECT * FROM users) as u2 ON friendssym.friend_id=u2.id WHERE TEMPCOLUMN=1 group by u1.id; I want to only have results where the count (whi...
Hi, I have a table id|level|name level can be 1,2 or 3 what I want to get is: id|lvl1name|lvl2name|lvl3name I'm using the following query SELECT L1."name" as lvl1name, L2."name" as lvl2name, L3."name" as lvl3name, L1.id FROM table as L1 JOIN table as L2 ON L1.id = L2.id JOIN table as L3 ON L2.id = L3.id WHE...
I would like a fairly efficient way to condense an entire table to a hash value. I have some tools that generate entire data tables, which can then be used to generate further tables, and so on. I'm trying to implement a simplistic build system to coordinate build runs and avoid repeating work. I want to be able to record hashes of the ...
in ms sql I have the following code: ALTER PROCEDURE [dbo].[xmlDictamen_Alta] @Xml text as begin declare @Id integer declare @DictamenId numeric(18,0) declare @DocumentoId numeric(18,0) declare @Descripcion varchar(300) begin set nocount on exec dbo.sp_xml_preparedocument @Id output, @Xml select ...
Hi, I have an array of ids of objects that I want to get from the database, but PostGreSQL returns them sorted by ids: Users.find([4, 1, 3]) => [User 1, User 3, User 4] I know I could sort them back like this: ids = [4, 3, 1] r = Users.find(ids) users = ids.map{|id| r.detect{|each| each.id == id}} But wouldn't it be better if I co...
Is there a reason why all sqlite3, mysql, postgres driver compiles on Mac OS X Leopard when it is Rails 2.3.8, but can't compile when it is Rails 3.x? Theoretically, isn't it just some compile flags to make them work? (They all compile on Snow Leopard with the latest Xcode, but can't compile with Leopard with a slightly older Xcode... ...
We have a large POSTGRESQL transactional database (around 70 million rows in all), and have previously created a data warehouse from this (updated daily) to run reports off of. To make this more flexible (as lots of different users require different reports and aren't very good at specifying what they want) we would like to create a mul...
I have loaded a huge CSV dataset -- Eclipse's Filtered Usage Data using PostgreSQL's COPY, and it's taking a huge amount of space because it's not normalized: three of the TEXT columns is much more efficiently refactored into separate tables, to be referenced from the main table with foreign key columns. My question is: is it faster to ...
Suppose we have a textarea in which we put example string. Textbox contain : Earth is revolving around the Sun. But at the time of saving, I just pressed a enter key after "the sun". Now the statements in texbox :: Earth is revolving around the Sun Now, in database where enter was pressed the \r is stored. Now i am trying to fetch...
I've got a fairly simple table which stores the records' authors in a text field as shown here: CREATE TABLE "public"."test_tbl" ( "index" SERIAL, "testdate" DATE, "pfr_author" TEXT DEFAULT "current_user"(), CONSTRAINT "test_tbl_pkey" PRIMARY KEY("index"); The user will never see the index or pfr_author fields, but I'd like...
I had the debugger working in pgAdminIII at one point, some time before upgrading to 9.0, but as of right now I can't seem to get it working using the usual steps. I believe it may have something to do with the DLL not loading correctly, because the following query: LOAD '$libdir/plugins/plugin_debugger.dll'; show shared_preload_librar...
Hi, I installed postgresql via synaptic on ubuntu. Now I dont know how to create new user, run server and create it in pgAdmin3. ...
Hi I have an entity annotated with the following: @Entity @Table(uniqueConstraints={@UniqueConstraint(columnNames={"name"})}) public class Component extends Model { ... } Is it possible to make the UniqueConstraint case insensitive? We are using PostgreSQL. Thanks in advance Rich ...
I was wondering what the best approach would be for the following situation: I have an Orders table in a database that obviously contains all orders. But these are literally ALL orders, so including the complete/finished ones that are just flagged as 'complete'. From all the open orders I want to calculate some stuff (like open amount, ...
Well, there is similar question for Oracle, now, Postgres? ...
Hi, I want to "replicate" a database to an external service. For doing so I could just copy the entire database (SELECT * FROM TABLE). If some changes are made (INSERT, UPDATE, DELETE), do I need to upload the entire database again or there is a log file describing these operations? Thanks! ...
I recently switched to from using integer sequence IDs to UUIDs for primary keys in my rails 3 app using Postgresql, using a method similar to this method. As such, none of my tables have sequences, yet rails is still making (what I believe to be) unnecessary calls to select sequences when records are created. For example: PK and seria...
My database driver for Postgres 8/9 does not return a count of records affected when executing INSERT or UPDATE. Postgres offers the non-standard syntax "RETURNING" which seems like a good workaround. But what might be the syntax? The example returns the ID of a record, but I need a count. INSERT INTO distributors (did, dname) VALU...
I want to be able to take a LINQ statement like this. var User = from u in Users where u.UserID == 7 select u.UserName; And have it generate SQL like this. SELECT UserName FROM Users WHERE Users.UserID = 7 I know LINQ TO SQL does this but I don't want all that added xml mapping and generated code. Obviously thi...
Hi Is there a way to register some kind of SchemaExportEventListener (that's a name I've chosen) with Hibernate, so that after it has generated the DDL we can make some updates (which Hibernate doesn't support) to the database? We need to set a case-insensitive unique constraint on a PostgreSQL column, and while this is perfectly possi...