[SQL] slow query - only uses indices

Hey Folks, This query is running really slowly. Sometimes much slower then others. I have a feeling that there may be contention on one of the indices it is using. In the explain plan, it looks like it estimates 2 rows but actually finds 228 rows? Is that really bad? Query and explain plan a

[SQL] datatype SET

Does anyone have information on how to create a datatype SET in postgres???

[SQL] Outer Joins

.col2 or a.col1 not in (select b.col2 from b) These would seem to be far more efficient than a union query (I would try this but I don't have a Unix box at the moment to install PostgreSQL on!) Marc Rohloff

Re: [SQL] Outer Joins

ot;Query Problem" for a discussion. UNION queries are, in fact, very fast >... just awkward to code and manipulate. Why should this be slower since the UNION Query still has an identical not in clause? This is far easier (for me) to read. Marc

[SQL] Returning Recordsets from Stored-procs

Is there anyway to return a recordset from a Stored Procedure in Postgres so that it can be used as a type of view or select? I know that you can do this in Interbase or MS-SQL. I have seen that you can return a complete record but that's not really the same thing. Marc Rohloff

[SQL] postgres

Hi, I in the search for a DB that would work with our product and have been told to have a look at postgres. Would you be able to foward me any information on your product and or point me to where I might be able to find some. Thank you in advance for your help! Marc

Re: [Re: [SQL] postgres]

Thank you very much Reberto, It appears that your co-workers are not inerested in potential funding. For the rude onesmaybe/perhaps people like myself were givin the email address ever think of that. A potential client that is having second thoughts. Roberto Mello <[EMAIL PROTECTED]> wro

[SQL] MD5 use in PL/Perl

I'd like to be able to only store the database of usernames and passwrods here locally as a md5 hash. (in case the black hats come to visitI'd like to make life hard for them) Using AuthPG, I should be able to create a SQL call to postgresbut there is no native md5 hashing function. I

[SQL] Extracting user db tabel info from system tables???

8 | t | prey_pkey | t | t prey| mac | {"=r","mac=rw"} | wstom| float8 | 8 | f | NULL` | NULL| NULL prey| mac | {"=r","mac=rw"} | stomcon | float8 | 8 | f | NULL` | NULL| NULL (7 rows) QUESTION 4: How do I extract also information on foreign keys from the system tables, and add two columns to the above table like the following? fkey | ftable ---+- pred_pkey | pred pred_pkey | pred pred_pkey | pred NULL | NULL NULL | NULL NULL | NULL NULL | NULL I do thank you very much in advance on any hints on how to juggle around with PostgreSQL system tables. Your's Marc Cromme [EMAIL PROTECTED]

[SQL] Problem using IP functions

ing everything to type text, but that didn't do the trick. ipdb=> select texteq(host('216.46.13.0/24')::text, '216.46.13.0'::text); texteq f (1 row) Any insight would be greatly appreciated. Marc ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl

[SQL] Select most recent record?

y id); There must be a better way to do this; any tips? Thanks, - Marc ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

RE: [SQL] Select most recent record?

From: Tom Lane [mailto:[EMAIL PROTECTED]] > > "Marc Sherman" <[EMAIL PROTECTED]> writes: > > I'd like to select the newest (max(timestamp)) row for each id, > > before a given cutoff date; is this possible? > > select * from log order by timestamp d

RE: [SQL] Select most recent record?

From: Mark Hamby [mailto:[EMAIL PROTECTED]] > > Marc, > Did you ever get your problem solved to your satisfaction? > We have a very simular problem with a historical database > containing typically 5K id and updates to data every few > seconds. We tried unsuccessfully to optim

Re: [SQL] Index of a table is not used (in any case)

pecial thing I do is every few days I reindex the table involved to reclame the space burned by the indexes not reclaiming space on deletion of rows. What other good and useful things could I do? Thanks marc > > -Josh > > __AGLIO DATABASE SOLUTIONS_

Re: [SQL] Slow SELECT -> Growing Database

if I remember correctly update is handled by inserting a new row and deleteing the old row. So it looks like 540,000 index entries changed per day. good luck marc > I've two other ones, that were mentioned in my previous emails, that > have 12.000 records each, and: > - insert 48 re

Re: [SQL] XML to Postgres conversion

