Re: [SQL] Comparing two tables of different database

ere is template0 as the default and you add addition databases to the same ‘instance’. If you are talking about 2 different database servers, then I have no idea. Edward W. Rouse How do you formulate the query using dblink? Thanks Wei -- Sent via pgsql-sql mailing list (pgs

Re: [SQL] Performance problem with row count trigger

;t a good way to optimize the COUNT(*)? Thanks Wei

Re: [SQL] How do I optimize this?

FROM D WHERE D.da = 10) as b where tkey = ; Thanks Wei On 03/17/2009 05:43 PM, Wei Weng wrote: Hi all. I have the following scenario: A table T (int t1; int t2; ... int t10; int tkey) A table D (int da; int db), And I have the following query update T set t1 = b.aa FROM (select COUNT(1) as

[SQL] How do I optimize this?

FROM D WHERE D.da = ) as b where tkey = ; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = ) as b where tkey = ; The queries are run on the same . Is there anyway to optimize this??? Thanks Wei -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make

Re: [SQL] Vacation days

On Monday 25 June 2007 15:22, Susan Young wrote: > Hi Wei, > That's OK - Enjoy! > Susan > > Wei Weng wrote: > > Can I take next week off? > > > > Thanks! > > > > Wei hi, susan, a change of plan. :) Instead of the whole week, I just wanted

Re: [SQL] [Fwd: 47G file]

On Friday 22 June 2007 12:23, Alexandre Parizot wrote: > Wei, Can you check the database error messages in the message "Main > Log". You can find the system information in the email "System Info 7.1". > > > > Alex. This is the script that caused the error i

[SQL] Question on interval

Hi all. How do I write a query that converts an interger to the interval type? Like convert integer 10 to INTERVAL '10 seconds'? The integer is a column in a table though, so it is more like convert integer tbl.theInteger to INTERVAL 'tbl.theInteger seconds&qu

[SQL] Search a range of cases/records

, 100+-1,etc for each records. Here I know the price tags counts are 5 for 99.5-100.5, 5 for 99.7-100.7, 6 for 99-101, etc How should I do for all of the records? Thanks in advance. Wei -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (MingW32) Comment: Using GnuPG with Mozilla - http

[SQL] How do I convert an integet to a timestamp?

Hi. I followed the doc and tried "select CAST(1126547334 AS timestamp)" and I only got an error response that says: ERROR: cannot cast type integer to timestamp without time zone. What is the proper way to do the conversion? Thanks Wei ---(end of

[SQL] How do I quit in the middle of a SQL script?

I do that? And is it a good idea to add arbitrary code to the database dump sql script? Thanks! Wei ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

[SQL] How do I do this?

/cache /var as the result of a query? Thanks! (I am using PostgreSQL 7.4) Wei ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Cast NULL into Timestamp?

ys "column "tempdate" is of type timestamp ... but expression is of type text... will need to rewrite or cast the expression". I really need the distinct. Is there anyway to cast this NULL into a timestamp or any other workarounds? Thanks Wei --

Re: [SQL] How do you compare (NULL) and (non-NULL)?

Bruno Wolff III wrote: On Tue, Oct 26, 2004 at 16:23:20 -0400, Wei Weng <[EMAIL PROTECTED]> wrote: In the following query SELECT Parent FROM Channels ORDER BY Parent ASC; If I have a couple of (NULL)s in the field [Parent], they will be listed at the bottom of the query result. Is it b

[SQL] How do you compare (NULL) and (non-NULL)?

get the exact opposite result regarding the order of (NULL)s and (non-NULL) values. They are listed at the very beginning of the query result. Thanks Wei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] LIKE operator and string comparison

Tom Lane wrote: Wei Weng <[EMAIL PROTECTED]> writes: But if I choose to use string comparison, instead of 4 escape characters, I only need 2. Why is that? Backslash is an escape character for LIKE. regards, tom lane What about in regular strings? I do need

[SQL] LIKE operator and string comparison

stgreSQL 7.4, and the SQL code was entered through psql. Thanks! Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] postgreSQL editors

om I would vote for emacs/vim if you only want simple stuff such as color syntax highlighting. :) And with some emacs extension, you probably can do much cooler stuff with it too. Wei ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Anti log in PostgreSQL

