postgresql

POSTGRESQL Effective way to split table performance-wise

I have a user table in postgress. Each user can have from 0 to many websites. I know that it will be waste of memory to bring the user-websites everytime I get the user object from the database and of course I cant know how many websites the user will have. I could have a table called websites but then I think this could happen again w...

How to login to Postgres after install on Mac Snow Leopard?

I got Postgres 8.4 installed on my mac with Snow Leopard. I know the 'postgres' user was created but I have no idea what the default password is. How should I login to the database? ...

Is there a better way to extract the time of day ?

I'm trying to extract the time of day from a 'timestamp' column in PostgreSQL. Here is how I did it but... it's awful. An idea of how to do it better ? SELECT ( date_part('hour', date_demande)::text || ' hours ' || date_part('minute', date_demande)::text || ' minutes ' || date_part('second', date_demande)::text || ' seco...

Linked server (read data from pgsql and insert it into SQL Server)

I have a SQL Server 2005. I have created a linked server to PG SQL server. It works great. SELECT * FROM OpenQuery(POSTGRESQL_SERV, 'SELECT comp_id,comp_name FROM company WHERE comp_type = 5') I need to read all data from PG SQL and insert it into SQL Server. How to create a while ? (also unique id`s) Thx. ...

use RETURNING-data in another statement

Hello, I want to use data 'theID' from a PostgreSQL-RETURNING statement INSERT INTO ... RETURNING theID; further in another statement. How can this be done (without cursors)? My full statement (copying some data and delete them from source table; tab1 & tab2 definition are equal): DELETE FROM tab1 WHERE tab1id IN ( INSERT INTO...

PostgreSQL: Why can't subqueries as expressions return more than one row, but functions can?

If I try to create a column whose value is a select returning more than one row, I get an error. => select (select 1 union select 2); ERROR: more than one row returned by a subquery used as an expression But if I create a function that does the same thing, I get the behavior I want. => create or replace function onetwo() returns set...

postgres public schema function aliasing

Hi all, I am currently running postgres 8.4.4 and I have the need to override calls to functions that reside in the public schema of my database. For instance in pg_catalog there exists a function upper(text) I have a function placed within the public schema that overrides upper(text) My question comes down to ...

How to delete all databases on Postgres?

I take daily backs of our postgres development box using: pg_dumpall -h 127.0.0.1 -U user -w | gzip blah.gz Since 9.0 is now a release candidate I would like to restore this daily backup on a daily basis to a postgres9.0rc1 box for testing, however I'm not sure how to script it repeatedly. Is there some directory I can nuke to do th...

PostgreSQL : Transaction and foreign key problem

Hi all!!! I'm inserting a value in table A, that has a serial type as primary key. I wanna use the returned value of the query as a foreign key of table B... but I get this message: *ERROR: insert or update on table "tb_midia_pessoa" violates foreign key constraint "tb_midia_pessoa_id_pessoa_fkey" DETAIL: Key (id_pessoa)=(30) is not pre...

Many to many query with a count of condition > 0

Hi everyone, I have 3 tables: Emails Foo EmailFoos (The many to many join table) Foo can be complete or not. I need to find the set of emails where the count of completed foos > 0 (and the inverse, but I can probably do that ;) I tried something like: SELECT e.id, e.address, count(l.foo_id) as foo_count FROM emails e LEFT ...

Database concurrent connections in regard to web (http) requests and scalability

One database connection is equal to one web request (in case, of course, your client reads the database on each request). By using a connection pool these connections are pre-created, but they are still used one-per-request. Now to some numbers - if you google for "Tomcat concurrent connections" or "Apache concurrent connections", you'l...

Copy data from table of one db into table of another db using query (both tables have same structure)

I have two database in the same schema. My db is in Postgres. I want to copy data of any table (i.e product) of my 1st db into the same table of the 2nd db. Is it possible to do so using query? ...

Is there a better way to calculate the median (not average)

Suppose I have the following table definition : CREATE TABLE x (i serial primary key, value integer not null); I want to calculate the MEDIAN of "value" (not the AVG). The median is a value that divides the set in two subsets containing the same number of elements. If the number of elements is even, the median is the average of the b...

Query works in PostgreSQL but fails with HSQL. How can I fix it?

Dear SQL gurus ;-) I have the following query (inherited from legacy) similar to SELECT bla FROM table WHERE some.id IN ( SELECT id FROM ( SELECT some FROM tag WHERE bla UNION SELECT some FROM dossierinfo WHERE bla ORDER BY tag LIMIT :limit OFFSET :offset ) AS aggregated WHERE dossier_type = ...

Update or Insert (multiple rows and cols) from Subquery in PostgreSQL

hi folks, i'm trying to do something like this in postgres: UPDATE table1 SET (col1, col2) = (SELECT col2, col3 FROM othertable WHERE othertable.col1 = 123); INSERT INTO table1 (col1, col2) VALUES (SELECT col1, col2 FROM othertable) but point 1 is not possible even with postgres 9.0 as mentioned in the docs (http://www.postgresql.or...

How to get a "top X with the rest" in SQL ?

Say I have a table with a 'name' and 'quantity' columns, and I want to get the first X products which are the most present, and another line, which is the sum of all the other products. We can take this sample SQL script as an example: CREATE TEMPORARY TABLE product ( name TEXT, quantity INT ); INSERT INTO product (name, quant...

KO3, PostgresSQL, Transactions, and PDOException

I've run into an issue that I'm hoping to get a little help on. I'm using the following: Kohana 3.0.7 PostgreSQL 8.4 Transactions in PostgreSQL using $db->query(NULL, 'BEGIN', FALSE) $db->query(NULL, 'ROLLBACK', FALSE); $db->query(NULL, 'COMMIT', FALSE); The issue is that when I send a query to the database that results in a...

How would I write this SQL query?

I have the following tables: PERSON_T DISEASE_T DRUG_T ========= ========== ======== PERSON_ID DISEASE_ID DRUG_ID GENDER PERSON_ID PERSON_ID NAME DISEASE_START_DATE DRUG_START_DATE DISEA...

Slow Postgres JOIN Query

I'm trying to optimize a slow query that was generated by the Django ORM. It is a many-to-many query. It takes over 1 min to run. The tables have a good amount of data, but they aren't huge (400k rows in sp_article and 300k rows in sp_article_categories) #categories.article_set.filter(post_count__lte=50) EXPLAIN ANALYZE SELECT * ...

Which is faster, horizontal or vertical counting?

I need to get summary data from many many rows. The summary fields are counts of how many entries have each value for different fields. For example, a table with people's age, city, job, etc, the summary data would include fields like "countManager", "countCodeMonkey" for each job, and then "countChicago", "countNewYork" etc for cities. ...