postgresql

What's wrong with this simple query using NOT IN?

Schema: radio_bin.id radio.id radio.radio_bin -> radio_bin.id Queries: select * from radio_bin 72 rows. select * from radio_bin where id in (select radio_bin from radio) 50 rows. (And FWIW:) select distinct radio_bin from radio 51 rows, including a null. That's all good. Now: select * from radio_bin ...

Postgresql 8.3, simple query not using index

Hi! I have two tables: table1 (about 200000 records) number varchar(8) table2 (about 2000000 records) number varchar(8) Fields 'number' in both tables have standard indexes. For each record in table1 there is about 10 records in table2 assigned. I execute query: explain select table1.number from table1, table2 where table1.num...

Moving PostgreSQL database fails on non-ascii characters with 'value too long'

# Dump my database to a tar file pg_dump -f myDatabase.tar -F t -h myServer -U myUser -W -i myDatabase # create a new database createdb -h myServer -U myUser -T template0 myDatabaseCopy # restore my database pg_restore -d myDatabaseCopy -h myServer -U myUser myDatabase.tar Then I get this error, and the import fails for an entire table...

PHP get result string from PostgreSQL Query

I'm new to PHP and SQL, but I need a way to store the result of an SQL Query into a variable. The query is like this: $q = "SELECT type FROM users WHERE username='foo user'"; $result = pg_query($q); The query will only return one string; the user's account type, and I just need to store that in a variable so I can check to see if the...

Randomly disappearing struct

Hi, I've created a Objective-C class to interface with PostgreSQL using libpq. It mostly works but I have one small problem - the connection seems to disappear randomly. This class is set to open a connection when a window is opened and close that connection before the window closes. On the init code of the window I open the connection...

Sorting results with arbitrary distribution

I have database with few thousends jobs that have a column with value "active" or "inactive". About 5% of all jobs are "inactive". Jobs are added on daily basis (in numbers of hundrends). When existing job is marked as "inactive" we maintain the ratio to accurate (by deleting "inactive" jobs if there are too many of them). I need to so...

Postgresq, get table definition, pg_get_tabledef

Hi! Do you know what happend with pg_get_tabledef function in postgresql 8.4? I'm sure I've been using it with previous versions of postgresql? I need to get sql 'create table' syntax for existing table in db with indexes, defaults as so on... Maybe they replaced it with something else? ...

Can a transaction have many threads?

Generally We have some business logic that is causing a bottle neck within a transaction. The business logic queries the database for a set of data (read only), processes it, and returns an object. This must be done many times with varying parameters in a given request. Can we theoretically break off each business logic call into a sepa...

Django admin search functionality

I have a simple database in django with SQLite and now I want to improve it with a better search capability (I will create a new project with new models). I would like to ask about how to plan and go about this project. The existing database has these fields first, initial, last, school, yearGraduated I am using django admin to sor...

Retrieving the most common keywords from a tsvector column

I'm considering adding a tsvector column to an existing table that will hold possible search terms for multiple columns in that same table (e.g. the tsvector column will equal to_tsvector(header || ' ' || body || ' ' || footer)). Before I decide to do so, one of my requirements is that I am able to find the most popular keywords amongst...

SUM(a*b) not working...

I have a PHP page running in postgres. I have 3 tables - workorders, *wo_parts* and part2vendor. I am trying to multiply 2 table column row datas together, ie *wo_parts* has a field called qty and part2vendor has a field called cost. These 2 are joined by *wo_parts.pn* and part2vendor.pn. I have created a query like this: $scoreCostQuer...

How to find a gap in a time ordered table where a given column does not have a certain value for a specified interval.

I have a large table (millions of rows) where I need to find groups of records based on the presence of a certain column value and where a specified 'timeout' has not occurred. I figure one approach would be to find across the entire table where these 'timeout' gaps have occurred. Example table: +----------------+------+ | time ...

With sqlalchemy how to dynamically bind to database engine on a per-request basis

I have a Pylons-based web application which connects via Sqlalchemy (v0.5) to a Postgres database. For security, rather than follow the typical pattern of simple web apps (as seen in just about all tutorials), I'm not using a generic Postgres user (e.g. "webapp") but am requiring that users enter their own Postgres userid and password, ...

Unique model field in Django and case sensitivity (postgres)

Consider the following situation: - Suppose my app allows users to create the states / provinces in their country. Just for clarity, we are considering only ASCII characters here. In the US, a user could create the state called "Texas". If this app is being used internally, let's say the user doesn't care if it is spelled "texas" or "T...

Add all lines multiplied by another line in another table

Hi, I hope I can explain this good enough. I have 3 tables. wo_parts, workorders and part2vendor. I am trying to get the cost price of all parts sold in a month. I have this script. $scoreCostQuery = "SELECT SUM(part2vendor.cost*wo_parts.qty) as total_score FROM part2vendor INNER JOIN wo_...

How to bind SQL variables in Php?

How to bind SQL variables in Php? I want to bind variables instead of just building SQL strings. Anyway to do this in Php? either MySQL or PostgreSQL answers would help. Thanks ...

Django 1.1.1: How should I store an empty IP address using PostgreSQL?

I am writing a Django application that stores IP addresses with optional routing information. One of the fields for the IP model I have created is nexthop (for next-hop routes), which will usually be empty. Originally we intended to use MySQL, but now project requirements have changed to use PostgreSQL. Here is a stripped down version...

MySQL to PostgreSQL: GROUP BY issues.

So I decided to try out PostgreSQL instead of MySQL but I am having some slight conversion problems. This was a query of mine that samples data from four tables and spit them out all in on result. I am at a loss of how to convey this in PostgreSQL and specifically in Django but I am leaving that for another quesiton so bonus points if ...

Performing PostgreSQL LEFT OUTER JOINS and CASEs in Django.

So I have a fairly involved sql query here. SELECT links_link.id, links_link.created, links_link.url, links_link.title, links_category.title, SUM(links_vote.karma_delta) AS karma, SUM(CASE WHEN links_vote.user_id = 1 THEN links_vote.karma_delta ELSE 0 END) AS user_vote FROM links_link LEFT OUTER JOIN auth_user ON (links_link.user_id = a...

postgresql- restoring .dump file

Hi, I am new for psql. i got from my server data.dump file. I need to restore it in my local. i tried these commands. i) psql -U postgres dbname -f servicedb.dump Error: psql: warning: extra command-line argument "-f" ignored psql: warning: extra command-line argument "servicedb.dump" ignored ii) psql -U postgres dbname ...