Re: [SQL] I need some magical advice

OULD be updated and verify your query is going to do what you want before you clobber data. (or use a transaction, but if its a live database you don't want a transaction around locking users out) Terry Terry Fielder te...@greatgulfhomes.com Associate Director Software Development and D

Re: [SQL] Case Insensitive searches

that approach. I have never done this but I have read about it. I'm sure it can be done. > > CREATE INDEX indtest_01 ON table_01 > ((SUBSTRING(month_year, 3, 4) || SUBSTRING(month_year, 1, 2)) > > 2008/8/4 Terry Lee Tucker <[EMAIL PROTECTED]> > > > On Monday 04 A

Re: [SQL] Case Insensitive searches

On Monday 04 August 2008 11:09, Frank Bax wrote: > Terry Lee Tucker wrote: > > On Monday 04 August 2008 10:05, Richard Broersma wrote: > >> On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <[EMAIL PROTECTED]> wrote: > >>> In some db's if you > >>>

Re: [SQL] Case Insensitive searches

can. > > > -- What about using the operator, ~* ? Does that cause a table scan as well? -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 40

Re: [SQL] First day of month, last day of month

On Thursday 24 April 2008 10:47, Bart Degryse wrote: > Well, that's what it does afaikt. And what does afaikt mean? -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 3

Re: [SQL] outer join issues

loan.dataset = 0 AND share.dataset = 0 AND draft.dataset = 0 ; try AND (share.dateset = 0 OR share.dataset IS NULL) AND (draft.dataset = 0 OR draft.dataset IS NULL) because when the left join is utilized, the dateset field will be a null, which is not =0 and hence would fail the AND clause in

Re: [SQL] Authentification failed

Connecting as root may be ill advised (doing stuff as root is a bad idea unless one HAS to). All that notwithstanding, you need to setup the correct permissions to allow the connections you want in pg_hba.conf, usually is /var/lib/pgsql/data/pg_hba.conf Terry Terry Fielder [EMAIL

Re: [SQL] Getting pk of the most recent row, in a group by

Do you have a table of coupon types? Terry Terry Fielder [EMAIL PROTECTED] Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 Bryce Nesbitt wrote: I've got a table of "coupons" which have an expiration date. F

Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...)

quote_literal() works.Thanks a lot! From: Richard Huxton <[EMAIL PROTECTED]> To: Nemo Terry <[EMAIL PROTECTED]> CC: pgsql-sql@postgresql.org Subject: Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...) Date: Tue, 15 May 2007 08:12:55 +0100 N

Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...)

But I must use it in function,so... Do you have another solution? From: "Rodrigo De Le�n" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org CC: "Nemo Terry" <[EMAIL PROTECTED]> Subject: Re: [SQL] How to process inverted comma in "EXECUTE 'insert into x

[SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...)';"?

Look at this problem: when execute 'insert into lse_installations values(' || ||obj_id|| || ',' || ||div|| || ',' || ||sub|| || ',' || ||obj_type|| || ',' || ||obj_name|| || ',' || ||pstcd|| || ',' || ||rdcd|| || ',' || ||blkno|| |

[SQL] How to use function PointN?

select PointN(envelope(polyline),1) from highway; return null,why? _ 享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com ---(end of broadcast)--- TIP 1: if posting/reading through U

Re: [SQL] Which function transform x,y to latitude/longitude?

I just know the correct data must be longitude 103.926669,latitude0.111827. x,y from Cassini system. Could you give me the source code how you calculate.Thanks a lot! From: Michael Fuhr <[EMAIL PROTECTED]> To: Nemo Terry <[EMAIL PROTECTED]> CC: pgsql-sql@postgresql.org Subject: Re:

[SQL] Which function transform x,y to latitude/longitude?

Hi, For example x= 38356.62 y= 42365.19.how to transform it to latitude 1.399948, longitude 193.92644? Which function I could use? I don’t know the algorithm. Initial data is: ("+proj=cass +a=6378137.0 +rf=298.257223563 +lat_0=1.287639n +lon_0=103.8516e +x_0=3 +y_0=3"). Thanks a lot.

Re: [SQL] migrating numeric to serial from MSSQL to postgres

had to do that, fortunately not for many statements :) Terry Fielder [EMAIL PROTECTED] Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 Kenneth Gonsalves wrote: hi, am migrating a database from MSSQL to postgres. How would i migrate this

