Re: [GENERAL] how to do this select?

only 2 rows. if lesse than 2, I want get all the result of them. ps: I' think, the *2* in my example is not appropriate, how about 10, 50? thanks. On Thu, 2009-02-19 at 08:33 +0100, A. Kretschmer wrote: > In response to Yi Zhao : > > thanks Ringer. > > my mean is that: > > I

Re: [GENERAL] how to do this select?

thanks Ringer. my mean is that: I want less than 2 rows which have the same value of column "b"! for example, there is 3 columns have the same value "A", X | A X | A Y | A I want my result have two of them. thanks. On Thu, 2009-02-19 at 15:49 +0900, Craig Ringer wr

[GENERAL] how to do this select?

hi, all, I have a table in postgresql which have 2 columns like this: a|b -- X A X A Y A D B H B E B D B P C when I do select and order by, I got this: a | b ---+--- X | A X | A Y | A D | B H | B | B D | B P | C I want to get the only 2 rows(limit or random) if the column "b" have

[GENERAL] how can I returns a set of integer in a plpgsql function?

hi, all: there is a function in postgresql contrib "int_arrgreagte": CREATE OR REPLACE FUNCTION int_array_enum(int4[]) RETURNS setof integer AS '$libdir/int_aggregate','int_enum' LANGUAGE C IMMUTABLE STRICT; I can use this function like this: chry=# SELECT int_array_enum('{1,2}'); or chry=# SELE

Re: [GENERAL] can someone help me to make a sql more pretty and more concise?

thanks for ur help, but, I think that is not my want:D if you use sum like this, it must be with group by, what I want is do sum on all columns, not group by. thanks regards, 在 2008-11-13四的 19:27 +1100，Russell Smith写道： > Yi Zhao wrote: > > I want to select some column(a, b) from

[GENERAL] can someone help me to make a sql more pretty and more concise?

I want to select some column(a, b) from the table with the specified condition, so, i can do like this: select a, b from mytable where id = (select id from temptable where tname = 'df' ) and stype = 'def' and range = 'afk' but, I want the result contains a sum(c) and a count value extra, so,

[GENERAL] how to remove the duplicate records from a table

I have a table contains some duplicate records, and this table create without oids, for example: id | temp_id +- 10 | 1 10 | 1 10 | 1 20 | 4 20 | 4 30 | 5 30 | 5 I want get the duplicated records removed and only one is reserved, so th

Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.

yes, thanks u very much, it's work:) regards, Yi On Fri, 2008-09-19 at 11:06 +0200, Andreas Kretschmer wrote: > Yi Zhao <[EMAIL PROTECTED]> schrieb: > > > hi all: > > I have a table with columns(>2) named "query", "pop", "dfk". > &

Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.

| lk 30 | abc | 1 btw: *distinct on* is useful:) thanks, On Fri, 2008-09-19 at 09:00 -0700, Lennin Caro wrote: > > > --- On Fri, 9/19/08, Yi Zhao <[EMAIL PROTECTED]> wrote: > > > From: Yi Zhao <[EMAIL PROTECTED]> > > Subject: [GENERAL] how to return

Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.

now, I do it like this(plpgsql) --- this methold have low efficiency, when the records is large, it will become slow, so someone can tell me some high efficiency way??? thanks. On Fri, 2008-09-19 at 16:51 +0800, Yi Zhao wrote: > hi all: > I have a table with columns(>2) nam

[GENERAL] how to return the first record from the sorted records which may have duplicated value.

hi all: I have a table with columns(>2) named "query", "pop", "dfk". what I want is: when I do some select, if the column "query" in result records have duplicate value, I only want the record which have the maximum value of the "pop". for example, the content of table: query pop dfk ---

[GENERAL] why my delete command blocked....

hi, all: I use a plproxy to execute sql like below: CREATE OR REPLACE FUNCTION public.dmlexec(query text,op text) RETURNS setof integer AS $$ CLUSTER 'testcluster'; RUN ON all; $$ LANGUAGE plproxy; create or replace function public.dmlExec(query text, op text) returns integer as $