there is middleware available to perform this, but am having some > > difficulty in finding the actual applications. I am trying to stay > > with open source applications, if possible. Can anyone give me any > > suggestions or resources to pull from? > > > >

Re: [SQL] Editor for pgsql

gt; > - James James, That sounds very ugly, I will usually have 1-4 files per db. Either everything goes into 1 file, drops at the front then creates. Or 2 files, 1 for ddl( create/drop table) and another for plpgsql procedures and triggers. Sometimes I will split each of those i

[SQL] convert a bigint into a timestamp

Hello, Does anyone know how to convert a bigint into a date or timestamp in a SQL query. Thanks a lot. Marc __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com ---(end of broadcast

Re: [SQL] 1 milion data insertion

zakal$ > zakal$ DEBUG: recycled transaction log file 0001 > -- > > the log has overflowed. > > Ok, this was a test. I'd like to know what would be happen. > But,

[SQL] Triggers for inserting on VIEWS

Hi, at least with PostgreSQL 7.1 it was possible to create a trigger on a view. 7.2 seems to fail with: psql:t:25: ERROR: CreateTrigger: relation "egg_view" is not a table is there any replacement so that inserting somewhere acts on multiple tables ? Thank you. Code reference: (stupid, re

Re: [SQL] Triggers for inserting on VIEWS

On Sat, 3 Aug 2002, Marc SCHAEFER wrote: > is there any replacement so that inserting somewhere acts on multiple > tables ? Thanks for the suggestion to use RULES. My solution (comments welcome): DROP RULE r_entree_rapide_ecriture_insert; DROP FUNCTION f_entree_rapide_ecriture_inser

Re: [SQL] slowing down too fast - why ?

select speed, it does other stuff. Try something like this: echo 'select now() ; select count(*) from yourtable; select now()'|psql dbname to try to localize the problem. good luck marc > On Sun, 11 Aug 2002 [EMAIL PROTECTED] wrote: > > > > > and I forgot to me

[SQL] retrieving specific info. from one column and locating it in another

tewith info in it similar to 'UPS NS Ground' How do I create a second column called 'Delivery' and pull only the 'NS' out of the Route column and put it into the 'Reciever' column? Similarly how woul

[SQL] INSERT INTO VIEW - Replacement

,'c2'); (here a row in "a" with aval 'a1' should not be created but referenced.) (Same for "b"). "Insert into view" would't work, how could I do my INSERTS efficiently in postgres without having eache time an extra query which asks whether th

[SQL] Order of columns in a table important in a stored procedure?

Hi, I use a FOR one_row IN loop where one_row is of type saisies%ROWTYPE. The loop does a SELECT on a table, bennes, changing a few values. The idea is that the function should return some bennes rows, but with additional information, which makes the returned rows a saisies table-like row set. I

[SQL] Returning a Cross Tab record set from a function

igher level language, but it drives me crazy that I can't perform this operation as a function inside of Postgres... Thanks for any thoughts you might have... -- Marc Wrubleski ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] Returning a Cross Tab record set from a function

d like to have a function defined at the database level. Many Thanks, Marc Wrubleski On Mon, 2005-06-06 at 12:20 +0200, KÖPFERL Robert wrote: |-Original Message- |From: Marc Wrubleski [mailto:[EMAIL PROTECTED]] |Sent: Mittwoch, 01. Juni 2005 16:15 |To: pgsql-sql@postgresql.org |Subject

[SQL] Last access date for databases/users

Is there anyway to determine when a database was last accessed, or when a user last connected to the database using the pg_* views? I'm trying to determine what databases in my system are currently active. ---(end of broadcast)--- TIP 9: In vers

Re: [SQL] Ask To Optimize Looping

be used is not readily predictable. Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join. " HTH, Marc Mamin

[SQL] record to columns: syntax question and strange behaviour

lumn1, (test(column1)).* FROM (values(1),(2)) foo => 1,2,3 2,3,4 NOTICE: done: 1 NOTICE: done: 1 NOTICE: done: 2 NOTICE: done: 2 Is there a way to avoid it ??? Thanks, Marc Mamin

Re: [SQL] record to columns: syntax question and strange behaviour

Hello, Your proposal unfortunately does not work if you try to query more than one value and want additional columns in the results, like in select column1,test(column1) FROM (values(1),(2)) foo cheers, Marc Mamin >IMO easiest would be to include a RETURNS SETOF record in

[SQL] workaround for missing ROWNUM feature with the help of GUC variables

Hello, here my two pence on this recurring thema. (just a workaround) regards, Marc Mamin The PG parameter must be set to allow defining own configuration variables

Re: [SQL] workaround for missing ROWNUM feature with the help of GUC variables

nately still using 8.3. sorry not to have mentioned that. Marc Mamin

[SQL] Controlling join order with parenthesis

s=2400 width=4) Sort Key: t1.i -> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4) best regards, Marc Mamin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Overlapping Ranges- Query Alternative

