Re: [SQL] interval syntax change from 7.1.2 to 7.2.1?

From: "Jinn Koriech" <[EMAIL PROTECTED]> wrote: > hi - i recently upgraded my server from 7.1.2 to 7.2.1 and now one of my > apps is no longer functioning. this is the query: > > SELECT DISTINCT rate, sum(extract(epoch from interval (end_time - > start_time)))/3600) AS duration > FROM lo

Re: [SQL] Queries not using Index

"Daryl Herzmann" <[EMAIL PROTECTED]> wrote: > snet=# select count(valid) from t2002_06; > count > - > 1513895 > snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4'; > NOTICE: QUERY PLAN: > > Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47) Can you do the

[SQL] unsubscribe

unsubscribe ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

[SQL] unsubscribe

unsubscribe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Select the max on a field

Hi all, Suppose that I have a table like this: att_1 |att_2 |att_3 | att_4 1 | a |y |y1 2 | b |y |y2 3 | a |xx |y3 4 | c |zz |y4 5

Re: [SQL] Select the max on a field

"Gaetano Mendola" <[EMAIL PROTECTED]> wrote in message alq3mr$2s7o$[EMAIL PROTECTED]">news:alq3mr$2s7o$[EMAIL PROTECTED]... > Hi all, > > Suppose that I have a table like this: > > > att_1 |att_2 |att_3 | att_4 > --

[SQL] Performance inside and outside view ( WAS Re: Select the max on a field )

"Stephan Szabo" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > select distinct on (att_2) * from test > order by att_2, att_1 desc; Yes that's right it's help me, but here the optimizer have some problems: CREATE VIEW last_user_logs AS SELECT DISTINCT ON (i

Re: [SQL] Performance inside and outside view ( WAS Re: Select the max on a field )

"Tom Lane" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > "Gaetano Mendola" <[EMAIL PROTECTED]> writes: > > Yes that's right it's help me, > > but here the optimizer have some problems: > &g

Re: [SQL] functions that return a dataset or set of rows

"Brian Ward" <[EMAIL PROTECTED]> wrote in message news:aofqbd$10v5$1@;news.hub.org... > How do I create a function that returns a set of row; > > I can't seem to find the datatype that the return set should be declared as. You should wait for Postgres 7.3 currently in beta. Ciao Gaetano

Re: [SQL] 7.3 "group by" issue

- Original Message - From: "Dan Langille" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, February 21, 2003 7:01 PM Subject: [SQL] 7.3 "group by" issue > Hi folks, > > This query: > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > FROM watch_list JOIN watch_l

Re: [SQL] Column limits in table/ views

lly select a row across both tables and make a view that hides > the split? Hi, just for curiosity, can I known why do you need a table or a view with more then 560 Columns ? Usually have a big table like yours is sign of a not good design. Regards Gaetano Mendola -

Re: [SQL] postgres 7.1.3: why does the query plan ignore indexes?

gt; fundamental problem. In the mean time that the fundamental problem is solved may be a warning is usefull. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Triggers Help...

help me? What you are looking for is a replication tools, look here: http://www.postgresql.org/news/147.html Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column&#

Re: [SQL] does postgresql execute unions in parallel?

teknokrat wrote: If I have several selects joined with unions does postgresql execute the concurrently or not? nope. Regards Gaetan Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgre

Re: [SQL] SQL Syntax problem

e.g. (+)-marked equations used as a joining condition Not exactly see above Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Now() in a function

hen the transaction started, so what you see is the intended behavior, in your case you must use: timeofday(). Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Dynamic Query for System functions - now()

t; select now()+ interval'n month'; What about: select now() + n * '1 month'::intervall; Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] FATAL 2: PageIndexTupleDelete

server was lost. Attempting reset: Succeeded. Why do I have this error message and how can I fix it? Wich version are you running, and what about give us informations on your platform ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you

Re: [SQL] How to quote date value?

nobody wrote: I have found it in documentation, it is single quote. But it does not explain why SELECT '1/11/2003' AS "InvoiceDate"; returns "unknown" data type instead of "date". Why not a string ? or a fancy custom type ? Regards Gaetano Mendola

Re: [SQL] Datatype Inet and Searching

where cider >>= '10.0.0.5' The operation a >>= b mean: a contain or is equal b. Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregiste

Re: [SQL] CONTEXT on PL/pgSQL