[GENERAL] missing chunk number 0 for toast value when pg_dump??

hi, all: I use pg_dump command to dump a specified table: pg_dump mydb -p -Uyahoo -t mytable -D -a > /export/dump.sql but, I got this: --- pg_dump: SQL command failed pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 14319826 pg_dump: The

[GENERAL] why so many error when I load the data to database from a script which generated by pg_dump.

hi, all: I have a database to store the information about the html source of the web page. I wan't to move the data to another database, so I pg_dump the data to a file: /usr/local/pgsql/bin/pg_dump htmldb -Upostgres -p 5433 > /tmp/dump.sql now, I load the data into new database: /u

Re: [GENERAL] why can't I load pgxml.sql

my version is 8.3.3: I found that it's part of the server. is it said that I should build postgresql with libxml again?? On Mon, 2008-07-28 at 23:00 -0400, Tom Lane wrote: > Yi Zhao <[EMAIL PROTECTED]> writes: > > 1. ./configure --with-libxml --with-libxslt > > 2. make

[GENERAL] why can't I load pgxml.sql

1. ./configure --with-libxml --with-libxslt 2. make&&make install (successful) 3. test=# \i /usr/local/pgsql/share/contrib/pgxml.sql error appearance: SET psql:/usr/local/pgsql/share/contrib/pgxml.sql:10: ERROR: could not load library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2: undefi

Re: [GENERAL] why my postgresql auto crashed???

yes, I thinks it is unrelated with index, beacause the problem is still exist after reindex. I try to change the work memory or shared memory, it's no use:( regards, Yi On Mon, 2008-07-28 at 11:10 -0400, Tom Lane wrote: > Craig Ringer <[EMAIL PROTECTED]> writes: > > Y

Re: [GENERAL] why my postgresql auto crashed???

I'm sorry for the lack of information given in this mail; Postgresql: 8.3.3 System:Linux 2.6.9-55.ELsmp Install: I compile it myself Others: the same command, it works fine on another machine(called A):( I EXPLAIN the sql machine A and machine B, I found that on machine B, it never use index when

[GENERAL] why my postgresql auto crashed???

hi, all when I do the command from a sql file by psql client, I got the message: psql:/home/zhay/insert.sql:8: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:/home/zhay/insert.sql:8:

Re: [GENERAL] how to remove the duplicate elements from an array?

$1,1), array_upper($1,1)) g(i)); > $$ language sql strict immutable; > > postgres=# select uniq(array[1,2,3,1,2,3,5,2,2]); >uniq > --- > {1,2,3,5} > (1 row) > > regards > Pavel Stehule > > 2008/7/26 Yi Zhao <[EMAIL PROTECTED]>: > > hi all: > > i

[GENERAL] how to remove the duplicate elements from an array?

hi all: if I create an array '{44,55,66,c4,55,66,b4,55,66}', how to remove the duplicate elements(55, 66) from it, after remove, the array will be '{44,55,66,c4,b4}' thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.

Re: [GENERAL] how to found a variable is in a aggregation or not?

al in ('hello', 'world','test'); > end; > $$ language plpgsql immutable strict; > > Pavel > > 2008/7/15 Yi Zhao <[EMAIL PROTECTED]>: > > I want to check a variable is in a aggregattion or not, so I create a > > function as below: > >

Re: [GENERAL] how to found a variable is in a aggregation or not?

x27;hello'); > anytest > - > t > (1 row) > > Time: 42,034 ms > postgres=# select anytest('helloa'); > anytest > - > f > (1 row) > > Time: 0,468 ms > postgres=# > > you have to use quoting only together dynamic sql,

[GENERAL] how to found a variable is in a aggregation or not?

I want to check a variable is in a aggregattion or not, so I create a function as below: create or replace function anytest(val text) returns boolean as $$ begin perform 1 where quote_literal(val) in ('hello', 'world', 'test'); if not found then return false;