[SQL] error...what to do?

The I am trying to do a set difference query. The query question is as follows: 3.Find the names and costs of all abilities that Zidane can learn, but that Steiner cannot. Can anyone help with this ….please.   The tables to use are as follows: beckerbalab2=> select * from ffix_abi

Re: [SQL] Invoice number

e right at least twice a day. -- mrc > We are all of us living in the shadow of Manhattan. -- Watchmen I hope this can help ... Sorry for my poor english. George Moga, Data Systems SRL Slobozia, ROMANIA

[SQL] "SELECT" problem on 7.0.3

and conditions) I have no problems with this "SELECT", it works (the biggest structure have 10.000 rows). Any sugestions ... ??? Thanks in advance and ... sorry for my english!! George Moga, Data SYSTEMS Srl Slobozia, ROMANIA

Re: [SQL] Selecting Current value from a sequence

Try: SELECT last_value FROM sequence_name; > Regards, Najm For me works (PostgreSQL 7.0.3, Mandrake Linux 7.2). George Moga, Data Systems Srl Slobozia, ROMANIA

[SQL] plpgsql notify trigger

un_name), not the *value* of the variable nm, is passwd to the notify command. Since notify only takes a name, not a string, I don't see how to proceed. Is there some way in plsql to construct a string and have it executed in sql? disappointed in plsql, George -- George Youn

Re: [SQL] dates in functions

BEGIN RETURN date_part(\'month\',$1::datetime); END; ' LANGUAGE 'plpgsql'; CREATE test=# select anyo_hidro('1-1-1999'); anyo_hidro 1 (1 row) I use: test=# select version(); version --

[SQL] need to join successive log entries into one

g the full span of time. Null 'done' just means it's not done yet. Unfortunately, the start time of a 'succesive' op is sometimes 1 second later that the 'done' time of the previous one, so maybe using the seq field is simpler. Can anyone think of a way I c

Re: [SQL] need to join successive log entries into one

On Wed, 14 Mar 2001, you wrote: > On 3/14/01, 5:24:12 PM, George Young <[EMAIL PROTECTED]> wrote regarding [SQL] > I need to join successive log entries into one: > > I have a table like: > > > run | seq | start| done > > 1415|261| 2001

Re: [SQL] problem with copy command

to delete the unique index testpri_pk ... but if you want to create the unique index again you must delete (or modify) you'r not_unique rows. George Moga, Data Systems Srl Slobozia, ROMANIA ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Select most recent record?

query both "log" and "l1" for the same table: "log as l1"; 2. you use log.timestamp in () ... but in this case you have ony one value ... use "=" instead "in". == George Moga, Data Systems Srl Slobozia, ROMANIA P.S. Sorry for my english ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] how to add an new record from part of an old one

2 int4, b3 varchar(32)); CREATE test=# insert into b (b1, b2, b3) select a1, a2, 'something else' from a where id = 1; INSERT 580468 1 test=# select * from b;  id |   b1    | b2 |   b3 +-++   1 | 1221211 | 12 | something else (1 row)   test=# I hope

Re: [SQL] set datestyle to European PROBLEM

master start >/dev/null 2>&1" < /dev/null to su -l postgres -c "LC_ALL=C /usr/bin/pg_ctl -D $PGDATA -o '-i -o -e' -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null and, after I connect my application to the database, the first command is:

[SQL] select question

3 bar E.g. Given run 'a' and wafers (1,3) I should get one row: foo, since only foo matches both 1 and 3. Given run 'a' and wafers (3) I should get two rows: foo,bar, since both foo and bar match 3. Is there some neat way to do this in a single query? Puzzled,

[SQL] int id's helpful for indexing, or just use text names?