nly technique available for seeing what's going on inside >>a plpgsql function, and crummy as it is, it's better than nothing... > > > So the CONTEXT line just tells where the statement was made? U'r right, you can get rid of that CONTEXT configuring postgresql with l

Re: [SQL] How to speed up a time dimension query

ex_tuple_cost = 0.0005 cpu_operator_cost= 0.0025 cpu_tuple_cost = 0.005 decreasing these value you decrease the cost for the index scans if the Total time is higher you have to deal with the parameter effective_cache_size in order to use more ram. Regards Gaetano Mendola --

Re: [SQL] Memory usage on subselect

d nightly and monthly cron jobs to do this for me in future. Out of curiosity, why is this deemed a DBA task rather than an automated postgres task? Once again, many thanks. You have to use the pg_autovacuum demon. Run the vacuum full and the reindex once in a week. Regards Gaetano Mendola -

Re: [SQL] FOR-IN-EXECUTE, why fail?

e problem is in the FOR but i see correct all. Can i help me? Any idea? You shall declare actual as RECORD and perform inside the loop: valor_actual = actual.last_value; Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off a

Re: [SQL] Please help - performance problems

alyze, etc...nothing worked. I post this hoping it could help somebody. How many memory are you using for the sort operations, is that column indexed ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] constraitnt on case sensetive and case insensetive columns

ar, b varchar, c varchar ); CREATE TABLE regression=# create unique index test_idx on test ( upper(a), upper(b), c); CREATE INDEX Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozde

Re: [SQL] Object Create Date

Sameer Deshpande wrote: Hello, Which data dictionary I have to query to determine the object creation date.. F.ex I would like to find out on which date table or Index has been created.. You can't. Regards Gaeatano Mendola ---(end of broadcast)---

Re: [SQL] Problems with UNION ALL and ORDER BY

This have to work. Could you please provide a real example ( creation table, insertion data and query execution ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Displaying two tables side by side

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Andreas Haumer wrote: | test=# select * from t1 right outer join t2 on (t1.ctid=t2.ctid); "full outer join" is better in this case. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

OFFSET 7) AS tk8, FROM (SELECT DISTINCT cid FROM ats) AS bob; Don't you miss for each subselect an order by tid ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

Greg Sabino Mullane wrote: Don't you miss for each subselect an order by tid ? No: since all the SELECTs are part of one statement, they will have the same (pseudo-random) implicit order. Is this guaranted ? Regards Gaetano Mendola ---(end of broa

Re: [SQL] backup of a specific schema

h one ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] backup of a specific schema

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kenneth Gonsalves wrote: | On Saturday 28 August 2004 07:46 am, Gaetano Mendola wrote: | |>Kenneth Gonsalves wrote: |> |>>On Friday 27 August 2004 01:17 pm, Michalis Kabrianis wrote: |>> |>>>Kenneth Gonsalves wrote: |>&g

Re: [SQL] backup of a specific schema

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kenneth Gonsalves wrote: | On Saturday 28 August 2004 06:18 pm, Gaetano Mendola wrote: | | |>| |>| backup/restore |> |>Well, that chapter speak about pg_dump, don't you had the curiosity |>to look at the complete opti

Re: [SQL] How to check postgres running or not ?

, in the $DATADIR you habe postmaster.pid that contains the supposed pid... Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] How to check postgres running or not ?

ering a PostgreSQL setup. Is not enough because it check only for the postmaster.pid and not if the engine is really up. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] How to check postgres running or not ?

ally works is ps aux | grep postmaster | grep -v grep (or use "ps -ef" if using a SysV-ish ps). Just to enforce the test is better looking for the entire executable path: ps aux | grep /usr/bin/postmaster | grep -v grep Regards Gaetano Mendola ---

Re: [SQL] How to check postgres running or not ?

" | is more reliable(?) It only depends on your distribution, in your case: ps aux | grep /usr/lib/postgresql/bin/postmaster | grep -v grep consider also the if you run different postmaster version in different location this is the only way I believe Regards Gaetano Mendola -BEGIN PG

Re: [SQL] A transaction in transaction? Possible?

introduced with savepoint: the exception. For more information look at: http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Your delete customer can do: * BEGIN * for all contacts call delete contact * ... * EXCEPTION * handle y

Re: [SQL] A transaction in transaction? Possible?

egin/commit/rollback blocks? Is not naive because in this way you can do what you could do with nested begin/commit/rollback blocks, in this way you can do more. Think for example in the example above to convert the "ROLLBACK TO s3" in a "ROLLBACK TO s1", that is impossi