Re: [SQL] About sequences that works BAD !!!!

On Wednesday 14 June 2006 02:02 pm, "Alexis Palma Espinosa" <[EMAIL PROTECTED]> thus communicated: --> Hello everyone: --> --> --> --> We are working with serials fields and we found a problem with then: When we insert in a table that has e unique restrict, and this makes insert fails, the seque

Re: [SQL] problem with uniques and foreing keys

On Saturday 06 May 2006 01:27 pm, "kernel.alert kernel.alert" <[EMAIL PROTECTED]> thus communicated: --> Hi list... --> --> Please i have a problem with this... --> --> I create the follow tables... --> --> --> --> CREATE TABLE empresa ( -->

Re: [SQL] Simple plpgsql question

On Thursday 13 April 2006 11:38 pm, "Todd Kennedy" <[EMAIL PROTECTED]> thus communicated: --> Hi, --> --> I have, what I hope to be, a simple question about plpgsql. --> --> I have a trigger on a table right now that updates a count everytime --> that a new record is entered into a database (or re

Re: [SQL] concatenation with a null column (using ||) nulls the result?

On Monday 10 April 2006 05:55 pm, Alvaro Herrera saith: > Neil Harkins wrote: > > Note: The cabinets_description for the "548-4th-Cab1" row is " ", > > not NULL, hence it being displayed. Is this standard SQL behavior? > > Yes; something || NULL yields NULL. If you want NULL to behave as "" > for

Re: [SQL] Query from shell

On Thursday 06 April 2006 02:37 pm, Judith saith: >Hi every body, somebody can show me hot to execute a query from a > shell > > thanks in advanced!!! > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >

Re: [SQL] Custom type

On Wednesday 22 March 2006 03:25 pm, Daniel Caune saith: > Hi, > > How can I enter description for my custom types? > > \dT provides information such as schema, name, and description for all > the registered types and custom types. I would like to provide a > description for each custom type I cr

Re: [SQL] Merry Xmas and a Happy New Year

Merry Christmas to you. On Friday 23 December 2005 09:16 am, Achilleus Mantzios saith: > to All! > > -- > -Achilleus > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq --

Re: [SQL] casting character varying to integer - order by numeric

umber(t.section, text()), t.section And if the field section can actually START with an alpha, then to prevent to_number from failing do this: to_number(textcat('0', t.section), text()), t.section Terry Bryce W Nesbitt wrote: How can I force a character field to sort as

Re: [SQL] NULL in IN clause

se. Therefore No rows returned. Ever. Terry And it resulted is zero rows. Without NULL it is OK. Is this a bug, or the standard has such a rule? Best Regards, Otto ---(end of broadcast)--- TIP 4: Have you searched our list archive

Re: [SQL] query

now() returns a timestamp. Cast it to a date and then you can subtract days. e.g. select now()::date -1 Terry Chandan_Kumaraiah wrote:   Hi,   In oracle we write sysdate-1 For example,we write a query (select * from table1 where created_date>=sysdate-1).Whats

Re: [SQL] Simple Question

* '478'::int4 > / '45.0'::float8 > )::int4::reltime::interval ; > interval > -- > 10:37:20 > (1 row) > > I don't know if "::int4::reltime::interval" is the best > way to end up with an interval, but its the only way I > could fi

Re: [SQL] Simple Question

Thank you for the reply in spite of the subject. On Tuesday 11 January 2005 05:15 pm, Michael Fuhr saith: > On Tue, Jan 11, 2005 at 04:42:21PM -0500, Terry Lee Tucker wrote: > > Subject: [SQL] Simple Question > > Please use a more descriptive subject -- think about how somebody

Re: [SQL] Simple Question

If anybody knows any other ways, I'd be interested in see that too. On Tuesday 11 January 2005 04:42 pm, Terry Lee Tucker saith: > Hello: > > I'm trying to figure out how to convert a floating point value into an > interval of time. I'm calculating the time required to drive

[SQL] Simple Question

Hello: I'm trying to figure out how to convert a floating point value into an interval of time. I'm calculating the time required to drive from point A to point B. For the sake of this question, we'll just say it is miles/speed. So: drv_time = 478 / 45.0; The value of this is: 10.6

Re: [SQL] NULLS and string concatenation

There is an easy solution anyway, use coalesce to ensure you are never returning a null result for any components of the concat. e.g. select 'some text, blah:' || coalesce(NULL, '') equates to 'some text, blah:' || '' hence 'some text, blah:

Re: [SQL] NULLS and string concatenation

Slick ;o) This goes in my tool kit... On Friday 19 November 2004 03:03 pm, Gregory S. Williamson saith: > Someone on this list provided me with a rather elegant solution to this a > few weeks ago: > > CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text, > text) RETURNS text A

