[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_ability; ability_name | ability_description | type | cost --+++-- Flee | Escape from battle with high probability. | Active | 0 Cure | Restores HP of single/multiple. | Active | 6 Power Break | Reduces the enemy's attack power. | Active | 8 Thunder Slash | Causes Thunder damage to the enemy. | Active | 24 Auto-Haste | Automatically casts Haste in battle. | Passive | 9 Counter | Counterattacks when physically attacked. | Passive | 8 MP+20% | Increases MP by 20% | Passive | 8 Thievery | Deals physical damage to the target | Active | 8 Fire | Causes Fire damage to single/multiple targets. | Active | 6 Flare | Causes Non-Elemental damage. | Active | 40 Leviathan | Causes water damage to all enemies. | Active | 42 beckerbalab2=> select * from ffix_can_wear; character_name | equipment_name +-- Dagger | Rod Dagger | Aquamarine Zidane | Aquamarine Vivi | Aquamarine Steiner | Diamond Sword Steiner | Ragnarok Dagger | Running Shoes Zidane | Running Shoes Vivi | Running Shoes Steiner | Running Shoes Dagger | Ritual Hat Zidane | Ritual Hat Vivi | Ritual Hat Dagger | Angel Earrings Zidane | Dagger Zidane | The Tower Dagger | Leather Hat Zidane | Leather Hat Vivi | Leather Hat Vivi | Black Robe Steiner | Aquamarine (21 rows) beckerbalab2=> SELECT ffix_ability.name, ffix_ability.cost beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability beckerbalab2-> WHERE ffix_can_learn.character_name = 'Zidane' beckerbalab2-> EXCEPT --this is the difference operator hope it works beckerbalab2-> SELECT ffix_ability.name, ffix_ability.cost beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability beckerbalab2-> WHERE ffix_can_learn.character_name = 'Steiner'; ERROR: No such attribute or function 'name' beckerbalab2=>
Re: [SQL] Invoice number
Mike Castle wrote: > What do you do on the following scenario: > > Client 1 is placing an order, gets invoice #1. > Client 2 is simultaneously placing an order, and gets invoice #2. > > Client 1 changes mind and cancels order. Invoice #1 is not used. Invoice > #2 is. > > Client 3 comes along. Do they use invoice #1, out of order, or invoice #3? You must use invoice #3, because #1 was already used for another client and it remain printed on paper. I made an billing application (Tcl/Tk + PostgreSQL) and invoice numbers are generated by a sequence. It works with multiple billing location. When an operator generate an invoice, he work without a number ... the number is generated in the moment of saving and printing. In this mode, is posible to cancel a invoice before the number is generated and is imposible to duplicate numbers. When a numer is generated, operators can not cancel printing. In this case, one number represent always one printed invoice. If the invoice is canceled, it's "paper version" is canceled too. > I suppose of the assignment of the invoice number is set up in such a way > as it is the very last action performed just before the commit, you should > be able to ensure that indeed the situation of having to deal with a > rollback would never occur (outside of a system crash between assignment of > invoice and commit, and that could be explained to auditors). > I use "begin transaction" ... "commit" (abort) in time to write positions in invoice because I want to decrease (on increase) stocks on line. In this mode the other uses get the real stock (I do not use a "stocks" table ... stocks are calculated "on-line" ... sum(in)-sum(out) on that product for all prices who are active at that moment) for the products who are in curent invoice. When invoice is saved positions remain in database and is generated an invoice number or, if invoice is canceled, all this position are deleted, stocks returns to original values and no invoice number are generated. > > [What happens in a paper world if a cup of coffee is spilt on some > invoices, and these precious items are thrown in the trash?] > I cancel all this invoices but I never thrown them to trash because, in ROMANIA, printed invoces already have a printed number (with safety marks) and when taxman came to check he want to see all this numbers, valids or canceled. > > mrc > -- >Mike Castle Life is like a clock: You can work constantly > [EMAIL PROTECTED] and be right all the time, or not work at all > www.netcom.com/~dalgoda/ and be 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
Hi, I use: agro=# select version(); version --- PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.3 compiled with "--enable-locales --enable-encoding --with-tcl" options on Mandrake LINUX 7.2 with 2.2.17-21mdk kernel. I have BIG problems when I want to do the following "select": agro=# SELECT * FROM conturi WHERE id ~* '^1:1:[0-9]*:$' ORDER BY id; The conditions are the following: Table "conturi" has the following structure: agro=# \d conturi Table "conturi" Attribute |Type | Modifier ---+-+-- id| varchar(32) | not null cheie | varchar(32) | denumire | varchar(60) | not null tip | char(1) | not null functie | char(1) | cc| integer | Index: conturi_id I use the unique index conturi_id on field id. agro=# SELECT * FROM conturi ORDER BY id LIMIT 20; id| cheie | denumire | tip | functie | cc --+---+--+-+-+ 1: | 1 | Capitaluri | F | P | 1:0: | 10| Capital si rezerve | F | P | 1:0:1: | 101 | Capital social | F | P | 1:0:1:1: | 1011 | Capital subscris nevarsat | O | P | 1:0:1:2: | 1012 | Capital subscris varsat | O | P | 1:0:1:7: | 1017 | Capital social reevaluat | O | P | 1:0:4: | 104 | Prime legate de capital | F | P | 1:0:4:1: | 1041 | Prime de emisiune sau de aport | O | P | 1:0:4:2: | 1042 | Prime de fuziune | O | P | 1:0:5: | 105 | Diferente din reevaluare | F | B | 1:0:5:3: | 1053 | Diferente din reevaluari cladiri | O | B | 1:0:5:4: | 1054 | Diferente din reevaluari constructii speciale | O | B | 1:0:5:6: | 1056 | Diferente din reevaluarea amortizarii la cladiri | O | B | 1:0:5:7: | 1057 | Diferente din reevaluarea amortizarii la constructii special | O | B | 1:0:6: | 106 | Rezerve | F | P | 1:0:6:1: | 1061 | Rezerve legale | O | P | 1:0:6:3: | 1063 | Rezerve statutare | O | P | 1:0:6:8: | 1068 | Alte rezerve | O | P | 1:0:7: | 107 | Rezultatul reportat | O | P | 1:0:8: | 108 | Contul intreprinzatorului individual | F | P | (20 rows) Realy I have: agro=# SELECT count(*) FROM conturi; count --- 2690 (1 row) rows in table. (VACUUM ... VACUUM ANALYZE was used before I do this example) The "id" field define a tree structure and, if I want to find all children of "1:0:", I do: agro=# SELECT * FROM conturi WHERE id ~* '^1:0:[0-9]*:$' ORDER BY id; id | cheie | denumire | tip | functie | cc +---+--+-+-+ (0 rows) When I drop the unique index ... agro=# DROP INDEX conturi_id; DROP agro=# SELECT * FROM conturi WHERE id ~* '^1:0:[0-9]*:$' ORDER BY id; id | cheie | denumire | tip | functie | cc +---+--+-+-+ 1:0:1: | 101 | Capital social | F | P | 1:0:4: | 104 | Prime legate de capital | F | P | 1:0:5: | 105 | Diferente din reevaluare | F | B | 1:0:6: | 106 | Rezerve | F | P | 1:0:7: | 107 | Rezultatul reportat | O | P | 1:0:8: | 108 | Contul intreprinzatorului individual | F | P | (6 rows) it works fine ... but if create the index again ... agro=# CREATE UNIQUE INDEX conturi_id ON conturi(id); CREATE agro=# SELECT * FROM conturi WHERE id ~* '^1:0:[0-9]*:$' ORDER BY id; id | cheie | denumire | tip | functie | cc +---+--+-+-+ (0 rows) nothing again. When I use the same succesion of commands on a table with the same structure but wonly 10 rows the "SELECT" works fine with or without index. On 6.5.3 (compiled exactly on same machine 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
Najm Hashmi wrote: > Hi all, > It is a very simple but I am not able to recall how to do it I > just need to find out the current value of a seq. It is very simple > select statement but I can't recall it... Help me please before I get > myself get fired -:). > 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
[postgres 7.0.2, x86 linux] I am trying to use a trigger to perform an sql 'notify' command. I do something like: CREATE FUNCTION run_changed() RETURNS opaque AS ' declare nm text; begin nm := NEW.run_name; notify nm return null; end; ' LANGUAGE 'plpgsql'; create trigger run_changed_tr after update on runs for each row execute procedure run_changed(); BUT, when I update the table, I get: ERROR: parser: parse error at or near "$1" It looks like the *name* (or it's alias here: $1.run_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 Young, Rm. L-204[EMAIL PROTECTED] MIT Lincoln Laboratory 244 Wood St. Lexington, Massachusetts 02420-9108(781) 981-2756
Re: [SQL] dates in functions
Salvador Mainé wrote:
> Hello:
>
> I'm trying to define a function that, given a date, returns its month.
> The definition is as follows:
>
> CREATE function anyo_hidro (date) returns int AS '
>BEGIN
>RETURN date_part("month",$1);
>END;
> ' LANGUAGE 'plpgsql';
>
> But when I do:
>
> select anyo_hidro('1-1-1999');
>
> I get the following error:
>
> ERROR: Attribute 'month' not found
Try,
test=# CREATE function anyo_hidro (date) returns int AS '
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
---
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.3
(1 row)
test=#
George Moga,
Data Systems Srl
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] need to join successive log entries into one
I have a table like: run | seq | start| done 1415|261| 2001-01-29 12:36:55| 2001-02-07 13:02:38 1415|263| 2001-02-14 07:40:04| 2001-02-15 16:05:04 1415|264| 2001-02-16 16:05:05| 2001-03-08 16:34:03 1415|265| 2001-03-08 16:34:04| 1747|257| 2001-02-15 09:14:39| 2001-03-01 08:58:03 1747|258| 2001-03-01 08:58:04| 2001-03-01 09:01:27 1747|260| 2001-03-01 09:01:39| 2001-03-01 09:02:39 1747|262| 2001-03-05 13:13:58| 1954|218| 2001-02-02 20:55:39| 2001-02-08 12:44:48 1954|219| 2001-02-08 12:44:49| 2001-02-08 12:47:36 1954|220| 2001-02-08 12:47:36| 2001-02-08 16:50:33 1954|221| 2001-02-08 16:50:33| 2001-02-08 16:50:45 1954|222| 2001-02-08 16:50:46| 2001-02-12 14:36:41 1954|223| 2001-02-12 14:36:41| 2001-03-02 10:17:15 This is a log of some operations done on some runs. 'seq' is the step within the run. I need to produce a new table that coalesces immediately successive operations on a run into one, e.g.: run | start | done 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38 1415| 2001-02-14 07:40:04| 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 1747| 2001-03-05 13:13:58| 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 i.e. where a run has one or more steps with succesive seq values, or equivalently, with abutting start/end values, then I want the new table to have only one entry representing 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 can do this in postgres? -- George Young, Rm. L-204[EMAIL PROTECTED] MIT Lincoln Laboratory 244 Wood St. Lexington, Massachusetts 02420-9108(781) 981-2756 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 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-01-29 12:36:55| 2001-02-07 13:02:38 > > 1415|263| 2001-02-14 07:40:04| 2001-02-15 16:05:04 > > 1415|264| 2001-02-16 16:05:05| 2001-03-08 16:34:03 > > 1415|265| 2001-03-08 16:34:04| > > > This is a log of some operations done on some runs. 'seq' is the step > > within the run. > > > I need to produce a new table that coalesces immediately successive > > operations on a run into one, e.g.: > > > run | start | done > > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > > 1415| 2001-02-14 07:40:04| > > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 > > 1747| 2001-03-05 13:13:58| > > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 > > Try: > > select run,min(start),max(done) from mytable group by run; Alas, this combines *all* entries for a given run, not just those that are imediately adjacent (in time, or by 'seq' number)... -- George Young, Rm. L-204[EMAIL PROTECTED] MIT Lincoln Laboratory 244 Wood St. Lexington, Massachusetts 02420-9108(781) 981-2756 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] problem with copy command
Jaruwan Laongmal wrote: > dear all, > I currently using postgresql v7.0.3 > when i import text file to table with command "copy tablename from > '/tmp/a.txt'; > and it shows > "copy: line 20, Cannot insert a duplicate key into unique index testpri_pk" > ,then it exits with doing nothing. > > I want to ignore this errors and continue copy the next record. How to do > that? > if I don't filter in '/tmp/a.txt' before using copy command. > > Thank you so much for your help in advance . > Regards > Jaruwan Try 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?
Marc Sherman wrote: > .. > > Heh. I obviously simplified my situation too much. > > This is closer to what I've really got: > > create table user (userid int4 primary key, groupid int4); > create table log (userid int4, timestamp datetime, value int4); > > I need to select sum(value) for each group, where the values chosen > are the newest log entry for each group member that is before a cutoff > date (April 1, 2001). > > Here's what I'm currently using: > > select user.groupid, sum(l1.value) > from log as l1, user > where user.userid=log.userid > and log.timestamp in ( > select max(timestamp) from log > where log.timestamp<'2001-04-01' > and log.userid=l1.userid) > group by user.groupid; > > When I first posted, this was _very_ slow. I've since improved > it by adding an index on log(userid,timestamp) - now it's just > slow. If anyone knows how to make it faster, I'd appreciate it. > > - Marc Try something like this: SELECT user.groupid, sum(l1.value) FROM log as l1, user WHERE user.userid = l1.userid and l1.timestamp = ( SELECT max(timestamp) from log WHERE log.timestamp < '2001-04-01' and log.userid = l1.userid ) GROUP by user.groupid; 1. you use in the same 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
[EMAIL PROTECTED] wrote:
Hi there
I need to half copy and insert new data in a new record. Please see
the SQL
statement, that is not working, but i think is shows what I'd like
to do.
The fields a_nr and a_kurzbez will get the values from a form and a_bez
needs to be copied from an existing auftrag.
Thanks for any help ... jr
insert into auftrag (a_nr, a_kurzbez) values (123456789, 'testtext')
(select a_bez from auftrag where a_nr='200113672732')
Try something like the folowing example:
test=# create table a (id serial, a1 text, a2 int4);
CREATE
test=# insert into a (a1, a2) values ('1221211', 12);
INSERT 580415 1
test=# insert into a (a1, a2) values ('12345', 11);
INSERT 580416 1
test=# select * from a;
id | a1 | a2
+-+
1 | 1221211 | 12
2 | 12345 | 11
(2 rows)
test=# create table b (id serial, b1 text, b2 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 this can help you !!
George Moga,
Data Systems Srl
Slobozia, ROMANIA
Re: [SQL] set datestyle to European PROBLEM
Alessandro Rossi wrote: > I have the defaul installation of postgres 7.0.3 and on another machine > 7.1.2 on redhat 7.1 > > I cannont get the date in correct form: . > Is this a bug ? > > I think i should get dd-mm-yyy date format and not -mm-dd > > Is postgres using ISO date format as default ? I had the same problem ... but I solved ... (I use PostgreSQL 7.1 on RH 7.1 installed from rpm): In "/etc/rc.d/init.d/postgresql" I modify in "start" arm ... from: su -l postgres -c "LC_ALL=C /usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster 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: SET DATESTYLE TO 'Postgres'; and work fine ... > Thanks > Alex George Moga, Data Systems Srl, Slobozia, ROMANIA ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] select question
[postgreql 7.2, linux] I have a table T with columns run, wafer, and test: T(run text, wafer int, test text) Given a run and a set of wafers, I need the set of tests that match *all* the specified wafers: run wafer test a 1 foo a 2 foo a 3 foo a 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, 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 Detective" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[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) ) etc, where foonames and barnames are known to be, say <20 characters long. And the fooid's and barid's are arbitrary ints only known inside the db. The original reason for these numeric id's, (in another db system long long ago), was to conserve space(now irrelvant with 120G disks) and to make searching and index usage more efficient. Recently, there is increasing call for new apps and even ad-hoc queries.(Thank goodness people are finally interested in this data!) The artificial numeric id's make it a lot harder for naive users to understand the data structure, and sometimes actually requires an extra order of joins. The question is: would I be better off losing all those integer ids and just using the text names as primary indices? Is there much performance lost comparing text strings for every index operation? My db is not huge: longest table has 100k tuples, biggest table has 1k pages, total pg_dump output is 51 Mbytes. Typical activity: ~6000 updates and inserts/day, 30,000 selects/day. -- 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 Detective" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] design question: status table+log table, indexes, triggers
[postgresql-7.2, pgsql, linux] Here's a schema-design problem I've hit a few times -- it seems like there should be a better way: I have a machine table (140 rows), currently very static: machine(machine_name text NOT NULL, machine_id smallint NOT NULL, area text NOT NULL, text text NOT NULL); and a machine_log table (8400 rows), appended to ~4 times/hour: machine_log(machine_name text,date timestamp, status text, usr text, comment text); This schema seemed logical at the outset, but the most common query is: select m.machine_name, m.text, ml.status, ml.date from machine m, machine_log ml where m.machine_name=ml.machine_name and ml.date=(select max(date)from machine_log where machine_name=ml.machine_name); This takes ~25 seconds which is way too long for interactive status check. The max(date) subselect kills me -- I've tried various indexes but to no avail. It looks like I need to put status and date_changed columns into the machine table, even though that info is implicit in the machine_log table. Is there some other schema that I'm just not thinking of which neatly stores some static info about each of a list of things as well as log info of status changes of those things? Or is there some index I could create on machine_log that would do the above query fast? Finally, I've never used triggers or rules -- what's the best way to maintain the status and date_changed columns in "machine" 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 "The Dying Detective" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Design Q.:logic in app or db?
I have general design question about Postgres usage: How does one decide how much, and what parts of logic should go in DB rules, triggers, functions, constraints etc, versus what should go in the application? I see postings here from people who obviously have a lot of domain logic in the DB side. I currently have almost none. I plan to set up a bunch of RI constraints to keep things clean and consistant, but what about logic that implements frequent domain operations? Brief sketch of my project: 2 developers, 4k lines of python(gtk, pygres), 2 main GUI user apps and a few read-only scripts for web display, 50 concurrent users(all local), DB performance important but not currently a problem. The main thing not done yet is to facilitate ad-hoc queries (via odbc excel etc.) from db-naive users: maybe restructuring the db to make it simpler, maybe views and functions... The data 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 Detective" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 29, 2003 11:11 AM Subject: [SQL] "record" datatype - plpgsql > Given a variable of the "record" data type in pl/pgsql, is it possible to > get the names of the columns ( attributes ) of that record? > > eg, given record "NEW" for table "foo", is there a way to get information > concerning the columns that make up that record? > > Brian Knox > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] "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 Subject: 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 29, 2003 11:11 AM > Subject: [SQL] "record" datatype - plpgsql > > > > Given a variable of the "record" data type in pl/pgsql, is it possible to > > get the names of the columns ( attributes ) of that record? > > > > eg, given record "NEW" for table "foo", is there a way to get information > > concerning the columns that make up that record? > > > > Brian Knox > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] find open transactions/locks in 7.2?
[select version() --> PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 3.0.4] I'm getting hangups every day or so, I presume due to some open transaction that insert/update/delete'ed on a table that is used by my main app without a commit. Is there some way (in 7.2!) to find 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 "The Dying Detective" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) >\\\!/< 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/
[SQL] backend cpu usage? [7.2]
[postgresql-7.2, x86 linux] How can I get cpu usage info in 7.2 about the backend process of my db connection? I tried looking at the pg_stat_get_backend_pid function but it requires a backend_id that I don't know how to get. If I can get the backend pid I can fork a 'ps' command 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 "The Dying Detective" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Table conversion query...
Hello everyone, I have a table that looks like: 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 ... and I need to convert to: date glcr gldb amt 08/20/03 1060 3010 30.00 08/20/03 1060 1030 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-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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 > > ... > > and I need to convert to: > > > > date glcr gldb amt > > 08/20/03 1060 3010 30.00 > > 08/20/03 1060 1030 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? > > There's no simple way to do this in Postgres. You > could do it with a > PL/pgSQL table function, or for better performance a > C function. Interesting, my C is gone a long time ago. Would the table function be fairly complex for someone who's never done one? I'm tempted by the java option, but initial jdbc speed tests don't look very promising (the avg file has 1/2 million records). > There are a couple of questions yet to be answered > though: > 1) Can there ever be more than one credit account, > e.g. -30, -300, +150, +180? No, so far all the examples I've seen involve a single credit account. > 2) What happens if sum(neg values) != sum(pos > values)? Throw an error? Yes, this would indicate a system out of balance that requires external assistance. Thanks for the help. george __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Strange behavior with timestamptz
Hi Everyone, I have a situation where two tables have a "Created" field defined as follows: table seedlot "created timestamptz DEFAULT now(), " table transaction"created timestamptz NOT NULL DEFAULT now()," The transaction table records when a seedlot record is created, with both events happening in the same plpgsql function. The seedlot record is created first, with the transaction table being updated later in the procedure. Seedlot 153 was received on August 11 with the following result: base=# select created from seedlot where syslotid=153; created 2003-11-08 12:13:39-06(1 row) base=# select created from transaction where syslotid=153 and transactiontypeid=22; created 2003-08-11 12:13:39-05(1 row) Does anyone have any idea why the default for seedlot recorded the time with the day and month switched, resulting in the seedlot record being stamped Nov 8, 2003 while the transaction was stamped correctly as Aug 11, 2003? In the same function a number of other records are created and timestamped. In all cases where the table definition statement does not include NOT NULL, the time was stamped with the day and month reversed. However! Two of the tables define created thus: "created timestamptz DEFAULT now() NOT NULL," but still ended up with the day and month being switched. The transaction 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
Hi Tom, I have written both the application and the PostgreSQL procedures (still in development). All the timestamps I referred to are created by default when the stored procedure is invoked. The problem may be due to some inconsistency in how PostgreSQL is interpreting what the operating system (Windows XP) is supplying. While I have not been able to recreate the problem directly, another aspect of the process does show an inconsistency. In the application the user enters the date the product was received (which may be different from the date the record is created). This is passed to the stored procedure as a parameter of type date, and is inserted into a date field (datereceived). By changing the system date and running the application the following occurred: Date received entered as August 11, 2003: ? me.datetimepicker1.value #8/11/2003 8:18:28 PM# base=# select datereceived, created from receiving where receivingid=56; datereceived | created --+-- 2003-11-08 | 2003-08-25 20:20:55.41425-05 (1 row) Date received entered as August 25, 2003: ? me.datetimepicker1.value #8/25/2003 8:22:37 PM# base=# select datereceived, created from receiving where receivingid=57; datereceived | created --+-- 2003-08-25 | 2003-08-25 20:22:39.68625-05 (1 row) base=# show datestyle; DateStyle --- ISO with US (NonEuropean) 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" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, August 25, 2003 3:59 PM Subject: Re: [SQL] Strange behavior with timestamptz > "George Weaver" <[EMAIL PROTECTED]> writes: > > Does anyone have any idea why the default for seedlot recorded the time wit= > > h the day and month switched, resulting in the seedlot record being stamped= > > Nov 8, 2003 while the transaction was stamped correctly as Aug 11, 2003? > > It's really not possible to believe that both of those were loaded from > the defaults you show. now() doesn't ever break down the system clock > value into day/month/year --- it just takes the system clock time in > seconds-since-epoch and adds a constant to get the right zero offset. > So there's no credible mechanism for now() to make such a mistake. > > I think that your client software supplied a value for one field and > didn't supply a value for the other, and the supplied value was provided > in the wrong DateStyle. > > regards, tom lane > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Strange behavior with timestamptz
Hi Tom, Not believing that PostgreSQL would be less consistent than Microsoft :-), I spent some time looking at how the application actually was transferring the date to the database procedure. When the datereceived parameter was defined as type Date, it was actually arriving at the procedure as "11-08-2003" and "25-08-2003" in spite of showing up as indicated below. When I redefined the datereceived parameter as type OdbcDate, it arrived correctly at the procedure as "2003-08-11" and "2003-08-25". Checking further, this appears to result from the computer's time settings (English - Canada) which I know realize use the European format. So it looks like this problem arose due to my inexperience. (Nonetheless I am still perplexed by the fact that the default values were assigned inconsistently as explained earlier and will try to puzzle 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 behavior with timestamptz > Hi Tom, > > I have written both the application and the PostgreSQL procedures (still in > development). All the timestamps I referred to are created by default when > the stored procedure is invoked. The problem may be due to some > inconsistency in how PostgreSQL is interpreting what the operating system > (Windows XP) is supplying. While I have not been able to recreate the > problem directly, another aspect of the process does show an inconsistency. > > In the application the user enters the date the product was received (which > may be different from the date the record is created). This is passed to > the stored procedure as a parameter of type date, and is inserted into a > date field (datereceived). By changing the system date and running the > application the following occurred: > > Date received entered as August 11, 2003: > > ? me.datetimepicker1.value > #8/11/2003 8:18:28 PM# > > base=# select datereceived, created from receiving where receivingid=56; > datereceived | created > --+-- > 2003-11-08 | 2003-08-25 20:20:55.41425-05 > (1 row) > > Date received entered as August 25, 2003: > > ? me.datetimepicker1.value > #8/25/2003 8:22:37 PM# > > base=# select datereceived, created from receiving where receivingid=57; > datereceived | created > --+-- > 2003-08-25 | 2003-08-25 20:22:39.68625-05 > (1 row) > > base=# show datestyle; >DateStyle > --- > ISO with US (NonEuropean) 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" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Monday, August 25, 2003 3:59 PM > Subject: Re: [SQL] Strange behavior with timestamptz > > > > "George Weaver" <[EMAIL PROTECTED]> writes: > > > Does anyone have any idea why the default for seedlot recorded the time > wit= > > > h the day and month switched, resulting in the seedlot record being > stamped= > > > Nov 8, 2003 while the transaction was stamped correctly as Aug 11, > 2003? > > > > It's really not possible to believe that both of those were loaded from > > the defaults you show. now() doesn't ever break down the system clock > > value into day/month/year --- it just takes the system clock time in > > seconds-since-epoch and adds a constant to get the right zero offset. > > So there's no credible mechanism for now() to make such a mistake. > > > > I think that your client software supplied a value for one field and > > didn't supply a value for the other, and the supplied value was provided > > in the wrong DateStyle. > > > > regards, tom lane > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
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 write new function with local variables (see image in attachment). That I do wrong? Best regards,Yaroslav Ulyanov[EMAIL PROTECTED] ---(end of broadcast)---TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [GENERAL] plPGSQL bug in function creation
I had the same success using 7.3.2 with Cygwin:
e=# SELECT functest1('A','B'), functest1(null,'B'), functest2('A','B'),
functest2(null,'B');
functest1 | functest1 | functest2 | functest2
---+---+---+---
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 row)
George
- snip -
> Not sure whether this is because of the change 7.3.1->7.3.2 or cygwin vs
> linux. Don't have a copy of 7.3.1 to check against, sorry. Perhaps check
the
> changes list to see if it mentions something like this.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---(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
>
---(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
[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 sides (=). Is there any option to set in postgresql? Is there any problem in overriding the = operator that compare text. ie droping the current operator = and creating a new = operator(text,text). Does the existing = operator is using internally by postgres for some porpose. please help Another problem is in creating function... How can i create a function that accept and return any type. the type "any" is not allowing as parameter or return type. Is it possible? i want to create a function similar to NULLIF(). jinujose Do you Yahoo!? The New Yahoo! Shopping - with improved product search
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 function that accept and return any type. the type "any">> is not allowing as parameter or return type. Is it possible? i want to>> create a function similar to NULLIF().> You can't, nor will you be able to -- in te future, some 7.4 functions will be > able to *accept* any type, but they will still return a specific type.Au contraire. The 7.4 polymorphic-function features may well solvejinujose's problem, if he can indeed write a type-independent functionbody. For examplecreate function nullif(anyelement, anyelement) returns anyelement as'select case when $1 = $2 then null else $1 end' language sql;I'm not sure we're all the way there yet, cfhttp://archives.postgresql.org/pgsql-general/2003-09/msg00500.phpregards, tom lane---(end of broadcast)---TIP 4: Don't 'kill -9' the postmaster Do you Yahoo!? The New Yahoo! Shopping - with improved product search
[SQL] Capturing pgsql ERRORS/NOTICES to file
I am in the process of creating a batch file that will update some functions in a database for a remote user similar to: psql -o output dbname < functionupdate.sql Is there any way to save any ERROR and NOTICE messages to a file? The -o option doesn't capture this information. 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 PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, September 26, 2003 1:33 PM Subject: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file > George, > > > I am in the process of creating a batch file that will update some > > functions in a database for a remote user similar to: > > > > psql -o output dbname < functionupdate.sql > > > > Is there any way to save any ERROR and NOTICE messages to a file? > > > > The -o option doesn't capture this information. > > You have to use command shell redirects. > > For example, I commonly do in bash > psql -o output dbname < functionupdate.sql >out.dump > ... which sends all the command responses to a file, allowing me to read only > the errors on the screen. > > See a guide to your shell for more creative redirection. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(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] 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: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file
> George:
>
> Have you tried psql {whatever operations} 2> error_output ?
>
> (for Bash)
>
> Thanks
>
> Wei
>
>
> On Fri, 26 Sep 2003, George Weaver wrote:
>
> > 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 PROTECTED]>; <[EMAIL PROTECTED]>
> > Sent: Friday, September 26, 2003 1:33 PM
> > Subject: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file
> >
> >
> > > George,
> > >
> > > > I am in the process of creating a batch file that will update some
> > > > functions in a database for a remote user similar to:
> > > >
> > > > psql -o output dbname < functionupdate.sql
> > > >
> > > > Is there any way to save any ERROR and NOTICE messages to a file?
> > > >
> > > > The -o option doesn't capture this information.
> > >
> > > You have to use command shell redirects.
> > >
> > > For example, I commonly do in bash
> > > psql -o output dbname < functionupdate.sql >out.dump
> > > ... which sends all the command responses to a file, allowing me to
read
> > only
> > > the errors on the screen.
> > >
> > > See a guide to your shell for more creative redirection.
> > >
> > > --
> > > Josh Berkus
> > > Aglio Database Solutions
> > > San Francisco
> > >
> > > ---(end of
broadcast)---
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]
g)
> > >
> >
> >
> > ---(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
> >
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[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 database connection. If I run the same procedure second time in the same connection it produces the error "ERROR: pg_class_aclcheck: relation 219389 not foundWARNING: Error occurred while executing PL/pgSQL function testFunWARNING: line 20 at SQL statement " Here is the function -CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof intAS'DECLARE --Aliases for parameters vSBAcNo ALIAS FOR $1; --local variables vRow RECORD; BEGIN -- create a tempory table to hold the numbers CREATE TABLE tempTable ( testNo int ) ; for vRow IN select Entryno from EntryTable LOOP return next vRow.Entryno; insert into tempTable values( vRow.Entryno); end loop; drop table tempTable; return; END;' LANGUAGE 'plpgsql'; - If i commented the "insert into tempTable values( vRow.Entryno);" linethe function works correctly. The problem is the oid of tempTable is kept when the function is first executed. the next execution creates another table with different oid. So the insert fails. I want to check whether the temporary table exist. If exist do not create the temporary table in subsequent calls and do not dorp it. This will solve the problem. When i searched the pg_class i found the temp table name more than once. ie, a temporary table is created for each connection.I cannot distingush the temp tables. But the tables are in different schema.Is there a method to get the current temporary schema? How postgres distinguishthis temp tables?.Is there a way to distinguish temporary tables.The entries in pg_class table is same except the schema.When i used the current_schema() function it returns public. There is a lot of functions that uses temporary tables. I think that there is an option when creating temp tables in postgres 7.4 . But no way to use 7.4 now it is a working database. can i write a function to check the existance of the temporary table...please help... jinujose Do you Yahoo!? The New Yahoo! Shopping - with improved product search
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*/ PERFORM relname,relnamespace FROM pg_class WHERE relkind = ''r'' AND Upper(relname) = Upper($1) AND pg_table_is_visible(oid); IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END;' LANGUAGE 'plpgsql'; thanks again jinujose Do you Yahoo!? The New Yahoo! Shopping - with improved product search
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
- Original Message -
From:
Kumar
To: psql
Sent: Tuesday, October 07, 2003 7:57
AM
Subject: [SQL] Problem with Escape
charactor
Dear Friends,
I am working with Postgres 7.3.4 on RH Linux 7.2 . I am
executing a dynamic query inside a PL/pgSQL procedure and I am having the
following problem.
While a line in the PL/pgSQL function is like the
following
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = 'Y' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
I got the following error.
ERROR: parser: parse error at
or near "Y" at character 68
While
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = '|| 'Y' ||' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
Error is
ERROR: Attribute "y" not
found
While
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = '|| \'Y\' ||' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
Error is
WARNING: plpgsql: ERROR during
compile of sp_del_met_001 near line 47 ERROR:
unterminated string
How can I specify a string charactor, as the PgAdmin3 is not
using double quotes for Strings. Anyone pls shed some light.
Regards
Kumar
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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 06, 2003 5:45 AM
Subject: [SQL] Bad encoding in URL
> Hi
>
> I have some URLs in a database but some of the URL contain %3A and so on..
>
> Is there an easy way of converting '%3A' to ':' ???
>
> I cannot find any replace(String str, String str) among the string
> functions.
>
> ThankYou.
>
>
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 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, October 08, 2003 5:47 AM Subject: [SQL] Possible to have array as input paramter for a function? Dear Friends, I am working with Postgres 7.3.4 on RH Linux 7.2 and my windows client is PgAdmin 3. Is it possible to pass a Varchar[] as a input parameter for a PL/pgSQL function. While I tried it give a error Type "varchar[]" does not exists. Does this is supported in Postgres? Anyone have a link or while paper on handling arrays in functions? Please enlighten me on this. Regards Kumar
Re: [SQL] Possible to have array as input paramter for a function?
Hi Kumar, Looks like I got up too early this morning - please ignore my previous reply re: varchar(30) (I was looking at the wrong function :-( ). I do use arrays in Pl/pgSQL functions, and have defined them as you did, e.g. varchar[], which does not return an error. What version of 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 Postgres 7.3.4 on RH Linux 7.2 and my windows client is PgAdmin 3. Is it possible to pass a Varchar[] as a input parameter for a PL/pgSQL function. While I tried it give a error Type "varchar[]" does not exists. Does this is supported in Postgres? Anyone have a link or while paper on handling arrays in functions? Please enlighten me on this. Regards Kumar
[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.. create table accounts ( AcNo varchar (10), Name varchar(100), balance numeric(19,4) ) when i used the query select * from accounts order by acno. the result is not correct suppose that the acno field contains values '###1' ,'###2' ,'##10' , '#100' the sort order in postgres is '###1' '##10' '#100' '###2' But i want the result as follows '###1' '###2' '##10' '#100' that means the ascii value of # should be considered for sorting.. what is the problem. is it the behaviour of postgres. do i need to change any configuration. i am using all default configurations or is it a bug...? the problem actually is of < & > operators for varchar. in a simple comparison select '###2' < '##10' returns false but i need true. is there any solution exist. even if i replaced # with any non alphanumeric character the result is same.. pls help jinu jose Do you Yahoo!? The New Yahoo! Shopping - with improved product search
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_table(varchar)
> RETURNS refcursor AS
> 'DECLARE
>
> ref REFCURSOR ;
> p_tablename ALIAS FOR $1;
>
> BEGIN
> OPEN ref FOR
> SELECT a.attname,
> format_type(a.atttypid, a.atttypmod),
> a.attnotnull,
> a.atthasdef,
> a.attnum
> FROM pg_class c, pg_attribute a
> WHERE c.relname = p_tablename
> AND a.attnum > 0
> AND a.attrelid = c.oid
> ORDER BY a.attnum;
>
> RETURN ref;
> END;'
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> While trying to execute this
> select desc_table('companies');
>
> I got the following error.
> WARNING: Error occurred while executing PL/pgSQL function desc_table
> WARNING: line 7 at open
>
> ERROR: Unable to identify an operator '=' for types 'name' and 'character
> varying'
> You will have to retype this query using an explicit cast
>
SNIP
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] 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
> hi
>
> I have menu table:
> id | integer | not null default
> nextval('public.menu_id_seq'::text)
> parent_id | integer |
> description | text|
>
> I do select:
> test=> SELECT * FROM connectby('menu','id','parent_id','2',0,'~') t(id
> integer, parent_id integer, level int, branch text);
> id | parent_id | level | branch
> +---+---+-
>2 | | 0 | 2
>4 | 2 | 1 | 2~4
>7 | 4 | 2 | 2~4~7
> 10 | 7 | 3 | 2~4~7~10
> 16 |10 | 4 | 2~4~7~10~16
>9 | 4 | 2 | 2~4~9
>
> How am I able to select description file from menu table, not only id,
> parent_id, level, branch fields?
>
> --
> WBR, sector119
Try a join 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 only. Please help jinujose Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
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 years -10, moths-1, days-5.
this is not i want.Actual month difference between the dates are 121 months.
jinu jose
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
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 ___ Original Message: Hi, I have a content management system, for my database driven website developed using php and postgresql but I don't know how to develop a search facility for it. How do I go about it?. I have seen database driven website developed using php and postgresql and they have their built search facilities where can I learn to develop my customized search facility please do advice. Kind Regards +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] connectby
> I use postgresql 7.2.3 > How can I use connectby ?? > > Must I install files ? or packages ? or it is recommanded to upgrade dataserver ? For recent versions of PostgreSQL, go into the contrib/tablefunc directory and see the readme file for how to install. I downloaded PostgreSQL 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 PROTECTED]> Sent: Monday, November 10, 2003 5:57 AM Subject: Re: [SQL] Dynamic Query for System functions - now() > Dear Christoph Haller, > > The code that u sent is not working > test=> EXECUTE 'select now()+ interval\'' ||to_char(3,'9')|| 'month\''; > ERROR: parser: parse error at or near "'select now()+ interval\''" at > character 9 > test=> > > Also I tried just to run the now() function with a dynamic query, I got the > following error. Please correct me. > > test=> select now(); > now > --- > 2003-11-10 17:06:36.783779+00 > (1 row) > > test=> execute 'select now()'; > ERROR: parser: parse error at or near "'select now()'" at character 9 > test=> > > > Regards > kumar > > - Original Message - > From: "Christoph Haller" <[EMAIL PROTECTED]> > To: ""Kumar"" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Monday, November 10, 2003 5:07 PM > Subject: Re: [SQL] Dynamic Query for System functions - now() > > > > > > > > Dear Friends, > > > > > > I am using PostgreSQL 7.3.4 Server on RH Linux 7.2. I am trying to > generate= > > > a dynamic query to fetch the next month interval. > > > > > > select now()+ interval'1 month'; -- This is working fine. > > > > > > I wanna dynamically assign the interval number. i,e --> select now()+ > inter= > > > val'n month'; > > > > > > For this I wanted to write a dynamic query. > > > EXECUTE 'select now()+ interval\'' || 3|| 'month\''; > > > Error > > > ERROR: parser: parse error at or near "'select now()+ interval\''" > at = > > > character 9 > > > > > > Help help me with this. I wanted to use this query inside a PLpgSQL > functio= > > > n. > > > > > > Regards > > > Kumar > > > > > You'd probably want to use the to_char() function like this > > EXECUTE 'select now()+ interval\'' ||to_char(3,'9')|| 'month\''; > > > > The || operator is the string concatination operator. > > > > Regards, Christoph > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Looks are important
Hi Everyone, I am trying to concatenate two fields through a query: SELECT RPAD(no,30,' ') || tableb.kind FROM tablea WHERE tablea.kind = tableb.kind The result gives (for example): 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue What I would like to have is better alignment: 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 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
Title: Message Hi Louise, Josh, Thanks for the suggestions. What I'm trying to accomplish is to have a space between no and kind. Length(no) can vary. I would like all the kinds to line up evenly when displayed, with a space between no and kind. But when I RPAD no (to try and get an even starting point for kind), the ' 's are not quite the same width as an ordinary number or letter. Thus the physical display length of "30 characters" (padded) can vary from row to row. The result is that the kinds don't necessary line up neatly. I need to concatenate 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 PM Subject: RE: [SQL] Looks are important Try the TRIM function or the LTRIM function: SELECT RPAD(no,30,' ') || TRIM(tableb.kind) FROM tablea WHERE tablea.kind = tableb.kind Louise -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of George WeaverSent: Wednesday, November 12, 2003 3:12 PMTo: [EMAIL PROTECTED]Subject: [SQL] Looks are important Hi Everyone, I am trying to concatenate two fields through a query: SELECT RPAD(no,30,' ') || tableb.kind FROM tablea WHERE tablea.kind = tableb.kind The result gives (for example): 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue What I would like to have is better alignment: 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 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
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 PROTECTED]>; "Louise Cofield" <[EMAIL PROTECTED]> Sent: Wednesday, November 12, 2003 9:31 PM Subject: Re: [SQL] Looks are important > "George Weaver" <[EMAIL PROTECTED]> writes: > > ... the ' 's are not quite the same width as= > > an ordinary number or letter. Thus the physical display length of "30 cha= > > racters" (padded) can vary from row to row. The result is that the kinds do= > > n't necessary line up neatly. I need to concatenate the two as they are be= > > ing displayed as one column in a drop down combobox. > > Use a fixed-width font. > > > Is what I'm trying to do possible??? > > Not with a variable-width font that you haven't even told us the exact > character widths of ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[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. My first thought
was just:
update rtest set seq=seq+1 where run='foo' and seq>1;
which gets:
ERROR: Cannot insert a duplicate key into unique index rtest_pkey
no surprise :-(.
This doesn't work, since the *order* of execution of these updates
is not guaranteed, and I actually would need to start with the highest
value of seq and work down. There may be a thousand or so rows for 'foo'
run, so an external loop of queries would be very expensive.
How can I increment all the seq values for foo columns where seq > something?
create table rtest(run text,seq int,data int,primary key (run,seq));
insert into rtest values('foo',1,11);
insert into rtest values('foo',2,22);
insert into rtest values('foo',3,33);
insert into rtest values('foo',4,44);
insert into rtest values('bar',1,99);
I want to shift all foo rows and insert a new one so that:
select * from rtest where run='foo' order by seq;
would get:
run | seq | data
-+-+--
foo | 1 | 11
foo | 2 | 999
foo | 3 | 22
foo | 4 | 33
foo | 5 | 44
--
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 Detective"
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] increment int value in subset of rows?
On Mon, 24 Nov 2003 09:21:39 -
"Matthew Lunnon" <[EMAIL PROTECTED]> threw this fish to the penguins:
> You could write a function to do it.
>
> Matthew
That would save me the external interaction, but still amount to ~1000
sql queries -- I'm hoping to find something 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?
>
>
> [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. My first thought
> was just:
> update rtest set seq=seq+1 where run='foo' and seq>1;
> which gets:
> ERROR: Cannot insert a duplicate key into unique index rtest_pkey
> no surprise :-(.
>
> This doesn't work, since the *order* of execution of these updates
> is not guaranteed, and I actually would need to start with the highest
> value of seq and work down. There may be a thousand or so rows for 'foo'
> run, so an external loop of queries would be very expensive.
> How can I increment all the seq values for foo columns where seq > something?
>
> create table rtest(run text,seq int,data int,primary key (run,seq));
> insert into rtest values('foo',1,11);
> insert into rtest values('foo',2,22);
> insert into rtest values('foo',3,33);
> insert into rtest values('foo',4,44);
> insert into rtest values('bar',1,99);
>
> I want to shift all foo rows and insert a new one so that:
> select * from rtest where run='foo' order by seq;
> would get:
>
>run | seq | data
> -+-+--
>foo | 1 | 11
>foo | 2 | 999
>foo | 3 | 22
>foo | 4 | 33
>foo | 5 | 44
--
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 Detective"
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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.. waited for a long time for completion. but no result . when ever there is an infinite loop happened the system have to be restarted. unfortunately it happened many time during development of the pl/pg sql function. is it a bug in postgres. or is it configuration problem? when the system is hanged disk access is 100%. the harddisk read indicator does not stop. is there a solution for this problem.. pls help regards jinujose Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
[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 solution in PostgreSQL.2. Does PostgreSQL Support distributed transactions. 3. If not does it included in the next release. Or when will be the distributed version of postgreSQL available.4. Is there a replication solution availbale for postgreSQL.5. Does postgreSQL support 2 phase commit and Distributed transaction standards.6. Is there a transaction manager(or co-ordinater) available for postgreSQL.7. Can we use MTS (Microsot Transaction Server) wtih postgreSQL. 8. Does postgreSQL support Load balancing and all other enterprice features. Can we expect these features in the next version.. regards, jinujose Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want.
[SQL] debugging query to put message in pg logfile?
[postgresql-7.4RC2, python-2.3.3, PyGreSQL-3.4, SuSE x86 Linux 8.2] I've started putting debugging queries like: select "opwin.py: committing step signoff" in my app, just to have an entry in the postgres logfile. These are especially helpful in tracking down what piece 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 Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(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] 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"
> > in my app, just to have an entry in the postgres logfile.
>
> > Is there some cheaper (or more appropriate) sql statement that will show
> > up in the postgres log?
>
> You could just send SQL comments:
>
> -- opwin.py: committing step signoff
>
> One advantage of this is that you can merge the comments with actual
> commands, thus not incurring even a network round-trip time for them.
> If you do send it separately, it will act like an empty query string.
>
> People tend not to think of this because psql strips -- comments before
> sending commands. But I believe all the lower-level libraries will pass
> them through. (If you need to pass loggable comments through psql, I
> think the /* ... */ form will work.)
Alas no:
Python 2.3.3 (#1, Jan 3 2004, 07:17:11)
[GCC 3.3.2] on linux2
>>> import pgdb
>>> db=pgdb.connect(host='ivy:5433',database='pigtest')
>>> c=db.cursor()
>>> cur.execute('-- the rain')
Traceback (most recent call last):
File "", line 1, in ?
File "/usr/local/lib/python2.3/site-packages/pgdb.py", line 189, in execute
self.executemany(operation, (params,))
File "/usr/local/lib/python2.3/site-packages/pgdb.py", line 210, in executemany
raise OperationalError, "internal error in '%s'" % sql
pgdb.OperationalError: internal error in '-- the rain'
Likewise for /* comments */. :-(
I'll continue this on the pygresql mailing list; and I guess stick to
"select 'comment text'" for now...
Thanks,
-- George Young
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Syntax for cmd to EXEC...how many quotes?
David,
I tend to use \ to escape things like ' - I find it makes it somewhat easier
to debug.
What about:
sql_string :=\' INSERT INTO temp_table ( view_name, row_count ) SELECT \'
|| r_rec.viewname ||
\', 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...how many quotes?
> Folks,
>
> This is driving me crazy...I'm sure it's possible but that I am getting
the
> #quotes wrong in some way...
> I keep getting unterminated string errors...now matter how many quotes I
> use.
>
> I have a FN that I want to loop through all views and populate a table
with
> a count(*) from each views.
>
> To do it I'm doing a LOOP around all views...something like:
>
> FOR r_rec IN SELECT viewname from pg_views
> LOOP
>
> sql_string := 'INSERT INTO temp_table ( view_name, row_count ) SELECT
> ''' || r_rec.viewname || ''', count(*) FROM ' || r_rec.viewname || ' ; '
;
>
> EXEC sql_string ;
>
> END LOOP ;
>
> END ;
>
>
> Building that sql_string is the problem.
> Any thoughts folks?
>
> -D
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.648 / Virus Database: 415 - Release Date: 3/31/2004
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Rank
On Sun, 2 May 2004 02:22:37 +0800 "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]> threw this fish to the penguins: > I Have below table > > id | site_name | point > +---+--- > 1 | Site A|40 > 2 | Site B|90 > 3 | Site D|22 > 4 | Site X|98 > > Would like to calc that Rank for each site, and look like > > id | site_name | point | rank > +---+---+-- > 1 | Site A|40 |3 > 2 | Site B|90 |2 > 3 | Site D|22 |4 > 4 | Site X|98 |1 Well, a simple minded solution would be: select id,site_name,point,(select count(*)from mytable t2 where t2.point >= t1.point) as rank from mytable t1; id | site_name | point | rank +---+---+-- 4 | Site X|98 |1 2 | Site B|90 |2 1 | Site A|40 |3 3 | Site D|22 |4 (4 rows) If mytable is huge this may be prohibitively slow, but it's worth a try. There's probably a self join that would be faster. Hmm... in fact: select t1.id,t1.site_name,t1.point,count(t2.point) as 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) -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(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] 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=14544 width=33) Filter: (route >>= '62.1.1.0/24'::cidr) (2 rows) The select returns just one route, Thanks [EMAIL PROTECTED] (Josh Berkus) wrote in message news:<[EMAIL PROTECTED]>... > Georgos, > > > select * from tmp where route >>= some_cidr > > Can you post an EXPLAIN ANALYZE for this? And when's the last time you ran > ANALYZE on the table? > > > The index on route is not used and I get a sequential scan. The index is > > used only for the <<= operator. > > Most likely Postgres thinks that the >>= query is returning 60% of your table, > which makes indexes useless. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about a CIDR based query
Hi Josh, Ok, first the explain analyze June_03=# explain analyze select * from tmp where route >>='62.1.1.0/24'; QUERY PLAN --- Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) (actual time=3.862..15.366 rows=1 loops=1) Filter: (route >>= '62.1.1.0/24'::cidr) Total runtime: 15.493 ms (3 rows) And the version of postgres June_03=# select version(); version PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031022 (Gentoo Linux 3.3.2-r2, propolice) (1 row) Thanks, Georgos [EMAIL PROTECTED] (Josh 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 route >>='62.1.1.0/24'; > > >QUERY PLAN > > > > > > Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) > > >Filter: (route >>= '62.1.1.0/24'::cidr) > > Oh, and also a SELECT VERSION(); would be nice. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 Function
> Hi
>
> this has been puzzling me for a few days now
>
> I have created a function that I want to use in a database to select a
> value from a table based on the value passed in.
> The table is fairly simple
> CREATE TABLE public.feeder_next_status
> (
> status varchar NOT NULL,
> previous_status varchar,
> next_status varchar
> ) WITH OIDS;
>
> The function is defined as
>
> -- Function: public.spgetnextstatus(varchar)
>
> -- DROP FUNCTION public.spgetnextstatus(varchar);
>
> CREATE OR REPLACE FUNCTION public.spgetnextstatus(varchar)
> RETURNS varchar AS
> '
> select cast(next_status as varchar)
> from feeder_next_status
> where trim(status) = trim(\'$1\')
> order by next_status;'
> LANGUAGE 'sql' STABLE;
>
>
> However when i run the query
> select spgetnextstatus('NEW')
> in pgadmin
> the dataoutput shows two columns
> the row column shows a row number of '1' and the column header
> spgetnextstatus(varchar) shows blank
>
> I have tested the equivalent sql in the pgadmin query and it works
> fine.
>
> Hoping someone can point me inthe right direction
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Moving from Transact SQL to PL/pgsql
> ... > Where can I find > primer on PL/pgsql, with lots of examples? > ... Download openacs at http://openacs.org/projects/openacs/download/. Look at directories matching the pattern openacs-*/packages/*/sql/postgresql/. The older openacs version 4.6.3 has more examples than openacs 5. George Essig ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/PgSQL - returning multiple columns ...
Hi Marc,
One option is to create a simple data type and return the rowtype of the
datatype
eg CREATE TYPE tserverload AS ("server_name" text, "load_avg" int4);
CREATE FUNCTION getserverload()
RETURNS tserverload
AS
'DECLARE
r tserverload%rowtype;
etc.
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 want to return 'server_name, avg(load_avg)' ...
if I wanted to return matching rows in a table, I can do a 'setof
', with a for loop inside ... but what do I set the 'RETURNS' to if
I want to return the results of query that returns only 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
---(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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Working with XML.
Title: Working with XML. Hi Theo, I am not aware of any means of passing xml to stored procedures, apart from writing your own function to parse the xml. In regard to your second question - how to access the second record - try using a more explicit xpath query incorporating a "where" component. For example, if you wanted to access the second row based upon the value of cola, you could use '//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the position of the record if you know its position: '//query/row[2]/text()'. 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] Working with XML. Hi Folks, Is there a way to pass in an xml string into a stored proc and thenplace this into a temp table? I use to be able to do this in sql server, it was quite handy as I could call one stored proc to update multiple records, here is a sample in sql server: select CoverTypeID, ItemSQ, SituationID, ItemDescription, CoverAmount From OpenXML ( @XmlHandle, '/cover/covertype/item',1 ) With ( CoverTypeID int '../@id', ItemSQ int '@id', SituationID int '@situationID', ItemDescription varchar(100) '@description', CoverAmount money '@amount' ) I have managed to get get pgxml_xpath working, however Im not sure how to access specific rows in an xml document. E.g below there are two records, how do I access the second record, the following returns both ,'//query/row/cola values being (284122,525887): select pgxml_xpath( '284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65525887493253{ts ''2005-02-14 16:13:18''}6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3' ,'//query/row/cola/text()','','') __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
Re: [SQL] Working with XML.
Title: Message
Hi Theo,
I'm not sure if it can be done with the xml contrib
module. You may want to install and work with the xml2 contrib module,
which is more recent, has more extensive capabilities, and is easier to
work with.
It will give you the result you want:
jan28-05=# select xpath_string(jan28-05(#
'284122789648<colc>{ts
''2005-02-14
16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts
''2005-02-14
16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts
''2005-02-14
16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(#
,'//query/@rows') as rows;
rows-- 100(1 row)
Another example:
jan28-05=# select xpath_string(jan28-05(# '284122789648<colc>{ts
''2005-02-14
16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts
''2005-02-14
16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts
''2005-02-14
16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(#
,'//row[cola=284122]/colb') as colb;
colb 789648(1 row)
HTH,
George
- Original Message -
From:
Theo Galanakis
To: 'George Weaver'
Cc: '[email protected]'
Sent: Monday, February 14, 2005 9:46
PM
Subject: RE: [SQL] Working with
XML.
Thanks George.
How do you get an attributes value the following returns
the attribute tag. i.e. rows="100", all i want is the 100.
select pgxml_xpath('284122789648{ts
''2005-02-14
16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts
''2005-02-14
16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts
''2005-02-14
16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r','//query/@rows','','')
Theo
-Original Message-From: George Weaver
[mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 February 2005 12:39
AMTo: Theo Galanakis; [email protected]:
Re: [SQL] Working with XML.
Hi Theo,
I am not aware of any means of passing xml to
stored procedures, apart from writing your own function to parse the
xml.
In regard to your second question - how to
access the second record - try using a more explicit xpath query
incorporating a "where" component. For example, if you wanted to
access the second row based upon the value of cola, you could use
'//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the
position of the record if you know its position:
'//query/row[2]/text()'.
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] Working with
XML.
Hi Folks,
Is there a way to pass in an xml string into a
stored proc and thenplace this into a temp table?
I use to be able to do this in sql server, it
was quite handy as I could call one stored proc to update multiple
records, here is a sample in sql server:
select CoverTypeID, ItemSQ, SituationID,
ItemDescription, CoverAmount From
OpenXML ( @XmlHandle, '/cover/covertype/item',1 )
With ( CoverTypeID int '../@id',
ItemSQ int '@id',
SituationID int
'@situationID',
ItemDescription varchar(100) '@description',
CoverAmount money '@amount' )
I have managed to get get pgxml_xpath working,
however Im not sure how to access specific rows in an xml document. E.g
below there are two records, how do I access the second record, the
following returns both ,'//query/row/cola values being
(284122,525887):
select pgxml_xpath( '284122789648{ts
''2005-02-14
Re: [SQL] Working with XML.
Title: Message Hi Theo, You can find the source code for xml2 it in the 8 source tree http://www.postgresql.org/download/. If you're working with an earlier version of PostgreSQL than 8, you may have to make some modifications to the contrib code to get it to compile and link properly - 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] Working with XML. Hi George, Thanks for your help once again. However I cant seem to find XML2, the contrib package for RedHat ES3 I downloaded only has xml. Where can I find the compiled or source code. The only link I have is http://developer.postgresql.org/docs/pgsql/contrib/ , it there ftp access or cvs access to contrib? Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Wednesday, 16 February 2005 12:37 AMTo: Theo GalanakisCc: [email protected]: Re: [SQL] Working with XML. Hi Theo, I'm not sure if it can be done with the xml contrib module. You may want to install and work with the xml2 contrib module, which is more recent, has more extensive capabilities, and is easier to work with. It will give you the result you want: jan28-05=# select xpath_string(jan28-05(# '284122789648<colc>{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//query/@rows') as rows; rows-- 100(1 row) Another example: jan28-05=# select xpath_string(jan28-05(# '284122789648<colc>{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//row[cola=284122]/colb') as colb; colb 789648(1 row) HTH, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: '[email protected]' Sent: Monday, February 14, 2005 9:46 PM Subject: RE: [SQL] Working with XML. Thanks George. How do you get an attributes value the following returns the attribute tag. i.e. rows="100", all i want is the 100. select pgxml_xpath('284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r','//query/@rows','','') Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 February 2005 12:39 AMTo: Theo Galanakis; [email protected]: Re: [SQL] Working with XML. Hi Theo, I am not aware of any means of passing xml to stored procedures, apart from writing your own function to parse the xml. In regard to your second question - how to access the second record - try using a more explicit xpath query incorporating a "where" component. For example, if you wanted to access the second row based upon the value of cola, you could use '//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the position of the record if you know its position: '//query/row[2]/text()'. Microsoft has a very good reference on xp
Re: [SQL] Working with XML.
Title: Message
Hi Theo,There have been 2 major changes
between the 7 and 8 versions that affect the coding in xml2. You need to
edit the source code in order for it to compile properly on 7.First,
work_mem has to be changed to SortMem (line 666).
I.e. tupstore = tuplestore_begin_heap(true, false,
work_mem);should be changed to:
tupstore =
tuplestore_begin_heap(true, SortMem);
Second, the error reporting framework has changed.
To fix this, you need to change all the ereport coding to use
elog. I.e.
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
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- Original Message - From: Theo Galanakis
To: '[email protected]' Sent: Sunday, February 20, 2005 11:28
PMSubject: FW: [SQL] Working with XML.Hi,
I have copied all the files manually from
http://developer.postgresql.org/docs/pgsql/contrib/ for the xml2 contribution.
However I have the following issue when I attempt to compile with
gmake:gcc -I/usr/include/libxml2 -fpic -I. -I../../src/include
-D_GNU_SOURCE -c -o xpath.o xpath.cxpath.c: In function
`xpath_table':xpath.c:689: `work_mem' undeclared (first use in this
function)xpath.c:689: (Each undeclared identifier is reported only
oncexpath.c:689: for each function it appears in.)gmake: *** [xpath.o]
Error 1I have installed :
libxml2-devel-2.5.10-1.rpmWhat am I doing wrong, or can someone point me
to the direction of a binary for XML2 on RedHat ES3, Postgres
7.4.5.Cheers,
Theo
/* Parser interface for DOM-based parser (libxml) rather than
stream-based SAX-type parser */
/*#include "errcodes.h"*/
#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "lib/stringinfo.h"
/* libxml includes */
#include
#include
#include
#include
#include
/* declarations */
static void *pgxml_palloc(size_t size);
static void *pgxml_repalloc(void *ptr, size_t size);
static void pgxml_pfree(void *ptr);
static char *pgxml_pstrdup(const char *string);
static void pgxml_errorHandler(void *ctxt, const char *msg,...);
voidelog_error(int level, char *explain, int force);
voidpgxml_parser_init(void);
static xmlChar *pgxmlNodeSetToText(xmlNodeSetPtr nodeset,
xmlChar * toptagname, xmlChar * septagname,
xmlChar * plainsep);
text *pgxml_result_to_text(xmlXPathObjectPtr res, xmlChar * toptag,
xmlChar * septag, xmlChar * plainsep);
xmlChar*pgxml_texttoxmlchar(text *textstring);
static xmlXPathObjectPtr pgxml_xpath(text *document, xmlChar * xpath);
Datum xml_valid(PG_FUNCTION_ARGS);
Datum xpath_nodeset(PG_FUNCTION_ARGS);
Datum xpath_string(PG_FUNCTION_ARGS);
Datum xpath_number(PG_FUNCTION_ARGS);
Datum xpath_bool(PG_FUNCTION_ARGS);
Datum xpath_list(PG_FUNCTION_ARGS);
Datum xpath_table(PG_FUNCTION_ARGS);
/* Global variables */
char *errbuf; /* per line error buffer */
char *pgxml_errorMsg = NULL; /* overall error message */
/* Convenience macros */
#define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin,
CStringGetDatum(cstrp)))
#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(textp)))
#define ERRBUF_SIZE 200
/* memory handling passthrough functions (e.g. palloc, pstrdup are
currently macros, and the others might become so...) */
static void *
pgxml_palloc(size_t size)
{
/* elog(DEBUG1,"Alloc %d in CMC %x",size,CurrentMemoryContext); */
return palloc(size);
}
static void *
pgxml_repalloc(void *ptr, size_t size)
{
/* elog(DEBUG1,"ReAlloc in CMC %x",CurrentMemoryContext);*/
return repalloc(ptr, size);
}
static void
pgxml_pfree(void *ptr)
{
/* elog(DEBUG1,"Free in CMC %x",CurrentMemoryContext); */
return pfree(ptr);
}
static char *
pgxml_pstrdup(const char *string)
{
return pstrdup(string);
}
/* The error handling function. This formats an error message and sets
* a flag - an ereport will be issued prior to return
*/
static void
pgxml_errorHandler(void *ctxt, const char *msg,...)
{
va_list args;
va_start(args, msg);
vsnprintf(errbuf, ERRBUF_SIZE, msg, args);
va_end(args);
/* Now copy the argument across */
if (pgxml_errorMsg == NULL)
pgxml_errorMsg = pstrdup(errbuf);
else
{
int32 xsize = strlen(pgxml_err
Re: [SQL] Working with XML.
Title: Message Hi Theo, I am not able to duplicate the problem you experienced. I tried the query you provided below using pgAdmin and psql on a 7.3.2 and an 8.0 database with success. I then copied all the rows and pasted them to the end so that I would have 100 rows, and the query 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, February 21, 2005 11:54 PM Subject: RE: [SQL] Working with XML. Thankyou George, XML2 compiled ok... next stummbling block.. when I pass a very long XML string to xpath_string() it crashes the postgres server. Client receives a message like: server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request. Below is the function call, a smaller xml string with say 40 records works fine, anything bigger crashes the server. select xpath_string(' 7741872226632005-02-22
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 - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: '[email protected]' Sent: Tuesday, February 22, 2005 4:13 PM Subject: Re: [SQL] Working with XML. George, I have run this SP in Cold Fusion, PgAdmin and EMS PostgreSQL Manager 2, with all the same results. We are currently using Redhat ES3 and Postgres 7.4.5. Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Wednesday, 23 February 2005 12:46 AMTo: Theo GalanakisCc: [email protected]: Re: [SQL] Working with XML. Hi Theo, I am not able to duplicate the problem you experienced. I tried the query you provided below using pgAdmin and psql on a 7.3.2 and an 8.0 database with success. I then copied all the rows and pasted them to the end so that I would have 100 rows, and the query 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, February 21, 2005 11:54 PM Subject: RE: [SQL] Working with XML. Thankyou George, XML2 compiled ok... next stummbling block.. when I pass a very long XML string to xpath_string() it crashes the postgres server. Client receives a message like: server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request. Below is the function call, a smaller xml string with say 40 records works fine, anything bigger crashes the server. select xpath_string(' 7741872226632005-02-22 __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
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 looked for EXECUTE procedure but it not run the correct function. If you show us what you've tried and the results you received we may be able to help more. Some points: 1. To create a string to run with EXECUTE you would need to concatenate the above phrase: (Assuming "query" has been declared as text): query := 'Select * from ' || $1; 2. Since you're likely returning multiple rows, you need to write your function as a set-returning function. See: http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING and Executing Dynamic Commands in http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-PERFORM Is there a way to construct this clause? Using plpgsql/pltcl/anything ??? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query performance problem
Only specifying a default value does not prevent a NULL from being entered
either through accident or ignorance:
jan28-05=# create table test (foo text, foo1 int4 default(0));
CREATE TABLE
jan28-05=# insert into test values('a',1);
INSERT 98685 1
jan28-05=# insert into test values('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 -
From: "Kenneth Gonsalves" <[EMAIL PROTECTED]>
To: "Richard Huxton"
Cc: <[EMAIL PROTECTED]>;
Sent: Thursday, March 17, 2005 11:19 PM
Subject: Re: [SQL] Query performance problem
On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote:
Not necessarily. NOT NULL here helps to ensure you can add values
together without the risk of a null result. There are plenty of
"amount" columns that should be not-null (total spent, total
ordered etc).
that makes sense - but is it necessary to have a not null constraint
when there is a default value?
--
regards
kg
http://www.livejournal.com/users/lawgon
tally ho! http://avsap.sourceforge.net
àà à!
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 8: explain analyze is your friend
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 a FOR ... LOOP but I am getting syntax error on this contol structure... If I comment the IF / ELSE / ENDIF the fuction works ... snip vstrupdate := $$UPDATE table1 SET code = $$ || new_code || $$ WHERE old_code = $$ || recs.old_code || $$ AND is_customer $$; IF records.is_customer IS FALSE THEN vstrupdate := vstrupdate || $$ IS true ;$$; I believe you need an ";" after true and false to complete the string as an SQL statement ELSE vstrupdate := vstrupdate || $$ IS false ;$$; ENDIF; EXECUTE vstrupdate; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] owner of data type "areas" appears to be invalid ?
[PostgreSQL 7.4RC2, x86 linux]
Several tables are giving the errors like:
pg_dump: WARNING: owner of data type "areas" appears to be invalid
from pg_dump.
This is my production database, (50 users, 18 hours/day, 21MB compressed dump).
The output of "pg_dump -t areas" starts with:
REVOKE ALL ON TABLE areas FROM PUBLIC;
REVOKE ALL ON TABLE areas FROM geoyou;
SET SESSION AUTHORIZATION "101";
GRANT ALL ON TABLE areas TO "101" WITH GRANT OPTION;
RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION "101";
GRANT ALL ON TABLE areas TO PUBLIC;
RESET SESSION AUTHORIZATION;
GRANT ALL ON TABLE areas TO PUBLIC;
But "101" is the numerical group id of the "operator" group! (see dump from
pg_group below). The table owner is "geoyou" as seen from the pg_class select
below. User "geoyou" is in pg_user 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';
relname | relnamespace | reltype | relowner | relam | relfilenode | relpages
| reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared |
relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs |
relhasoids | relhaspkey | relhasrules | relhassubclass |relacl
-+--+--+--+---+-+--+---+---+---+-+-+-+--+---+-+--+--+-+++-++---
areas | 2200 | 14745246 | 501 | 0 |14745245 |1
|41 | 14745247 | 0 | f | f | r
|2 | 0 | 0 |0 |0 | 0 | t
| f | f | f |
{101=a*r*w*d*R*x*t*/101,=arwdRxt/101}
pig5=> select * from pg_user where usesysid=501;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil
| useconfig
-+--+-+--+---+--+--+---
geoyou | 501 | f | f| f | |
|
select * from pg_group where grosysid=101;
groname | grosysid |
grolist
--+--+---
operator | 101 |
{602,616,509,525,614,514,617,539,517,558,581,567,506,609,94,511,573,17115,327,17422,512,537,626,503,519,583,547,570,584,78,10980,518,557,564,528,546,592,599,613,510,513,536,554,500,530,594,608,524,17114,533,17116,17289,17290,17292,17294,17345,17347,17421,17423,17425,214,17430,17427,17428,574,11,391,17431,17667,17703,8309,17769,17842,17773,17874,17877,13283,12758,17966,1,17902,18099,18117,18129,18170,18173,18163,32766,18195,18202,18208,17786,17704,18375,18322,18399,18410,17904,18438,18424,28424,18437,102137,9877,502,32768,18553,13065,10681,8245,17049,15885,15886,8977,18706,18717}
select * from pg_tables where tablename='areas';
schemaname | tablename | tableowner | hasindexes | hasrules | hastriggers
+---+++--+-
public | areas | geoyou | f | f| f
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
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 > sysid 101. > > PG 8.1 will make it impossible to drop users who still own objects or > have permissions ... although that will bring its own set of gotchas ... > > regards, tom lane > -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] 8.0.x windows installer fails?
[Win XP Professional 2002, SP 1, Pentium 4, 1.4 GHz, 512MB RAM] I'm trying to install postgres 8.0.4 on my windows machine. I downloaded ftp.us.postgresql.org/pub/mirrors/postgresql/binary/v8.0.4/win32/postgresql-8.0.4.zip. I then double-clicked the file "postgresql-8.0". It says "Welcome to the PostgreSQL Installation Wizard...". The default english language is ok, so... I click the "Start" button, and immediately get a window labeled "Windows Installer" saying: This installation package could not be opened. Verify that the package exists 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 of us if they were?" (CSL) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Design question: Scalability and tens of thousands of
On Thu, 3 Nov 2005 09:58:29 -0800 "zackchandler" <[EMAIL PROTECTED]> threw this fish to the penguins: > I'm designing an application that will allow users to create custom objects > on the application level. A custom object can have zero or more attributes. > Attributes can be one of 5-10 types (ex. String, Number, List, Date, Time, > Currency, etc...). This will allow users to track anything exactly as they > want. My first thought on how to structure this is to make a custom table > in the db for each custom object. The attributes would map to fields and > everything would work just dandy. > > The problem is I am very concerned about scalability with having a different > table created for each custom object. I want to design to site to handle > tens of thousands of users. If each user has 3-5 custom objects the > database would have to handle tens of thousands of tables. > > So it would appear that this is a broken solution and will not scale. Has > anyone designed a similar system or have ideas to share? This is a sticky problem. My app is a bit similar. Trying something like your solution, I found that zillions of tables, constantly created and destroyed by users, to be terrible to manage. Now I use one table that defines objects' attributes, e.g. something like: create table fields(owner text, obj text, name text, type text, seq int) create table objs(owner text, name text) create 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 performance in all 20,000 applications... -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] how to update table to make dup values distinct
[PostgreSQL 7.4RC2 on i686-pc-linux-gnu](I know, I know... must upgrade soon) I have a table mytable like: i | txt ---+--- 1 | the 2 | the 3 | rain 4 | in 5 | mainly 6 | spain 7 | stays 8 | mainly 9 | in I want to update it, adding a ':' to txt so that each txt value is unique. I don't care which entry gets changed. I tried: update mytable set txt=mytable.txt || ':' from mytable t2 where mytable.txt=t2.txt and mytable.i=t2.i; but this updated both duplicated entries. Um, there may sometimes be 3 or 4 duplicates, not just two. For these, I 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 8 | mainly 9 | in: -- George -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(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
[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 thought I could use ctid's instead. Suppose I have a table steps: create table steps(x text, y text, z text) but I want there to be a primary key(x,y). If I try to do: create table temp_steps(x text, y text, z text, primary key(x,y)) insert into temp_steps select * from steps; drop table steps; alter table temp_steps rename to steps; I get an error that "duplicate key violates unique constraint". Some of the rows in steps differ only in value of z. OK, I'll just fix the data... I thought I could force values of x to be distinct with: (I've done this several times in the past with oid's) update steps set x=x||'X' from steps s where steps.key1=s.key1 and steps.key2=s.key2 and step.ctidhttp://www.postgresql.org/docs/faq
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 because there is no less-than operator (or function) on > > type "tid". > > Probably a good thing, too, since if there was it wouldn't have anything > reliable to do with the age of the tuple. Well, I don't have any need for it to correlate with the age of the tuple. My use of step.ctid
Re: [SQL] problems with array
On Sat, 15 Oct 2005 08:49:15 + "paperinik 100" <[EMAIL PROTECTED]> threw this fish to the penguins: > PostgreSQL is 7.4.7. > > My first table > CREATE TABLE tb_cat ( > id INTEGER, > desc text > ); > INSERT INTO tb_cat VALUES (10, 'cat10'); > INSERT INTO tb_cat VALUES (20, 'cat20'); > INSERT INTO tb_cat VALUES (30, 'cat30'); > > My second table > CREATE TABLE tb_array( > id INTEGER, > cat INTEGER[] > ); > INSERT INTO tb_array VALUES(1, ARRAY [10, 20]); > > When I write my select > SELECT * from tb_cat WHERE id IN (SELECT cat FROM tb_array WHERE id=1); > the output is: > ERROR: operator does not exist: integer = integer[] > HINT: No operator matches the given name and argument type(s). You may need > to add explicit type casts. Use the "any" function (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.postgresql.org/docs/7.4/interactive/functions-comparisons.html -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] rename idx's with table; avoid confusing idx names?
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
After tearing out some hair over the following sequence of events:
[a few weeks ago]
alter table foo rename to old_foo;
create table foo();
insert into foo select blahblahblah from old_foo;
[today]
cluster foo_pkey on foo;
ERROR: "foo_pkey" is not an index for table "foo"
What? Why does \d say the primary key idx is foo_pkey1
[light dawns]
Aha! "alter table rename to" did not rename the table's indexes!
I put together a plpgsql function to rename a table and it's indexes
correspondingly[see below]. I would like to know:
Is there a more robust/portable/clear way to do this?
Is this a bad idea for some subtle reason?
Is there any way to get a less cumbersome interface than "select
rename_table_and_indexes('foo','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
prefix_len integer;
r record;
begin
prefix_len = length(old_name);
for r in select indexrelname from pg_stat_user_indexes where
relname=old_name loop
execute 'alter index ' || r.indexrelname || ' rename to ' ||
quote_ident(new_name) || substr(r.indexrelname, prefix_len + 1);
raise NOTICE 'renamed index % to %', r.indexrelname, new_name ||
substr(r.indexrelname, prefix_len + 1);
end loop;
execute 'alter table ' || quote_ident(old_name) || ' rename to ' ||
quote_ident(new_name);
raise NOTICE 'alter table % rename to %', old_name, new_name;
end;
$$
LANGUAGE plpgsql;
^^
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] how to convert relational column to array?
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I'm trying to convert a column from a traditional relational form to an array:
create table old_tab(name text, id int, permits text);
newschm3=# select * from old_tab order by name;
name | id | permits
--+---+
baker| 581 | operator
lawless | 509 | operator
lawless | 509 | originator
lcalvet | 622 | originator
loomis | 514 | operator
loomis | 514 | originator
pig | 614 | operator
pig | 614 | originator
pig | 614 | supervisor
create table new_tab(name text, id int, permits text[]);
-- I insert one row per name:
insert into new_tab select distinct name,id,cast('{}' as text[]) from old_tab;
Now I want to fold all the 'permits' values into the new permits arrays.
I can do:
update new_tab set permits=new_tab.permits||ot.permits from old_tab ot where
ot.name=new_tab.name and ot.permits!=all(new_tab.permits);
but this only gets one permits value per name. Repeating this many times would
eventually get all of them, but it seems there must be a more reliable way?
[I don't care about the *order* of permits values in the array, since order did
not exist in old_tab]
Just to be clear, I want to end up with:
newschm3=# select * from new_tab order by name;
name | id | permits
-+-+--
baker | 581 | {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?" (CSL)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
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:
> > insert into new_tab select distinct name,id,cast('{}' as text[]) from
> > old_tab;
> >
> > Now I want to fold all the 'permits' values into the new permits arrays.
>
> In PostgreSQL 7.4 and later you can build an array from a select,
> so I think the following update should work (it did for me when I
> tested it):
>
> UPDATE new_tab SET permits = array(
> SELECT permits
> FROM old_tab
> WHERE old_tab.name = new_tab.name AND old_tab.id = new_tab.id
> );
That's exactly what I needed. Works great.
Thanks,
George
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] The Information Schema vs the PG Catalog
I would say that pg_catalog is the more complete one whereas the information_schema the more generic, standards-conformant place. I would stick with the information_schema unless that becomes inadequate. A case in point may be sequences. Apart from information_schema.columns.column_default I 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 writing PL/pgSQL routines that generate triggers, functions, > and rules based on design characteristics of tables, columns, and > other database objects. These routines need to be able to look up > the definitions of these objects. I see that there are two places > available to look up this info: the Information Schema and in the > PG Catalog. > > Which source is preferable? Or if that answer isn't absolute, > what are the reasons or conditions for preferring one over the > other? > > Also, a specific question: Does the Information Schema offer any > way to list the sequences that exist and their attributes? I > can't seem to find any. > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[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 exact threshholds seem to depend on the data, subquery and possibly the indexes in place. Nothing in the documentation mentions a limit to what "IN subquery" can take so I am quite dismayed (if there is a limit I would expect an ERROR/WARNING/something, but not wrong data). Read below for details. This has been tried on PG 8.0.4 and 8.1.1 on WinXP, and 8.0.4 and 8.0.5 on Linux. I have two tables: t1 (id, name, a_type) t2 (id, name) The t1 table is "big" (483211 rows), the name column contains duplicates, the a_type has only two values. The t2 table is "small" (40 rows), the name values are unique. Some, but not all, t2.name values are present in t1.name. To be precise, there are 10 t2.name values that do not occur in t1.name (this is based on extraneous knowledge). I want to find out which of the t2.name values are not ever used in t1.name: select count(*) from t2 where t2.name not in ( select t1.name from t1); --> 0 This should return 10, instead it returns 0!!! Adding a LIMIT to the subquery and doing some trial and error produces very interesting results: select count(*) from t2 where t2.name not in ( select t1.name from t1 limit 261683) --> 13 select count(*) from t2 where t2.name not in ( select t1.name from t1 limit 261684) --> 0 What is so magical about 261683? The JOIN alternative produces the correct results no matter what: select count(*) from t2 left join t1 using (name) where t1.name is null --> 10 This pretty much summarizes the issue. Any thoughts greatly appreciated. Follow a few variations to show how the threshhold varies. -- restrict to only one a_type -- select count(*) from t2 where t2.name not in ( select t1.name from t1 where t1.a_type='P') --> 0 select count(*) from t2 where t2.name not in ( select t1.name from t1 where t1.a_type='P' limit 289014) --> 11 select count(*) from t2 where t2.name not in ( select t1.name from t1 where t1.a_type='P' limit 289015) --> 0 select count(*) from t2 left join (select * from t1 where a_type='P') t1 using (name) where t1.name is null --> 11 -- try distinct instead -- select count(*) from t2 where t2.name not in ( select distinct t1.name from t1) --> 0 select count(*) from t2 where t2.name not in ( select distinct t1.name from t1 limit 56001) --> 10 select count(*) from t2 where t2.name not in ( select distinct t1.name from t1 limit 56002) --> 0 -- distinct + a_type restriction (adding the a_type restriction -- here _lowers_ the threshhold) -- select count(*) from t2 where t2.name not in ( select distinct t1.name from t1 where t1.a_type='P') --> 0 select count(*) from t2 where t2.name not in ( select distinct t1.name from t1 where t1.a_type='P' limit 33620) --> 11 select count(*) from t2 where t2.name not in ( select distinct t1.name from t1 where t1.a_type='P' limit 33621) --> 0 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] "large" IN/NOT IN subqueries result in query returning wrong data
Yes, yes, of course... There are nulls in my t1 table. And, of course,
NOT IN can return THREE possible values: not just TRUE or FALSE but also
NULL...
select distinct (moo.goo not in (null)) from moo;
--> null
select count(*) from moo where moo.goo not in ('gai', 'pan', 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
What does The SQL Standard say about this one? create table foo (a varchar, b int); insert into foo (a, b) select null, null from bar; -- no problem insert into foo (a, b) select distinct null, null from bar; -- ERROR: column "b" is of type integer but expression is of type text -- HINT: You will need to rewrite or cast the expression. It seems that applying DISTINCT makes the NULL be of type TEXT... I just tried the exact same thing in Ms. SQL Server 2005 and it works with no errors. Looks like SQL Server makes the NULLs be INTs, but is probably better at doing the implicit 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 lax about implicit coercions, but that doesn't make
> it a good idea to coerce anything to anything else without complaint.
> You might as well not have a type system at all, if you're going to
> destroy its ability to detect mistakes that way.
indeed! but, wait, doesn't our favorite dbms do some implicit casting
too? continuing with my table foo (a varchar, b int):
test=# delete from foo;
DELETE 2
test=# insert into foo values (4,4);
INSERT 0 1
test=# insert into foo values ('4','4');
INSERT 0 1
test=# insert into foo values ('oh','no');
ERROR: invalid input syntax for integer: "no"
test=# select * from foo;
a | b
---+---
4 | 4
4 | 4
(2 rows)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
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 we eventually resolve as varchar or int.) hmmm... with the risk of boring everyone to tears: 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 int),cast(4 as int)); INSERT 0 1 test=# insert into foo values (cast(4 as text),cast(4 as text)); ERROR: column "b" is of type integer but expression is of type text HINT: You will need to rewrite or cast the expression. (i actually think it is important to understand how this stuff works and thanks for explaining!) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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 to increase your free space map settings
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 int),cast(4 as int)); > > INSERT 0 1 > > test=# insert into foo values (cast(4 as text),cast(4 as text)); > > ERROR: column "b" is of type integer but expression is of type text > > HINT: You will need to rewrite or cast the expression. > > The last sentence (and the second one as well -- they are exactly > equivalent) exemplifies that there isn't an implicit typecase > from text > to integer. If you use single quotes instead of an explicit cast, the > literal is assumed to be of type "unknown", which _can_ be casted > automatically to integer. yes, #1 is the same as #3, and #2 is the same as #4. i was trying to provide a counter example to tom's statement that pgsql does not do type coercions. the interesting thing here is that 4::int gets into a text field whereas 4::text does not get into an integer field. seems to me like there is an implicit int-to-text cast (without a symmetrical text-to-int one) unless 4::int is somehow *still* considered an 'untyped literal' (4 and '4' seem untyped to me, but 4::int does not)?? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[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 from a geographical perspective), so I have a statement: update city set latitude = city2.lat from (select c.city_id, avg(pc.latitude) as lat from city c left join city_postal_code cpc using (city_id) left join postal_code pc using (postal_code_id) group by c.city_id) city2 where city2.city_id = city.city_id The datatype of both city.latitude and postal_code.latitude is number(16,12). This works, but I would like to understand why there is sometimes a discrepancy between avg(pc.latitude) and what actually gets inserted into the city table -- is it the usual floating-point discrepancy or is there something I can do about it? E.g. after the above update: select c.latitude, avg(pc.latitude), c.latitude-avg(pc.latitude) as diff from city c left join city_postal_code cpc using (city_id) left join postal_code pc using (postal_code_id) group by c.city_id,c.latitude having avg(pc.latitude) != c.latitude latitude | avg |diff -+-+- 36.70937433 | 36.70937433 | -0. 41.07838573 | 41.07838573 | -0. 31.57643789 | 31.57643789 | 0. 42.6967 | 42.6967 | 0. 35.10458117 | 35.10458117 | 0. 31.263006142857 | 31.2630061428571429 | -0.1429 38.805648772727 | 38.8056487727272727 | -0.2727 ... An additional question -- is the UPDATE above written as cleanly as possible (I am not very confident on my understanding of UPDATE-SET-FROM syntax)? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] PostgreSQL and uuid/guid
On Mon, 02 Jan 2006 14:26:14 +0100 Mario Splivalo <[EMAIL PROTECTED]> threw this fish to the penguins: > While browsing the web I've seen that many people 'need' the ability to > create uuid/guid values from within the PostgreSQL. Most of them are > switching from MSSQL, and they're missing the newid() function, which in > MSSQL created the uuid/guid value. > > Now I'm in need of such function too. I'm finaly putting together > MSSQL-like-merge-replication for PostgreSQL, and the guid/uuid values > would come more than handy here. ... > Now, it should be possible to create postgresql function (in python, > forn instance) wich would call the uuidgen program, but that approach > seems rather slow. Since it looks like you are familiar with python, you could try a uuid generator in pure python -- here's one I played with a while ago: (don't know if it's still maintained, but there are others if you search comp.lang.python). http://www.alcyone.com/pyos/uid/ > I'd like to have postgresql function written in C that would call > uuid_generate > (http://www.die.net/doc/linux/man/man3/uuid_generate.3.html). > Considering what is said for the uuidgen manpage (concerning random and > pseudorandom generated numbers) I feel that uuids generated this way are > what I need. > > What is the general feeling concerning the uuid issue I explained above? > I've never 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?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] non-equi self-join optimization
I have a table of names with two subsets of entities. I want to find those names from set 1 that are substrings of names from set 2 from the same table. Basically the pared down query I want is something like this: select t1.myname, t2.myname from mytable t1 inner join mytable t2 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 of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] CREATE VIEW form stored in database?
- Original Message - From: "Mario Splivalo" <[EMAIL PROTECTED]> Yes, I'm tied to the pgadmin3 for the moment, so there's nothing I could do. It's a pain to develop a database such way. Mario, If you keep your definition in a script file, you can copy 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
what would you expect the following command to insert into column a: copy foo (a,b) from stdin with csv; "bar" , 3 \. i was expecting to see 'bar', but instead i get 'bar ' (the spaces between the double quote and the comma get inserted. select length(a), * 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 desire to choose an index scan if your joining column's datatypes do not match
[SQL] trecherous subselect needs warning?
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] This query returns zero rows: newschm3=# select run_id from s_bake where opset_id not in (select opset_id from opset_steps); run_id (0 rows) But, by my mistake, table opset_steps has no column "opset_id"! Surely it should return an error, or at least a warning, not just an empty rowset. "s_bake" *does* have an "opset_id" column, so that's what it uses. The "from opset_steps" is useless. I can understand it might be inappropriate to make such illegal, but wouldn't a warning be appropriate? It seems like postgres should know immediately that there is a useless "from" clause. Even trickier would be: select run_id from s_bake where opset_id in (select opset_id from opset_steps); which would return all rows from s_bake IFF opset_steps 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 missing something here? -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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); > > run_id > > > > (0 rows) > > > But, by my mistake, table opset_steps has no column "opset_id"! > > Surely it should return an error, or at least a warning, not just an > > empty rowset. > > Access to upper-level variables from subqueries is (a) useful and (b) My orginal posting suggested a warning for the *useless* "from opset_steps" clause, since it's presence is misleading. But I don't suppose the SQL spec allows warnings that are not explicitly in the spec, alas. Of course upper-level variables must in general be accessible from subqueries. > required by the SQL spec, so we are not going to start throwing warnings > about it. I was just trying to find a way to prevent other innocent users from wasting many hours of torment tracking down this subtle twist of SQL... How about a *documentation* suggestion that sub-queries can be very dangerous if one doesn't qualify all column references? Maybe with an example like the one that bit me? -- George -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
