Re: [SQL] update syntax error?
query should have a comma instead of the word and...
HTH
mikeo
At 04:27 PM 8/20/00 -0300, The Hermit Hacker wrote:
>
>UPDATE SET field1=,field2=
>
>On Tue, 15 Aug 2000, k.c. hemelstrand wrote:
>
>> Can anybody help with why I am receiving the error below?
>>
>> Thanks
>> K.C.
>>
>>
>> parts=# UPDATE av_parts
>> parts-# SET rawalternaternumber = 'NULL' AND alternatepartnumber = 'NULL'
>> parts-# WHERE rawalternatenumber = 'IVA81A';
>> ERROR: left-hand side of AND is type 'unknown', not 'bool'
>>
>
>Marc G. Fournier ICQ#7615664 IRC Nick:
Scrappy
>Systems Administrator @ hub.org
>primary: [EMAIL PROTECTED] secondary:
scrappy@{freebsd|postgresql}.org
>
Re: [SQL] remove line type?
for your perusal... >From: Bruce Momjian <[EMAIL PROTECTED]> >Subject: Re: [SQL] remove line type? >To: mikeo <[EMAIL PROTECTED]> >Date: Tue, 30 May 2000 10:48:02 -0400 (EDT) > >That is very strange. I would send it to the mailing lists. > >> hi bruce, >> we've run into a problem after having deleted the line type. >> when we attempt to query a table by column which is defined as float8 >> we get this error: >> >> \d test1 >>Table "test1" >> Attribute |Type | Modifier >> ---+-+-- >> tfap_id | float8 | >> tfap_name | varchar(50) | >> groupid | integer | >> groupdesc | varchar(50) | >> switch| varchar(50) | >> >> select * from test1 where tfap_id = 49232; >> ERROR: Unable to locate type oid 628 in catalog >> >> if the column is defined as an integer we get the desired results: >> >> spectl=# \d topflow_application >> Table "topflow_application" >> Attribute |Type | Modifier >> ---+-+-- >> tfap_id | integer | >> tfap_name | varchar(50) | >> groupid | integer | >> groupdesc | varchar(50) | >> switch| varchar(50) | >> >> select * from topflow_application where tfap_id = 049232; >> tfap_id | tfap_name | groupid |groupdesc| >> switch >> -+---+-+-+-- >> - >>49232 | x | 31 | Remote Control Services | >> 111.111.1.111 >> >> now, the programmer who created test1 table made that column a float by >> accident >> but that could mean trouble down the road for us as we do use float for >> some other columns. >> you can still select from test1 as long as you don't reference the float >> column in the where >> clause. >> >> oid 628 is the oid for the line row in the pg_type table. is there >> something else that we >> need to do or is deleting this type not a good idea after all? >> >> thanks, >> mikeo >> >> >> >> >> At 03:04 PM 5/17/00 -0400, you wrote: >> >If you do it in template1 database after initdb, all new databases will >> >not have that type either. >> > >> >> that worked!!! thanks! >> >> >> >> mikeo >> >> >> >> >> >> At 02:51 PM 5/17/00 -0400, Bruce Momjian wrote: >> >> >I guess you could remove the line type from the pg_type table and see if >> >> >that helps. >> >> > >> >> >> hi, >> >> >> we're looking at migrating from ORACLE to postgres in the >> >> >> very near future and we've run into a small problem. there's >> >> >> a data type defined "LINE". we have named one of our tables >> >> >> as "LINE" also and it would require a great deal of code >> >> >> changes to rename that table. is it possible to simply >> >> >> "turn off" the line type? any help is appreciated. >> >> >> >> >> >> thanks, >> >> >>mikeo >> >> > > >-- > Bruce Momjian| http://www.op.net/~candle > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 >
Re: [GENERAL] Re: [SQL] remove line type?
thanks much. that now works! :) At 01:41 PM 5/30/00 -0400, Tom Lane wrote: >mikeo <[EMAIL PROTECTED]> writes: >>>> we've run into a problem after having deleted the line type. >>>> when we attempt to query a table by column which is defined as float8 >>>> we get this error: >>>> >>>> select * from test1 where tfap_id = 49232; >>>> ERROR: Unable to locate type oid 628 in catalog > >Interesting. I get: > >bust=# create table foo (f1 int, f2 float8); >CREATE >bust=# insert into foo values(1,2.5); >INSERT 148298 1 >bust=# select * from foo; > f1 | f2 >+- > 1 | 2.5 >(1 row) > >bust=# drop type line; >DROP >bust=# select * from foo; > f1 | f2 >+- > 1 | 2.5 >(1 row) > >bust=# select * from foo where f2 = 2.5; > f1 | f2 >+- > 1 | 2.5 >(1 row) > >bust=# select * from foo where f2 < 3; > f1 | f2 >+- > 1 | 2.5 >(1 row) > >bust=# select * from foo where f2 = 3; >ERROR: Unable to locate type oid 628 in catalog > >It looks to me like the problem appears when the parser has to resolve >an ambiguous operator. (Since there isn't a "float8 = int" operator, >this last case requires some smarts to figure out what to do.) >Presumably there is a line = line operator still in the system, and >it doesn't surprise me a whole lot that this error would pop up if the >parser had occasion to scan through the '=' operators looking for a >possible match and came across that one. Let's see: > >bust=# select * from pg_operator where oprname = '=' and >bust-# (oprleft = 628 or oprright = 628); > oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin >-+--+-+-+---++- +--+---++---+++- +-+--- > = | 256 | 0 | b | t | f | 628 | 628 |16 | 1616 | 0 | 0 | 0 | line_eq | eqsel | eqjoinsel >(1 row) > >bust=# delete from pg_operator where oprname = '=' and >bust-# (oprleft = 628 or oprright = 628); >DELETE 1 >bust=# select * from foo where f2 = 3; > f1 | f2 >+ >(0 rows) > >Yup, looks like that's the problem. > >It's probably not good that DROP TYPE only zaps the pg_type entry and >doesn't go hunting for stuff that depends on it. In the meantime you >might want to do > >delete from pg_operator where oprleft = 628 or oprright = 628; > >and perhaps something similar for pg_proc, although name collisions for >functions are probably less of a problem there. > > regards, tom lane >
[SQL] short query becomes long
hi, we have a weird situation here. we have a table of approx. 10k rows representing accumulated activity by specific customers. as information is gathered those customers rows are updated. the number of rows does not increase unless we get a new customer so that is not a factor. the table is defined as follows: Table "account_summary_02" Attribute |Type | Modifier -+-+-- bill_br_id | bigint | not null cust_id | varchar(15) | not null btn_id | varchar(15) | not null ln_id | varchar(15) | not null ct_key | float8 | not null as_quantity | float8 | not null as_charges | float8 | not null as_count| float8 | not null Index: account_summary_02_unq_idx the index is on the first 5 columns. here's the situation. after about 50,000 updates, which fly right along, the process begins to really bog down. we perform a vacuum analzye and it speeds right up again. my question is, is there a way to perform these updates, potentially 500k to 1 million in a day, without having to vacuum so frequently? maybe some setting or parameter to be changed? the update query is doing an index scan. mikeo
[SQL] TOP SESSIONS?
hi, in oracle you would use these two cursors to determine who was connected and what they were doing. select distinct s.sid sid, s.serial# serial, s.status status, osuser, spid , count(o.sid) counter, s.username username, s.program program, sql_address from v$session s, v$open_cursor o, v$process p where s.sid = o.sid(+) and paddr = addr group by s.sid,s.serial#, s.status , osuser, spid ,s.username, s.program ,sql_address order by 1,3 select distinct name from sys.audit_actions, v$sqltext where address = ? and action = command_type does anyone know what tables in postgres would give me the same or similar information? TIA, mikeo
[SQL] oracle rownum equivalent?
is there an equivalent to rownum in oracle? also, where can one find reference to "hidden columns" such as OID or, as in oracle, rownum? thanks, mikeo
Re: [SQL] oracle rownum equivalent?
thanks for the response. oid is equivalent to oracle rowid. rownum can be used similar to the limit option of select in postgres but in oracle it can also be used in a where clause, or as an assigment in an update statement for instance. eg: update ctmaster set bsc_id = mod(rownum,3) +1; this gives me a way to assign streams to rows in a load balanced manner on the fly, for example. i use it in other more involved ways than this also. i cannot do this with limit. i could do this with sequence with a max value but i'd have to define a sequence each time i wanted to do something "on the fly" or for what ifs. what i'm also interested in is how to find reference to these type of pseudo-columns, even just the names of them, if they're listed somewhere. thanks again, mikeo At 08:29 AM 6/7/00 -0500, Ed Loehr wrote: >mikeo wrote: >> >> is there an equivalent to rownum in oracle? >> >> also, where can one find reference to "hidden columns" >> such as OID or, as in oracle, rownum? > >oid is the equivalent. not sure documentation exists for these... > >Regards, >Ed Loehr >
Re: [SQL] Re: Simple search question
do
select setval('seqname',#);
At 01:17 PM 6/15/00 -0500, John McKown wrote:
>
>Alex wrote in message <[EMAIL PROTECTED]>...
>>Hi,
>>
>>> I had the same problem but was using Java, not PHP (I guess that
>>> whatever I can do in JDBC, you can do in PHP ;-)).
>>>
>>> SELECT last_value FROM ;
>>
>>This brings me back to another unanswered question recently posted up,
>maybe
>>it is impossible...
>>I declared a new table with one of the types as serial (which is really
>just a
>>sort of macro I believe, which automates the creation of a few things for
>your
>>convenience), which initialises the last_value of the relation (the
>sequence
>>itself), as 1. Therefore, upon INSERTing my first row, the serial number
>began
>>at 1, next was 2, then 3, and so forth.
>> My question is, is it possible to alter the sequence last_value column, as
>>I need the serial number to begin from 1000?
>>Thanks,
>>Alex
>>
>
>Nope, I tried doing an
>
>UPDATE name-of-sequence SET last_value=200
>
>and I got an error about not being able to update the sequence. However,
>always being one to hit a brick until it cracks, I then tried:
>
>DROP name-of-sequence;
>CREATE name-of-sequence START 1000;
>
>This seemed to work! When I then INSERTed a new tuple, the attribute defined
>as SERIAL actually got the value of 1000. Curious, but nice.
>
>John McKown (note - not! Jack, but John)
>
>
