[SQL] sqlloader features
Hi , does postgresql support for the sql loader functionality like oracle does ??? if no, is there any tools specific to do this task connect to postgresql database??? i'm looking into open-source tools that can do import & export facility. thanks in advance
[SQL] where not unique
Hi folks, I've got a table which contains amongst other things a stock number and a registration number. I need to print out a list of stock number and reg number where reg number is not unique (cherished number plate transfer not completed). I've tried variations of a theme based on select stock_number, registration from stock where registration in (select registration, count(registration) as count from stock where count > 1 group by registration); but I have two problems. Firstly with the sub-select I get: usedcars=# select registration, count(registration) as count from stock where count > 1 group by registration; ERROR: Attribute 'count' not found usedcars=# although if I miss out the where clause I get the expected results. Secondly, when I run the full query I get: usedcars=# select stock_number, registration from stock usedcars-# where registration in usedcars-# (select registration, count(registration) as count from stock group by registration); ERROR: Subselect has too many fields usedcars=# which is obviously because of the count field. Can anyone tell me where I'm going wroing with these count fields? (I've tried renaming the field to regcount in case it was a reserved word problem). -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] where not unique
Gary Stainburn wrote: Hi folks, I've got a table which contains amongst other things a stock number and a registration number. I need to print out a list of stock number and reg number where reg number is not unique (cherished number plate transfer not completed). I've tried variations of a theme based on select stock_number, registration from stock where registration in (select registration, count(registration) as count from stock where count > 1 group by registration); but I have two problems. Firstly with the sub-select I get: usedcars=# select registration, count(registration) as count from stock where count > 1 group by registration; ERROR: Attribute 'count' not found usedcars=# although if I miss out the where clause I get the expected results. I think that this is not the best way, but : select * from (select registration, count(registration) as counter from stock group by registration) where counter > 1; Secondly, when I run the full query I get: usedcars=# select stock_number, registration from stock usedcars-# where registration in usedcars-# (select registration, count(registration) as count from stock group by registration); ERROR: Subselect has too many fields usedcars=# This is because the subselect is returning 2 columns but "in clause" is expecting just one column. Try to use "exists" instead of "in", ok ? which is obviously because of the count field. Can anyone tell me where I'm going wroing with these count fields? (I've tried renaming the field to regcount in case it was a reserved word problem). I hope this helps you. ---(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] where not unique
You're right, Reinoud. Sorry for my mystake... Reinoud van Leeuwen wrote: On Fri, Mar 12, 2004 at 10:54:25AM -0300, Daniel Henrique Alves Lima wrote: Gary Stainburn wrote: Hi folks, I've got a table which contains amongst other things a stock number and a registration number. I need to print out a list of stock number and reg number where reg number is not unique (cherished number plate transfer not completed). I've tried variations of a theme based on select stock_number, registration from stock where registration in (select registration, count(registration) as count from stock where count > 1 group by registration); but I have two problems. Firstly with the sub-select I get: usedcars=# select registration, count(registration) as count from stock where count > 1 group by registration; ERROR: Attribute 'count' not found usedcars=# although if I miss out the where clause I get the expected results. I think that this is not the best way, but : select * from (select registration, count(registration) as counter from stock group by registration) where counter > 1; Isn't that the same as select registration from stock group by registration having count (registration) > 1; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] where not unique
On 12/03/2004 13:02 Gary Stainburn wrote: Hi folks, I've got a table which contains amongst other things a stock number and a registration number. I need to print out a list of stock number and reg number where reg number is not unique (cherished number plate transfer not completed). I've tried variations of a theme based on select stock_number, registration from stock where registration in (select registration, count(registration) as count from stock where count > 1 group by registration); but I have two problems. Firstly with the sub-select I get: usedcars=# select registration, count(registration) as count from stock where count > 1 group by registration; ERROR: Attribute 'count' not found usedcars=# although if I miss out the where clause I get the expected results. Secondly, when I run the full query I get: usedcars=# select stock_number, registration from stock usedcars-# where registration in usedcars-# (select registration, count(registration) as count from stock group by registration); ERROR: Subselect has too many fields usedcars=# which is obviously because of the count field. Can anyone tell me where I'm going wroing with these count fields? (I've tried renaming the field to regcount in case it was a reserved word problem) If I understand you correctly, you've got something like mytable stockno regno -- SN1 REG1 SN2 REG2 SN3 REG3 SN4 REG2 and you want to list REG2. Something like select regno from mytable group by regno having count(stockno) > 1; might do it. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL query --- big question???
Hi to all, I have a big question. Can I write a query/function/agregate function (whatever) so that I can return the result of a query in a single line? I don't know if this is possible to be made and please if it is not possible please say. I better give an example: table (id, name) 1 xxx 2 yyy 3 zzz Now I want to have a query like: Select id from table and the result to be something like: 1,2,3 and not 1 2 3 in different lines. I just want to concatenate the results in a single result so this query result will have 1 row and 1 column. This looks like an agregate function but I don't know how to make this. I hope you will understand what I want to do. Please advise. Thnx. Andy.
Re: [SQL] sqlloader features
--- [EMAIL PROTECTED] wrote: > Hi , > > >does postgresql support for the sql loader > functionality like oracle > does ??? if no, is there any tools specific to do > this task connect to > postgresql database??? > i'm looking into open-source tools that can do > import & export facility. > thanks in advance The PostgreSQL "COPY" command is very efficient at bulk loading, but has restrictive requirements as to the data formats that it will accept. I haven't tried this one, but it might work for you: http://gborg.postgresql.org/project/pgimport/projdisplay.php Otherwise your best alternative is probably to write a custom script using something like Perl, TCL, Awk or Sed to edit your data, which can then be piped to COPY. Something like: perlscript datafile | psql -c "copy table from stdin" database You can read about "COPY" in the "SQL Commands" section of the manual. > > __ Do you Yahoo!? Yahoo! Search - Find what youre looking for faster http://search.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL query --- big question???
On Friday 12 March 2004 14:36, Andrei Bintintan wrote: > Hi to all, > > I have a big question. > > Can I write a query/function/agregate function (whatever) so that I can > return the result of a query in a single line? I don't know if this is > possible to be made and please if it is not possible please say. You're best doing this in the application really. However, if you only want to work with text, search the mailing list archives using keywords like: text concat aggregate and you should see an example solution where you build your own aggregate function. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] pg_dump backwards compat issues
Hi all, anybody have a sed script (or flags for pg_dump) that will make a pg_dump 7.4 dump load into a Pg 7.3.x backend? TIA! --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 347.524.5631 // Welches ist das groessere Verbrechen? // Massenvernichtungswaffen besitzen oder sie erfinden? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] function returning resultset
Hi, I'm using PGSQL 7.4.1 and JDBC 75dev client. I want to ask about PL/PGSQL function returning resultset. I read in the docs, there are 'setof' and 'refcursor' method, is there another method to returning resultset? What advantages and disadvantages for each method? Which is the best? Plz explain to me. Thx. W.A. Need a new email address that people can remember Check out the new EudoraMail at http://www.eudoramail.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
