[SQL] newby question
Hello, does anybody know how to iterate over an array column in a plsql function? We want to do something like begin for i in 0.. loop end loop Thx Uwe
Re: [INTERFACES] Re: [SQL] improve performance
Hannu Krosing wrote: > Tom Lane wrote: > > > > > > I've heard lots of people want to increase BLCKSZ, but you're the first > > one who ever wanted to reduce it. You sure you want to do this? It's > > going to make the maximum row length uncomfortably short. > > And it may even not work, as some system tables (that are also affected > by this) > may need the full 8k. AFAIK it has never been tested with BLCKSZ < 8k Except for two different sorted (but correct) results while selecting inherited tables in "misc", regression tests passed with 2K. Why shouldn't it work? All the catalogs that require really big data have toast tables now. Anyway, the 8K default BLCKSZ already restricts index tuples to 2700 bytes. So I wouldn't recommend it at all. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
[SQL] speed of recursive queries, 7.1
I experienced terrible speed decrease with some recursive PLPGSQL functions in 7.1beta3. Has anybody got similar behaviour? Details soon... Zoltan
[SQL] sort order with < & > in varchar fields
It is possible to extract rows in a table using SELECT name FROM table WHERE name >= 'Bergman'. The problem I have is that I get names like "Berg, Paul" with this query, and I don't want that. Is there documentation on how >= and <= works with varchar fields? Does pg count in the "," character or ignore it? And so on... Version is 7.0.1. /Rolf
Re: [SQL] sort order with < & > in varchar fields
Rolf Johansson <[EMAIL PROTECTED]> writes: > Is there documentation on how >= and <= works with varchar > fields? Does pg count in the "," character or ignore it? That depends. If you compiled with --enable-locale then it's whatever strcoll() says for the locale you are running the postmaster in. If not, it's whatever strcmp() says, which is normally plain ASCII collation order. Note: beware of changing the postmaster's locale on the fly; this can leave your indexes on textual columns out of order and hence effectively corrupt. You can fix that by dropping/rebuilding such indexes, or with REINDEX. regards, tom lane
[SQL] Re: [INTERFACES] outer join in PostgreSql
Thomas Lockhart wrote: > > Mauricio Hipp Werner wrote: > > > > I need help, which is the symbol used in postgreSql to carry out the outer > > join. > > > > in oracle the is used (+) > > in sybase the is used * and > > in postgreSql? > > The PostgreSQL outer join is accomplished using SQL92 syntax. You will > not find real outer joins *except* in the current beta release, and > beware that there may be some tweaks to the grammar to help with > conformance to the standard. > > In any case, check the standard or try something like "select * from t1 > left outer join t2 on (i)". To get a feel you could use MS Access visual query builder and then view the source. I have not checked it lately, but it very likely produces SQL92 compliant outer joins. --- Hannu
Re: [SQL] Re: Boolean and Bit
Josh Berkus wrote: > Well, yes. This is beacause BLOBs are NOT part of the SQL > standard and IMHO a bad idea relationally; thus their > implementation is entirely proprietary to the RDBMS. The > solution is not to use BLOBs. Ooops. Let me re-state: This is because the *implementation* of BLOBS is not defined in the SQL standard, and BLOBs are IMHO a bad idea for relational database design, as they violate Codd's Rules. Thus the implementation of, anf functions and operators for BLOBs are entirely proprietary to the RDBMS platform. The solution to this is not to use BLOBs, but rather to use file system handles for the location of the binary data on the server. This way, all you need is DOS-to-UNIX and UNIX-to-DOS translation for the filesystem handles, something easily accomplished through string-manipulation functions or stored procedures. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
[SQL] Re: Yet one more question
Justin, > How do you do the "drop and create" of tables? 1. Save table definition as text. 2. Create a duplicate of the table definition as "temp_table" 3. INSERT all of the table records into the temp_table 4. DROP the existing table 5. Re-CREATE the table with the altered definition. 6. INSERT the rows from the temp_table back into the table. Of course, this process fails to preserve SERIAL keys, FORIEGN KEYS, etc. and is somewhat labor intensive. ANybody create a script to do this dynamically? > I use pg_dump -d > something.sql > > Then I use vi/sed/something-else to modify the schema in the dumped > file, > then reload it into postgreSQL with psql -e < something.sql > > /dev/null Thanks. SOunds like a good alternate strategy, although it still blows away our test data. I'd also need to see if our functions survive the dump ... -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
[SQL] Re: [INTERFACES] outer join in PostgreSql
On Tue, Jan 16, 2001 at 06:44:18PM +0200, Hannu Krosing wrote: > > To get a feel you could use MS Access visual query builder and then view > the source. > I have not checked it lately, but it very likely produces SQL92 > compliant outer joins. > I fired up MS-Access 97SR1, just to see, and here's one result: SELECT Institution.InstitutionName, InstituteAssignment.PersonID FROM Institution LEFT JOIN InstituteAssignment ON Institution.InstID = InstituteAssignment.InstID; I'm surprised: looks pretty standard, to me. Ross -- Open source code is like a natural resource, it's the result of providing food and sunshine to programmers, and then staying out of their way. [...] [It] is not going away because it has utility for both the developers and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.
Re: [SQL] RE: Help with query. (*)
Mike, In that case, you want to use this construction: DELETE FROM a WHERE EXISTS ( SELECT 1 FROM b WHERE b.1 = a.1 AND b.2 = a.2 AND b.3 = a.3 ); Of course, a good primary keying system would make this somewhat less complex ... -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
[SQL] Re: Boolean and Bit
Josh Berkus wrote: > > > The solution to this is not to use BLOBs, but rather to use file system > handles for the location of the binary data on the server. This way, > all you need is DOS-to-UNIX and UNIX-to-DOS translation for the > filesystem handles, something easily accomplished through > string-manipulation functions or stored procedures. > > -Josh Berkus Do you have an example for implementing this?
[SQL] Select 'Sunday' in month ??
i want to select date in january where day='Sunday' any idea ?? please help me Nizomi
Re: [SQL] Select 'Sunday' in month ??
E.g.
create table mytable (created datetime);
insert into mytable values ('01-01-2001');
...
insert into mytable values ('01-31-2001');
select created from mytable where date_part('dow', created) = 7 and date_part('month',
created) = 1;
Troy
>
> i want to select date in january where day='Sunday'
>
> any idea ??
>
> please help me
>
> Nizomi
>
[SQL] update help
I'd like to know what's the correct SQL statement to do the following: update t1 a set a.amount = sum(b.amount) fromt2 b where a.id = b.id
Re: [SQL] update help
update t1 set amount = sum(b.amount) from ts b where a.id=b.id On Thursday 18 January 2001 09:54, Carolyn Wong wrote: > I'd like to know what's the correct SQL statement to do the following: > > updatet1 a > set a.amount = sum(b.amount) > from t2 b > where a.id = b.id
Re: [SQL] update help
Carolyn Wong <[EMAIL PROTECTED]> writes: > I'd like to know what's the correct SQL statement to do the following: > updatet1 a > set a.amount = sum(b.amount) > from t2 b > where a.id = b.id Try UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id); Or possibly you want UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id) WHERE EXISTS (select * FROM t2 b WHERE t1.id = b.id); depending on whether you mean to zero out any t1 rows that have no matching rows in t2. Note that you cannot attach an alias name to the target table, you have to use its real name in the subselects. There was a thread about this very issue a few months ago, and IIRC we decided that an aggregate in an UPDATE doesn't have well-defined semantics. The SQL92 spec explicitly disallows it. Right now PG will take it, but we probably do something pretty surprising :-( regards, tom lane
[SQL] Re: update help
This update field with the sum of all amounts in t2. I want to update sum of each individual IDs. Tubagus Nizomi wrote: > > update t1 > set amount = sum(b.amount) > from ts b > where a.id=b.id > > On Thursday 18 January 2001 09:54, Carolyn Wong wrote: > > I'd like to know what's the correct SQL statement to do the following: > > > > updatet1 a > > set a.amount = sum(b.amount) > > from t2 b > > where a.id = b.id
[SQL] Re: update help
Forgot to mention that I'm using V6.5. It doesn't seem to like subqueries, got the following error: ERROR: parser: parse error at or near "select" What I really want to do is follows t2: ID Amount --- 1 1 .. 1 2 .. 2 3 .. 2 2 .. and want the following result in t1 t1: ID Amount ... --- 1 3 .. 2 5 .. Tom Lane wrote: > > Carolyn Wong <[EMAIL PROTECTED]> writes: > > I'd like to know what's the correct SQL statement to do the following: > > updatet1 a > > set a.amount = sum(b.amount) > > from t2 b > > where a.id = b.id > > Try > > UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id); > > Or possibly you want > > UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id) > WHERE EXISTS (select * FROM t2 b WHERE t1.id = b.id); > > depending on whether you mean to zero out any t1 rows that have no > matching rows in t2. Note that you cannot attach an alias name to the > target table, you have to use its real name in the subselects. > > There was a thread about this very issue a few months ago, and IIRC > we decided that an aggregate in an UPDATE doesn't have well-defined > semantics. The SQL92 spec explicitly disallows it. Right now PG will > take it, but we probably do something pretty surprising :-( > > regards, tom lane
Re: [SQL] update help
Tom, > UPDATE t1 SET amount = (select sum(b.amount) from t2 b > WHERE t1.id = b.id); Interesting. I'm used to (not necessarily in PGSQL): UPDATE t1 SET amount = t2ttl.totalamount FROM (SELECT sum(amount) as totalamount, id FROM t2 GROUP BY id) t2ttl WHERE t1.id = t2.id Although this is a subselect in the FROM clause, something we've had trouble with as I recall. -Josh Berkus
[SQL] Re: update help
Carolyn Lu Wong <[EMAIL PROTECTED]> writes: > Forgot to mention that I'm using V6.5. Oh. 6.5's support for sub-selects is pretty limited :-(. I think the only way to do it in 6.5 is with a temp table, eg SELECT id, sum(amount) as sum into temp table tt from t2 group by id; update t1 set amount = tt.sum where id = tt.id; drop table tt; You might wanna think about updating sometime soon... regards, tom lane
[SQL] Re: Help with query. (*)
[NOTE: I'm a pgsql newbie myself. Take this reply with a large-ish grain of salt!) Shouldn't it be something straightforward like: select a.a, a.b, a.c, ... from a a, b b where a.x = b.x, and a.y = b.y, ... (I'd watch out for too many clauses here... if you've got a lot of clauses, you're probably not normalized as much as you should be.) If you have indexes on the relevant fields, you shouldn't get a table scan and this should return rather quickly, right? -Ken "Diehl, Jeffrey" wrote: > > I'm having difficulty writing a query which I really can't live without... > > I need to get a list of records from table A for which there are > corresponding records in table B. I've tried to use the intersect clause, > but it doesn't seem to work, or it runs far too long. For example: > > select * from A > where 1=1 > intersect select * from A where > B.x=A.x > and A.y=B.y > and A.z=B.z > limit 100 > > I need the most efficient method possible; my A tables have upward of 5 > Million records. The B table, btw, only has about 100 records. > > Any help will be most appreciated.
[SQL] One Question Answered
Folks- Answered my own question about the backslashes before spaces, in text returned as results from functions: it's a bug in kpsql, one of the interface tools I was using. Somebody might want to forward this to the Interfaces list. -Josh Berkus