or: Select Groups, generate_series FROM ranges JOIN generate_series(10,50,10) on ( ColumnA < generate_series) ORDER by Groups , generate_series ; regards, Marc Mamin From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andr

[SQL] First aggregate with null support

fault init condition of the aggregate :-( I also have a working one using an array function (first_wnull_a, below) , but I wonder if there is a simpler solution ... best regards, Marc Mamin CREATE OR REPLACE FUNCTION first_element_state(anyarray, anyelement) RETURNS anyarray AS $$ SELECT CASE

Re: [SQL] conditional aggregates

something like ? Select min (case when X > 0 then X end) HTH, Marc Mamin From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Marcin Krawczyk Sent: Mittwoch, 8. Dezember 2010 14:20 To: Pavel Stehule Cc: pgsql-sql@postgresql.org Subject:

[SQL] checking for the existence of a current_setting ?

ay to make the check more smoothly, i.e. without relying on the exception ? maybe some undocumented internal function ? many thanks, Marc Mamin CREATE OR REPLACE FUNCTION public.var_get_check(int,text) RETURNS text AS $BODY$ BEGIN return current_setting('publ

Re: [SQL] Prevent double entries ... no simple unique index

same, but requires less space: create unique index on log(state) WHERE state IN (0,1); best regards, Marc Mamin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] reduce many loosely related rows down to one

your "raw" data are as following: id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv +---+---+-+-+--+--+-+- 1 | 2 | 3 | 4 | t | | | | 1 | 2 | 3 | 5 | t | | | | 1 | 2 | 3

Re: [SQL] Advice on re-writing a SELECT query.

n b ON ((a.a,a.b,a.c)<>(b.a,b.b,b.c)) but beware if null values are involved( 1<>NULL => NULL). In this case you can use : select * from a join b ON ((a.a,a.b,a.c) IS DISTINCT FROM (b.a,b.b,b.c)) regards, Marc Mamin

Re: [SQL] reduce many loosely related rows down to one

lement ); regards, Marc Mamin > -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Torsten Grust > Sent: Dienstag, 28. Mai 2013 17:08 > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] reduce many loosely

Re: [SQL] delete where not in another table

1.user_id ) regards, Marc Mamin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] value from max row in group by

ution with distinct on: select distinct on (stts_id, stts_offset) stts_id, stts_offset+stts_duration from table order by stts_id, stts_offset desc Marc Mamin From: pgsql-sql-ow...@postgresql.org on behalf of Gary Stainburn Sent: Thursday, July 25, 2013 10:

Re: [SQL] Need to subtract values between previous and current row

://www.omegahat.org/RSPostgres/ This may be a solution to implement complex cross-rows aggregation. But I never got the time to test it; I'd be interested to know which concerns this solution can show (stability, memory & cpu load, concurent requests) Cheers, Marc

[SQL] a way to generate functions dynamically ?

BEGIN return p2 + ' || p1 || '; END; $BODY$ LANGUAGE ''plpgsql'' VOLATILE '; ERROR: syntax error at or near "BEGIN" SQL state: 42601 Character: 156 Thanks, Marc

[SQL] EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?

0 * 1031 20 1132 21 In the following function, I would like to use a prepared statement for the update command but I get stuck with the tho different meanings of EXECUTE ... Is there a way to achieve this ? Thanks, Marc CREATE OR REPLACE FUNCTION tes

Re: [SQL] EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?

, much time is lost in i/o wait) Cheers, Marc ---(end of broadcast)--- TIP 1: 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

Re: [SQL] SQL stored function inserting and returning data in a row.