[linux, postgresql 7.2, 500MHz * 4 xeon cpu's, 1GB ram, hardware raid] My current db has serveral instances of something like: table foos(fooid int2, fooname text, foouser text, foobar int2 references bars(barid)) table bars(barid int2, barname text, barcolor text, primary key(barid) ) et

[SQL] design question: status table+log table, indexes, triggers

uot; automatically when "machine_log" is appended to? Thanks, George -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in "T

[SQL] Design Q.:logic in app or db?

is somewhat complex in structure. -- George -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in "The Dying Dete

Re: [SQL] "record" datatype - plpgsql

Hi Brian; Assuming "NEW" has been declared as foo%rowtype, you can access the columns thus NEW.xxx where xxx is the column name HTH. George - Original Message - From: "Brian Knox" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, May

Re: [SQL] "record" datatype - plpgsql

Brian, You can also use the "record" type as well in the same way. George - Original Message ----- From: "George Weaver" <[EMAIL PROTECTED]> To: "Brian Knox" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, May 29, 2003 2:01 PM Subje

[SQL] find open transactions/locks in 7.2?

who's locking what or who has a transaction open? -- George -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in &q

Re: [SQL] ALTER TABLE ... DROP CONSTRAINT

It works in 7.3.2.   George - Original Message - From: Elielson Fontanezi To: pgsql-general ; pgsql-sql Sent: Wednesday, July 30, 2003 10:52 AM Subject: [SQL] ALTER TABLE ... DROP CONSTRAINT Hi all!       Who can tell me what postgres version supports

[SQL] backend cpu usage? [7.2]

and from my app, though it would be nicer to get it directly through sql. -- George Young -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in "

[SQL] Table conversion query...

300.00 in other words, the negative gl's go into gldb and they make up the total for the positive gl. is there a way to accomplish this in postgresql? or should I implement it inside the java app? thanks george __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-t

Re: [SQL] Table conversion query...

--- Joe Conway <[EMAIL PROTECTED]> wrote: > George McQuade wrote: > > date tran glamt > > 08/20/03 1001 3010 -30.00 > > 08/20/03 1001 1030 -300.00 > > 08/20/03 1001 1060 +330.00 > > 08/20/03 1002 ...next transaction > > ... > > an

[SQL] Strange behavior with timestamptz

record is the last one in the procedure to be created.   I am using version 7.3.2.   I hope I'm not missing something obvious...   Thanks for your help, George

Re: [SQL] Strange behavior with timestamptz

) conventions (1 row) In both cases the month is being sent to the stored procedure first, but in the first instance (month < 13) it is being interpreted as the day. George - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "George Weaver" &l

Re: [SQL] Strange behavior with timestamptz

zle this one through as well). Sorry for the confusion. George - Original Message - From: "George Weaver" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, August 25, 2003 9:51 PM Subject: Re: [SQL] Strange be

Re: [SQL] Help me

Hi Yaroslav,   You must set the language as:     LANGUAGE 'plpgsql';   Regards, George - Original Message - From: Yaroslav Ulyanov To: [EMAIL PROTECTED] Sent: Thursday, September 04, 2003 2:46 AM Subject: [SQL] Help me Hello   I cannot

Re: [SQL] [GENERAL] plPGSQL bug in function creation

- A | B | A | B (1 row) e=# select version(); version -- PostgreSQL 7.3.2 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2 20020927 (prerelease) (1 r

[SQL] Case Insensitive comparison

hai   i am using postgresql 7.3.x. I am converting a database in MS SQL server to PostgreSQL.   The main problems i am facing is that in sql server the text comparisons are case insensitive. how can i compare text case insensitive in postgresql without using an upper() or lower() function in both

Re: [SQL] Case Insensitive comparison

Thanks to all of you for your valuable suggesstions does postgresql internally uses the = operator(text,text) for any other purposes. i think that overloading it solves the index problem too...     Tom Lane <[EMAIL PROTECTED]> wrote: Josh Berkus <[EMAIL PROTECTED]>writes:>> How can i create a f

