[SQL] postgresql.largeobject package
Hai, Anyone has used postgresql.largeobject package in your java program? If yes please help me 1. In getting the package 2. Use the package Thanks -- Ramesh HR Trainee Engineer EASi Technologies 213, 3rd Main, 4th Cross Chamrajpet, Bangalore - 560 018 India Ph.: 660 1086 / 660 2365 / 667 2984 Extn.: 155 Facsimile: 667 5274 www.easi.soft.net
[SQL] grouping by date increments
I am trying to write a select statement to count the occurences of a
particular string between a set of dates.
I have written this successfully but need to get the count in time
increments such as per day/week/month.
At the moment I am doing a select for each increment seperately but figure
that as its doing a seqential scan then it may be possible to do it all at
once.
Has anyone done anything similar that or maybe could recommend a more
efficient solution.
Thanks
Graham
current select:
SELECT to_char(timestamp('01-Jun-2000'), 'DD-Mon-'),
to_char(timestamp('01-Aug-2000'), 'DD-Mon-'), count(*) FROM table WHERE
date >= timestamp('01-Jun-2000') AND date < timestamp('01-Aug-2000') AND
text = 'FOOBAR';
winmail.dat
[SQL] select returns no line
Hi, Our database has a SELECT problem using varchar columns in WHERE clause (but not in all rows!!!). We can fix the whole table (or just the row) as shown below but later it seems wrong again (and after the fix the row isn't UPDATEd). Any idea? Thanks Attila Environment: [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2] goodwill=>\d users Table= users +--+--+---+ | Field | Type| Length| +--+--+---+ | user_id | int4 not null default nextval ( | 4 | | user_login | varchar() not null |15 | | user_passwd | varchar() not null |15 | | user_exp | timestamp| 4 | +--+--+---+ Indices: users_pkey users_user_login_key The problem: goodwill=>select * from users where user_login='test'; user_id|user_login|user_passwd|user_exp ---+--+---+ (0 rows) goodwill=> select * from users where user_id=4; user_id|user_login|user_passwd |user_exp ---+--+-+ 4|test |0PDv7a2EESjZo| (1 row) goodwill=> update users set user_login=user_login where user_id=4; UPDATE 1 goodwill=>select * from users where user_login='test'; user_id|user_login|user_passwd |user_exp ---+--+-+ 4|test |0PDv7a2EESjZo| (1 row) -- x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x
Re: [SQL] select returns no line
Attila Kevei wrote: > Hi, > > Our database has a SELECT problem using varchar columns in WHERE clause > (but not in all rows!!!). > We can fix the whole table (or just the row) as shown below but later it > seems wrong again (and after the fix the row isn't UPDATEd). > > Any idea? > > Thanks > Attila > > > Environment: > > [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2] > > goodwill=>\d users > Table= users > +--+--+---+ > | Field | Type| Length| > +--+--+---+ > | user_id | int4 not null default nextval ( | 4 | > | user_login | varchar() not null |15 | > | user_passwd | varchar() not null |15 | > | user_exp | timestamp| 4 | > +--+--+---+ > Indices: users_pkey > > users_user_login_key > > > The problem: > > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd|user_exp > ---+--+---+ > (0 rows) > > goodwill=> select * from users where user_id=4; > user_id|user_login|user_passwd |user_exp > ---+--+-+ > 4|test |0PDv7a2EESjZo| > (1 row) > > goodwill=> update users set user_login=user_login where user_id=4; > UPDATE > 1 > > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd |user_exp > ---+--+-+ > 4|test |0PDv7a2EESjZo| > (1 row) > > -- > x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x hello are you sure the value of the user_id in that line is "test" and not "test " i.e it has not spurious spaces at the end of it ? HTH Patrick
Re: [SQL] select returns no line
No, there's no space. The query (point 4. below) after the fix (point 3.) is the _same_ as the first (p.1.). In the first select there's no result but in the last select we get the right result. Attila Quoting [EMAIL PROTECTED]: 1. > > goodwill=>select * from users where user_login='test'; > > user_id|user_login|user_passwd|user_exp > > ---+--+---+ > > (0 rows) > > 2. > > goodwill=> select * from users where user_id=4; > > user_id|user_login|user_passwd |user_exp > > ---+--+-+ > > 4|test |0PDv7a2EESjZo| > > (1 row) > > 3. > > goodwill=> update users set user_login=user_login where user_id=4; > > UPDATE > > 1 > > 4. > > goodwill=>select * from users where user_login='test'; > > user_id|user_login|user_passwd |user_exp > > ---+--+-+ > > 4|test |0PDv7a2EESjZo| > > (1 row) > > > > hello > are you sure the value of the user_id in that line is "test" and not > "test " > i.e it has not spurious spaces at the end of it ? > HTH > Patrick > -- x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x
Re: [SQL] select returns no line
A space or something like that is also what I was thinking of. I'd suggest to: select * from users, length(user_login) where user_id=4; before and after the update. V.Paul
[SQL] monster query, how to make it smaller
Hello everybody I need some help on a monster query. Please see the attached file for the query itself. The only difference is Z_durch_soll and the offset, which is currently 0.25. The query will run in a loop where I increment this offset until I find enough records. Thanks in advance ... jr (See attached file: monsterQuery.txt) PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 monsterQuery.txt
[SQL] [notion]: a possible language addition: XQL
Hi all: has anyone heard of XQL? XQL is xml sql i just was catching up on some email and noticed this nugget http://www.ibiblio.org/xql/ anyways there is a xml::xql perl module so perl could do it but the ability to spit it right out of pg would be neato
Re: [SQL] select returns no line
- Original Message - From: "Attila Kevei" <[EMAIL PROTECTED]> > goodwill=>\d users > Table= users > +--+--+- --+ > | Field | Type| Length| > +--+--+- --+ > | user_id | int4 not null default nextval ( | 4 | > | user_login | varchar() not null | 15 | > | user_passwd | varchar() not null | 15 | > | user_exp | timestamp| 4 | > +--+--+- --+ > Indices: users_pkey > >users_user_login_key Have you tried dropping the index? Could be mangled or a locale problem... - Richard Huxton
Re: [SQL] monster query, how to make it smaller
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, January 23, 2001 2:42 PM
Subject: [SQL] monster query, how to make it smaller
> Hello everybody
>
> I need some help on a monster query. Please see the attached file for the
> query itself. The only difference is Z_durch_soll and the offset, which is
> currently 0.25. The query will run in a loop where I increment this offset
> until I find enough records.
I'm not entirely clear on what you are trying to do, but perhaps something
like
... AND Z_durch_soll in ('286.35', '286.30', '286.25')
instead of all the UNIONs? This is the same as
... AND (Z_durch_sol1='286.35' OR Z_durch_sol1='286.30' ...)
HTH
- Richard Huxton
Re: [SQL] select returns no line
Attila Kevei writes: > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd|user_exp > ---+--+---+ > (0 rows) > > goodwill=> select * from users where user_id=4; > user_id|user_login|user_passwd |user_exp > ---+--+-+ > 4|test |0PDv7a2EESjZo| > (1 row) You probably started the postmaster with two different LC_COLLATE (locale sort order) settings. The setting that was active when you ran initdb must be kept when you run the postmaster. > goodwill=> update users set user_login=user_login where user_id=4; > UPDATE > 1 > > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd |user_exp > ---+--+-+ > 4|test |0PDv7a2EESjZo| > (1 row) Yep, the update will fix the corrupted index (at least to the extent that this particular case now works). -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] grouping by date increments
"Graham Vickrage" <[EMAIL PROTECTED]> writes: > I have written this successfully but need to get the count in time > increments such as per day/week/month. Perhaps use GROUP BY date_part(...). See stgresql.org/devel-corner/docs/postgres/functions-datetime.htm for doco on date_part(). regards, tom lane
Re: [SQL] monster query, how to make it smaller
Can't you skip the UNIONing and do this with something like
where (Z_A_nr is NULL
AND Z_umfang = '900' AND Z_blaenge = '2340' AND
Z_durch_soll IN ('286.1', '286.15', '286.20', ...)
AND Z_status = 'zcu'
AND (((Z_durch_soll+0.25)-Z_durch_ist) / 2) >= 0.085
AND (((Z_durch_soll+0.25)-Z_durch_ist) / 2) > 0.12)
?
I'm not sure whether the difference in the last output expression
(Z_durch_soll+0.25 in the first UNION'd select, Z_durch_soll+0.2
in the rest) is intentional or a typo. If it's intentional, you
could write it as something like
CASE WHEN Z_durch_soll = '286.45' THEN Z_durch_soll+0.25
ELSE Z_durch_soll+0.2 END
regards, tom lane
Re: [SQL] select returns no line
Attila Kevei <[EMAIL PROTECTED]> writes: > Our database has a SELECT problem using varchar columns in WHERE clause > (but not in all rows!!!). > We can fix the whole table (or just the row) as shown below but later it > seems wrong again (and after the fix the row isn't UPDATEd). Very bizarre. Is the bogus SELECT using an index? (Check with EXPLAIN if you're not sure.) I am suspicious that it is using a corrupted index. Try dropping and rebuilding the index and see if the problem persists. If you have built Postgres with locale support, an easy way to get corrupted indexes on text/char/varchar columns is to start the postmaster with different locale environment variables at different times. Different locales mean different logical sort orders, and a btree index that is out of order is corrupt by definition. The most common way of shooting yourself in the foot is to sometimes start the postmaster from a boot script, and sometimes start it by hand from a user login that has different locale environment than the boot script. 7.1 will have some defenses against this, but in older releases you have to be careful. regards, tom lane
[SQL] finding foreign keys
Can anyone suggest a more elegant way of finding foreign keys than parsing the tgargs value returned by this query? I'd really rather do pure SQL, sans string parsing, if possible. -- Find tables and foreign keys CREATE VIEW foreignkeysa AS SELECT t.oid as toid, c1.relname AS relid, c2.relname AS constrrelid, t.tgargs AS tgargs FROMpg_trigger AS t, pg_class AS c1, pg_class AS c2 WHERE t.tgrelid=c1.oid AND t.tgconstrrelid=c2.oid AND t.tgfoid=1644; -Ron-
[SQL] Rules and transactions
If I have a rule, is the rule inside a tranaction along with the table that it references. For example, if I have a rule that deletes an entry from table B, whenever an entry in table A gets deleted, then is the delete for table A and table B wrapped inside the same transaction? Same question I guess goes for triggers. thanks, --brett
[SQL] retrieving user's groups
hello, how will i retrieve group names of a certain user? using sql. richard
[SQL] RE: retrieving user's groups
Here's a start: select g.groname as group, g.grosysid as group_id, u.usename as user, u.usesysid as user_id from pg_group g, pg_user u where u.usesysid = g.grolist[1] or u.usesysid = g.grolist[2] or u.usesysid = g.grolist[3] or u.usesysid = g.grolist[4] or u.usesysid = g.grolist[5] or u.usesysid = g.grolist[6] or u.usesysid = g.grolist[7] or u.usesysid = g.grolist[8] or u.usesysid = g.grolist[9] or u.usesysid = g.grolist[10] order by groname, usename; -Original Message- From: chard [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, January 23, 2001 7:39 PM To: [EMAIL PROTECTED] Subject:retrieving user's groups hello, how will i retrieve group names of a certain user? using sql. richard
[SQL] #DELETED error when using Access 2000 as frontend
Hi, I tried to subscribe to pgsql-interfaces several times and received "user not found". I also searched the pgsql-interfaces archives, without success. So here is my problem. I want to use pg 7.x as a backend for a MS Access application. I linked a table via ODBC, using the newest ODBC driver. I can open and view tables. But after I insert a new record, all fields will contain "#deleted". When I reopen the table, the inserted data is displayed correctly. How can I fix this? Thank you, Markus
[SQL] DATE
PLEASE ADVISE HOW I SHOULD ALTER THE COMMAND: <$NOW;DD;> TO GIVE ME A DATE THAT IS X DAYS FORWARD ie: I WISH TO REPRESENT A DATE IN FORM WHICH IS A 7 DAYS FORWARD OF THE DATE NOW. REGARDS JOHN WHALE
[SQL] select returns no line
Hi, Our database has a SELECT problem using varchar columns in WHERE clause (but not in all rows!!!). We can fix the whole table (or just the row) as shown below but later it seems wrong again (and after the fix the row isn't UPDATEd). Idea? Thanks Attila Environment: [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2] goodwill=>\d users Table= users +--+--+---+ | Field | Type| Length| +--+--+---+ | user_id | int4 not null default nextval ( | 4 | | user_login | varchar() not null |15 | | user_passwd | varchar() not null |15 | | user_exp | timestamp | 4 | +--+--+---+ Indices: users_pkey users_user_login_key The problem: goodwill=>select * from users where user_login='test'; user_id|user_login|user_passwd|user_exp ---+--+---+ (0 rows) goodwill=> select * from users where user_id=4; user_id|user_login|user_passwd |user_exp ---+--+-+ 4|test |0PDv7a2EESjZo| (1 row) goodwill=> update users set user_login=user_login where user_id=4; UPDATE 1 goodwill=>select * from users where user_login='test'; user_id|user_login|user_passwd |user_exp ---+--+-+ 4|test |0PDv7a2EESjZo| (1 row) -- x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x
Re: [SQL] unreferenced primary keys: garbage collection
Jan, Thanks for the reply, but your solution is rather unattractive to me. It requires that, any time a reference to an address id is changed, five tables be searched for the address id. This will create unwanted overhead every time a change is made. In order to make those searches even remotely fast, I'd have to add indexes to every one of those tables, which will mean an additional performance hit on table inserts. Moreover, if a new table is created that references address ids, and the maintainer at the time forgets to rewrite those trigger functions, the system will break. I'd much rather be able to simply attempt a delete of any given address, relying on referential integrity to prevent the delete if the address is still being referenced. I don't see why postgres has to treat such a situation as a fatal error. If postgres issued (for example) a warning instead of an error here, I'd be home free! Hasn't there been some talk on the lists about this lately? Forest Jan Wieck wrote: >> While this behaviour makes sense in your case, it's not >> subject to referential integrity constraints. You could >> arrange for it with custom trigger procedures, checking all >> the five tables on DELETE or UPDATE on one of them. Forest Wilkinson wrote: >> > I have a database in which five separate tables may (or may not) reference >> > any given row in a table of postal addresses. I am using the primary / >> > foreign key support in postgres 7 to represent these references. >> > >> > My problem is that, any time a reference is removed (either by deleting or >> > updating a row in one of the five referencing tables), no garbage >> > collection is being performed on the address table. That is, when the >> > last reference to an address record goes away, the record is not removed >> > from the address table. Over time, my database will fill up with >> > abandoned address records.
Re: [SQL] unreferenced primary keys: garbage collection
One other method is to setup up the foreign keys as ON DELETE RESTRICT, then outside of your transaction block issue a DELETE FROM address WHERE add_id = 1; If there are still records in the other tables referencing this record, it will error out and nothing will happen, however if no related records are left, the delete will succeed (you have to do it outside of transaction, otherwise I belive it will rollback on the error if other rows are found to be referencing the primary key) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 23 Jan 2001, Forest Wilkinson wrote: > Jan, > > Thanks for the reply, but your solution is rather unattractive to me. It > requires that, any time a reference to an address id is changed, five > tables be searched for the address id. This will create unwanted overhead > every time a change is made. In order to make those searches even > remotely fast, I'd have to add indexes to every one of those tables, which > will mean an additional performance hit on table inserts. Moreover, if a > new table is created that references address ids, and the maintainer at > the time forgets to rewrite those trigger functions, the system will > break. > > I'd much rather be able to simply attempt a delete of any given address, > relying on referential integrity to prevent the delete if the address is > still being referenced. I don't see why postgres has to treat such a > situation as a fatal error. If postgres issued (for example) a warning > instead of an error here, I'd be home free! Hasn't there been some talk > on the lists about this lately? > > Forest > > Jan Wieck wrote: > >> While this behaviour makes sense in your case, it's not > >> subject to referential integrity constraints. You could > >> arrange for it with custom trigger procedures, checking all > >> the five tables on DELETE or UPDATE on one of them. > > Forest Wilkinson wrote: > >> > I have a database in which five separate tables may (or may not) reference > >> > any given row in a table of postal addresses. I am using the primary / > >> > foreign key support in postgres 7 to represent these references. > >> > > >> > My problem is that, any time a reference is removed (either by deleting or > >> > updating a row in one of the five referencing tables), no garbage > >> > collection is being performed on the address table. That is, when the > >> > last reference to an address record goes away, the record is not removed > >> > from the address table. Over time, my database will fill up with > >> > abandoned address records. >
[SQL] plpgsql language
hello, i got this error when i tried to create a function "unrecognized language specified in CREATE FUNCTION: 'plpgsql'" why is that?
[SQL] RE: DATE
Select now() + 7; -Original Message- From: john whale [SMTP:[EMAIL PROTECTED]] Sent: Monday, January 22, 2001 10:30 AM To: '[EMAIL PROTECTED]' Subject:DATE PLEASE ADVISE HOW I SHOULD ALTER THE COMMAND: <$NOW;DD;> TO GIVE ME A DATE THAT IS X DAYS FORWARD ie: I WISH TO REPRESENT A DATE IN FORM WHICH IS A 7 DAYS FORWARD OF THE DATE NOW. REGARDS JOHN WHALE
[SQL] Order By Question
This seems like the answer must be pretty easy, but I can't think of it: In the following statement: select field1 from my_table where field2 in (3, 1, 2); How can I modify this statement so that the record are returned in the order of having field2 = 3, then field2 = 1, then field2 = 2. As it stands, I am getting them returned in the order of the value of field1.
[SQL] Re: Order By Question
Tristan Colson wrote: > This seems like the answer must be pretty easy, but I can't think of it: > > In the following statement: > > select field1 from my_table where field2 in (3, 1, 2); > > How can I modify this statement so that the record are returned in the > order of first those records having field2 = 3, then field2 = 1, then > field2 = 2. > As it stands, I am getting them returned in the order of the value of > field1. You can't =;^) If you add field 2 to the select list and order by it, you can get them in 1,2,3 or 3,2,1 order. Alternatively, you could add a case statement to your query to add a column that would be used just to order the data. My syntax may be foobar but it would be something like: select field1, CASE WHEN field2 = 3 THEN 1 WHEN field2=1 THEN 2 WHEN field2=2 THEN 3 AS orderfield from my_table where field2 in (3,1,2) order by orderfield; - Ian
[SQL] Order By Question
This seems like the answer must be pretty easy, but I can't think of it: In the following statement: select field1 from my_table where field2 in (3, 1, 2); How can I modify this statement so that the record are returned in the order of first those records having field2 = 3, then field2 = 1, then field2 = 2. As it stands, I am getting them returned in the order of the value of field1.
[SQL] RE: plpgsql language
Did you execute the following after you created your database? CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; These are needed for the plpgsql language to work. -Original Message- From: chard [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, January 23, 2001 10:40 PM To: [EMAIL PROTECTED] Subject:plpgsql language hello, i got this error when i tried to create a function "unrecognized language specified in CREATE FUNCTION: 'plpgsql'" why is that?
[SQL] Re: [INTERFACES] pl/pgSQL & transaction
On Thu, 18 Jan 2001, Zolof wrote: > This code doesn't work. I use Begin Work to start a transaction but BEGIN is > a PL/pgSQL command so I have a parse error when executing it. > > CREATE FUNCTION a () RETURNS int4 AS ' > BEGIN >BEGIN WORK; >COMMIT WORK; > return 1; > END; > ' LANGUAGE 'plpgsql'; > > What's wrong You didn't read the documentation. "It is important not to misunderstand the meaning of BEGIN/END for grouping statements in PL/pgSQL and the database commands for transaction control. Functions and trigger procedures cannot start or commit transactions and Postgres does not have nested transactions. "
Re: [SQL] Order By Question
> This seems like the answer must be pretty easy, but I can't think of it: > > In the following statement: > > select field1 from my_table where field2 in (3, 1, 2); > > How can I modify this statement so that the record are returned in the > order of first those records having field2 = 3, then field2 = 1, then > field2 = 2. > As it stands, I am getting them returned in the order of the value of > field1. > One way is to have a priority table where each value is mapped to its associated priority and then you do a join against this table and order by the priority value instead. So you might have a table like follows: priorityvalue 1 3 2 1 3 2 -- Prasanth Kumar [EMAIL PROTECTED]