Re: [SQL] ORDER BY and NULLs

first most value that your TO result can return. Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of T E Schmitz &

Re: [SQL] next integer in serial key

character(10) | not null Hence SELECT nextval('public.gbs_floorplans_floorplan_id_seq'::text) Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED

Re: [SQL] next integer in serial key

serts are done OR 2) NONE of the inserts are done (Note it doesn't roll back the sequence, that id on rollback would become unused) Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Messag

Re: [SQL] next integer in serial key

> > Alternatively, you can do: > > INSERT (accepting the default) > > then SELECT currval(the_sequence_object); > > then > > > > NOTE: 2nd method assumes that nobody else called nextval() on the > > sequence between when you did the > > insert and when you did the select currval(). Note that > b

Re: [SQL] next integer in serial key

lock on the sequence. I do not recommend the 2nd method, too much can go wrong. Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: Kenneth Gonsalves [mailto:[EMAIL PRO

Re: [SQL] "=" operator vs. "IS"

. NOTE: A related topic is OUTER JOIN's which is how the above join would properly be implemented Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED

Re: [SQL] Formatting Functions and Group By

Thank you both for your responses. That's just what I needed and thanks for catching my mistake Tom. And may I say that I am VERY happy to be moving to Postgres. The lack of a native Win32 version was thing only thing holding us back from Postgres previously. I think this is the only kin

[SQL] Formatting Functions and Group By

Hi, I'm porting a bunch of queries from MySQL to Postgres 7.4 and am having a problem with one particular area. For example, a query like this works in MySQL: select to_char(myCol,'Mon YY') from myTable group by to_char(myCol,'MM ') order by to_char(myCol,'MM ') Postgres will gi

Re: [SQL] Trying to make efficient "all vendors who can provide all items"

Of all the proposed solutions, this appears to run the fastest, and not require the creation of an additional table. Thanks! Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- >

Re: [SQL] Trying to make efficient "all vendors who can provide all items"

That's pretty nifty code. It certainly looks nicer, and looks like it would work providing vendor_id&item_id is the pk of item_vendors (and it is). I will let you know if it runs any faster... Thanks Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ash

Re: [SQL] Trying to make efficient "all vendors who can provide all items"

misunderstand your query? Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: Jeremy Semeiks [mailto:[EMAIL PROTECTED] > Sent: Monday, March 08, 2004 2:07 PM > To: [EMAIL PRO

[SQL] Trying to make efficient "all vendors who can provide all items"

d AND items_2.item_id = 'item_4' AND items_5.vendor_id = vendors.vendor_id AND items_2.item_id = 'item_5' Yep, both my solutions are pretty ugly, especially in situations where my list of items that need to be provided grow large. There must be a better way. Can anyone help

Re: [SQL] CHAR(n) always trims trailing spaces in 7.4

> select 'x'||' '||'x' > > should produce xx, but it produces x x. > INCORRECT This select 'x'||' '::char ||'x' Should produce xx This select 'x'||' '||'x' is restateable as select 'x&

[SQL] Return relation table data in a single value CSV

X LIMIT 1, and the ',' is wrapped with a case statement to hide the comma if there are no further elevations. It gets very messy very fast as and further I end up hard coding the max number of elevations. Any ideas? Terry Fielder Manager Software Development and Deployment Great Gulf

Re: [SQL] Novice SQL Question

To answer my own question: I discoverd that the order by fields had to be in the select list. Apparently, this is a requirement when using "DISTINCT". On Monday 02 February 2004 05:38 pm, Terry Lee Tucker wrote: > I need to the following query: > select distinct event_code, leve

[SQL] Novice SQL Question

I need to the following query: select distinct event_code, level from logs join stat on (stat.prime is not null) where order_num = 130680; Ok, no problem. Does exactly what I want; however, I need to sort this is a particular way to get the right results. When I try to add the order by clause,

Re: [SQL] How to quote date value?

If you want an explicit date, then cast it like this: SELECT '1/11/2003'::date AS "InvoiceDate"; Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- &

Re: [SQL] Select and functions

umber,'' '',''''); RETURN NEW; END; ' Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTE

Re: [SQL] OR vs UNION

uses of sample 1 prevented the use of an INDEX, Reason: It is faster to scan an index 3 times then scan this very large table once. I do not know if there is a proof to say that one can *always* replace OR's with a union, but sometimes certainly, and in this case it made things much bet

Re: [SQL] NOT and AND problem

em_id) OR NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id); Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTE

Re: [SQL] NOT and AND problem

DELETE FROM myTable WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id) AND NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id); Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085

[SQL] Parameterized Views on 7.3

Hello all, Are parameterized views already fully functional in 7.3 or I must use fuctions returning rows set ? I have been looking for that in developer documentacion /7.3.1) and in the google groups but I haven't had so many luck. Thanks. ---(end of broadcast)---

Re: [SQL] query optimization question

u_group_projects WHERE menu_code = 'WA' AND division_id = proj.division_id AND project_id = proj.project_id AND status = 'I') GROUP BY projects.project_id, projects.marketing_name Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] > -Original Messag

Re: [SQL] primary keys

primary key. Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] > -Original Message- > From: [EMAIL PROTECTED] > [mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of Huub > Sent: Wednesday, November 06, 2002 9:19 AM > To: [EMAIL PROTECTED]

Re: [SQL] query optimization question

way I can see to do a group by would be to break out the aging categories into separate queries, but that wins me nothing because each query then does its own scan... The expected simplified output of this query looks like this: Project <30 30-60 >=60lot total <30 30-

Re: [SQL] query optimization question

That looks really promising as a possibility, however I think you intended to add a group by clause. Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] > -Original Message- > From: Masaru Sugawara [mailto:rk73@;sea.plala.or.jp] > Sent:

Re: FW: [SQL] query optimization question

Actually, come to think of it, just the implementation of re-querying a temporary table could alone significantly improve performance, because the temp table would: a) have fewer records to scan on the subselects b) not require any joins Thanks! Terry Fielder Network Engineer Great Gulf Homes

FW: [SQL] query optimization question

way I can see to do a group by would be to break out the aging categories into separate queries, but that wins me nothing because each query then does its own scan... The expected simplified output of this query looks like this: Project <30 30-60 >=60lot total <30 30-

[SQL] query optimization question

proj.division_id = '#variables.local_division_id#' AND NOT EXISTS (SELECT 1 FROM menu_group_projects WHERE menu_code = 'WA' AND division_id = proj.division_id AND project_id = proj.project_id AND status = 'I') ORDER BY proj.project_id Thanks in advance Terry

Re: [SQL] Comparing Numeric and Double Precision (float8)..

Great I don't know if my customers can wait until 7.3 official release, but I'll try to distract them a bit... :-\ Thanks a lot Bruno... Bruno Wolff III wrote: > > On Mon, Nov 04, 2002 at 09:11:30 +0100, > Terry Yapt <[EMAIL PROTECTED]> wrote: > > > >

[SQL] Comparing Numeric and Double Precision (float8)..

Hello all, i DON'T know what is the proper forum to throw this question and I must to insist in this "feature". Sorry. I have a lot of tables from Oracle 8i Databases with a lot of columns with numeric(x,0) definition. Ok.. I am traslating my oracle tables to PostgreSQL tables. But I am having

[SQL] Restricting a VIEW.

Hello all, I have a doubt. In the next example, I have a table with two columns: - DATE - MONEY And a VIEW which SUM's the money GROUPing by 'month/year' (I cut off the day)... Ok.. I would like to be able to SELECT * FROM VIEW.. but restricting by complete dates (dd/mm/)... (Last select i

Re: [SQL] Limiting database size

t disks are a whole lot less expensive than terminating a database application that depends them. But then, M$ never takes any responsibility for the amount of disk space it wastes. terry ---(end of broadcast)--- TIP 5: Have you checked our ext

[SQL] SQL copy from csv with explicit field ordering

OPY [BINARY] class_name [WITH OIDS] TO|FROM filename|STDIN|STDOUT [USING DELIMITERS 'delim']; I have tried WITH OIDS but with same results. Is there somewhere that I can either enable the first line of CSV as header names OR Can I explicitly define my import field ordering fr