[SQL] Capturing pgsql ERRORS/NOTICES to file

ormation.   Thanks, George

Re: [SQL] Capturing pgsql ERRORS/NOTICES to file

Hi Josh, Thanks for the reply. What I am trying to achieve is to have errors go to a file, rather than show up on the screen. Is this possible? George - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: "George Weaver" <[EMAIL PROTECTE

Re: [SQL] Capturing pgsql ERRORS/NOTICES to file

Hi Wei, I hadn't tried that, and it did the trick! Thank you! George - Original Message - From: "Wei Weng" <[EMAIL PROTECTED]> To: "George Weaver" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, September 26, 2003 2:16 PM Subject:

[SQL] Temporary tables

hi, I am using postgresql 7.3.2. Is there any function to determine whether a table exists in the database.Or is there any function that returns the current temp schema.I am using a pl/pgsql function that create and drop a temporary table.The procedure run correctly for the first time for each data

Re: [SQL] Temporary tables

Thanks to all of you for your suggestions. the problem is solved by creating a function istableexist() that returns whether a table exist or not. the function is bellow. CREATE FUNCTION istableexist(varchar) RETURNS bool AS '  DECLARE  BEGIN      /* check the table exist in database and is visible

Re: [SQL] Problem with Escape charactor

Kumar,   Have you tried   EXECUTE 'update "WATS".action_plan_master set rec_deleted_flag =  \'Y\' WHERE action_plan_id IN ('|| p_action_plan_ids || ')';    ^  ^   HTH, George -

Re: [SQL] Bad encoding in URL

>From the manual: replace(string text, from text, to text) text Replace all occurrences in string of substring from with substring to replace('abcdefabcdef', 'cd', 'XX') abXXefabXXef HTH. George - Original Message - From: "HR" <[

Re: [SQL] Possible to have array as input paramter for a function?

Hi Kumar,   It is possible to pass an array to a PL/pgSQL function, but I believe you must specify the length of the array (at least doing so works for me). E.g. "varchar(20)".   Regards, George - Original Message - From: Kumar To: psql Sent: Wednesday,

Re: [SQL] Possible to have array as input paramter for a function?

PostgreSQL are you using?  I am running 7.3.2.   Regards, George - Original Message - From: Kumar To: psql Sent: Wednesday, October 08, 2003 5:47 AM Subject: [SQL] Possible to have array as input paramter for a function? Dear Friends,   I am working with

[SQL] Sorting problem

hi all, i am using postgres 7.3.2 .i am converitng a mssql database to postgres. now i am facing a strange problem. sorting based on a varchar field is not working as expected. the non alphanumeric characters are not sorting based on the ascii value of them.   i have the following table structure.

Re: [SQL] Object description at Client Window

Kumar, pg_class.relname is type "name". You are trying to compare it to p_tablename which is type "varchar". Try changing your function definition to: CREATE OR REPLACE FUNCTION public.desc_table(name) HTH George SNIP > CREATE OR REPLACE FUNCTION public.desc_tab

[SQL] Which is faster SQL or PL/PGSQL

hi, i am converting an MSSQL database to Postgres. there is a lot of procedures to convert. which language is best for functions, SQL or plpgsql. which is faster . i am using postgres 7.3.2 jinujose Do you Yahoo!? The New Yahoo! Shopping - with improved product search

Re: [SQL] connectby

in with the original table: SELECT t.*, description FROM connectby('menu','id','parent_id','2',0,'~') AS t(id integer, parent_id integer, level int, branch text), menu WHERE t.id = menu.id George Essig ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] DateDiff in PostgreSQL

Hi, i am converting a MSSQL Server database to PostgreSQL. Using PostgreSQL version 7.3.2. Is there any function like the DateDiff() in MSSQL Server. ie, a function that returns difference of two dates(timestamp) in days or months or year.. The - operator for timestamp retuns the intervel in days

Re: [SQL] DateDiff in PostgreSQL

Yasir Malik <[EMAIL PROTECTED]> wrote: >You can use the age() function to find the difference between dates, and>use the extract() function to get the years, months, days, etc.>Yasir this will not work because age('25/12/1975','30/01/1986')wil return '10 year 1 month 5 days' extract only returns

Re: [SQL] search facilities

Download the latest version of PostgreSQL and look in the contrib/tsearch or contrib/tsearch2 directories. For documentation, see: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ http://sourceforge.net/mailarchive/forum.php?forum_id=7671 George Essig

Re: [SQL] connectby

QL 7.2.3 and there was no contrib/tablefunc directory. You'll have to upgrade. George Essig ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

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

Kumar, What about this: EXECUTE 'select now()+ interval \' || to_char(3,\'9\') || \'month\' '; George - Original Message - From: "Kumar" <[EMAIL PROTECTED]> To: "Christoph Haller" <[EMAIL PROTECTED]> Cc: <[EMAIL

[SQL] Looks are important

 Blue   Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad?   Thanks in advance, George        

Re: [SQL] Looks are important

oncatenate the two as they are being displayed as one column in a drop down combobox.   Is what I'm trying to do possible???   George - Original Message - From: Louise Cofield To: 'George Weaver' ; [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:19 P

Re: [SQL] Looks are important

Hi Tom, Switching to a fixed-width font did the trick. Thanks for the help. George - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "George Weaver" <[EMAIL PROTECTED]> Cc: "Josh Berkus" <[EMAIL PROTECTED]>; <[EMAIL PRO

[SQL] increment int value in subset of rows?

[postgresql 7.4, SuSE x86 linux] I have a table "rtest" with primary key (run,seq) and other data. For a given value of "run", seq is a sequential run of integers, 1,2,3,4.. Now I want to insert a row into this "sequence", say run='foo', seq=2, adjusting the seq up for all subsequent foo rows. M

Re: [SQL] increment int value in subset of rows?

thing O(0), i.e. a few queries regardless of the number of rows... > - Original Message - > From: george young > To: [EMAIL PROTECTED] > Sent: Monday, November 24, 2003 1:59 AM > Subject: [SQL] increment int value in subset of rows? > > > [postgresq

[SQL] Infinite loop crashes server

hi all, i am using PostgreSQL 7.3.2 on redhat linux 9. there is problem when executing pl/pg sql functions. if the function enter an infinite loop. the server is hanged. cannot cancel the query. the linux itself is hanged.i cannot kill postgres process. i have to reboot the machine manually.. waite

[SQL] Distributed Transactions

Hi all,   i am using PostgreSQL 7.3.2. How can i do distributed transactions in PostgreSQL. is there a transaction coordinator available for Postgres..   thanks in advance regards jinujose Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want.

[SQL] Enterprice support in PostgreSQL

Hi all,   We are providing database solutions in postgreSQL...Now using PostgreSQL 7.3. It is performing well. But Now we have some enterprice level requirements. One of Our requirement is to provide a distributed solution in PostgreSQL. The questions are...1. Is it posible to provide a distributed

[SQL] debugging query to put message in pg logfile?

e of code did a 'commit', since there's nothing to distinguish one from another in the log. Is there some cheaper (or more appropriate) sql statement that will show up in the postgres log? I thought I remembered a "message" sql statement or something like that. -- George You

Re: [SQL] debugging query to put message in pg logfile?

On Thu, 04 Mar 2004 16:35:01 -0500 Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins: > george young <[EMAIL PROTECTED]> writes: > > I've started putting debugging queries like: > >select "opwin.py: committing step signoff" > >

Re: [SQL] Syntax for cmd to EXEC...how many quotes?

#x27;, count(*) FROM \' || r_rec.viewname || \' ; \'; HTH George - Original Message - From: "David B" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, April 20, 2004 6:24 PM Subject: [SQL] Syntax for cmd to EXEC..

Re: [SQL] Rank

rank from mytable t1,mytable t2 where t2.point >=t1.point group by t1.id,t1.site_name,t1.point; id | site_name | point | rank +---+---+-- 3 | Site D|22 |4 2 | Site B|90 |2 4 | Site X|98 |1 1 | Site A|40 |3 (4 rows) -- G

Re: [SQL] Question about a CIDR based query

I did a vacuum analyze before I run the following explain June_03=# explain select * from tmp where route >>='62.1.1.0/24'; QUERY PLAN Seq Scan on tmp (cost=0.00..606.60 rows=1

Re: [SQL] Question about a CIDR based query

sh Berkus) wrote in message news:<[EMAIL PROTECTED]>... > George, > > > Um, no, I need an EXPLAIN ANALYZE, not just an EXPLAIN. Thanks. > > > > > June_03=# explain select * from tmp where ro

Re: [SQL] Returning A Varchar From A Function

Hi Richard, What happens if you just do: where trim(status) = trim($1) Regards, George - Original Message - From: "Richard Hurst" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 12, 2004 4:04 AM Subject: [SQL] Returning A Varchar From A Func

Re: [SQL] Moving from Transact SQL to PL/pgsql

an openacs 5. George Essig ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

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

. You would then return r, comprised of r.server_name and r.load_avg. George - Original Message - From: "Marc G. Fournier" <[EMAIL PROTECTED]> To: Sent: Wednesday, February 02, 2005 3:10 PM Subject: [SQL] PL/PgSQL - returning multiple columns ... I have a function that I

Re: [SQL] Working with XML.

  Microsoft has a very good reference on xpath expressions: http://msdn.microsoft.com/library/default.asp?url="">.   Regards, George - Original Message - From: Theo Galanakis To: [email protected] Sent: Sunday, February 13, 2005 11:48 PM Subject: [SQL]

Re: [SQL] Working with XML.

:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,&

Re: [SQL] Working with XML.

perly - I should be able to help you with that.  I don't think you can use it on any version earlier than 7.2.   Regards, George - Original Message - From: Theo Galanakis To: 'George Weaver' Sent: Tuesday, February 15, 2005 4:30 PM Subject: RE: [SQL] Workin

Re: [SQL] Working with XML.

),  errmsg("xpath_table must be called as a table function")));should be changed to:     elog(ERROR,"xpath_table must be called as a table function"); I have attached an edited copy of xpath.c with these changes, if you would like to work with it.   Regards, George--