(3.3234) as a2144 Gives me 27.754555808589792 But the answer expected is some what near to 2144 The log tables show this Regards, Vishal Kashyap. What is the mathematical operation that "antilog" is supposed to perform? Wei ---(end of

Re: [SQL] testing for null value in integer field?

erator = to compare with NULLs, you use IS. HTH. Wei ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

[SQL] How do I convert an interval into integer?

I want to convert an interval (from substraction between two timestamps) into a integer that represents how many seconds that interval has. How do I do that? I am using postgresql 7.3.1 Thanks Wei ---(end of broadcast)--- TIP 5: Have you

Re: [SQL] [PERFORM] sql performance and cache

default max_fsm... settings > > (although I am not sure what they do). sort_mem is 8192. > > PostgreSQL does not have, and has never had a query cache - so nothing > you do is going to make that second query faster. > > Perhaps you are confusing it with the MySQL query cache? &g

[SQL] suggestion needed for implementation

guments to pass on tsearch2. What can I do here to implement this trigger? Thanks Wei ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

[SQL] function with variable length of parameters

How do I create one, such as the tsearch2 function in tsearch2 module. It takes arbitrary number of parameters. The only difference is that it is writtein in C, instead of PL/pgsql. I looked into the doc, doesn't say much about it. Thanks Wei ---(end of broa

Re: [SQL] Bug in psql - Postgresql 7.3.1?

Shouldn't that be "UPDATE my_table SET field1 = 'new_value', field2 = 'different_value' WHERE my_table_id = 'key';"? Wei On Fri, 3 Oct 2003, John B. Scalia wrote: > All, > > > > I'm not certain if what I'm trying t

Re: [SQL] Capturing pgsql ERRORS/NOTICES to file

George: Have you tried psql {whatever operations} 2> error_output ? (for Bash) Thanks Wei On Fri, 26 Sep 2003, George Weaver wrote: > Hi Josh, > > Thanks for the reply. > > What I am trying to achieve is to have errors go to a file, rather than show > up on the

Re: [SQL] tsearch2 question

nd alter table testtb drop column idxtest tsvector. Where did I do wrong? Thanks Wei ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] tsearch2 question

When I run psql < tsearch2.sql, is psql going to substitute $libdir internally with what $libdir really is (in my case, it would be /usr/lib/pgsql)? Thanks Wei On Fri, 26 Sep 2003, Tom Lane wrote: > Wei Weng <[EMAIL PROTECTED]> writes: > > But then when I do a psql

Re: [SQL] tsearch2 question

ch2': No such file or directory ERROR: current transaction is aborted, queries ignored until end of transaction block ERROR: current transaction is aborted, queries ignored until end of transaction block ... (and the ERROR message repeats many times) Where did I do wrong?? Thanks Wei

[SQL] tsearch2 question

t some special parameters with ./configure (in the root level of the source tree) to replace $libdir with the real library path? Thanks Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Using sql statements in file

try psql -f filename On Mon, 22 Sep 2003, Suresh Basandra wrote: > Hi, > > I would like to do the following using files: > > 1. put create database, create tables sql statements in a file and > execute through prompt > 2. insert or update data that is put in a file > > Please let me know i

Re: [SQL] GiST and full text search

#x27;text' columns in the table? Thanks Wei On Mon, 22 Sep 2003, Richard Huxton wrote: > On Monday 22 September 2003 16:02, Wei Weng wrote: > > Hi all. > > > > I have been doing a little research on how to do full text search under > > postgresql, and GiST seems

[SQL] GiST and full text search

. Thanks Wei ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

[SQL] Extending Datatype

Where can I find a more detailed doc on how to write module for extended datatype? There isn't much about it on techdoc.postgresql.org. (I need to write one for UNIQUEIDENTIFIER.) Thanks! Wei ---(end of broadcast)--- TIP 5: Have you ch

Re: [SQL] What is wrong with this identification configuration?

rong and should be fixed accordingly? Thanks Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] iceberg queries

It is a query that looks like SELECT target1, target2... targetn, SUN(t.qty) FROM Table t GROUP BY target1 HAVING SUM(t.qty)>=10 You can replace SUM(t.qty)>=10 with other aggregate constraints. - Original Message - From: Christoph Haller To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]

[SQL] iceberg queries

Does PostgreSQL optimizer handle iceberg queries well? Thanks Wei ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Which version is this?

Since which version PostgreSQL is able to do Vacuum Analyze even in the middle of a transaction, namely, insert, delete, update?     Thanks     Wei  

Re: [SQL] help: triggers

It would be better if you could provide the source of that trigger and involved table schemas? Wei - Original Message - From: "Tony Simbine" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 29, 2003 8:29 AM Subject: [SQL] help: triggers >

Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql

What about a UNIQUEIDENTIFIER type? Is the only way casting it to a CHAR(38)? (Create a domain for it) And does the performance suffer if I do the Domain/create my own data type tricks? Thanks! Wei - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: &quo

Re: [SQL] Scheduling Events?

Or if you are so paranoid about the stability of crond, you can probably do a check to see whether crond is up when you update the database. If crond is up then update else mail root the error reject the update end This is going to affect the performance dramatically though. - Or

[SQL] Rename database?

Can I use a SQL statement to rename database?   e.g Rename Database Bank to Database Bank_backup   Thanks!   Wei  

[SQL] Can I do this?