> What about > $$ > INSERT INTO ; > select currval('seq_matchmaking_session_id'); > $$ language sql; > > ? Hello, I'm not sure that this would return the correct id in case of concurrent calls to your function. I'm using following kind of functio

Re: [SQL] SQL question: Highest column value of unique column pairs

order by date desc,name regards, Marc Mamin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin Jenkins Sent: Saturday, January 12, 2008 1:10 AM To: pgsql-sql@postgresql.org Subject: [SQL] SQL question: Highest column value of unique column pai

Re: [SQL] dynamic events categorization

ruct and at least part of the tests should happen on indexes only. If the query is run very often, you may want to add a boolean column is_past on show_date, and have a separate job that put the concerned records to true every x minutes ... HTH, Marc Mamin SELECT s.id_event_subtype,

Re: [SQL] exclude part of result

, p.b, p.c HTH, Marc Mamin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] varchar::timezone conversion

(varchar,varchar) RETURNS int8 AS $body$ SELECT EXTRACT(EPOCH FROM $1 AT TIME ZONE $2)::int8*1000; $body$ LANGUAGE 'sql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER; Thanks for your help, Marc Mamin; -- Sent via pgsql-sql mailing list (pgsql-sql@postgres

Re: [SQL] Query how-to

osedCount sum(case when status ='New' then 1 else 0 end) as openedCount from Yourtable where status in ('Closed','New') )x group by adate order by adate HTH, Marc

Re: [SQL] Query how-to

n 1 else 0 end) as openedCount from Yourtable where status in ('Closed','New') group by case when status ='Closed' then stop_date else start_date end order by adate Marc > Hi, > What about something like that ? > select adate, sum(openedCount) as o

[SQL] aggregation problem: first/last/count(*)

set? This would allow to make a single scan of the table. something like select a_group, first(category) as first_category, last(category) as last_category, ... from test order by a_group,time Many thanks for any hints. Marc Mamin Here are some dummy values if you'd like to play with

Re: [SQL] aggregation problem: first/last/count(*)

hives/68-More-Aggreg ate-Fun-Whos-on-First-and-Whos-on-Last.html But its is slightly slower as my solution. I'll still make a test with more data As I guess that swapping will grow fatser mith my query than with the first/last aggregate functions. cheers, Marc Mamin -Original

[SQL] select regexp_matches('a a a', '([a-z]) a','g');

ches the first 2 'a', only the trailing ' a' will be used to seek for further matching... Cheers, Marc Mamin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] constraint/restrict

, ... country: 2, ... now country wouldn't be allowed to be deleted. how to do that? thanks fo help olaf -- soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23, 3013 Bern / Switzerland fon:+41-31-332 9782, mob:+41-76-572 9782 mailto:[EMAIL PROTECTED], http://www

[SQL] cannot create sequence

hi there, as it seems postgresql 7.0 has trouble to create ver_id_seq and own_id_seq there is nothing visible with \dt \ds \di are these names occupied for other use? thanks for help olaf -- soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23, 3013 Bern

[SQL] sum(bool)?

hi there, i want to add up the "true" values of a comparison like sum(a>b) it just doesn't work like this any workaround? it is postgresql 7.0 under linux thanks olaf -- soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23, 3013 Bern / Swit

[SQL] a tricky one

re-clause. what's wrong can anybody help, this goes over my know-how :-) happy weekend and thanks for the great help lately from the list olaf -- soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23, 3013 Bern / Switzerland fon:+41-31-332 9782, mob:+41-76-572 9782 mailto:[EMAIL PROTECTED], http://www.soli-con.com

[SQL] 2 tables, joins and same name...

dest.dest_name, air.name as airport1, air.name as airport2 from destination, airport air where dest.airport_dep_id_id=air.airport_id and dest.airport_arr_id=air.airport_id; This is not good... Any help? Thanks! -- Marc Andre Paquin ---(end of broadcast

[SQL] List archives moved and cleaned up ...

Finally figuring that enough is enough, I've been spending the past few days working on the list archives ... I've reformatted, so far, the following lists into a cleaner format: pgsql-hackers pgsql-sql pgsql-bugs pgsql-general pgadmin-hackers pga

[SQL] pgsql-performance mailing list / newsgroup created

Morning all ... Josh Berkus the other day shook my cage a bit and reminded me to create the -performance list that had been discussed awhile back ... so I did :) [EMAIL PROTECTED] or comp.databases.postgresql.performance archives wont' show it up yet,

