[SQL]
please remove my mail id from the mailing list. my mail id : [EMAIL PROTECTED] __ FREE Personalized Email at Mail.com Sign up at http://www.mail.com/?sr=signup
Re: [SQL] Weird problem with script...
- Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, January 03, 2001 1:02 AM Subject: Re: [SQL] Weird problem with script... > [EMAIL PROTECTED] writes: > > I'm building a script to create the tables in my database. Everything > > works fine except one thing with this part of my script: > > > create table tbl_resume_free_text_type ( > > type_id int, > >type text > > ); > > > -- insert into tbl_resume_free_text_type (type_id, type) values (1, 'Hobbies'); > > -- insert into tbl_resume_free_text_type (type_id, type) values (2, 'Special Talents'); > > > If I uncomment the two insert statements I get an error message > > stating that tbl_resume_free_text_type doesn't exist. > > Odd. The three statements work just fine for me when executed by hand > in 7.0.3. Anyone else able to reproduce a problem? > > regards, tom lane Works fine on 7.0.0 putting them in a text file and doing psql < filename Jamu - how are you executing this? - Richard
[SQL] how to build this query ??? Please help !!!
Hello there I have a problem, building a query. Who can help ? I use postgreSQL 7.0.3. Thanks for any help and suggestions ... jr orders a_nr ¦ product ¦ state - 11 ¦ tp ¦ 12 ¦ fi ¦ 13 ¦ tp ¦ 14 ¦ ok ¦ 15 ¦ dm ¦ cylinders z_a_nr ¦ z_status -- 11 ¦ zdr 11 ¦ zdr 12 ¦ zdr 12 ¦ zcu 13 ¦ zdr 13 ¦ zcu 13 ¦ zcu 13 ¦ zcr 15 ¦ zcu 15 ¦ zcu 15 ¦ zdr I need a query for the following output : a_nr ¦ #cylinder #zdr #zcu #zcr productstate -- 112 200dm 122 110tp 134 121fi 140 000ok 153 120dm PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315
Sv: [SQL] how to build this query ??? Please help !!!
Something like this oughtta work (not tested) SELECT a_nr, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zdr') AS #zdr, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zcu') AS #zcu, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zcr') AS #zcr, product, state FROM orders /Nikolaj -Oprindelig meddelelse- Fra: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Til: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Dato: 3. januar 2001 16:16 Emne: [SQL] how to build this query ??? Please help !!! >Hello there > >I have a problem, building a query. Who can help ? >I use postgreSQL 7.0.3. > >Thanks for any help and suggestions ... jr > >orders >a_nr ¦ product ¦ state >- >11 ¦ tp ¦ >12 ¦ fi ¦ >13 ¦ tp ¦ >14 ¦ ok ¦ >15 ¦ dm ¦ > >cylinders >z_a_nr ¦ z_status >-- >11 ¦ zdr >11 ¦ zdr >12 ¦ zdr >12 ¦ zcu >13 ¦ zdr >13 ¦ zcu >13 ¦ zcu >13 ¦ zcr >15 ¦ zcu >15 ¦ zcu >15 ¦ zdr > >I need a query for the following output : > >a_nr ¦ #cylinder #zdr #zcu #zcr productstate >-- >112 200dm >122 110tp >134 121fi >140 000ok >153 120dm > > > > > > >PFISTER + PARTNER, SYSTEM - ENGINEERING AG >Juerg Rietmann >Grundstrasse 22a >6343 Rotkreuz >Switzerland > >phone: +4141 790 4040 >fax: +4141 790 2545 >mobile: +4179 211 0315 > > >
[SQL] PL/pgSQL: recursion?
Saluton, is there any possibility to do recursion in plpgsql-functions? Here is what I tried (I know that I can do it with a simple loop, of course; I am just curious about what one can or cannot do in plpgsql): ,[ test-recursion ] | drop table ttt; | create table ttt | ( id int4, | pid int4 ); | | insert into ttt values (1, NULL); | insert into ttt values (2, NULL); | insert into ttt values (3, 1); | insert into ttt values (4, 1); | insert into ttt values (5, 1); | insert into ttt values (6, 2); | insert into ttt values (7, 2); | | drop function idPath(int4); | create function idPath(int4) returns text as ' | declare | str text; | str2 text; | r record; | begin | select id, pid into r from ttt where id = $1; | str := r.id::text; | if r.pid IS NOT NULL then | str2 := idPath(r.id); | str := str || '':'' || str2; | end if; | return str; | end;' language 'plpgsql'; | ` And when I tried to use it, I got: , | select idPath(5); | pqReadData() -- backend closed the channel unexpectedly. | This probably means the backend terminated abnormally | before or while processing the request. | The connection to the server was lost. Attempting reset: Failed. ` I'm running Postgres 7.0.2. Thanks in advance, Albert. -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
[SQL] Ensuring primary key is referenced at least once upon commit
Saluton, suppose we have PostgreSQL 7.0.2 and two tables, a and b, like: a: id serial primary key ... b: a int4 not null references a(id) ... In general there will be several rows in b with the same b.a, but how can I make sure there is AT LEAST ONE row referencing a given id from table a? It seems obvious to me that this can only be meaningful in a transaction, like: begin insert into a ...; insert into b (a,...) values (curr_val(a_id_seq),...); commit And the check can only be made before committing. My first idea (which was not very good) was to add a table constraint on table a similar to `... foreign key (a) references b(a) initially deferred', because only a foreign key seems to allow checks to be deferred, and I cannot tell from the docs whether a foreign key is actually incompatible with a primary key declaration on the same field. - Of course this does not work, as there is no table b by the time a is created, or vice versa. Looking at the documentation for CREATE TRIGGER, I do not see how to get it to fire only just before commit - I would need a syntax like create trigger ... before commit insert on a execute ..., which is not what is there. Rules - another one of those exotic things I never thought I might actually need - do not seem to provide a solution, either. As long as I know that nobody will mess around with the database directly but only with scripts I provide, I can easily provide the necessary checks etc., so this may be not so much of an issue really. Still, it would be good to know that this works, and I am pretty sure that this must be possible, but I seem to be looking in the wrong direction. Any help would be appreciated. Thanks in advance, Albert. -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
[SQL] Support for arrays in PL/pgSQL
Saluton, does anyone know whether there is any support at all for arrays in PL/pgSQL? Thanks in advance, Albert. -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
RE: [SQL] order by day or month, etc
Hi Leo,
to_char() definitely works on my system (pg 7.02) so I'm not quite sure
whether you're using an older version or whether something else is
wrong. You can find more documentation on the function here:
http://www.postgresql.org/devel-corner/docs/postgres/functions-formattin
g.htm
Whether you can get it working or not, I'm not sure that doing text
formatting and then extracting month and year information from it is all
that good an idea. I think you would be better off using the functions
provided for manipulating dates and times rather than using that sort of
a hack. You might try doing something like this:
For a certain month/year:
SELECT field FROM table WHERE date_part('year', datefield)=1999 AND
date_part('month', datefield)=9;
For a date range:
SELECT field FROM table WHERE datefield BETWEEN '1999-09-01' AND
'1999-09-30';
Hope this helps.
Francis Solomon
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Leo Xavier
> Sent: 03 January 2001 00:22
> To: [EMAIL PROTECTED]
> Subject: [SQL] order by day or month, etc
>
>
> Hello
> first time I post something... good morning everyone!
> short presentation: Leo Xavier, Lisbon - Portugal, 17 years,
> my home-made
> site: www.megabenfica.com
> Sql7, win 2000...
>
> The question:
> How for example do I select all entrys from a certain month
> (of a certain
> year, of course) ?
> Or from a certain day? And how from a certain period, between
> day X and Y,
> i.e?
>
> The solution i found is to create three columns in the table:
> one with the
> day, a second with the month, a thir with the year... but
> this really is a
> little bit unprofessional ...
>
> doing this:
> SELECT to_char(field, 'DD/MM/') AS new_date
>
> as Francis Solomon said, didnt work... "unrecognized function" ...
>
> can anyone help me??
> Leo Xavier
>
>
>
Re: [SQL] PL/pgSQL: recursion?
"Albert REINER" <[EMAIL PROTECTED]> writes: > is there any possibility to do recursion in plpgsql-functions? Recursion works fine ... but an infinite recursion, such as you have here, will quickly overflow the available stack space and cause the backend to crash. You're invoking idPath with the same argument it was passed, no? I changed > | str2 := idPath(r.id); to > | str2 := idPath(r.id-1); and got regression=# select idPath(5); idpath - 5:4:3:2 (1 row) which may or may not be the answer you wanted, but it does demonstrate that a plpgsql function can recurse. regards, tom lane
RE: [SQL] Numeric and money
Hi Michael, >create table tst (id int, amount numeric(9,2)); >insert into tst values (1, 1.10); >insert into tst values (2, 1.00); >insert into tst values (2, 2.00); >select * from tst where amount = 1; -- works >select * from tst where amount = 1.1; -- fails >select * from tst where amount = 1.10; -- fails You could try: SELECT * FROM tst WHERE amount=1.1::numeric; >select amount::varchar from tst; -- fails This is a bit ugly, but it works: SELECT ltrim(to_char(amount, '999D99')) FROM tst; >select amount::money from tst; -- fails I'm not quite sure why you need to do this. 'amount' is already 'numeric(9,2)' which is as close as you get to 'money'. If you want to get the result into a var of type 'Currency' in your VB/VBA code (inside Access), can't you just CCur() the field? >select id || ', ' || id from tst; -- works >select id || ', ' || amount from tst; -- fails Again, a bit ugly, but ... SELECT id || ', ' || ltrim(to_char(amount, '999D99')) FROM tst; > > >From within Access, I can't update any table with a numeric > data type > because of the "select * from tst where amount = 1.1;" > failure. These > limitations have caused me to wonder what other PostgreSQL > users are using > for their money values? Is numeric(9,2) the best choice for > money? I I am using numeric(9,2) for all my "money" values with VB6 and it works fine. I use a wrapper function that I wrote to "fix up" arguments so that postgres plays nicely with them. I tend to manipulate recordset values with VB/VBA's conversion functions after they're returned, like CCur() as mentioned above. I'm willing to share my wrappers if you'd like them. Hope this helps Francis Solomon
[SQL] Numeric and money
Hello Everyone, I am in the process of migrating my Access97 application to PostgreSQL. So far everything looks great with one exception. I converted my currency fields in Access to numeric(9,2) as recommended in the PostgreSQL documentation. Many things to don't play well with the numeric the data type. Here are some examples: create table tst (id int, amount numeric(9,2)); insert into tst values (1, 1.10); insert into tst values (2, 1.00); insert into tst values (2, 2.00); select * from tst where amount = 1; -- works select * from tst where amount = 1.1; -- fails select * from tst where amount = 1.10; -- fails select amount::varchar from tst; -- fails select amount::money from tst; -- fails select id || ', ' || id from tst; -- works select id || ', ' || amount from tst; -- fails >From within Access, I can't update any table with a numeric data type because of the "select * from tst where amount = 1.1;" failure. These limitations have caused me to wonder what other PostgreSQL users are using for their money values? Is numeric(9,2) the best choice for money? I think that adding numeric to text and text to numeric operators will fix most of these issues. I plan to add these operators very soon and thought I would ask if anyone has done this before and could provide me an example or two before I start. Does anyone know of any internal functions that already exist to convert numeric to text so that I don't have to write one? I know that psql successfully does this. Thanks, Michael Davis Database Architect and Senior Software Engineer, Seva Inc. Office: 303-460-7360Fax: 303-460-7362 Mobile: 720-320-6971 Email: [EMAIL PROTECTED]
[GENERAL] Re: [SQL] Numeric and money
[ mail lists trimmed to something a tad more reasonable ] Michael Davis <[EMAIL PROTECTED]> writes: > From within Access, I can't update any table with a numeric data type > because of the "select * from tst where amount = 1.1;" failure. Yeah. The problem here is the conflict between interpreting '1.1' as a "float8" constant vs. interpreting it as a "numeric" constant. In order to fix this we need to settle on a better type promotion hierarchy among the various numeric datatypes. You can find past discussions of the issue in the pghackers archives. I made a proposal on 13-May-2000 that I think was objected to by some people, though I don't recall exactly why. regards, tom lane
Re: [SQL] sql99 / sql3
Ron Peterson <[EMAIL PROTECTED]> writes: > What organization is currently responsible for drafting SQL standards? ISO. > How can I obtain publications describing those standards? Is there > anything comprehensive and definitive online? In the US you can buy ISO standards from ANSI, see http://webstore.ansi.org/ansidocstore/default.asp The documents you want are ISO 9075-nnn. The ANSI price list shows two sets, ISO/IEC 9075-n:1999 and ANSI/ISO/IEC 9075-n-1999. The difference between them is not obvious (except for the huge price differential!?). I've been trying to find out what the difference is, if any, but no luck yet. regards, tom lane
[SQL] postmaster: address already in use
Hi, my postmaster suddenly begins to make problems. It was running fine, but then: FATAL: StreamServerPort: bind() failed: Address already in use Is another postmaster already running on that port? If not, remove socket node (/tmp/.s.PGSQL.5432) and retry. /sys/svr/pgs/bin/postmaster: cannot create UNIX stream port There is no other postmaster running and there is no socket node file in /tmp Any hints?? Thank you, Markus
Re: [SQL] postmaster: address already in use
Markus Wagner <[EMAIL PROTECTED]> writes: > my postmaster suddenly begins to make problems. It was running fine, but > then: > FATAL: StreamServerPort: bind() failed: Address already in use > Is another postmaster already running on that port? > If not, remove socket node (/tmp/.s.PGSQL.5432) and retry. > /sys/svr/pgs/bin/postmaster: cannot create UNIX stream port > There is no other postmaster running and there is no socket node file in > /tmp That's really weird. Is it possible that there are still some old clients holding open connections on that socket address, even though you shut down the old postmaster? For INET sockets, there's a timeout in many kernels, such that the address that had been used for an INET socket is considered "in use" for a few minutes after you close the socket. I've never heard of any similar behavior for Unix sockets, however. Ideas anyone? regards, tom lane
Re: [SQL] Numeric and money
> Date: Wed, 3 Jan 2001 11:11:36 -0700 > From: Michael Davis <[EMAIL PROTECTED]> !> To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, !> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, !> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, !> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > Subject: [SQL] Numeric and money Man, where is limit between spam and question to mailing list?! Karel
Re: Sv: [SQL] how to build this query ??? Please help !!!
> [...] > SELECT a_nr, > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, > [...] > FROM orders > [...] Is this a documented feature? Where can I find more information about this? I do not want to join the actual discussion about documentation of PostgreSQL but I never before have seen such a construct in SQL! Best regards, Jens Hartwig = Jens Hartwig - debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil: +49 (0)170 167-2648 E-Mail : [EMAIL PROTECTED] =
Re: Sv: [SQL] how to build this query ??? Please help !!!
On Thu, 4 Jan 2001, Jens Hartwig wrote: > > SELECT a_nr, > > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, > > [...] > > FROM orders > > Is this a documented feature? Where can I find more information about > this? I do not want to join the actual discussion about documentation > of PostgreSQL but I never before have seen such a construct in SQL! Subqueries are covered in Bruce Momjian's book: http://www.postgresql.org/docs/aw_pgsql_book/node93.html I don't think the PostgreSQL User's Manual mentions sub-selects. -- Tod McQuillin
