Re: [SQL] Comparing two tables of different database

On 05/01/2009 11:55 AM, John Zhang wrote: Hi Nicholas, The query is across database query. dblink is needed for that task. Hope it helps, John On Thu, Apr 30, 2009 at 3:07 PM, Edward W. Rouse ero...@comsquared.com mailto:ero...@comsquared.com wrote: Can’t you use this? select

Re: [SQL] Performance problem with row count trigger

On 04/02/2009 03:32 PM, Tom Lane wrote: Tony Cebzanov tony...@andrew.cmu.edu writes: What I want to do is update the assoc_count field in the dataset table to reflect the count of related records in the assoc field. To do so, I added the following trigger:

[SQL] How do I optimize this?

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 aa FROM D WHERE D.da = count) as b where tkey = value; update T set t2 = b.aa FROM (select

Re: [SQL] How do I optimize this?

(1) as aa FROM D WHERE D.da = 10) as b where tkey = value; 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

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 to take next monday and tuesday off. Thanks! Wei

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 in 7.1.0.0. (Fixed already) The script in CVS is

[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. Thanks! Wei

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

Say if I want to add a small snip of code in front of the sql script generated by the pg_dump, to check for something then if the condition doesn't match, the script terminates right away. (Without actually doing the restoring stuff that the following large chunk is supposed to do) Can I do

[SQL] How do I do this?

I have a table with column filepath with contents that look like the following: filepath == /var/log/foo /var/log/bar /var/cache/foo /var/cache/bar /var/foo /var/bar Is there anyway to retrieve the directory information only regarding those filepaths? So that I can get /var/log

[SQL] Cast NULL into Timestamp?

I have a table create table temp ( tempdatetimestamp, tempnamevarchar(10) ); And I tried to insert the following: insert into table temp (tempname, tempdate) select distinct 'tempname', null from some_other_relevant_table; And I got an error that says column

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 because

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

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 because PostgreSQL considers (NULL) as the biggest value? If I run the same query under MSSQL Server 2000, I

[SQL] LIKE operator and string comparison

I used the following SQL code to match '\foo\bar' SELECT text FROM test WHERE text LIKE 'foo%' But if I choose to use string comparison, instead of 4 escape characters, I only need 2. SELECT text FROM test WHERE text = '\\foo\\bar' Why is that? I am using PostgreSQL 7.4, and the SQL code

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 to use

Re: [SQL] postgreSQL editors

Michael Glaesemann wrote: On Dec 30, 2003, at 10:05 AM, beyaRecords - The home Urban music wrote: Hi, can anyone recommend a good editor for postgreSQl wich wil enable me to create functions/stored procedures? I am currently running version 7.4.1 on OS X 10.3 If you're looking for an editor to

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

Geoffrey wrote: How does one check for an unset value in an integer field? I've tried such things as: select . where intnumber = '' select .. where intnumber = ? select . where intnumber = NULL Thanks. It is actually WHERE intnumber IS NULL. You don't use operator = to compare

[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

On Sat, 11 Oct 2003, Christopher Kings-Lynne wrote: I have two very similar queries which I need to execute. They both have exactly the same from / where conditions. When I execute the first, it takes about 16 seconds. The second is executed almost immediately after, it takes 13

[SQL] suggestion needed for implementation

I need to create triggers on a number of tables that have full text search capability. The trigger, basically, needs to call select set_curcfg('default') first to set the locale , then call tsearch2 function on the text columns in the table to update the index. How can I implement a trigger that

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 to do is legal, but if I execute a statement like: UPDATE my_table SET

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 tsearch2.sql, it complains

Re: [SQL] tsearch2 question

When I ran psql testdb untsearch2.sql I got the following error message: psql:untsearch2.sql:15: ERROR: RemoveAggregate: aggregate stat(tsvector) does not exist I didn't really do anything before this. Only dropped the trigger and gist index I created (in order to use tsearch2), and alter

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 screen. Is this possible?

[SQL] tsearch2 question

In my tsearch2.sql there are statements like : --dict interface CREATE FUNCTION lexize(oid, text) returns _text as '$libdir/tsearch2' language 'C' with (isstrict); I don't think $libdir is the real value that we want. Do I need to set some special parameters

[SQL] GiST and full text search

Hi all. I have been doing a little research on how to do full text search under postgresql, and GiST seems to be the way to go. But the documentation on this is pretty lacking and I wonder if there are better sources that describe the details on how to implement full text search with GiST.

Re: [SQL] GiST and full text search

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 to be the way to go. But the documentation on this is pretty lacking

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 if there

[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 checked

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

SNIP The format of the hba.conf file changed between 7.1 and 7.2. It looks like you are using an old one. After the database field, there is now a user field. To get the same effect as before, use 'all' for the user. I installed PostgreSQL rpm on a fresh installed Redhat 7.3. There is no

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 hello, I'm trying to update a

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: Guy Fraser [EMAIL

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. -

[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?

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 primary key, I have

[SQL] How do you write this query?

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 through Usenet, please send

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

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

Re: [SQL] Database Design tool

[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

Re: [SQL] select question

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

[SQL] IDENT authentication problem

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. Thanks -- Wei Weng Network

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 index. It took me

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 30 times] Yeah, but the problem

[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

[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 are you

[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] 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 complex way

[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? http

Re: [SQL] text vs varchar

Berkus I noticed that it is a characteristics of MS SQL Server, but I did successfully create unique index based on a TEXT field in Postgresql 7.2, while that failed in MS SQL Server (7). Or do they behave the same in this aspect? Thanks -- Wei Weng Network Software Engineer KenCast Inc

[SQL] What is wrong?

Hi there. I wrote a simple postgresql sql function as follows: create function test() returns integer as ' begin fixed_path := translate (''/text'', ''\\'', ''/''); raise notice ''fixed_path:'', fixed_path; return 1; end ' language 'plpgsql'; And when I ran it as pgsqlselect test(); I got:

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 Behalf Of Frederick

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 Behalf Of Yoann Sent

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

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

[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 broadcast

[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 Network Software

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 possible to acquire

Re: [SQL] Is function atomic?

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 concurrently? Interruption, atomicity, and concurrency are separate issues. The function

[SQL] Null set

/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 can get through

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

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]

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

. ( 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

[SQL] control structure in a transaction block?

'); } 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 update. Is there any tricks I can play here? Thanks. -- Wei Weng

[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

[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