[SQL] index problem

Title: Message Hi, I have an objectid field of type bigint, but when I run queries like: select * from table where objectid=123; index is not used on objectid even though it's declared. However when I run: select * from table where objectid='123'; index is used. Is there an option I can

[SQL] table permissions

Hi everybody, I can't seem to figure this one out. I have a superuser root and a regular user oleg. I am trying to insert a new row in table Set, which references table Activity, being logged in as root. I get an error saying: activity: Permission denied. This is very weird because root has all th

[SQL] URGENT: restoring a database

Hi, I think I got a problem here. I tried to restore my database from dump.bac file, which was created with pg_dumpall -o > dump.bac This is what I did: > createdb replica > psql -d replica -f dump.bac Notice that I have two different databases stored in this file. This is what I got: You are no

[SQL] rollback

Hi everybody, I was playing with psql and accidently deleted a couple of records from my database. I am wondering if there is any way to restore them. I know that in Oracle you can do 'rollback work' from SQLPlus interface and it would rollback all the updates done to the database. I am pretty sur

Re: [SQL] [ADMIN] update in rule

You can use the following to install plpgsql: CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; I assume that you have defaul

Re: [SQL] COUNT func

It worked! I checked the query plan it generates, and it's really a one-pass scan. thanks, Oleg Stephan Szabo wrote: > On Thu, 18 Oct 2001, Oleg Lebedev wrote: > > > Hi, > > I am trying to count the number or rows in a table with similar field > > values. I wa

[SQL] COUNT func

Hi, I am trying to count the number or rows in a table with similar field values. I want to do it in one table scan. In other words, say I want to count the number of rows in Person table, having age equal to 40, and the number of rows with status as 'married'. I want Person table to be scanned on

[SQL] SQL parser and/or optimizer

Hi everybody, I have a question, the answer to which may not directly relate to PostreSQL. Sorry about that. I am working on the problem of incremental view maintenance and need to implement the strategies I came up with. I am looking for an existing implementation of an SQL parser and/or optimize

[SQL] DROP VIEWS

Hi, I am trying to drop a view 'activity_IP' (notice that last two letters are capitalized), but get an exception saying: ERROR: view "activity_ip" does not exist Here, the last two letters are lower-case. There is an entry in pg_views table for view 'activity_IP', but not for 'activity_ip'. How c

Re: [SQL] Quotes and spaces

I just upgraded to 7.1 and the query works for me now. thanks, Oleg Stephan Szabo wrote: > On Fri, 5 Oct 2001, Oleg Lebedev wrote: > > > Hi, > > I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to > > work in queries executed from

[SQL] Quotes and spaces

Hi, I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to work in queries executed from Pl/pgSQl. Here is an example: create procedure get_name(varchar) ... BEGIN query := ''SELECT first_name || || last_name FROM user''; EXECUTE query; ... END; ... Basically

Re: [SQL] On Differing Optimizer Choices ( Again)

Hi, this is a very silly question, but how do I run PL/pgSQL function from SQL? I created a function test(varchar, varchar) and I am trying to invoke it with parameters 'hello', 'world' using an SQL statement. I tried selects and executes - nothing works. Please help, Oleg Mark kirkwood wrote:

Re: [SQL] Strange DISTINCT !

I think this is because if you remove duplicates before joining the tables, then you would join smaller tables, therefore cutting the cost of the join (and later sorting and removing duplicates). Say tmp_stat has the size of 1000 and 10 duplicates on the everage for each distinct tuple. Also, if t

Re: [SQL] Nested JOINs - upgrade to 7.1.2

I think Tom was right and the problem with nested joins is caused by the outdated installation of my PostgreSQL. So, I am trying to upgrade to 7.1.2 and when I use: pg_dumpall -o > file.bac I get an error saying: dumpRules(): SELECT failed for table setmedias. Explanation from backend: 'ERROR: cac

[SQL] Nested JOINs

Hello, I am trying to execute an SQL query that contains nested joins, but I get parser error for some reason. Below is the query I am trying to execute and the error I am getting: SELECT media FROM(dtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid)) c INNER JOIN dtcol d