Re: [SQL] Re: psql win32 version
Hi, Christopher Thank you for your help, but the URL http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip is a broken link. Would you please check again. Thank you very much. Jack - Original Message - From: "Christopher Sawtell" <[EMAIL PROTECTED]> Newsgroups: comp.databases.postgresql.sql Sent: Thursday, March 15, 2001 1:19 PM Subject: Re: [SQL] Re: psql win32 version > This one worked a minute ago:- > > http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip > > > On Thu, 15 Mar 2001 14:14, datactrl wrote: > > The URL shown below can not get through. Would you please check again. > > > -- > Sincerely etc., > > NAME Christopher Sawtell > CELL PHONE 021 257 4451 > ICQ UIN45863470 > EMAIL csawtell @ xtra . co . nz > CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz > > -->> Please refrain from using HTML or WORD attachments in e-mails to me > <<-- > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] about raise exception
According to user guide, both Raise Notice & Raise Exception will write message to database log. Which system table is the database log about? By the way what is the difference between Raise Notice & Raise Exception? Jack ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Client Applications come with PostGreSQL V7.1
Is there any Windows version of all Client Applications come with PostGreSQL V7.1? Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] execute client application from PL/pgSql
Is that possible to execute a client application from server site by PL/Pgsql, such as pg_dump? Because my client sites are running Windows OS, or is there any Windows version of all Client Applications come from PostGreSQL V7.1? Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] transaction isolation question
Regarding "Committed Isolation" on PostgreSql Ver 7.1 Users Guide, is that possible a "Dead Lock" happened when two concurrent transactions are waiting each other? And how to avoid or fix it? Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] array variables in pl/pgsql
Is there any sample to show how to use array variable in PL/pgsql? How do I get an array from a table and do a loop to manipulate every element in that array? jack ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] array variable in pl/pgsql
Hi all Is there any sample to show how to use array variable in PL/pgsql? How do I get an array from a table and do a loop to manipulate every element in that array? jack ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL]
Hi, Regarding temp tables in the same connection session. If there is SQL procedure creating temp tables, process temp tables and drop all the temp tables finally. This SQL procedure can't run twice in the same session. Because all the temp tables are referred to the first physical temp tables. I posted this question couple months ago and the final reply is that in the future version, all the sql procedures, which use temp tables, will be forced to re-compile though it's been used in the session. Now, my question is, if it has been done, do I still need to drop all the temp tables before I re-run the same SQL procedure in the same session? Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] the best way to get the topest 3 record in every group
Hi, There is a table like : << itemNo supplier purchaseDate Price Qty << Please provide an idea if I want to get the latest 3 puchase records for each item and supplier. Thank you in advance. jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] the best way to get the topest 3 record in every group
Dima My question is that I want to produce ALL the lastest 3 records for EACH itemNo and supplier. Jack - Original Message - From: "dima" <[EMAIL PROTECTED]> To: "jack" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, September 09, 2002 4:34 PM Subject: Re: [SQL] the best way to get the topest 3 record in every group > > There is a table like : > > << > > itemNo > > supplier > > purchaseDate > > Price > > Qty > > << > > Please provide an idea if I want to get the latest 3 puchase records for > > each item and supplier. Thank you in advance. > select * from table_name where supplier=value order by purchaseDate desc > limit 3 > ??? > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] time limit of a database connection
I use a window client to conncet to a postgreSQL server with windows ODBC. It seems if I haven't done anything on the connection for hours, the connection will be dropped. Is that possible to set the time limit of a connection? Thank you in advance! Jack ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] numeric problems
I'm using pgAdmin II version 1.3.82 , psqlODBC 7.2.3 , postgreSQl 7.2.1. There is a problem when I do an SQL query with a field of numeric, for eample NUMERIC (10.2). Following staement causes an error such as "Unable to indentify an operator '>' for type 'numeric' and 'double precision'..." select itemNo, listprice from itmt_info where listprice > 50.99; And this one works. select itemNo, listprice from itmt_info where listprice > '50.99'; It seems all numeric operators are not available on NUMERIC FIELDS. Is there anyone has done somethings on this problems? Or should I change NUMERIC type to FLOAT type? Please advise, thank you in advance. Jack ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] is numeric relational operator problem fixed in v7.3
Does the following now works in postgreSQL v7.3? Select * from a_table where num1 >10; *** type of num1 is NUMERIC (12,2) Jack ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] A problem about alter table
Hi, all I'm using postgreSQL 7.2.3. The following statement always cuases a parser error, "parse error at or near NOT". Please adivse, thank you in advance. ALTER TABLE _acct_group1 ALTER groupkey SET NOT NULL; Jack ---(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] A problem about alter table
But on postgreSQL 7.2 reference manual, there is a statement for alter table
such as, ALTER TABLE [ ONLY ] table [ * ]
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
Do you mean this one hasn't been implemented?
Jack
- Original Message -
From: "Tomasz Myrta" <[EMAIL PROTECTED]>
To: "jack" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, January 07, 2003 11:31 PM
Subject: Re: [SQL] A problem about alter table
> jack wrote:
>
> > Hi, all
> > I'm using postgreSQL 7.2.3. The following statement always cuases a
parser
> > error, "parse error at or near NOT". Please adivse, thank you in
advance.
> >
> > ALTER TABLE _acct_group1
> > ALTER groupkey SET NOT NULL;
> >
> > Jack
> >
> From Postgres 7.2 documentation:
>
> "In the current implementation of ADD COLUMN, default and NOT NULL
> clauses for the new column are not supported. You can use the SET
> DEFAULT form of ALTER TABLE to set the default later. (You may also want
> to update the already existing rows to the new default value, using
> UPDATE.)"
>
> There is nothing about setting not null fields.
> I think you have to create trigger instead of altering table.
>
> Regards,
> Tomasz Myrta
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] quastions about primary key
Is that possible to have a two columns primary key on a table with null value on second column? Jack ---(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] quastions about primary key
I try use uique instead of primary key. And it works, it allows null values. Is there any other difference between primary key and unique? Jack - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "jack" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, January 25, 2003 1:14 AM Subject: Re: [SQL] quastions about primary key > On Fri, 24 Jan 2003, jack wrote: > > > Is that possible to have a two columns primary key on a table with null > > value on second column? > > No, because primary key implies not null on all columns involved > (technically I think it's that a non-deferrable primary key implies > not null on all columns involved, but we don't support deferrable ones) > > ---(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] window version of psql of postgreSQL v7.3.1
Hi, all Where can I get a window version of psql of postgreSQL v7.3.1? Thank you very much! Jack ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] pl/pgsql how to return multiple values from a function
Stephan , Both of two suggestion work. Thank you very much! Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] About Create Type
Thanks Joe. Yes, that is exactly what I need. Jack ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pl/pgsql how to return multiple values from a function
Thanks Rajesh. It 's very useful reference site. Jack ---(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] the best way to get some records not in another table
Hi, According to the following report, I think using "except" would be the best way to do. Thank you! Jack EXPLAIN SELECT DISTINCT a.c1 FROM test_j2 a WHERE a.c1 NOT IN (SELECT DISTINCT b.c1 FROM test_j1 b); Unique (cost=54544.91..54547.41 rows=50 width=6) -> Sort (cost=54544.91..54546.16 rows=500 width=6) Sort Key: c1 -> Seq Scan on test_j2 a (cost=0.00..54522.50 rows=500 width=6) Filter: (subplan) SubPlan -> Materialize (cost=54.50..54.50 rows=100 width=6) -> Unique (cost=0.00..54.50 rows=100 width=6) -> Index Scan using test_j1_pkey on test_j1 b (cost=0.00..52.00 rows=1000 width=6) (9 rows) === EXPLAIN SELECT a.c1 FROM test_j2 a EXCEPT SELECT b.c1 FROM test_j1 b; SetOp Except (cost=149.66..159.66 rows=200 width=6) -> Sort (cost=149.66..154.66 rows=2000 width=6) Sort Key: c1 -> Append (cost=0.00..40.00 rows=2000 width=6) -> Subquery Scan "*SELECT* 1" (cost=0.00..20.00 rows=1000 width=6) -> Seq Scan on test_j2 a (cost=0.00..20.00 rows=1000 width=6) -> Subquery Scan "*SELECT* 2" (cost=0.00..20.00 rows=1000 width=6) -> Seq Scan on test_j1 b (cost=0.00..20.00 rows=1000 width=6) (8 rows) = EXPLAIN SELECT DISTINCT a.c1 FROM test_j2 a WHERE NOT EXISTS (SELECT b.c1 FROM test_j1 b WHERE b.c1 = a.c1) ; Unique (cost=3455.91..3458.41 rows=50 width=6) -> Sort (cost=3455.91..3457.16 rows=500 width=6) Sort Key: c1 -> Seq Scan on test_j2 a (cost=0.00..3433.50 rows=500 width=6) Filter: (NOT (subplan)) SubPlan -> Index Scan using test_j1_pkey on test_j1 b (cost=0.00..17.07 rows=5 width=6) Index Cond: (c1 = $0) (8 rows) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] EXECUTE problem on schema
Hi, all postgreSQL v7.3.3 I found EXECUTE on pl/pgsql doesn't support schema. When I specify a table such as "public.tablename", it reported "not such a relation object". The only SQL statement I used is "update". Don't know wether other statements have the same problem. Jack ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] PHP LINK module (RPM format) for PostgreSQL V7.3
Hi, All Please advise where I can get the PHP LINK module (RPM format) for PostgreSQL V7.3. Thank you in advance! Jack ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] The problems about temporary tables in V7.3.2
Hi All A view is created with a normal table xx without a schema specified. And before using the view, a temporary table xx , which has the same name as the normal table xx used by the view. In the same connection session, it supposes the view would read the temporary table. But actually it always reads the normal table instead of temporary table. The same thing happens to the table specified with a schema. If a SELECT statement uses a table with a schema specified, the SELECT statement won't read the temporary table when it exists with the same name. I just wonder the schema feature just removes the access priority of the temporary tables. Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [SQL]
Can I be removed from this mailing list? > -Original Message- > From: Yury Don [SMTP:[EMAIL PROTECTED]] > Sent: Monday, October 30, 2000 6:55 AM > To: [EMAIL PROTECTED] > Subject: Re: [SQL] > > Hello Lengyel, > > Once, Monday, October 30, 2000, 5:36:23 PM, you wrote: > > LF> Hello everybody! > LF> I need some help with a simple query. > LF> I've got a problem with getting a maximum value from a very large > table > LF> (1000+ rows): > LF> I have table: > LF> CREATE TABLE TABLE_A > LF> ( > LF> COL1 INT, > LF> COL2 INT, > LF> CONSTRAINT PK PRIMARY KEY (COL1, COL2) > LF> ) > > LF> and when I want to get the maximum value for col1: > LF> SELECT MAX(COL1) FROM TABLE_A WHERE COL2 = 1 > LF> it takes a large amount of time. > LF> I created an index on column COL1, but it doesn't work. > > You need to create index on COL2. > > -- > Best regards, > Yury ICQ 11831432 > mailto:[EMAIL PROTECTED] > >
[SQL] unsubscribe
unsubscribe application/ms-tnef
RE: [SQL] really need help
Hi,
I would like to stop spending my work day deleting these messages. Is there
anybody out there responsible for this? Make it stop! Now!
Thanks
> -Original Message-
> From: Jie Liang [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, November 03, 2000 1:35 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: [SQL] need help urgent
>
> Hi,
>
> Is anybody have experience about core dump?
> We have some scripts have been used for a long time, however, recently,
> they seem to have some problems,
> the message I got:
>
> SSELECT priority from priority where source=lower('questionable')
> Total insert into preunchecked: 1
> Segmentation fault - core dumped
> Segmentation fault - core dumped
>
> This script has been used for a few monthes, no problem.
>
> I checked my pgsql.log, I saw:
>
> Nov 3 00:58:45 wipeout postgres[1638]: query: INSERT INTO
> source(id,source) SELECT id,'questionable' from
> preunchecked WHERE insertdate>= '2000-11
> -03 00:58:45-08'
> Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT version()
> Nov 3 00:58:46 wipeout postgres[1642]: query: begin
> Nov 3 00:58:46 wipeout postgres[1642]: ProcessUtility: begin
> Nov 3 00:58:46 wipeout postgres[1642]: query: set transaction isolation
> level serializable
> Nov 3 00:58:46 wipeout postgres[1642]: ProcessUtility: set transaction
> isolation level serializable
> Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT oid from
> pg_database where datname = 'template1'
> Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT pg_type.oid,
> typowner, typname, typlen, typprtlen, typinput, typoutput, typreceive,
> typsend, typelem, typdeli
> m, typdefault, typrelid, typbyval, usename from pg_type, pg_user where
> typowner = usesysid
> Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT pg_proc.oid,
> proname, prolang, pronargs, prorettype, proretset, proargtypes, prosrc,
> probin, usename from pg_
> proc, pg_user where pg_proc.oid > '17216'::oid and proowner = usesysid
> Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT pg_aggregate.oid,
> aggname, aggtransfn1, aggtransfn2, aggfinalfn, aggtranstype1,
> aggbasetype, aggtranstype2, a
> gginitval1, agginitval2, usename from pg_aggregate, pg_user where
> aggowner = usesysid
>
>
>
> We have no script to open a new session[1642], how this happen? how I
> to prevent this??
>
>
> Thanks!
>
>
> --
> Jie LIANG
>
> Internet Products Inc.
>
> 10350 Science Center Drive
> Suite 100, San Diego, CA 92121
> Office:(858)320-4873
>
> [EMAIL PROTECTED]
> www.ipinc.com
>
>
>
application/ms-tnef
[SQL] Inquiry From Form [pgsql]
Dear Sir/Madam, I would like to find out two things: 1)Do you have a function which is similar to Oracle\'s DECODE. 2) Can you extract day of week (Monday,etc) from yours date functions. Kind Regards, Jack Kiss ---(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] Trigger to identify which column(s) updated
Does anyone know how to write a trigger that would identify which columns have actually changed in an update (and then log them to an archive). I suspect that the function would look something like; CREATE FUNCTION FIND_CHANGED_COLUMNS() RETURNS OPAQUE AS ' BEGIN -- FOR EACH COLUMN IN THE RECORD: -- IF ( NEW COLUMNx <> OLD COLUMNx) -- LOG THE RECORD PRIMARY KEY, COLUMN NAME, OLD VALUE RETURN NEW; END; ' LANGUAGE 'plpgsql'; In other words - How might you parse, in general, old and new records to compare like columns? - How can you find out the primary key of a record? Thanks, Jack ---(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] unsubscribe
[SQL] unsubscribe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] RE : [SQL] Convert text from UTF8 to ASCII
Hi Perhaps you should try to set your db client_encoding to UTF8. How do you know you have 'patiënt' instead of 'patiënt'? I mean i also deals with UTF8 databases and all i store is correctly stored. But i can't check it with command line psql as my shell is configured with iso-8859-1 charset. So with psql i will also see 'patiënt' even if 'patiënt' is correctly stored! Perhaps your problem is as simple as that!? Sebastien. De: pgsql-sql-ow...@postgresql.org de la part de Paul Dam Date: mer. 11/03/2009 16:51 À: Leif B. Kristensen; pgsql-sql@postgresql.org Objet : Re: [SQL] Convert text from UTF8 to ASCII UNICODE. Met vriendelijke groet, Paul Dam Informatieanalist Amyyon Bijsterhuizen 11.58 6546 AS Nijmegen 050 - 311 5686 www.amyyon.nl -Oorspronkelijk bericht- Van: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Namens Leif B. Kristensen Verzonden: woensdag 11 maart 2009 16:46 Aan: pgsql-sql@postgresql.org CC: Paul Dam Onderwerp: Re: [SQL] Convert text from UTF8 to ASCII On Wednesday 11. March 2009, Paul Dam wrote: >Hoi, > > > >I store content of an .txt file in a text column in the database. > >server_encoding is UTF8. > > > >If the .txt file is in ASCII this is correctly stored in the database. > >If the .txt file is in UTF8 this is NOT correctly stored in the > database. > > > >Examples: > >In Dutch the term for patient is 'patiënt'. > >It is stored as 'patiënt'. That looks a lot like UTF-8 to me. What is your client-encoding? -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] error 3122 in ACCESS -
I have a query which uses an expression... dividing "1" by a number field in
a static table.
I changed the expression to divide by a different number field from the same
table. This caused an error.
Error 3122: I tried to execute a query that doesn't include the specific
expression < * > as part of an aggregate function.
The possible cause is listed as: I didn't enter an aggregate function in the
TRANSFORM statement.
OK - The only thing different from this query and the one that worked is
which field is included in the expression. The expression is { NewFieldName:
(1/[FieldName])*([FieldCreatedbyanEarlierExpressioninQuery]) }.
1st - What is a transform statement?
2nd- What is an aggregate function?
3rd - Would I need to change anything is SQL... and what?
4th- Why wouldn't the same expression work with a different field?
Jack Schlobohm