Re: [SQL] Working with XML.

worked as expected.   Perhaps the client you're using is causing the problem.  Can you run the query in pgAdmin?   Regards, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: '[email protected]' Sent: Monday, Febr

Re: [SQL] Working with XML.

Title: Message Hi Theo,   Hmm.  Well we're getting into territory that's over my head.  I've simply been a user of xml2 and do not know much about its inner workings.  Just out of curiosity, what is the size of Sort_Mem in your postgresql.conf?   Regards, George - O

Re: [SQL] Generic Function

- Original Message - From: <[EMAIL PROTECTED]> To: Sent: Monday, March 14, 2005 12:15 PM Subject: [SQL] Generic Function Hi, Can I built a generic function like: CREATE FUNCTION f (text) RETURNS TEXT as $$ return 'select * from $1'; $$ I know its impossible as writed. Also I have lo

Re: [SQL] Query performance problem

7;b',4); INSERT 98686 1 jan28-05=# insert into test values('c',NULL); INSERT 98687 1 jan28-05=# insert into test values('d'); INSERT 98688 1 jan28-05=# select * from test; foo | foo1 -+-- a | 1 b |4 c | d |0 (4 rows) George - Original Message

Re: [SQL] SYNTAX ERROR ON FOR... LOOP

Hi Rodrigo, - Original Message - From: "Rodrigo Carvalhaes" <[EMAIL PROTECTED]> To: Sent: Wednesday, April 27, 2005 12:39 PM Subject: [SQL] SYNTAX ERROR ON FOR... LOOP Hi Guys, I am having a "simple syntax problem" but very strange... I am trying to make an IF / ELSE / END IF inside of

