Re: [SQL] sql
Hi... I tried this its working.. can u please check this. select count(*) as all, sum(decode(entry_user_id,'VC',1)) as entry_user_id,sum(decode(entry_user_id,'VE',1)) as VE ,sum(decode(entry_user_id,CV,1))as CV,sum(decode(entry_user_id,'SC',1))as SC from vigilance_master; regards penchal -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Karthikeyan Sundaram Sent: Tuesday, February 06, 2007 12:47 PM To: [EMAIL PROTECTED]; pgsql-sql@postgresql.org Subject: Re: [SQL] sql try this: select entry_user_id, sum(decode(entry_user_id,'VC',1,0) as vc, sum(decode(entry_user_id,'VE',1,0) as ve, sum(decode(entry_user_id,'CV',1,0) as cv, sum(decode(entry_user_id,'SC',1,0) as SC from vigilance_master group where entry_user_id=78 group by entry_user_id >From: "Shyju Narayanan" <[EMAIL PROTECTED]> >To: pgsql-sql@postgresql.org >Subject: [SQL] sql >Date: Fri, 2 Feb 2007 13:09:09 +0530 > >Hi All > >this is my table ; >| ID|entry_user_id_int | category_id_chv | >-- >| 1|78|CV | >-- >| 2|78|VC | >-- >| 3|78|CV| >-- >| 4|78|CV| >-- >| 5|78|CV| >-- >| 6|78|CV| >-- >| 7|78|CV| >-- >| 8|78|CV| >-- >| 9|78|VE| >-- >| 10|78|CV| >-- >| 11|78|SC| >-- > >WHEN "select entry_user_id_int, category_id_chv,count(category_id_chv) from >vigilance_master group by category_id_chv,entry_user_id_int having" >entry_user_id_int=78 >result is : > >ID entry_user_id_int category_id_chv count > >178 VC 1 >278 VE 1 >378 CV 8 >478 SC 1 > >BUT I NEED THE RESULT AS >entry_user_id_int COUNT(VC) COUNT(VE) COUNT(CV) COUNT(SC) TOTAL >781 1 8 1 11 _ Get in the mood for Valentine's Day. View photos, recipes and more on your Live.com page. http://www.live.com/?addTemplate=ValentinesDay&ocid=T001MSN30A0701 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Index ANDing & Index ORing
Hiltibidal, Robert wrote: Does postgres have support for index ANDing and index ORing? Well, 8.1+ will create an on-the-fly bitmap for x=1 AND y=2 type of queries. What were you thinking for ORing? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Compilation errors
Hiltibidal, Robert wrote: Is this a good group to post compilation errors to? Probably better with pgsql-general -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] PL/pgsql declaration of string / bit / number with given (variable!) length
sneumann wrote: Hi, I have a PL/pgsql function that creates a certain bit string based on the parameters. Things work fine if I use bit(10) throughout the function. Now I'd like to return a bit string with the given size "len", but that breaks with a syntax error: ret := B'0'::bit(len); LINE 1: SELECT B'0'::bit( $1 ) ^ Any suggestion how to return a (bit) string of user-defined length ? Hmm - not sure it's possible (even theoretically) for a function to have multiple return types. Could you return a "bit varying" and cast it when received? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Index ANDing & Index ORing
Hiltibidal, Robert wrote: Morning I currently use DB2 on an AIX 5.2 p5 platform 7gb ram for log analytics with heavy OLAP querying. Index ANDing in DB2 allows for multiple indexes to be used in joins. I'm not sure of the ORing function yet. That's what 8.1+ support. The bitmap is in-memory though. DB2 does not really allow for WAL to be turned off. Consequently the max throughput on queries I have been able to achieve is 367 inserts a second. This is on shark disk not local. That number was achieved with 3 log imports running at once. Hmm - with battery-backed write-cache on the disks I'd expect better than that. Let's see - 367 inserts/sec * 60 =~ 22000 / 3 =~ 7000 inserts /min (assuming 1 log). You should be able to do better than that with decent disks. I'm looking for a solution that allows me to turn WAL on or off depending on need. I'm not sure what the connection is, but PostgreSQL doesn't allow WAL to be turned off. You can turn off fsync, but you can only change this when you restart postgresql. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Compilation Error AIX
Hiltibidal, Robert wrote: > I am getting this error I thought you were supposed to post in pgsql-general ... > make -C port all > > make[3]: Entering directory > `/db2/logs/downloads/postgres/postgresql-8.2.1/src/backend/port' > > gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline > -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing > -I../../../src/include -c -o dynloader.o dynloader.c Where does the src/backend/port/dynloader.c symlink point to? What AIX version is this? GCC version? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Index ANDing & Index ORing
Hiltibidal, Robert wrote: Keep in mind that 2/3 of the inserts are actual log entries. 1/3 are for supporting information like ip, query string values etc. The table is a "star" table. You are correct tho in terms of time.. When I did the same application with fpc pascal (http://www.freepascal.org) and MS SQL 2000 I averaged over 700 inserts a second on a Compaq DL380 with 2 gb ram and dual 1.3 ghz processors. 15000 rpm scsi hard drives in a raid 5 config. The different there is MS SQL 2000 allows transaction logging to be turned off. I think the transaction logging presents a "speed bump" If you can batch things into transactions of 2+ inserts (up to say 5000) you'll find things go much faster. The limiting factor should be (as you say) the speed of committing the transaction log. Failing that, try having the WAL on its own disks, with battery-backed write cache too. Failing that, you can turn fsync off, but don't complain if the power fails and your database gets corrupted. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Removing duplicate rows
I have some tables which I need to load from flat data files that can contain upwards of five million rows. It's possible (owing to the structure of the system the data is originating from) that on occasion the data extract that generates these flat files can include two records that have the same values in the fields that make up the primary key of the table in my Postgres database, thus throwing an error when I attempt to load. I'm assuming the best way to get around this is to load the data into a temporary table with "copy from" and then do a "select distinct into" my real table. The question I have is the best way to set up this temporary table in my reload script. (Having 14 servers running Postgres, each with 37 tables, I don't want to be creating temporary tables manually each time I need to reload the databases) As the table definitions get updated frequently, I'd like to put in my script to create the table as a copy of the existing 'real' table. I'm assuming "create table as" would be what I need to use, but what would I use as the select sql - i.e. how do I use select to pull the definition of a table. Apologies if this is a stupid question, I'm still fairly new to Postgres so I'm not sure what system tables are available for pulling out this kind of information. Thanks in advance for any assistance you can offer. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Removing duplicate rows
On Wed, Feb 07, 2007 at 07:15:02AM +0900, Paul Lambert wrote: > > I'm assuming the best way to get around this is to load the data into a > temporary table with "copy from" and then do a "select distinct into" my > real table. You might find that sort|uniq at the command prompt would be better. That said, > script to create the table as a copy of the existing 'real' table. I'm > assuming "create table as" would be what I need to use, but what would I > use as the select sql - i.e. how do I use select to pull the definition > of a table. an easy way to do this is "CREATE TABLE name AS SELECT . . . WHERE 1=0". You get a table with no rows. (WHERE FALSE and similar constructs all work equally well.) A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Removing duplicate rows
Andrew Sullivan wrote: On Wed, Feb 07, 2007 at 07:15:02AM +0900, Paul Lambert wrote: I'm assuming the best way to get around this is to load the data into a temporary table with "copy from" and then do a "select distinct into" my real table. You might find that sort|uniq at the command prompt would be better. That said, Sort on Weenblows is a bastard to work with, and I don't believe it has a unique option. I probably should have mentioned this was on Weenblows. script to create the table as a copy of the existing 'real' table. I'm assuming "create table as" would be what I need to use, but what would I use as the select sql - i.e. how do I use select to pull the definition of a table. an easy way to do this is "CREATE TABLE name AS SELECT . . . WHERE 1=0". You get a table with no rows. (WHERE FALSE and similar constructs all work equally well.) Thanks... I figured it would be blatantly obvious. This does the trick: CREATE TABLE billing_code_temp AS SELECT * FROM billing_code WHERE 1=0 A Appreciate the help. P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Removing duplicate rows
As the table definitions get updated frequently, I'd like to put in my script to create the table as a copy of the existing 'real' table. I'm assuming "create table as" would be what I need to use, but what would I use as the select sql - i.e. how do I use select to pull the definition of a table. The create statement allows you to copy a table structure, including or excluding constraints. For inclusion and exclusion effects, please read the manual info related with the 'like' clause of the 'create table' statement. Ex.: create temporary table mytesttable (like mymaintable excluding constraints excluding defaults); Regards, Hélder M. Vieira ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Removing duplicate rows
Paul Lambert wrote on 06.02.2007 23:44: Sort on Weenblows is a bastard to work with, and I don't believe it has a unique option. I probably should have mentioned this was on Weenblows. You can get all (or most?) of the *nix/GNU commandline tools for Windows as well. As "native" Win32 programs that do not require a pseudo *nix (aka as Cygwin) to run in: http://gnuwin32.sourceforge.net tsort and uniq are part of the coreutils package Thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