[SQL] Testing gateway

In theory, the news2mail gateway is back in place ... ---(end of broadcast)--- TIP 8: explain analyze is your friend

[SQL] removing precision from timestamp (microseconds) ..

>From the docs, if you do: traffic=# select CURRENT_TIMESTAMP(0); timestamptz 2003-10-13 11:04:09-03 (1 row) the 0 reduces the precision of the time to get rid of the microseconds ... is there a way of having this done by default on, if anything, a per connection

Re: [SQL] off subject - pg web hosting

http://www.hub.org http://www.pghoster.com http://www.commandprompt.com On Thu, 6 Nov 2003, chester c young wrote: > can anybody recomend web hosting that provides postgresql? I have > found a couple, but their pricing is several times the going rate using mySql. > > ___

Re: [SQL] [ADMIN] Field Size

If you define the field as CHAR, 1000 bytes will be consumed. If you define the field as VARCHAR, on sufficient bytes to store the contents will be used. Marc --Original Message Text--- From: Ganesan Kanavathy Date: Mon, 8 Dec 2003 20:56:06 +0800 Clean Clean DocumentEmail

[SQL] simple LEFT JOIN giving wrong results ...

16900355 1088 | 246166684 1227 | 44816947957 1179 | 3867502285 (8 rows) the storage/avg values come out right in the JOIN, but the traffic/sum values get royally screwed ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED]

Re: [SQL] simple LEFT JOIN giving wrong results ...

On Thu, 11 Dec 2003, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > So, I'm doing my LEFT JOIN wrong *somehow*, but its eluding me as to > > what/how .. :( > > > ams=# select ct.ip_id, sum(ct.bytes) as traffic, >

[SQL] Skip dups on INSERT instead of generating an error ...

that it just skips over those records. First thought would be to write a quite plpgsql function that would do a SELECT first, to see if the value already exists, and if not, then do the INSERT ... but am wondering if maybe there is a cleaner way that I'm not thinking of? Marc G

Re: [SQL] Ok, what am I doing wrong here?

; is invalid > inside the values part of the definition. > > SQL's language specification says otherwise, as does "\h insert" from the > command line. > > The query stand-alone returns a table with values that are valid for the > table I wish to insert into. > > Wh

[SQL] How do FKs work?

le, to make sure it exists ... Is this correct? So, its effectively having to do 3278 "SELECTS" against the REFERENCED table? (two fields have contraints on them, 1639 rows to be deleted) ... ? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) E

Re: [SQL] How do FKs work?

On Sat, 9 Oct 2004, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Have a table with two FKs on it ... 2 different fields in the table point to the same field in another table ... When I do an 'EXPLAIN ANALYZE DELETE FROM table WHERE field = #;', it n

Re: [SQL] How do FKs work?

On thing I failed to note here, that is probably critical ... its a 7.3 database ... On Sat, 9 Oct 2004, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Have a table with two FKs on it ... 2 different fields in the table point to the same field in another table .

[SQL] Easier way to 'strip' on multiple matches?

I need to strip out all spaces, and all \' from a string ... is there an easier way then doing: select lower(replace(replace(name, ' ', ''), '\\\'', '')) from business; Thanks ... Marc G. Fournier Hub.Org Networking Ser

[SQL] PL/PgSQL - returning multiple columns ...

two fields of a table, or, in the case of the above, one column and oen 'group by' column? thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664

Re: [SQL] PL/PgSQL - returning multiple columns ...

Perfect, worked like a charm ... but the RETURNS still needs to be a SETOF, other then that, I'm 'away to the races' ... thanks :) On Wed, 2 Feb 2005, George Weaver wrote: Hi Marc, One option is to create a simple data type and return the rowtype of the datatype eg CREATE TYPE

[SQL] Major flood of mail to lists ...