[SQL] owner of data type "areas" appears to be invalid ?

with usesysid=501. There is no row in pg_user with usesysid=101, and there is none with usename "101". How can I fix this? I must be able to get clean dumps that can be reloaded in case of a crash. -- George Young pig5=> select * from pg_class where relname='areas&#

Re: [SQL] owner of data type "areas" appears to be invalid ?

Yes, that worked. Thank you very much! -- George On Fri, 14 Oct 2005 12:04:13 -0400 Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins: > george young writes: > > How can I fix this? > > Re-create the owning user (which you evidently dropped), assigning it >

[SQL] 8.0.x windows installer fails?

ts adn that you can access it or contact the appilcation vendor to verify that this is a valid Windows Installer package. So I tried the analgous file from 8.0.3, with the same results. What am I doing wrong? -- George Young -- "Are the gods not just?" "Oh no, child. What would become

Re: [SQL] Design question: Scalability and tens of thousands of

e table values(owner text, obj text, name text, val text) That is, the values are stored in text type, not the native type. Yes, this takes a performance hit for conversion of values, but the simplicity of schema really wins for me. I suggest you seriously consider it unless you need blinding perfo

[SQL] how to update table to make dup values distinct

can add multiple colons, or one each of an assortment of characters, say ':+*&^#'. Performance does not matter here. The real table has 30K rows, ~200 dups. To clarify, I want to end up with something like: 1 | the 2 | the: 3 | rain 4 | in 5 | mainly: 6 | spain 7 | stays