I want to select some data out of database A, and insert them into database B. Is it possible to do in one SQL query?   Thanks   Wei  

[SQL] Postgresql Bug List?

Is there a bugzilla kind of thing for Postgresql? I would like to help out on the development, but have no idea where to start... Thanks Wei ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send

[SQL] Postgresql Bug List?

Is there a bugzilla kind of thing for Postgresql? I would like to help out on the development, but have no idea where to start... Thanks Wei ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users

Re: [SQL] [PERFORM] Does this matter?

Thanks, I noticed that sweet addon and will try to integrate it into our system once 7.3 is officially released. :) btw, do we have a release date yet? Thanks Wei At 11:08 PM 11/3/2002 -0500, you wrote: Wei Weng <[EMAIL PROTECTED]> writes: > Since I need to use a GUID as the prima

[SQL] select syntax question

COUNT(ID) FROM test should be valid while SELECT COUNT(DISTINCT ID) FROM test otherwise. while in fact, both are valid. Is there any difference between this two queries? Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broa

Re: [SQL] FIFO Queue Problems

e). > This way you don't need to worry about retrying. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Wei Weng <[EMAIL PROTECTED]

[SQL] How do you write this query?

lect data2 into @out from test where data1 = 'pooh' 2: select data1 from test where data2 = @out and data = 3 What do I do? Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 3: if posting/reading

Re: [SQL] Database Design tool

Josh: Do you have any idea of what the software is called? I could only find the redhat database ISO for download. Thanks On Wed, 2002-10-30 at 18:43, Josh Berkus wrote: > Wei, > > > My colleague installed a redhat database 2(which is based on postgresql) > > onto his redhat

Re: [SQL] Database Design tool

ce with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- > Kevin Old <[EMAIL PROTECTED]> > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] How do I get rid of these messages?

nt to be able to see the *real* sql error messages) Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Is there anyway to do this?

usernames. And do it in a way that is the most portable to MS SQL server. Is there anyway? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command

Re: [SQL] select question

- > I cannot think why the whole bed of the ocean is > not one solid mass of oysters, so prolific they seem. Ah, > I am wandering! Strange how the brain controls the brain! > -- Sherlock Holmes in "The Dying Detective" > > ---(end of broadcast)-

[SQL] IDENT authentication problem

t; was an old user I created for the database *before* I wiped out everything and reinstalled the RPMs. It is already gone from my /etc/password. (since I was using IDENT) What could have gone wrong? I must have left the trace of user foobar somewhere in my system but I couldn't find it. Than

Re: [SQL] concurrent connections is worse than serialization?

On Wed, 2002-08-14 at 10:49, Richard Huxton wrote: > On Wednesday 14 Aug 2002 3:20 pm, Wei Weng wrote: > > On Wed, 2002-08-14 at 05:18, Richard Huxton wrote: > > > On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote: > > [30 connections is much slower than 1 connection

Re: [SQL] concurrent connections is worse than serialization?

On Wed, 2002-08-14 at 05:18, Richard Huxton wrote: > On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote: > > I have a testing program that uses 30 concurrent connections > > (max_connections = 32 in my postgresql.conf) and each does 100 > > insertions to a simple table with inde

[SQL] concurrent connections is worse than serialization?

ne and the extension library that supports postgresql serializes the queries by simply locking when a query manipulates a PGconn object and unlocking when it is done. (And similiarly, it creates a PGconn object on the stack for each concurrent queries.) Thanks -- Wei Weng Network Software Engineer K

Re: [SQL] Is this valid?

On Mon, 2002-08-12 at 23:58, Weiping He wrote: > Wei Weng wrote: > > >I am not sure if this is the right mailing list I talk to. Please let me > >know if I had violated any unwritten rules. :) > > > >I have a global variable PGconn* m_pgconn that is the connection h

[SQL] 答复: [SQL] Is this valid?

I am using C++ and libpq. Thanks Wei -ÓʼþÔ­¼þ- ·¢¼þÈË: Robert Treat [mailto:[EMAIL PROTECTED]] ·¢ËÍʱ¼ä: Monday, August 12, 2002 8:10 PM ÊÕ¼þÈË: Wei Weng ³­ËÍ: [EMAIL PROTECTED] Ö÷Ìâ: Re: [SQL] Is this valid? I'm going to ask the crazy question of what language/interface ar

[SQL] Is this valid?

a thread that does some insertion through this handle/database connection(m_pgconn) and also another thread that do some insertion *on the same table* through this handle(m_pgconn), will that break? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of

[SQL] What about this?

Why can't postmaster run VACUUM ANALYZE automatically every once in a while? Since it is a very useful feature... -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ?

Re: [SQL] Seeking advice regarding a design problem

On Fri, 2002-08-02 at 14:24, Stephan Szabo wrote: > On 2 Aug 2002, Wei Weng wrote: > > > I am running postgresql as database backend, and I have some scripts > > dealing with constant incoming data and then insert these data into the > > database, in a quite complex wa

