postgresql

Complex SQL query help on aggregating values for nested subquery

Hi! I have people, companies, employees, events and event kinds. I'm making a report/followup sheet where people, companies and employees are the rows, and the columns are event kinds. Event kinds are simple values describing: "Promised Donation", "Received Donation", "Phoned", "Followed up" and such. Event kinds are ordered: CREATE T...

Speed up PostGreSQL createdb?

Is there a way to speed up PostgreSQL's createdb command? Normally I wouldn't care, but doing unit testing in Django creates a database every time, and it takes about 5 seconds. I'm using openSUSE 11.2 64-bit, PostgreSQL 8.4.2 ...

Check if row already exists, if so tell the referenced table the id

Let's assume I have a table magazine: CREATE TABLE magazine ( magazine_id integer NOT NULL DEFAULT nextval(('public.magazine_magazine_id_seq'::text)::regclass), longname character varying(1000), shortname character varying(200), issn character varying(9), CONSTRAINT pk_magazine PRIMARY KEY (magazine_id) ); And another table ...

phppgadmin : How does it kick users out of postgres, so it can db_drop?

I've got one Posgresql database (I'm the owner) and I'd like to drop it and re-create it from a dump. Problem is, there're a couple applications (two websites, rails and perl) that access the db regularly. So I get a "database is being accessed by other users" error. I've read that one possibility is getting the pids of the processes i...

Erlang: 2 database on 2 webserver ??

I have created a blogging system with php+postgresql. Now I want to add a web chat ( in REAL TIME for Million of users simultaneously ) where every message is saved in database. I am thinking to use Erlang+Mnesia on a different webserver for this issue. Message's table will be like this: message_id, user_id, message, date user_id sho...

Database: storing data from user registration form

Let's say I have an user registration form. In this form, I have the option for the user to upload a photo. I have an User table and Photo table. My User table has a "PathToPhoto" column. My question is how do I fill in the "PathToPhoto" column if the photo is uploaded and inserted into Photo table before the user is created? Another...

Query a column and a calculation of columns at the same time PostgreSQL

Hi I have two tables, Products and BundleProducts that have o2o relation with BaseProducts. A BundleProduct is a collection of Products using a m2m realtion to the Products table. Products has a price column and the price of a BundleProduct is calculated as the sum of the prices of its Products. BaseProducts have columns like name and ...

Left outer joins that don't return all the rows from T1

Left outer joins should return at least one row from the T1 table if it matches the conditions. But what if the left outer join performs a join successfully, then finds that another criterion is not satisfied? Is there a way to get the query to return a row with T1 values and T2 values set to NULL? Here's the specific query, in which ...

Can't install psycopg2 (Python2.6 Ubuntu9.10 PostgreSQL8.4.2)

~/psycopg2-2.0.13$ python setup.py install running install running build running build_py creating build creating build/lib.linux-x86_64-2.6 creating build/lib.linux-x86_64-2.6/psycopg2 copying lib/__init__.py -> build/lib.linux-x86_64-2.6/psycopg2 copying lib/pool.py -> build/lib.linux-x86_64-2.6/psycopg2 copying lib/extensions...

Fluent NHibernate and PostgreSQL, SchemaMetadataUpdater.QuoteTableAndColumns - System.NotSupportedException: Specified method is not supported.

Hello! I'm using fluentnhibernate with PostgreSQL. Fluentnhibernate is last version. PosrgreSQL version is 8.4. My code for create ISessionFactory: public static ISessionFactory CreateSessionFactory() { string connectionString = ConfigurationManager.ConnectionStrings["PostgreConnectionString"].ConnectionString; IPersist...

PostgreSQL: Auto-partition a table

Hi, I have a huge database which holds pairs of numbers (A,B), each ranging from 0 to 10,000 and stored as floats. e.g., (1, 9984.4), (2143.44, 124.243), (0.55, 0), ... Since the PostgreSQL table which stores these pairs grew quite large, I have decided to partition it into inheriting sub-tables. I intend to create 100 such tables, ...

postgresql help with php loop....

I keep getting an "Notice: Undefined index: did" error with this query, and I'm not understanding why... I'm much more used to mysql, so, maybe the syntax is wrong? This is the php query code: function get_demos() { global $session; $demo = array(); $result = pg_query("SELECT DISTINCT(did,vid,iid,value) FROM dv...

Is it worth it creating "categories" for an SO-type site, or are tags the replacement?

I have a posts table for my reddit-clone site and I'm wondering whether I should make categories per posts or just categorize by tags like SO has it. It seems like tags pretty much are a replacement for categories, but is there something I'm just overlooking? ...

failing to establish connection between Postgres db and gwt

Hi, I am using Postgres and gwt 2.0 for one of my applications. I am facing problem connecting to the database. When I try to connect it gives "ClassNotFoundException". Here is what I get when I try to connect to database: java.lang.ClassNotFoundException: org.postgresql.Driver at java.net.URLClassLoader$1.run(URLClassLoader.java:...

insert or update on table violates foreign key constraint

Hi, I have two tables entitytype and project. Here are the create table statements: Create table project ( pname varchar(20) not null, primary key(pname) ); create table entitytype( entityname varchar(20) not null, toppos char(100), leftpos char(100), pname varchar(20) not null, primary key(entityname), foreign key(pname) refe...

PHP postgres backup.

Hi. I am trying to make a Postgres PHP backup script. I have downloaded one for the command line which looks like this: #!/bin/bash find /home/russell/pg_bkp -type f -mtime +7 -exec rm {} \; time=`date +%Y-%m-%d`; # date in reverse so that lastest date appears last in the list of backup files. PGPASSWORD=****** pg_dump -i -h localhost -...

"bad record MAC" SSL error between Java and PortgreSQL

Hello there ! We've got here a problem of random disconnections between our Java apps and our PostgreSQL 8.3 server with a "bad record MAC" SSL error. We run Debian / Lenny on both side. On the client side, we see : main, WRITE: TLSv1 Application Data, length = 104 main, READ: TLSv1 Application Data, length = 24 main, READ: TLSv1 Appl...

Postgresql slave for Mysql Master. Possible ?

Hi, I need to make some tests for a potential migration from Mysql to PostgreSql. It will be easier to test if it is possible to use Postgre as slave for my MySQL master. Is it possible ? Thanks in advance ...

PostgreSQL - Error: SQL state: XX000.

I have a table in Postgres that looks like this: CREATE TABLE "Population" ( "Id" bigint NOT NULL DEFAULT nextval('"population_Id_seq"'::regclass), "Name" character varying(255) NOT NULL, "Description" character varying(1024), "IsVisible" boolean NOT NULL CONSTRAINT "pk_Population" PRIMARY KEY ("Id") ) WITH ( OIDS=FALSE ); ...

Database design for heavy timed data logging

Hi, I have an application where I receive each data 40.000 rows. I have 5 million rows to handle (500 Mb MySQL 5.0 database). Actually, thoses rows are stored in the same table => slow to update, hard to backup, ... Which king of scheme is used in such application to allow long term accessibility to the datas without problems with too...