[SQL] tid_le comparison for tuple id (ctid) values?

[PostgreSQL 8.1.0 on i686-pc-linux-gnu] I would like to suggest that there be a less-than (or greater-than) operator for the 'tid' type. I used to use oid's for finding and distinguishing duplicate data. Now that oid's are not included by default (and I do not quarrel with that change), I though

Re: [SQL] tid_le comparison for tuple id (ctid) values?

On Mon, 21 Nov 2005 16:19:28 -0500 Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins: > george young writes: > >update steps set x=x||'X' from steps s where steps.key1=s.key1 and > > steps.key2=s.key2 and step.ctid > > But this fails becau

Re: [SQL] problems with array

(pseudo function? builtin? whatever); no subquery is needed: select c.* from tb_cat c,tb_array a where a.id=1 and c.id=any(a.cat); Look at section 8.10.5 "Searching in Arrays" in http://www.postgresql.org/docs/7.4/interactive/arrays.html and section 9.17.3 in: http://www.postgre

[SQL] rename idx's with table; avoid confusing idx names?

;old_foo')? Does this look useful enough for me to package more formally? -- George Young CREATE or REPLACE FUNCTION rename_table_and_indexes(old_name text, new_name text) returns void AS $$ declare

[SQL] how to convert relational column to array?

81 | {operator} lawless | 509 | {operator,originator} lcalvet | 622 | {originator} loomis | 514 | {operator,originator} pig | 614 | {operator,originator,supervisor} -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (

Re: [SQL] how to convert relational column to array?

On Mon, 19 Dec 2005 09:54:49 -0700 Michael Fuhr <[EMAIL PROTECTED]> threw this fish to the penguins: > On Mon, Dec 19, 2005 at 11:06:12AM -0500, george young wrote: > > create table new_tab(name text, id int, permits text[]); > > > > -- I insert one row per name: >

Re: [SQL] The Information Schema vs the PG Catalog

haven't seen them represented anywhere there (please someone correct me if I am wrong). You can get more information about sequences from pg_catalog.pg_class (look for pg_class.relkind='S') and various views that sit on top of that (e.g. pg_statio_all_sequences). George > I'm wr

[SQL] "large" IN/NOT IN subqueries result in query returning wrong data

The following looks like a bug to me, but please someone review and tell me what I might be missing. Seems that past a certain result set size a "[NOT] IN (subquery)" stops behaving as expected and returns 0 matches even when there should be matches. No errors are returned, just faulty data. The ex

Re: [SQL] "large" IN/NOT IN subqueries result in query returning wrong data

null) --> 0, no matter what the goo values are Sorry... Thanks! George ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