Re: [SQL] Seeking advice regarding a design problem

Forgot to mention, the version of postgresql I am running is 7.1.3. On Fri, 2002-08-02 at 12:16, Wei Weng wrote: > I am running postgresql as database backend, and I have some scripts > dealing with constant incoming data and then insert these data into the > database, in a quite co

[SQL] Seeking advice regarding a design problem

? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] text vs varchar

On Tue, 2002-06-18 at 18:59, Josh Berkus wrote: > Wei, > > > Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR > > datatype with a maximum length, especially when I do searches on them? > > Yes. You can't index TEXT because it'

[SQL] text vs varchar

Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR datatype with a maximum length, especially when I do searches on them? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 3: if

[SQL] ON DELETE CASCADE question

Does ON DELETE CASCADE attribute you specify in CREATE TABLE statement actually create triggers for every foreign key it refers to? Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 5: Have you checked

[SQL] What is wrong?

turn 1; end ' language 'plpgsql'; And when I ran it as pgsql>select test(); I got: NOTICE: plpgsql: ERROR during compile of test near line 8 ERROR: unterminated string starting on line 10 What is wrong? (something fishy about translate function?) Could

Re: [SQL] is it possible to get the number of rows of a table?

This should be really easy to implement in a function yourself. And I don't think there is already something similar in pgsql. ====== Wei Weng Network Software Engineer KenCast Inc. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behal

Re: [SQL] Which SQL query makes it possible to optain the 3 greatest values of an interger list ?

I would use SELECT id FROM table ORDER BY id LIMIT 0, 3; in order to get the top 3 results. The key is "Limit" keyword. ====== Wei Weng Network Software Engineer KenCast Inc. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Beh

Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)

_ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.post

[SQL] shared memory size

Will increasing kernel shared memory size (in linux by doing "echo 134217728 >/proc/sys/kernel/shmall; echo 134217728 >/proc/sys/kernel/shmmax) help with the speed of a complicated query with a large return set? (average 2 or more entries in return) Thanks -- Wei Weng Netwo

[SQL] Turn off flushing after each write

How can I control that? Where is the setting I can tweak? I checked the doc at http://www.archonet.com/pgdocs/tweak-perf.html. Couldn't find any reference to it. Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broa

[SQL] view and performance

Is there any gain on performance if you choose to create a view over a very complex query instead of running the query itself directly? Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 4: Don't &#x

Re: [SQL] Is function atomic?

traut wrote: > Wei Weng writes: > > > Does that mean if I used > > DECLARE > > ... > > BEGIN > > DO_STUFF > > END; > > > > the DO_STUFF will not be interrupted (maintain atomicity) even when > > multiple threads use the function concurr

Re: [SQL] Is function atomic?

Does that mean if I used DECLARE ... BEGIN DO_STUFF END; the DO_STUFF will not be interrupted (maintain atomicity) even when multiple threads use the function concurrently? On 06 Jul 2001 18:13:37 +0200, Peter Eisentraut wrote: > Wei Weng writes: > > > If it is not, is it possibl

[SQL] Is function atomic?

If it is not, is it possible to acquire a lock on a row ? how about a lock on a table? Thanks a lot! -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] How do I print a message in a function?

as select test(); it broke down with an error message " Unterminated " " Can anyone help me on this? Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Null set

_NULL function in pl/pgsql? -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message ca

Re: [SQL] restoring a dump

INSERT. Does anyone know What do you mean by "dump that uses INSERT"?a > of a way I could undump my db? > -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 3: if posting/reading through Usen

Re: [SQL] Link many attributes in one table to one attribute inanother??

- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl

Re: [SQL] Subquery error. Help please!!

tatement should > work. > ( http://www.postgresql.org/idocs/index.php?queries.html#QUERIES-FROM ) > > Any help would be appreciated. TIA > kakerjak > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] control structure in a transaction block?

7;table_a_id_seq'), 'test'); } COMMIT But as we know, you can't use control structure in a transaction block. And I can't use a function either, because in the real application, there are way too many parameters needed to be passed in order to do the "insert" or "u

[SQL] Is this possible?

I have a table that has a serial for primary key. Is it possible to get the new available primary key right after I insert a row of new entry? Thanks Wei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] simulate union in subselect

I know this is not do-able, but is there any way to simulate the following in Postgresql 7.1? select id from (select id, recv_date as date from table1 union select id, send_date as date from table2) AS subtable order by date; Thanks a lot Wei ---(end of broadcast

[SQL] Sorting and then...

Suppose I have a table create table test ( id integer, name text ); And I want to get the names of the largest 10 "id"s. How can I do that in sql? Thanks! ---(end of broadcast)--- TIP 2: you can get off all lists at once with t