Do to moderator error (namely, mine), several hundred messages (spread across all the lists) were just approved ... Sorry for all the incoming junk :( Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy

[SQL] UPDATEABLE VIEWS ... Examples?

Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't seem to find any examples of this ... Does anyone know of an online example of doing this that I can read through? Thanks .

[SQL] Putting an INDEX on a boolean field?

Does that make sense? Would it ever get used? I can't see it, but figured I'd ask ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of

Re: [SQL] Putting an INDEX on a boolean field?

On Fri, 17 Jun 2005, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Does that make sense? Would it ever get used? It could get used if one of the two values is far less frequent than the other. Personally I'd think about a partial index instead ... H

[SQL] using 'zoneinfo' to manipulate dates ...

id = table.timezone )) = '2004-12-12'; Something like this, but that works: # select ( now() || ' ' || 'PST8PDT' )::timestamp; ERROR: invalid input syntax for type timestamp: "2005-06-26 00:23:29.789703-03 PST8PDT" Marc G. Fournier Hu

[SQL] ARRAYs and INDEXes ...

)[12] ); Is it possible to create an INDEX on customers.monthly_balance such that I could do something like: SELECT * FROM customers WHERE monthly_balance[6] = 0.00; As an example ... or SELECT * FROM customers WHERE 0.00 = any (monthly_balance); Marc G. Fournier Hub.Org Netw

[SQL] pl/PgSQL: Samples doing UPDATEs ...

ld be great) online? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend

[SQL] PL/SQL Function: self-contained transaction?

de of it) are considered one transaction, can you do a begin/end within the function itself to 'force' commit on a specific part of the function? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy

Re: [SQL] [HACKERS] ALTER TABLE ( smallinto -> boolean ) ...

Moved off of -hackers, since its long gotten out of that realm :) On Thu, 1 Sep 2005, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: On Mon, 29 Aug 2005, Tom Lane wrote: No, because there's no built-in cast from smallint to bool. 'k, I just took

[SQL] a "catch all" type ... such a thing?

Are there any data types that can hold pretty much any type of character? UTF-16 isn't supported (or its missing from teh docs), and UTF-8 doesn't appear to have a big enough range ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL

[SQL] convert timezone to string ...

I know that the server knows that ADT == -0400, and AST == -0300 ... is there any way of reversing that? Basically, I want to say: SELECT timezone_str(-0400, 'not dst'); and have it return ADT ... I've got a method of doing it right now, using a function, but just find it looks so messy, ju

Re: [SQL] convert timezone to string ...

On Tue, 25 Oct 2005, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: I know that the server knows that ADT == -0400, and AST == -0300 ... Other way around isn't it? Unless Canada observes a pretty strange variety of daylight saving time ;-) I knew I

[SQL] pl/* overhead ...

Does anyone know of, or have, any comparisions of the overhead going with something like pl/perl or pl/php vs using pl/pgsql? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ

[SQL] padding an integer ...

ble to find, I suspect I'm not going to have much of a choice ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)---

Re: [SQL] pl/* overhead ...

On Wed, 26 Oct 2005, Michael Fuhr wrote: On Wed, Oct 26, 2005 at 12:58:13AM -0300, Marc G. Fournier wrote: Does anyone know of, or have, any comparisions of the overhead going with something like pl/perl or pl/php vs using pl/pgsql? Benchmark results will probably depend on the type of

[SQL] # of 5 minute intervals in period of time ...

Is there a simpler way of doing this then: select (date_part('epoch', now()) - date_part('epoch', now() - '30 days'::interval)) / ( 5 * 60 ); Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECT

[SQL] FOREIGN KEYs ... I think ...

ld I be searching on / reading for this one? Pointers preferred, especially one with some good examples :) Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 --

Re: [SQL] FOREIGN KEYs ... I think ...

On Wed, 4 Jan 2006, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Now, what I want to do is add a FOREIGN KEY (again, I think) that when incident_summary.status is changed (either closed, or reopened), the associated records in incident_comments are changed t

Re: [SQL] FOREIGN KEYs ... I think ...

was havin gto figure out the 'active remark record' doing a 'max(create_time)' ... On Wed, 4 Jan 2006, Marc G. Fournier wrote: I'm not sure if I'm looking at (for) the right thing or not, but if I am, then I'm not finding any useful examples :( I

[SQL] Update counter when row SELECT'd ... ?

27;ve checked CREATE TRIGGER, and that doesn't work 'ON SELECT' ... is there anything that I *can* do, other then fire back an UPDATE based on the records I've received? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL P

[SQL] Reverse Index ... how to ...

verse order, so would need the INDEX to go from 'GREATEST to LOWEST', vs 'LOWEST to GREATEST' ... Thx Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 761

  1   2   >