[SQL] DISTINCT with NULLs and INT fields

conversion from INT. Any thoughts on what the "correct" behavior should be here? George ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] DISTINCT with NULLs and INT fields

> "Better" is in the eye of the beholder. sorry for the value-laden term. "laxer" is more appropriate, of course! the funny thing is that had they cast the NULLs to TEXT it would have failed there too (they do not do implicit TEXT to INT). > It surprises me not at all that > Microsoft would be

Re: [SQL] DISTINCT with NULLs and INT fields

> Sure, but in this example the required type of the value is clear from > immediate context (ie, the INSERT). This is one of the cases where > the SQL spec requires a bare NULL to be accepted. (BTW, 'no' and '4' > in this example are *not* values of type text; they are > untyped literals which

Re: [SQL] Help with simple query

or, from the "stupid tricks" category: SELECT n.user_id, max(lpad(extract(epoch from n.modified_date),12,'0') || ' ' || note) FROM notes n GROUP by n.user_id i am not *really* suggesting this! ---(end of broadcast)--- TIP 5: don't forget

Re: [SQL] DISTINCT with NULLs and INT fields

> > test=# insert into foo values (4::int,4::int); > > INSERT 0 1 > > test=# insert into foo values (4::text,4::text); > > ERROR: column "b" is of type integer but expression is of type text > > HINT: You will need to rewrite or cast the expression. > > test=# insert into foo values (cast(4 as

[SQL] avg() with floating-point types

I have city and postal_code tables linked by city_postal_code through a city_id and postal_code_id. The postal_codes have latitude/longitude, the cities don't. I want to set the city lat/long to the average of the associated postal codes (abstract for a minute on whether that actually makes sense f

Re: [SQL] PostgreSQL and uuid/guid

r written a C postgreSQL function, and any help (or > documentation pointout) would be greatly appreciated. > If I posted this to the wrong mailing list, please point me out to the > correct one. You question is quite welcome here! -- George Young -- "Are the gods not just?"

[SQL] non-equi self-join optimization

on position (t1.myname in t2.myname) > 0 where t1.flag = 1 and t2.flag = 2 ; I have gone through a few variations on the theme, but none perform too well. Any advice on the best way to optimize a query like this would be appreciated. Thanks! George ---(end

Re: [SQL] CREATE VIEW form stored in database?

y the script and paste it into pgAdmin's Execute Arbitrary SQL Queries window, and then execute the script from there. Regards, George ---(end of broadcast)--- TIP 6: explain analyze is your friend

[SQL] strange quoted csv behavior with COPY

from foo; length | a| b ++--- 6 | bar| 3 is this by design? what is the use of the quote in this context? i am on 8.0.6. george ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your de

[SQL] trecherous subselect needs warning?

has any rows! Eeek! I suppose the moral of the story is to ALWAYS, absolutely ALWAYS qualify a correlation name (table alias). Of course, what I meant in the original query was: select s.run_id from s_bake s where s.opset_id not in (select os.opset_id from old_opset_steps os); Sigh. Am I miss

Re: [SQL] trecherous subselect needs warning?

On Tue, 07 Feb 2006 12:45:53 -0500 Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins: > george young writes: > > This query returns zero rows: > > > newschm3=# select run_id from s_bake where opset_id not in (select opset_id > > from opset_steps); &

  1   2   >