Re: [SQL] SQL problem with aggregate functions.
On Tue, Jul 09, 2002 at 10:36:17AM +0200, David BOURIAUD wrote:
> Field group | count of D | count of R | count of X.
if you want this that way, i suggest using subselects.
like:
select
distinct field_group,
(select count(*) from table t2 where t2.field_group = t1.field_group and
t2.field='D') as count_of_d,
(select count(*) from table t2 where t2.field_group = t1.field_group and
t2.field='R') as count_of_r,
(select count(*) from table t2 where t2.field_group = t1.field_group and
t2.field='X') as count_of_x
from
table;
should work the way you want it.
anyway, i belive that making this:
select field_group, field, count(*) from table where field in
('D','R','X') group by field_group, field;
and then processing results in client application, should be a little
bit better/faster solution.
depesz
--
hubert depesz lubaczewski http://www.depesz.pl/
Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam
coś do powiedzenia. (c) 1998 depesz
msg06810/pgp0.pgp
Description: PGP signature
Re: [SQL] [GENERAL] id and ID in CREATE TABLE
On Fri, 19 Jul 2002, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > But anyway this is not so important, but why upper cases are bad ? > > It's well established that all-lower-case text is more readable than > all-upper-case ... Agreed. Absolutely. But, since the SQL standard says upper case, wouldn't it be useful to at least have a switch (run time, initdb time, or ./configure time???) called something like FOLDTOUPPER (in upper case of course :-) If it's an easy win I'd be willing to do it. I'm not the world's greatest C hacker, but I still remember enough of it to be competant. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] 7.3 features and enhancements
Hi All, Can somebody direct me to a list of the above. Would be nice to know in advance of its release. Regards Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] "no wait" in locks
Hi... anyone knows a workaround for Oracle "no wait" Option in "SELECT ... FOR UPDATE"? I wrote a programm which runs twice. Both poll the pgsql database all 5 seconds and make an "SELECT ... FOR UPDATE" for the first row found. After some work and commit the next row is processed. The second task waits until the first hast commited, but it should detect the lock and continue work with next row. How can i detect this lock? Oracle returns with "no wait" option an sql error code. Any hints? best regards Daniel Stuewe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] id and ID in CREATE TABLE
On Fri, 19 Jul 2002, Bruce Momjian wrote: > scott.marlowe wrote: > > On Fri, 19 Jul 2002, Tom Lane wrote: > > > > > [EMAIL PROTECTED] writes: > > > > But anyway this is not so important, but why upper cases are bad ? > > > > > > It's well established that all-lower-case text is more readable than > > > all-upper-case ... > > > > Agreed. Absolutely. But, since the SQL standard says upper case, > > wouldn't it be useful to at least have a switch (run time, initdb time, > > or ./configure time???) called something like FOLDTOUPPER (in upper case > > of course :-) > > > > If it's an easy win I'd be willing to do it. I'm not the world's greatest > > C hacker, but I still remember enough of it to be competant. > > Yea, I guess we could. I think the consensus is that the uppercase > default is so weird, we don't know anyone who would want to use it. > Would you? Not if I had a choice. But it isn't about what I want or what you want, it's about doing what makes postgresql the most useful for the most people. I can see many situations where this would make postgresql a better choice than it is right now for certain projects, like: writing an app to be as portable as possible (i.e. the reason we have standards...) -or- migrating from some other database that follows the standard (oracle does I believe) and don't want to go in and re-write SQL queries from all the front end apps that hit the database. -or- making closed source database mining tools more likely to get along with postgresql I can think of a lot of reasons people could desire this, even though I myself would prefer to NOT have upper case. My other thought on it was whether we could make it case insensitive. Again, settable by some switch. It could attribute names in mixed case, but all comparisons would be done in upper or lower case. But I don't know how much that would cost us in processing power, especially if it had to fold case based on locale where we can't just use a simple bit flip to make everything lower or upper case when parsing it. By the way, there have been messages about the problems with case folding in this mailing list for the last six months about once a month, so if there was a simple switch to make it behave the way people need it to behave instead of the way it currently behaves, answering those questions would be as easy as pointing to the right place in the docs. So, I would propose a run time setting that has the four possible settings: case_handling: fold_to_lower (default) fold_to_upper (i.e. SQL92/99 compatibility mode) case_insensitive (this one may be more work than it's worth) case_sensitive (i.e. unix mode) If case insensitive were implemented it I would recommend we do in a way that allows the database to store the tables in mixed case as put in, but parse internally on lower or upper case only. Is local an issue, or does postgresql expect you to do everything in ascii 8 bit with no funny chars in attribute names? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] "no wait" in locks
Hi... anyone knows a workaround for Oracle "no wait" Option in "SELECT ... FOR UPDATE"? I wrote a programm which runs twice. Both poll the pgsql database all 5 seconds and make an "SELECT ... FOR UPDATE" for the first row found. After some work and commit the next row is processed. The second task waits until the first hast commited, but it should detect the lock and continue work with next row. How can i detect this lock? Oracle returns with "no wait" option an sql error code. Any hints? best regard Daniel Stuewe ---(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] [GENERAL] id and ID in CREATE TABLE
On Fri, 19 Jul 2002, Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > Agreed. Absolutely. But, since the SQL standard says upper case, > > wouldn't it be useful to at least have a switch (run time, initdb time, > > or ./configure time???) called something like FOLDTOUPPER (in upper case > > of course :-) > > The main problem with this is what do you do with the system catalogs? > If we start folding to upper case then existing clients will break > unless we also rename pg_class to PG_CLASS, etc; and that will break > them anyway if they wrote "pg_class". > > I don't believe that conforming to this particular small aspect of the > spec is worth the pain it would cause. But it's not a small aspect if it means someone either can't use an app with postgresql because it was written to spec, or someone has to spend a bunch of time rewriting all their queries to work with postgresql. I'm not talking about something I'd want on all my tables or all my databases, but there are many instances (look over this list's archive and you'll see them) where following spec would help migration issues. Plus the pg_class stuff is kind of a blind ally. If we're looking at foldtoupper as a setting, then we're already admitting that we're doing it to be interchangable with other dbmses. If that's the case, no one is gonna be accessing the pg_* tables, because you wouldn't do that in an app you're writing to be portable. And if you're migrating to postgresql, you won't have anything there that would access pg_* as well. Leave the system catalogs in lower case, and don't fold calls to anything that's a system catalog. Or wrap them all to upper if the fold to upper is set. I don't see this being a setting that many people would change, but then again, most people don't change sql_inheritance or transform_nulls_equals but their both there for the same reason as what I'm talking about. Some other databases do things a little differently, and if we want to be a drop in replacement, we can either accomodate them or thumb our noses at them and tell them it's not our problem. In all honesty, this change is more "right" than transform_nulls_equals since transform_nulls_equals makes postgresql BREAK the SQL standard, and this one would make it follow it. ---(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] Editor for pgsql
Hello Folks! Maybe every DBA knows that there is a good shareware to editing Oracle PL/SQL programs named PL/SQL Developer. I'd like to know if someone knows something near to this software for Postgres pgSQL. Thanks! Elielson, ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 7.3 features and enhancements
On Sun, Jul 21, 2002 at 10:11:25 +1000, mark carew <[EMAIL PROTECTED]> wrote: > Hi All, > > Can somebody direct me to a list of the above. Would be nice to know in > advance of its release. You can read the development docs. If you follow the developers link on any of the mirrors you can find them. Here is a direct link to the development docs: http://developer.postgresql.org/docs/postgres/index.html Here is a direct link to the release notes: http://developer.postgresql.org/docs/postgres/release.html ---(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] FATAL 1
Couldn't find any mention of this anywhere. Anyone any idea what it means? A db stop/start seems to have cured it. postgres@judas:~$ psql master psql: FATAL 1: fixrdesc: no pg_class entry for pg_class postgres@judas:~$ psql -V psql (PostgreSQL) 7.2.1 contains support for: readline, history, multibyte Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group Portions Copyright (c) 1996, Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. postgres@judas:~$ uname -a Linux judas 2.4.18 #1 Fri May 3 11:45:59 UTC 2002 i686 unknown -- Ian Cass ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] [NOVICE] SET CONSTRAINTS question...
Hi,
Right after installting postgres 7.2.1 (from release source) and reading
some chapters of Bruce Momjian's great book I started to play around with a
test database. Now I have a question concerning deferred constraint
checking.
I am new to transactional SQL, so I don't really know, if this should work:
-- create query
BEGIN;
CREATE TABLE friend (
name VARCHAR(40) PRIMARY KEY,
country CHAR(2) NOT NULL DEFAULT 'AT'
);
CREATE SEQUENCE conn_id_seq;
CREATE TABLE conn (
id INTEGER NOT NULL DEFAULT nextval('conn_id_seq'),
name VARCHAR(40),
CONSTRAINT conn_friend_fkey FOREIGN KEY(name)
REFERENCES friend(name)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT conn_pkey PRIMARY KEY(id)
);
CREATE INDEX conn_name_idx ON conn (name);
INSERT INTO friend VALUES ('Michael Paesold', 'AT');
INSERT INTO conn (name) VALUES ('Michael Paesold');
COMMIT;
-- test
BEGIN;
SET CONSTRAINTS conn_friend_fkey DEFERRED;
DELETE FROM friend WHERE name='Michael Paesold';
INSERT INTO friend VALUES ('Michael Paesold', 'DE');
COMMIT;
I get an error just after the DELETE query. Perhaps I don't understand the
concept of deferred constraint checking. I know that the above query doesn't
make much sense, but I wanted to try these features.
Best Regards,
Michael Paesold
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [NOVICE] SET CONSTRAINTS question...
> CREATE SEQUENCE conn_id_seq;
> CREATE TABLE conn (
> id INTEGER NOT NULL DEFAULT nextval('conn_id_seq'),
> name VARCHAR(40),
> CONSTRAINT conn_friend_fkey FOREIGN KEY(name)
>REFERENCES friend(name)
>ON UPDATE CASCADE
>ON DELETE RESTRICT
I believe we interpreted restrict as being such that you
can't defer it. No action specification for delete
probably would do what you wanted. (We may also be wrong
about restrict, but the above would be a workaround in any case)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Editor for pgsql
Elielson, > Maybe every DBA knows that there is a good shareware to editing > Oracle PL/SQL programs > named PL/SQL Developer. > I'd like to know if someone knows something near to this software > for Postgres pgSQL. No such program, currently. PGAccess, slated for re-release with a overhaul and new version in September, will have somewhat enhanced Function Editing ability. However, nothing on the scale of PL/SQL Developer. Me, I use Kate, an MDI text editor from the KDE crew, and CVS for version control. Thanks, KDE guys!But, after 3 years of Postgres, I'm pretty fluent in PL/pgSQL. I even double-quote without thinking about it. You might want to send an e-mail to ActiveState suggesting that they could take on SQL script dialects (SQL, T-SQL, PL/SQL, PL/pgSQL and 4GL) as a new ActiveState IDE. Make sure they know you're willing to pay for development software. -Josh Berkus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Free 4GL Compiler, Informix-Postgress support
Hi. See Aubit-4GL a full compatible 4GL compiler which may access Informix and Postgress Database. See more about this at http://aubit4gl.sourceforge.net/index.html Enjoy it and feedback please... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] interval syntax change from 7.1.2 to 7.2.1?
hi - i recently upgraded my server from 7.1.2 to 7.2.1 and now one of my apps is no longer functioning. this is the query: SELECT DISTINCT rate, sum(extract(epoch from interval (end_time - start_time)))/3600) AS duration FROM log WHERE invoice_state='8' GROUP BY rate HAVING rate > 0 where 'end_time' and 'start_time' are columns in table 'log' the reported message is: PostgreSQL said: ERROR: parser: parse error at or near "end_time" any help greatly appreciated. jinn ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Float / Numeric ?
Rudi Starcevic wrote: > > Hi Folks, > > I have an ecommerce application I'm writing and I'm not sure / undecided > on a > particular way to store shipping rates in PostgreSQL > > In this application a user may set the shipping rate per item. > > So I have 3 fields - local,state and international. > A forth field, shipping_type (varchar), holds a value 'dollars' or > 'percent'. > > My twisted logic says a user may say this item has 10 perent shipping to > add or 10 dollars to add. > This means the shipping rate my be either dollars or percent and can be > set on a per item basis. > > For the 3 fields I'm not sure which is best to use - Float or Numeric ? > > My application language is ColdFusion and PHP. > > I'd like to ask which data type would be the better choice. > At this stage I'm using Float to represent a value that may be either > dollars or percent. > > Perhaps this technique, using a varchar as a type of switch > (shipping_type) , is incorrect / silly / stupid / not robust enough or > just plain wrong. > > Do you think this is OK and if so which would you use - Floast or Numeric ? > Thanks for your time. You can have separate fields for percent or fixed amount. With a zero default (not NULL) the calculation is still simple enough bacause adding zero or a multiple of it usually does not affect the result very much. For the data type I allways advise NUMERIC for anything that deals with money. There are legal issues with money and floating point in some countries around the world (especially the pickier ones who love to overengineer everything like Germany). And make sure that the application doesn't work with floating point either. PHP can be built with bcmath support, which does arbitrary precision like our NUMERIC type. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] interval syntax change from 7.1.2 to 7.2.1?
From: "Jinn Koriech" <[EMAIL PROTECTED]> wrote: > hi - i recently upgraded my server from 7.1.2 to 7.2.1 and now one of my > apps is no longer functioning. this is the query: > > SELECT DISTINCT rate, sum(extract(epoch from interval (end_time - > start_time)))/3600) AS duration > FROM log > WHERE invoice_state='8' > GROUP BY rate HAVING rate > 0 > > where 'end_time' and 'start_time' are columns in table 'log' > > the reported message is: > > PostgreSQL said: ERROR: parser: parse error at or near "end_time" > > any help greatly appreciated. > Redo your query putting intervall beetwen " " : SELECT DISTINCT rate, sum(extract(epoch from "interval"(end_time - start_time)))/3600) AS duration FROM log WHERE invoice_state='8' GROUP BY rate HAVING rate > 0 Ciao Gaetano. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] interval syntax change from 7.1.2 to 7.2.1?
hi - i recently upgraded my server from 7.1.2 to 7.2.1 and now one of my apps is no longer functioning. this is the query: SELECT DISTINCT rate, sum(extract(epoch from interval (end_time - start_time)))/3600) AS duration FROM log WHERE invoice_state='8' GROUP BY rate HAVING rate > 0 where 'end_time' and 'start_time' are columns in table 'log' the reported message is: PostgreSQL said: ERROR: parser: parse error at or near "end_time" any help greatly appreciated. jinn ---(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] Editor for pgsql
On Monday 22 July 2002 12:27 pm, Josh Berkus wrote: > Me, I use Kate, an MDI text editor from the KDE crew, and CVS for > version control. Thanks, KDE guys!But, after 3 years of Postgres, > I'm pretty fluent in PL/pgSQL. I even double-quote without thinking > about it. How do you use CVS on your database? I recently started doing this, and i'm wondering how other people handle it. Basically I create a sql folder with three sub-folders tables, views and functions. I have a file for each table in tables, each view in views and for each trigger and/or function in functions. For the actual editing? I'm a vi fan myself :). If i'm using the graphical vim I can even do CVS operations with a custom menu. - James ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Editor for pgsql
James, > How do you use CVS on your database? I recently started doing this, and i'm > wondering how other people handle it. > > Basically I create a sql folder with three sub-folders tables, views and > functions. I have a file for each table in tables, each view in views and for > each trigger and/or function in functions. Similar. I bundle my development scripts into related areas with a tabledef and a couple of views or functions in each script, but otherwise I do the same as you. As for VIM, as an ex-Win32 developer with an art degree, I never mastered the syntax of VI (or Emacs, for that matter). They're certainly powerful tools, but I can never set aside the 2 weeks of downtime required to get up to speed in either text editor. So Kate and Joe are my friends. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Editor for pgsql
On Mon, Jul 22, 2002 at 04:09:21PM -0400, James Orr wrote: > On Monday 22 July 2002 12:27 pm, Josh Berkus wrote: > > > Me, I use Kate, an MDI text editor from the KDE crew, and CVS for > > version control. Thanks, KDE guys!But, after 3 years of Postgres, > > I'm pretty fluent in PL/pgSQL. I even double-quote without thinking > > about it. > > How do you use CVS on your database? I recently started doing this, and i'm > wondering how other people handle it. > > Basically I create a sql folder with three sub-folders tables, views > and functions. I have a file for each table in tables, each view in > views and for each trigger and/or function in functions. > > For the actual editing? I'm a vi fan myself :). If i'm using the graphical > vim I can even do CVS operations with a custom menu. > > - James James, That sounds very ugly, I will usually have 1-4 files per db. Either everything goes into 1 file, drops at the front then creates. Or 2 files, 1 for ddl( create/drop table) and another for plpgsql procedures and triggers. Sometimes I will split each of those into a create and drop file. But that is about as complex as I want it to get. marc ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Editor for pgsql
On Mon, Jul 22, 2002 at 09:36:54AM -0300, Elielson Fontanezi wrote: > Hello Folks! > > > Maybe every DBA knows that there is a good shareware to editing > Oracle PL/SQL programs > named PL/SQL Developer. > I'd like to know if someone knows something near to this software > for Postgres pgSQL. Tora (tora.sf.net) has PL/SQL editing and supports PostgreSQL. Since Pl/pgSQL is very similar to PL/SQL, you can use Tora for PL/pgSQL editing very nicely. Tora is an overall very nice database administration tool. I use it with my Oracle and PostgreSQL installations all the time. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer Microsoft has been doing a really bad job on their OS - Linus Torvalds ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Editor for pgsql
Roberto, > Tora (tora.sf.net) has PL/SQL editing and supports PostgreSQL. Since > Pl/pgSQL is very similar to PL/SQL, you can use Tora for PL/pgSQL editing > very nicely. > > Tora is an overall very nice database administration tool. I use it with > my Oracle and PostgreSQL installations all the time. I tried to install Tora, but the build blew up since I don't have Oracle installed. Any tips? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Editor for pgsql
On Mon, Jul 22, 2002 at 05:41:39PM -0700, Josh Berkus wrote:
>
> I tried to install Tora, but the build blew up since I don't have Oracle
> installed. Any tips?
I just use the Debian packages (/me hugs Debian).
AFAIK, all you have to do is compile with the appropriate flags so it
doesn't try to build Oracle support (you need a full Oracle installation),
and also MySQL support.
You also need to have the PostgreSQL loadable Qt 3 module installed before
compiling, plus Qt 3 headers and such.
You could try downloading the binary Debian packages from
packages.debian.org ("unstable" distribution) and unpacking them (Debian
packages are just "ar" packages with extra headers).
-Roberto
--
+| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
Cannot open CATFOOD.CAN - Eat logitech mouse instead (Y/n)?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
