[SQL] Update value to "the first character is capital and the rest is small"

[Emi Lu]

Hello all,

Does anyone have available plpgsql codes to update all capital letters 
in a column to "the first character is capital and the rest is small" ?


For example, in tableA(id, description)
001,  'ZHANG ZHE XIN'  =>
'Zhang Zhe Xin'
002,  'LIU, WEI-HUAI'  
=>'Liu, Wei-Huai'
003,  'GU & WEI. NAN (CE SHI) & TOMMORROW'  =>   'Gu & Wei. Nan (Ce 
Shi) & Tommorrow'


Thanks a lot!
Ying


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] [GENERAL] Update value to "the first character is capital and

[Emi Lu]

I got the answer from the docs. |initcap|(text)

thanks anyway,
Ying


Hello all,

Does anyone have available plpgsql codes to update all capital letters 
in a column to "the first character is capital and the rest is small" ?


For example, in tableA(id, description)
001,  'ZHANG ZHE XIN'  
=>'Zhang Zhe Xin'
002,  'LIU, WEI-HUAI'  
=>'Liu, Wei-Huai'
003,  'GU & WEI. NAN (CE SHI) & TOMMORROW'  =>   'Gu & Wei. Nan 
(Ce Shi) & Tommorrow'


Thanks a lot!
Ying


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] [SQL] Update value to "the first character is capital

[Emi Lu]

I saw it from the docs as well. Thank you Alvaro :)



Alvaro Herrera wrote:


Emi Lu wrote:
 


Hello all,

Does anyone have available plpgsql codes to update all capital letters 
in a column to "the first character is capital and the rest is small" ?
   



I don't know about plpgsql codes, but there is a function initcap() that
you can use for that.

alvherre=# select initcap('GU & WEI. NAN (CE SHI) & TOMMORROW');
 initcap   


Gu & Wei. Nan (Ce Shi) & Tommorrow
(1 row)

 




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[SQL] how to get the size of array?

[Emi Lu]

Hello,

Is there a way that I can get the size of one array ?

For example, create table test (id varchar[]);
insert into test values('{}');
insert into test values('{1, 2, 3}');

I am looking for something like :
  select sizeOf(id) as size from test;


so that I can get results like:
size
---
0
3

Thanks a lot,
Ying


---(end of broadcast)---
TIP 1: 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] About how to use "exception when ??? then "

[Emi Lu]

Hello,

I am using PostgreSQL 8.0.1.

In a function, I try to use exception to catch sql errors:

begin

   begin
  exception
 WHEN  ???   THEN
   end;
...
end;

The place where I have ???, what I should put there please?

e.g.,
1. WHEN sqlcode = '02000' THEN
2. WHEN no_data then
3.  other ways?

From the 8.0 docs, I am not be able to find Constant values of all 
error codes.

http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html

Please enlighten me.


Thanks,
Ying

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] About how to use "exception when ??? then "

[Emi Lu]

Hi Pedro,


|>  The place where I have ???, what I should put there please?
|>
|>  e.g.,
|>  1. WHEN sqlcode = '02000' THEN
|>  2. WHEN no_data then
|>  3.  other ways?
|>
|>   From the 8.0 docs, I am not be able to find Constant values of all
|>  error codes.
|>  http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html
|>
|>  Please enlighten me.

Maybe this link will help you better, as it has the Constants:

http://developer.postgresql.org/docs/postgres/errcodes-appendix.html

Then, some logic real life examples would be something like:

(snip)
EXCEPTION
   WHEN NOT_NULL_VIOLATION THEN
  RAISE WARNING 'Not null...';
   WHEN OTHERS THEN
  RAISE NOTICE 'H [%,%]', SQLSTATE, SQLERRM;

or

 

I am using postgresql 8.0.1. The keyword "SQLSTATE" & "SQLERRM" did not 
work for me. But, I think I do need the two outputs "sql error code", 
and "sql error code statement".


Errors I got are:
syntax error at or near "SQLSTATE" at character 2613
LINE 58:RAISE NOTICE 'H [%,%]', SQLSTATE, SQL...


Should I install any patches or do anything elese to have SQLSTATE and 
SQLERRM work for me?


Ying




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] About how to use "exception when ??? then "

[Emi Lu]

 From the page:
"The PL/pgSQL condition name for each error code is the same as the 
phrase shown in the table, with underscores substituted for spaces. 
For example, code 22012, DIVISION BY ZERO, has condition name 
DIVISION_BY_ZERO. Condition names can be written in either upper or 
lower case.



(Note that PL/pgSQL does not recognize warning, as opposed to error, 
condition names; those are classes 00, 01, and 02.)"



That means pl/pgsql will not recognize error codes under classes 00, 01, 
02.


Is there a way, I can output error code?
exception
 when ... then
 when others then
  raise notice '%, %', SQLSTATE, SQLERRM;

But it seems that SQLERRM and SQLSTATE did not work for me. By the way, 
I am using postgresql 8.0.1.


Thanks a lot,
Ying







---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] About how to use "exception when ??? then "

[Emi Lu]

On Thursday 16 March 2006 19:32, Emi Lu wrote:
|>  Errors I got are:
|>  syntax error at or near "SQLSTATE" at character 2613
|>  LINE 58:RAISE NOTICE 'H [%,%]', SQLSTATE, SQL...
|>
|>
|>  Should I install any patches or do anything elese to have SQLSTATE and
|>  SQLERRM work for me?


I'm so sorry, i had a terrible day and in the rush to reply to you, i forgot 
to mention the patch i applied some time ago.


The patch and thread talking about it can be found here:

http://archives.postgresql.org/pgsql-patches/2005-04/msg00123.php

This was what i used in my 8.0.6, and it worked fine ever since. :)
 

Thanks a lot Pedro. Could you help me how to apply this patch such as 
the steps to load the patch please?


By the way, I am using postgresql 8.0.1. I think the patch will work for 
all 8.0.x version, right?


Thanks again,
Ying




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] in PlPgSQL function, how to use variable in a "select ... into .. where " query

[Emi Lu]

Hello,

In pl/pgsql (postgresql 8.01), how to use variables in select .. into .. 
command


CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
  var1   ALIAS FOR $1;  
  cm_tableName   tableA.col1%TYPE; 
  T1  VARCHAR := 'sourceTable';

  query_value   VARCHAR ;
BEGIN

  SELECT col2 INTO cm_tableName FROM  T1  WHERE col1 = var1 ;
  EXECUTE query_value;

 
  RETURN cm_tableName;

END;
$$ language 'plpgsql' IMMUTABLE STRICT;

select test('abc');

Failed.


Also, tried "SELECT col2 INTO cm_tableName FROM ||  T1  WHERE col1 =  || 
var1 " and

"SELECT col2 INTO cm_tableName FROM ||  T1 ||  WHERE col1 =  || var1"

Failed as well.

T1 and var1 both are variables, may I how to use variables in a "select 
... into " query please?


Thanks a lot,
Ying





---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] in PlPgSQL function, how to use variable in a "select ...

[Emi Lu]

Does not work either, the whole function is:

create table t1(col1 varchar(3), col2 varchar(100));
insert into t1 values('001', 'Result 1');
insert into t1 values('002', 'Result 2');
insert into t1 values('003', 'Result 3');

CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
  col1_valueALIAS FOR $1;  
  cm_tableName   st1_legend.code_map_tablename%TYPE; 
  lengendTableNameVARCHAR := 't1';

  query_valueVARCHAR ;
BEGIN

  SELECT INTO cm_tableName col2 FROM lengendTableName WHERE col1 = 
col1_value ;

  EXECUTE query_value;

  RETURN cm_tableName;
END;
$$ language 'plpgsql' IMMUTABLE STRICT;

select test('001');
Error:
ERROR:  syntax error at or near "$1" at character 20
QUERY:  SELECT  col2 FROM  $1  WHERE col1 =  $2
CONTEXT:  PL/pgSQL function "test" line 8 at select into variables
LINE 1: SELECT  col2 FROM  $1  WHERE col1 =  $2

Does it mean I have to use the cursor ?

Thanks,
Ying



I think it is SELECT INTO cm_tableName col2 FROM ... WHERE ...

2006/3/17, Emi Lu <[EMAIL PROTECTED]>:
 


Hello,

In pl/pgsql (postgresql 8.01), how to use variables in select .. into ..
command

CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
  var1   ALIAS FOR $1;
  cm_tableName   tableA.col1%TYPE;
  T1  VARCHAR := 'sourceTable';
  query_value   VARCHAR ;
BEGIN

  SELECT col2 INTO cm_tableName FROM  T1  WHERE col1 = var1 ;
  EXECUTE query_value;


  RETURN cm_tableName;
END;
$$ language 'plpgsql' IMMUTABLE STRICT;

select test('abc');

Failed.


Also, tried "SELECT col2 INTO cm_tableName FROM ||  T1  WHERE col1 =  ||
var1 " and
"SELECT col2 INTO cm_tableName FROM ||  T1 ||  WHERE col1 =  || var1"

Failed as well.

T1 and var1 both are variables, may I how to use variables in a "select
... into " query please?

Thanks a lot,
Ying





---(end of broadcast)---
TIP 6: explain analyze is your friend

   




---(end of broadcast)---
TIP 1: 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] in PlPgSQL function, how to use variable in a "select ...

[Emi Lu]

Does not work either, the whole function is:

create table t1(col1 varchar(3), col2 varchar(100));
insert into t1 values('001', 'Result 1');
insert into t1 values('002', 'Result 2');
insert into t1 values('003', 'Result 3');

CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
  col1_valueALIAS FOR $1;
cm_tableName   st1_legend.code_map_tablename%TYPE;   
lengendTableNameVARCHAR := 't1';

  query_valueVARCHAR ;
BEGIN

  SELECT INTO cm_tableName col2 FROM lengendTableName WHERE col1 = 
col1_value ;
   



This can't work, read the docu:
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

You should build a string with your SQL and EXECUTE this string.
 

Thank you Andreas. Unfortunately it did not work. maybe I made something 
wrong?


drop table t1;
create table t1(col1 varchar(3), col2 varchar(100));
insert into t1 values('001', 'Result 1');
insert into t1 values('002', 'Result 2');
insert into t1 values('003', 'Result 3');

CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
 col1_valueALIAS FOR $1;   
 cm_tableName   st1_legend.code_map_tablename%TYPE;  
 lengendTableNameVARCHAR := 't1';

 query_valueVARCHAR ;
BEGIN
 query_value := 'SELECT col2 FROM lengendTableName WHERE col1 = \'' || 
col1_value || '\'';


 EXECUTE query_value INTO cm_tableName;

 RETURN cm_tableName;
END;
$$ language 'plpgsql' IMMUTABLE STRICT;
select test('001');

Error:
ERROR:  syntax error at or near "$2" at character 20
QUERY:  SELECT   $1  INTO  $2
CONTEXT:  PL/pgSQL function "test" line 9 at execute statement
LINE 1: SELECT   $1  INTO  $2


I am using postgresql 8.0.1, and I am afraid that 8.0 does not support 
"excecute ... into " 


http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html

I will try to use cursor.

Thank you very much for all your help anyway.
Ying





---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[SQL] Getting more information about errorcodes such as when these error1 happen

[Emi Lu]

Good morning,

In my plpgsql functions I use "exception when..." to catch possible 
exceptions of my data. I found postgresql error code track functions are 
very helpful.  In my functions, I will not catch all error codes, but 
only the ones applying to my data operation .


I found errorcodes info here:
http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html

But I am afraid that I could not imagine when and under what possible 
circumstances some errorcodes may happen just by their names such as : 
locator_exception, invalid_grantor,  active_sql_transaction , and so on.


I tried to search the online docs in order to get more info such as when 
will errorcode X happens. But I could not find it.


Could someone tell me some links that I can find more information about 
these errorcodes please?


Thanks alot,
Ying

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: R: Re: R: R: Re: [SQL] schema inspection

[Emi Lu]

If it is for multiple columns' foreign key constraint.

Try this query:

SELECT DISTINCT n.nspname AS from_schema_name, c.relname AS 
from_table_name, toSchemaName.nspname AS to_schema_name, toTable.relname 
as to_table_name,

fk_col.attname
FROM pg_catalog.pg_class AS c
LEFT JOIN pg_namespace AS n ON (n.oid = c.relnamespace)
INNER JOIN pg_catalog.pg_constraint AS rel ON (c.oid=rel.conrelid)
LEFT JOIN pg_catalog.pg_class AS toTable ON (toTable.oid = rel.confrelid)
LEFT JOIN pg_namespace AS toSchemaName ON (toSchemaName.oid = 
toTable.relnamespace)
LEFT JOIN pg_catalog.pg_attribute AS fk_col ON fk_col.attrelid = 
rel.conrelid AND (position(fk_col.attnum in array_to_string(conkey, ' 
')) <>0 )

WHERE rel.contype='f'
ORDER BY from_schema_name, from_table_name;


Ying



O [EMAIL PROTECTED] έγραψε στις Mar 17, 2006 :

 

SELECT c1.relname,c2.relname from pg_constraint cons,pg_class c1, 
 


pg_class
   


c2 where cons.conrelid=c1.oid and cons.confrelid = c2.oid;

for column(s) names you will have to do extra homework.
 


Thanks!   I have obtained my query! Here is:

SELECT 
 (SELECT relname FROM pg_catalog.pg_class WHERE oid=conrelid) AS 
fromTbl,
 (SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS 
toTbl,
 (SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid 
AND conkey[1]=attnum) AS viaCol

FROM pg_catalog.pg_constraint AS rel WHERE contype='f';
   



Well thats it if you use only *single column* Foreign keys.
In the general case the above will need extra work.

Of course you will also have to ensure that the constraint is indeed
a FK constraint, that the column is not droped, etc
which leads to the answer that enabling statement logging,
and then \d and watching the log is a very good friend too.

 


TIA
Roberto Colmegna




Tiscali ADSL 4 Mega Flat
Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 � al 
mese!
Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. 
http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14


   



 




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Find min and max values across two columns?

[Emi Lu]

Hello,

I tried "select greatest(max(a), max(b)) from public.test",  but I got 
the following errors:


ERROR:  function greatest(integer, integer) does not exist
HINT:  No function matches the given name and argument types. You may 
need to add explicit type casts.


May I know where I can read the docs about greatest & least please.

I am using  PostgreSQL 8.0.7.

Thanks,
Ying




Amos Hayes <[EMAIL PROTECTED]> writes:
 

I'm trying to build a query that among other things, returns the  
minimum and maximum values contained in either of two columns.
   



I think you might be looking for

select greatest(max(columnA), max(columnB)) from tab;
select least(min(columnA), min(columnB)) from tab;

greatest/least are relatively new but you can roll your own in
older PG releases.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match
 




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Find min and max values across two columns?

[Emi Lu]

Thank you Tom.


Emi Lu <[EMAIL PROTECTED]> writes:
 


ERROR:  function greatest(integer, integer) does not exist
HINT:  No function matches the given name and argument types. You may 
need to add explicit type casts.
   



We added greatest/least in 8.1, but before that you can just use a
CASE expression instead, along the lines of
case when x>y then x else y end

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend
 




---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] Add column and specify the column position in a table

[Emi Lu]

Hello,

I am trying to insert one column to a specific position in a table.

In mysql, I can do:
. create table test(id varchar(3), name varchar(12));
. alter table test add column givename varchar(12) after id;


I am looking for similar things in postgresql to add a new column to the 
correct position in a table.


Could someone hint me please.

Thanks alot!
Ying Lu



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Like with special character

[Emi Lu]

I'm using postGre with tables which contain French character 
(?...). Is there a fonction which performs a like in replacing ? 
(e cute) by e ?


to_ascii() should helps you


I got the following error:

select to_ascii('ê');
ERROR:  encoding conversion from UNICODE to ASCII not supported

Some comments about it.



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[SQL] Request new version to support "on commit drop" for create temp table ... as select ?

[Emi Lu]

Good morning,

I googled to find that "on commit drop" does not support:

(a) create temp table as select * from table1 where 1<>2;
http://archives.postgresql.org/pgsql-sql/2005-09/msg00153.php

If table1 has complex table structure, grammar(a) will save lots of 
codes - col1 varchar(1), col2, . colN


I just wonder would the new version support "on commit drop" for select?


Thanks a lot!
--
Lu Ying

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Request new version to support "on commit drop" for create temp table ... as select ?

[Emi Lu]

Got it. Thank you Tom!


Scott Marlowe  writes:

That's what I thought, but



create temp table xyz as select * from abc on commit drop;



still fails on 8.3.  Was this fixed in 8.4 or is my syntax wonky?


Your syntax is wonky -- switch the clause order.
http://www.postgresql.org/docs/8.3/static/sql-createtableas.html

regards, tom lane




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Show CAS, USD first; the left ordered by currency name

[Emi Lu]

Good morning,

I have a currency table (code, description).

Example values:
 ADF | Andorran Franc
 ... ...
 ANG | NL Antillian Guilder
 AON | Angolan New Kwanza
 AUD | Australian Dollar
 AWG | Aruban Florin
 BBD | Barbados Dollar
 USD | US Dollar
 CAD | Canadian Dollar

Is there a way I can query to display USD AND CAD first, while other 
rows are ordered by Code.


For example,

CAS | Canadian Dollar
USD | US Dollar
ADF | Andorran Franc
...


Thanks a lot!
--
Lu Ying



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Show CAS, USD first; the left ordered by currency name

[Emi Lu]

"order by code not in ('USD', 'EUR', 'CAD') , code" is exactly what I 
was looking for!


Good to know how "order by not in" works and thank you very much for all 
your inputs!


--
Lu Ying




...order by currency not in('USD', 'AND', 'CAD');

this condition will be avaluated as FALSE for USD, AND and CAD, and as 
TRUE for all other currencies. When the records are sorted the "false" 
are placed on the top because false

On Thu, Jul 30, 2009 at 10:51 PM, Emi Lu <mailto:em...@encs.concordia.ca>> wrote:


Good morning,

I have a currency table (code, description).

Example values:
 ADF | Andorran Franc
 ... ...
 ANG | NL Antillian Guilder
 AON | Angolan New Kwanza
 AUD | Australian Dollar
 AWG | Aruban Florin
 BBD | Barbados Dollar
 USD | US Dollar
 CAD | Canadian Dollar

Is there a way I can query to display USD AND CAD first, while other
rows are ordered by Code.

For example,

CAS | Canadian Dollar
USD | US Dollar
ADF | Andorran Franc
...


Thanks a lot!
--
Lu Ying



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org

<mailto: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] Fuzzy match under PostgreSQL 8.0.15

[Emi Lu]

Good afternoon,

Under PostgreSQL 8.0.15, what is the good function to do the Fuzzy match 
between two strings?


For example,
(1) 'abcddd' vs. 'abc'
probably returns 3

(2) 'abcddd' vs. 'ab'
probably returns 4

I am looking for functions that could compare how two strings are 
similar to each other.


thanks a lot!

--
Lu Ying

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] force view column varchar(32) to varchar(128)

[Emi Lu]

Hello,

Is there a way to force the view column change from varhcar(32) to 
varchar(128)?


Example:
===
v1 (id varchar(32) ... )

There are more than 1000 other views depend on v1.

Instead of recreating all other 1000 views, is there a way postgresql 
8.3 can do/accept:


create or replace v1 AS

select id::varchar(128), ..

Thanks a lot!
--
Lu Ying


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] how to get row number in select query

[Emi Lu]

Good morning,

For postgresql 8.3, what is the system method/key word to get row number 
please?


E.g.,

==
lname1 gname1
lname2 gname2
lname3 gname3
..

I'd like to get

1  lname1   gname1
2  lname2   gname2
3  lname3   gname3

... ...

Something like
select row_number?, lname, gname from Table1;

Thanks a lot!


--
Lu Ying

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] how to get row number in select query

[Emi Lu]

Hi Oliveiros,


If it is to order in ascendent fashion by, say, lname,
one possibility would be

SELECT COUNT(b.*) as row_number, a.lname,a.gname
FROM "Table1" a, "Table2" b
WHERE a.lname >= b.lname
GROUP BY a.lname,a.gname
ORDER BY row_number

If you want to order by gname just change the WHERE clause accordingly

N.B. : This works as long as there is no repetition on the column you
use to order.
If there is, we'll need a way to tie break. What is your specific case?

Also, note that this method is time consuming, and would work only for
relatively small tables.
AFAIK, version 8.3 doesn't have any "non-standard SQL" way to get a row
number, but it is possible that something like that has been introduced
in later versions...


Thank you for the answer. I see psql8.4 has the method. I am not 
interest in ordering any columns, but just a line num.


I'd like to get it from DB since displaytag _rowNum does not display row 
num correctly: 1, 10, 11, 12 2, 3, 4, 5, 6...


Thanks,
--
Lu Ying

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Get days between two dates?

[Emi Lu]

Good morning,

Is there an existing method to get days between two dates?

For example,

select '2010-01-01'::date - '1999-10-12'::date

Returns how many days.

Thank you,

--
Lu Ying

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Get days between two dates?

[Emi Lu]

On 01/31/2011 11:33 AM, Oliveiros d'Azevedo Cristina wrote:

Doesn't the SELECT you indicated do what you need?


Ok, I figured out:

age() + date_part(...) would be able to get it:

select date_part('day', age(date1::timestamp, date2::timestamp) )

--
Lu Ying





- Original Message - From: "Emi Lu" 
To: 
Sent: Monday, January 31, 2011 3:50 PM
Subject: [SQL] Get days between two dates?



Good morning,

Is there an existing method to get days between two dates?

For example,

select '2010-01-01'::date - '1999-10-12'::date

Returns how many days.

Thank you,

--
Lu Ying

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] how to get row number in select query

[Emi Lu]

Ok, before 8.4, there I can use row_number().

For 8.3 + display tag + order by integer + paging based on pageBean

ArrayList alist;

In Bean.java, added:

private int rec_num;

in main .action java:

for(int i=0 ; i
Piotr Czekalski, 27.01.2011 16:21:

Gentelmen,

I follow this thread and I don't exactly get an idea of yours, but
isn't is as simple as (example: table "web.files" contains one column
named "fileurl" ):

select row_number() over(), X.fileurl from (select fileurl from
web.files order by fileurl) X

The only disadvantage is that if you do want to order resultset you
have to use "select from select" as numbers are added before order
which may cause some performance troubles.



You can get the row_number() without using the sub-select and without
ordering the whole result as you can specify the order in the over()
clause:

select fileurl
row_number() over (order by fileurl)
from web.files

Regards
Thomas





--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1

[Emi Lu]

Good afternoon,

Is there a method to retrieve the following results:

T1(c1 int, c2 varchar(128) )
-


(1, val1);
(1, val2);
(1, val3);
(2, val1);
(3, val5);
(3, val6);

select c1, method(c2)
group by c1

returns:

1,   "val1, val2, val3"
2,   "val1"
3,   "val5, val6"


Thanks a lot!

--
Lu Ying

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1

[Emi Lu]

On 02/08/2011 02:51 PM, Rolando Edwards wrote:

SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where 
A.c1=B.c1),', ') from T1 A order by c1;

Give it a Try !!!



Thanks a lot! Very helpful!

array_to_string() + array() is exactly what I am looking for!

I just wonder that array_to_string() + array() will provide me good 
performance, right? If the calculation will be based on millions records.


Thanks again!
--
Lu Ying






Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM&  Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Emi Lu
Sent: Tuesday, February 08, 2011 2:36 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for 
c1

Good afternoon,

Is there a method to retrieve the following results:

T1(c1 int, c2 varchar(128) )
-


(1, val1);
(1, val2);
(1, val3);
(2, val1);
(3, val5);
(3, val6);

select c1, method(c2)
group by c1

returns:

1,   "val1, val2, val3"
2,   "val1"
3,   "val5, val6"


Thanks a lot!

--
Lu Ying




--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] quotes etc

[Emi Lu]

On 02/22/2011 04:18 PM, Adrian Klaver wrote:

On Tuesday, February 22, 2011 12:26:41 pm John Fabiani wrote:

 > Hi,

 > I would have thought that there would be a simple built-in function that

 > would escape the quotes as ('D' Andes') to ('D\' Andes'). But I did not

 > see anything?

 >

 > I am I wrong?

 >

 > Johnf

Dollar quoting ? :

http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html

4.1.2.4. Dollar-Quoted String Constants

test(5432)aklaver=>SELECT $$D' Andes$$;

?column?

--

D' Andes


I like this $str$$str$ very much!

Vote for this!
--
Lu Ying

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] How to realize ROW_NUMBER() in 8.3?

[Emi Lu]

Hello,

ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to get 
row_number


select row_number(), col1, col2...
FROM   tableName

Thanks a lot!

Ding Ye

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How to realize ROW_NUMBER() in 8.3?

[Emi Lu]

Thank you for the info.

I found a simple way:
==
[1] create SEQUENCE tmp start 7820;
[2]
insert into desti_table_name
select nextval('tmp'),
   c1, c2... ... cN
from t1 left join t2... ... tn
where ... ...

Just for people using 8.3, this is mimic row_number.

Emi






If your table is not terribly big, you can
try something like

SELECT a.col1,a.col2, COUNT(*) as row_number
FROM yourTable a,yourTable b
WHERE a.col1 >= b.col1 -- I'm assuming col1 is primary key
GROUP BY a.col1,a.col2
ORDER BY row_number

This is pure SQL, should work in every version...

Best,
Oliveiros

- Original Message - From: "Emi Lu" 
To: 
Sent: Wednesday, April 20, 2011 4:45 PM
Subject: [SQL] How to realize ROW_NUMBER() in 8.3?



Hello,

ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to
get row_number

select row_number(), col1, col2...
FROM tableName

Thanks a lot!

Ding Ye

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql





--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Sorting Issue

[Emi Lu]

I have the following query

Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId

from VehicleTrimAbbreviated

Where vehicleTrimAbbreviated like 'CX%'

order by VehicleTrimAbbreviated asc

Results:

532;"CX Hatchback"

536;"CXL Minivan"

3255;"CXL Premium Sedan"

537;"CXL Sedan"

538;"CXL Sport Utility"

3319;"CXL Turbo Sedan"

533;"CX Minivan"

1959;"CX Plus Minivan"

534;"CX Sedan"

535;"CX Sport Utility"

539;"CXS Sedan"

Why would this not sort correctly? All the CX should be first, then CXL,
Then CXS


Would you mind try:

Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
from VehicleTrimAbbreviated
Where vehicleTrimAbbreviated like 'CX%'
order by

split_part(VehicleTrimAbbreviated, ' ', 1) asc,
split_part(VehicleTrimAbbreviated, ' ', 2) asc;



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Sorting Issue

[Emi Lu]

That works. Why?


http://www.postgresql.org/docs/current/static/functions-string.html


split_part(string text, delimiter text, field int)	text	Split string on 
delimiter and return the given field (counting from one) 
split_part('abc~@~def~@~ghi', '~@~', 2)	def


Emi



-Original Message-
From: Emi Lu [mailto:em...@encs.concordia.ca]
Sent: Monday, May 09, 2011 12:38 PM
To: Ozer, Pam
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue


I have the following query

Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId

from VehicleTrimAbbreviated

Where vehicleTrimAbbreviated like 'CX%'

order by VehicleTrimAbbreviated asc

Results:

532;"CX Hatchback"

536;"CXL Minivan"

3255;"CXL Premium Sedan"

537;"CXL Sedan"

538;"CXL Sport Utility"

3319;"CXL Turbo Sedan"

533;"CX Minivan"

1959;"CX Plus Minivan"

534;"CX Sedan"

535;"CX Sport Utility"

539;"CXS Sedan"

Why would this not sort correctly? All the CX should be first, then

CXL,

Then CXS


Would you mind try:

Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
from VehicleTrimAbbreviated
Where vehicleTrimAbbreviated like 'CX%'
order by

split_part(VehicleTrimAbbreviated, ' ', 1) asc,
split_part(VehicleTrimAbbreviated, ' ', 2) asc;




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Sorting Issue

[Emi Lu]

Hi Pam,

>> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
>> from VehicleTrimAbbreviated
>> Where vehicleTrimAbbreviated like 'CX%'
>> order by
>>
>> split_part(VehicleTrimAbbreviated, ' ', 1) asc,
>> split_part(VehicleTrimAbbreviated, ' ', 2) asc;

This query works, right?

Reason:
==
. split_part(VehicleTrimAbbreviated, ' ', 1) return the string before 
the blank


. split_part(VehicleTrimAbbreviated, ' ', 1) return the string after the 
blank


So
[1] you order by CX, CXL, CXS first
[2] you order by second part "Hatchback, Minivan... "

Is there clear now?

Emi



On 05/09/2011 03:52 PM, Ozer, Pam wrote:

Ok but why doesn't the other way work?  I can't use the function in my
query. It is dynamically created.

-Original Message-
From: Emi Lu [mailto:em...@encs.concordia.ca]
Sent: Monday, May 09, 2011 12:52 PM
To: Ozer, Pam
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue



That works. Why?


http://www.postgresql.org/docs/current/static/functions-string.html


split_part(string text, delimiter text, field int)  textSplit
string on
delimiter and return the given field (counting from one)
split_part('abc~@~def~@~ghi', '~@~', 2) def

Emi



-Original Message-
From: Emi Lu [mailto:em...@encs.concordia.ca]
Sent: Monday, May 09, 2011 12:38 PM
To: Ozer, Pam
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue


I have the following query

Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId

from VehicleTrimAbbreviated

Where vehicleTrimAbbreviated like 'CX%'

order by VehicleTrimAbbreviated asc

Results:

532;"CX Hatchback"

536;"CXL Minivan"

3255;"CXL Premium Sedan"

537;"CXL Sedan"

538;"CXL Sport Utility"

3319;"CXL Turbo Sedan"

533;"CX Minivan"

1959;"CX Plus Minivan"

534;"CX Sedan"

535;"CX Sport Utility"

539;"CXS Sedan"

Why would this not sort correctly? All the CX should be first, then

CXL,

Then CXS


Would you mind try:

Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
from VehicleTrimAbbreviated
Where vehicleTrimAbbreviated like 'CX%'
order by

split_part(VehicleTrimAbbreviated, ' ', 1) asc,
split_part(VehicleTrimAbbreviated, ' ', 2) asc;





--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Dates and NULL's`

[Emi Lu]

if I have a date field that contains a NULL
will it show up when I ask for a where date range for the same date field.

Where mydate>= "2011/04/01"::date and mydate<= "2011/04/30"::date

With the above where will the NULL's be selected

I ask because I was always told that a NULL matches everything and nothing!



I think the answer is no.

when mydate is null, record will not be returned.

e.g.,
select 'abc' where (null::date >='2011-01-01'::date) ;

0 rows returned.

Emi


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Dates and NULL's`

[Emi Lu]

Where mydate>= "2011/04/01"::date and mydate<= "2011/04/30"::date
With the above where will the NULL's be selected


Here is what I get when I try:

spi=>  SELECT NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<=
'2011-04-30'::DATE;
  ?column?
--

(1 row)


spi=>  SELECT (NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<=
'2011-04-30'::DATE) IS TRUE;
  ?column?
--
  f
(1 row)


spi=>  SELECT (NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<=
'2011-04-30'::DATE) IS FALSE;
  ?column?
--
  f
(1 row)


spi=>  SELECT (NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<=
'2011-04-30'::DATE) IS UNKNOWN;
  ?column?
--
  t
(1 row)

Sorry Richard, I do not understand.

It looks like you are saying the NULLS will be returned too


As a summary:

(1)
null:date COMPARE 'real date'::DATE
Will always return NUll, so you will not get the record returned at all!

(2) examples he provided:

. (null:date COMPARE 'real date'::DATE) IS NULL;
. (null:date COMPARE 'real date'::DATE) IS UNKNOWN;

Null returns true.

(3) null is true/false;
returns false

Emi




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] column type for pdf file

[Emi Lu]

Hello,

To save pdf files into postgresql8.3, what is the best column type?

bytea, blob, etc?

Thank you,
Emi

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] client-side lo_import() provided by libpq ?

[Emi Lu]

Hello,

Postgresql8.3, tried:

create table test(id, image oid);

insert into test values(1, lo_import('apple.jpg'));

ERROR:  must be superuser to use server-side lo_import()
HINT:  Anyone can use the client-side lo_import() provided by libpq.

About client-side lo_import(), is there an online doc about install 
lo_import?


Thank you,
Emi


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] column type for pdf file

[Emi Lu]

Hello all,

All right, it seems that everyone thinks saving a pdf into postgresql is 
not a good idea.


My situation is:
=
. pdf file: 500kb
. One year I need to save around 65 files = 32M

As a summary, disadvantages are:
==
. Memory issue when read/save/retrieve the file
. Consume connections
. Increase load
. during transaction lo may be lost?
. file systems do better than DB
   . storing a reference to a file stored outside the database is 
preferable


If I miss anything, please add them.

I am curious, for what circumstances, should the lo be used in postgresql?

Thanks a lot!
Emi







On 05/19/2011 02:00 AM, Piotr Czekalski wrote:

Right!
The external binary file storage has another advantage (that may be
considered as disadvantage as well) - it is usually easier to develop
and test mechanism as you're able to browse uploaded file result using
os / application or even "exchange" file contents for test purposes,
while in case of iternal storage you need some extra code to do it for
you and you newer know, if it works well. Another problem is caching /
feeding files in case of heavy load condition - caching of huge objects
is problematic while you never know the dimension of the problem,
non-caching approach may cause bottlenecks on frequent database reads.
I was considering both scenarios and finally've choosen external storage
for our invoice generation system.

Regards,

Piotr





--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] column type for pdf file

[Emi Lu]

Craig, Karsten,


Hardly everyone. You lose transaction safety when using file system
storage outside the DB, you need another way to talk to the server than
just the Pg connection, and most importantly your backups become more
complicated because you have two things to back up.

It's not simple, and it depends a lot on how much the data changes, how
big the files are, etc.


The situation is:
==
. pdf file size: 500kb
. 65 files per year = 32M
. operation: read/save/remove
  but the total file number is around 65 files per year

How is the above case, saving pdf files into psql8.3 is an acceptable way?

Thank you,
Emi

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Re: 500KB PDF files for postgresql8.3, which is the most efficient way?

[Emi Lu]

Hello,

I'd like to have more comments about the following case:


. 500KB per PDF file; 30 files per year
. PSQL8.3

  . struts2.2.3 + mybatis for sql operation
  . tomcat6

Added more info

 Solution:
 
 (1) Save pdfs to file system, only point file name in psql8.3

 (2) Save oids of pdfs into table

 (3) Save pdf files as bytea column in psql8.3


Pros and cons for (1), (2), (3), which is the most efficient way?

Thanks a lot!
Emi


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] 500KB PDF files for postgresql8.3, which is the most efficient way?

[Emi Lu]

Hello,

I'd like to have more comments about the following case:

. 500KB per PDF file; 30 files per year
. PSQL8.3

Solution:

(1) Save pdfs to file system, only point file name in psql8.3

(2) Save oids of pdfs into table

(3) Save pdf files as bytea column in psql8.3

Pros and cons for (1), (2), (3), which is the most efficient way?

Thanks a lot!
Emi





On 05/27/2011 12:45 AM, Jasen Betts wrote:

On 2011-05-26, Bosco Rama  wrote:


   select * into temp table foo from maintable where primcol=123;
   update foo set primcol = 456;
   insert into maintable select * from foo;

You also may need this is if you intend to use the same sequence of
calls on within the same session:

   drop table foo;


Yet another way to do the same thing:

begin;

create temportary table foo on commit drop as
  select * from maintable where primcol=123;
update foo, set primcol=456;
insert into maintable select * from foo;

commit;



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] ANY for Array value check

[Emi Lu]

Good morning,

String array compare command, I forgot how to do it.

E.g.,
create table z_drop(id varchar[]);
insert into z_drop values('{"a1", "a2", "b1", "b2", "b3"}');

I'd like to do:

select * from z_drop where id = any('a1', 'b1');

What is the command?

Thanks a lot!
Emi



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] pagination problem in postgresql need help

[Emi Lu]

select aiah_number.aiah_number_id, aiah_number.aiah_number,

...

order by rank_value desc limit 1 offset 1;


I use:
==
  select ...
  order by ...
  LIMIT #{pageSize}::INTEGER OFFSET #{offset}::INTEGER;

Emi



--
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: malformed record literal: "",DETAIL: Missing left parenthesis?

[Emi Lu]

Good morning,

A question about: ERROR:  malformed record literal: ""
DETAIL:  Missing left parenthesis.

Can someone tell me what cause the error?


Table z_drop;
   Column|  Type
-+
 run_date| character varying(128)
 adm_year| character varying(4)
 adm_sess| character varying(1)
 faculty | character varying(128)
 ac_cycle| character varying(128)
 deg_code| character varying(128)
 discipline  | character varying(128)
 thesis  | character varying(128)
 elig_stype  | character varying(128)
 stud_source | character varying(128)
 applied | numeric
 reviewed| numeric
 accepted| numeric
 confirmed   | numeric
 registered  | numeric
 hold| numeric
 forward | numeric
 refused | numeric
 cancelled   | numeric
 other   | numeric
 pending | numeric


PREPARE test(z_drop) AS  INSERT INTO z_drop VALUES  ($1, $2, $3, $4, $5, 
$6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, 
$21) ;




EXECUTE test('', '1', '1', '1', '1', '1', '1', '1', '1', '', 1, 1, '1', 
'0', '0', '0', '0', '0', '0', '0', '0') ;





Thank you,
Emi

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] ERROR: malformed record literal: "",DETAIL: Missing left parenthesis?

[Emi Lu]

A question about: ERROR: malformed record literal: ""
DETAIL: Missing left parenthesis.

Can someone tell me what cause the error?


Table z_drop;
Column | Type
-+
run_date | character varying(128)
adm_year | character varying(4)
adm_sess | character varying(1)
faculty | character varying(128)
ac_cycle | character varying(128)
deg_code | character varying(128)
discipline | character varying(128)
thesis | character varying(128)
elig_stype | character varying(128)
stud_source | character varying(128)
applied | numeric
reviewed | numeric
accepted | numeric
confirmed | numeric
registered | numeric
hold | numeric
forward | numeric
refused | numeric
cancelled | numeric
other | numeric
pending | numeric


PREPARE test(z_drop) AS INSERT INTO z_drop VALUES ($1, $2, $3, $4, $5,
$6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
$21) ;

I have fixed it.

It should not be z_drop, it should be real column names.

The mailing list email appears so slow :-( Only after 4 hours it show!

Emi




EXECUTE test('', '1', '1', '1', '1', '1', '1', '1', '1', '', 1, 1, '1',
'0', '0', '0', '0', '0', '0', '0', '0') ;




Thank you,
Emi




--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

[Emi Lu]

Good morning,

Does psql provide something like the following query command?

select * from tablename
where col1 not ilike ('str1%', 'str2%'... 'strN%')

Thanks a lot!
Emi

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

[Emi Lu]

On 08/30/2011 11:24 AM, Tom Lane wrote:

Emi Lu  writes:

Does psql provide something like the following query command?



select * from tablename
where col1 not ilike ('str1%', 'str2%'... 'strN%')


If you remember the operator name equivalent to ILIKE (~~*)
you can do

select * from tablename
where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));


Thank you Tom!

If next version could have "not ilike ('', '')" added into window 
functions, that's will be great!


Emi

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

[Emi Lu]

Hi Tom,


select * from tablename
where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));



If next version could have "not ilike ('', '')" added into window
functions, that's will be great!


Why?  And what's this got to do with window functions?


First, where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])) will 
work for me.


But I feel " ilike ('str1', ... 'strN')" is more intuitive, isn't it?

I have a feeling that windows functions deal with all fancy functions. I 
would consider ilike ('str'...) as a non-standard SQL command?


Emi


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

[Emi Lu]

On 08/31/2011 03:16 AM, Emre Hasegeli wrote:

2011/8/30 Emi Lu:


First, where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])) will
work for me.

But I feel " ilike ('str1', ... 'strN')" is more intuitive, isn't it?


It is not. It is like "where id = (3, 5, 7)".



What I mean is ilike ('%str1%', ... '%strN%')

I just forgot to put %

Emi

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] how to temporally disable foreign key constraint check

[Emi Lu]

Good morning,


Is there a way to temporally disabled foreign key constraints something 
like


SET FOREIGN_KEY_CHECKS=0

When population is done, will set FOREIGN_KEY_CHECKS=1

Thanks a lot!
Emi

--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] how to temporally disable foreign key constraint check

[Emi Lu]

Not really, sorry :(

What I am looking for is no DB structure changes.

But only disabling foreign key constraint check/verify for period of 
time and then recover it.


Similar to mysql's "set FOREIGN_KEY_CHECKS = true/false"

Emi



On 10/21/2011 10:58 AM, Oliveiros d'Azevedo Cristina wrote:

Something like

ALTER TABLE t_yourtable DROP CONSTRAINT
and then
ALTER TABLE t_yourtable ADD FOREIGN KEY
?

Best,
Oliveiros

- Original Message - From: "Emi Lu" 
To: 
Sent: Friday, October 21, 2011 2:36 PM
Subject: [SQL] how to temporally disable foreign key constraint check



Good morning,


Is there a way to temporally disabled foreign key constraints
something like

SET FOREIGN_KEY_CHECKS=0

When population is done, will set FOREIGN_KEY_CHECKS=1

Thanks a lot!
Emi

--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca +1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Change Ownership Recursively

[Emi Lu]

iamunix=# \c postgres

was really meant to be:

iamunix=# \c - postgres

The first changes to database postgres as current user, the second
changes the user while remaining on the current database.


This is very helpful!

psql> \c - username_for_new_connection

--
Emi


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Simple way to get missing number

[Emi Lu]

Good morning,

May I know is there a simple sql command which could return missing 
numbers please?


For example,

t1(id integer)

values= 1, 2, 3  500

select miss_num(id)
from   t1 ;


Will return:
===
37, 800, 8001

Thanks a lot!
Emi


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] [GENERAL] Simple way to get missing number

[Emi Lu]

Aha, generate_series, I got it. Thank you very much!!

I also tried left join, it seems that left join explain analyze returns 
faster comparing with except:


select num as missing
from   generate_series(5000, 22323) t(num)
 left join t1  on (t.num = t1.id)
where t1.id is null
limit 10;

Emi

On 04/24/2012 10:31 AM, hubert depesz lubaczewski wrote:

On Tue, Apr 24, 2012 at 10:15:26AM -0400, Emi Lu wrote:

May I know is there a simple sql command which could return missing
numbers please?
For example,
t1(id integer)
values= 1, 2, 3  500
select miss_num(id)
from   t1 ;


select generate_series( (select min(id) from t1), (select max(id) from
t1))
except
select id from t1;

Best regards,

depesz




--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Simple way to get missing number

[Emi Lu]

I got it and thank you very much for everyone's help!!

It seems that "left join where is null" is faster comparing with 
"except". And my final query is:


select num as missing
from   generate_series(5000, #{max_id}) t(num)
left join t1  on (t.num = t1.id)
where t1.id is null;

Emi

On 04/24/2012 11:42 AM, Steve Crawford wrote:

On 04/24/2012 07:15 AM, Emi Lu wrote:

Good morning,

May I know is there a simple sql command which could return missing
numbers please?

For example,

t1(id integer)

values= 1, 2, 3  500

select miss_num(id)
from t1 ;


Will return:
===
37, 800, 8001

T


select generate_series(1,500) except select id from t1;

Example

select anumber from fooo;
anumber
-
1
3
5
7
9
11
13
15

select generate_series(1,15) except select anumber from fooo order by 1;
generate_series
-
2
4
6
8
10
12
14

Cheers,
Steve



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Simple method to format a string

[Emi Lu]

Good morning,

Is there a simply method in psql to format a string?

For example, adding a space to every three consecutive letters:

abcdefgh -> *** *** ***

Thanks a lot!
Emi


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Simple method to format a string

[Emi Lu]

Just a small optimization would be to use a backreference with regexp_replace
instead of regexp_matches:

select regexp_replace('foobarbaz', '(...)', E'\\1 ', 'g');
  regexp_replace

  foo bar baz


Great.

After combined with several more replace(s), regexp_replace will provide 
me the expecting result.


Thanks!
Emi

--
select
regexp_replace(
   replace(
   replace(col-val, ' ', ''), '-', ''),
 replace...
'(...)', E'\\1 ', 'g')
from tn;



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Load UTF8@psql into latin1@mysql through JDBC

[Emi Lu]

Good morning,

Is there a simple way to load UTF8 data in psql to mysql(with latin1 
encoding) through JDBC?


Thanks a lot!
Emi


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Load UTF8@psql into latin1@mysql through JDBC

[Emi Lu]

Is there a simple way to load UTF8 data in psql to mysql(with latin1
encoding) through JDBC?


This would seem to be dependent on the MySQL JDBC adapter.


From here:


http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-charsets.html

"All strings sent from the JDBC driver to the server are
converted automatically from native Java Unicode form to
the client character encoding, including all queries sent "


This does not help. The reason I asked this is because through mybatis + 
JDBC, loading data from psql@utf8 to mysql@latin1, the french character 
cannot be auto-loaded.


Thanks.
--
Emi




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Load UTF8@psql into latin1@mysql through JDBC

[Emi Lu]

On 12/12/2012 12:47 PM, Emi Lu wrote:

Is there a simple way to load UTF8 data in psql to mysql(with latin1
encoding) through JDBC?


This would seem to be dependent on the MySQL JDBC adapter.


From here:


http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-charsets.html


"All strings sent from the JDBC driver to the server are
converted automatically from native Java Unicode form to
the client character encoding, including all queries sent "


This does not help. The reason I asked this is because through mybatis +
JDBC, loading data from psql@utf8 to mysql@latin1, the french character
cannot be auto-loaded.



JAVA codes work for most of characters, but not "-È". Someone knows why 
the following codes cannot load "-È" to mysql@latin1?


Thanks a lot!

--


public static String utf8_to_latin1(String str)
   throws Exception
   {
  try
  {
 String stringToConvert = str;
 byte[] convertStringToByte = stringToConvert.getBytes("UTF-8");
 return new String(convertStringToByte, "ISO-8859-1");
  }catch(Exception e)
  {
 log.error("utf8_to_latin1 Error: " + e.getMessage());
 log.error(e);
 throw e;
  }
   }







--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Split a string to rows?

[Emi Lu]

Hello,

Is there a function to split a string to different rows?

For example, t1(id, col1)
values(1, 'a, b, c');

select id, string_split_to_row(col1, ',');

Return:
=
1, a
1, b
1, c

Thanks alot!
Emi



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Split a string to rows?

[Emi Lu]

Thanks a lot! I just noticed that my postgresql is 8.3(unnest function 
is not there by default). Is there a way that I could download and load 
only this function from somewhere?


Thanks again!
Emi


On 01/07/2013 02:58 PM, Jonathan S. Katz wrote:

On Jan 7, 2013, at 2:44 PM, Emi Lu wrote:


Hello,

Is there a function to split a string to different rows?

For example, t1(id, col1)
values(1, 'a, b, c');

select id, string_split_to_row(col1, ',');

Return:
=
1, a
1, b
1, c



You can probably use some combination of "string_to_array" and "unnest"

e.g.

SELECT unnest(string_to_array('a,b,c', ','));

  unnest

  a
  b
  c
(3 rows)

If you need a more complex string splitting mechanism, there is the 
"regexp_split_to_array" function.

Jonathan




--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Split a string to rows?

[Emi Lu]

All right. I found the function.
http://wiki.postgresql.org/wiki/Array_Unnest

Thanks a lot!
Emi

On 01/07/2013 03:16 PM, Emi Lu wrote:

Thanks a lot! I just noticed that my postgresql is 8.3(unnest function
is not there by default). Is there a way that I could download and load
only this function from somewhere?

Thanks again!
Emi


On 01/07/2013 02:58 PM, Jonathan S. Katz wrote:

On Jan 7, 2013, at 2:44 PM, Emi Lu wrote:


Hello,

Is there a function to split a string to different rows?

For example, t1(id, col1)
values(1, 'a, b, c');

select id, string_split_to_row(col1, ',');

Return:
=
1, a
1, b
1, c



You can probably use some combination of "string_to_array" and "unnest"

e.g.

SELECT unnest(string_to_array('a,b,c', ','));

  unnest

  a
  b
  c
(3 rows)

If you need a more complex string splitting mechanism, there is the
"regexp_split_to_array" function.

Jonathan







--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Split a string to rows?

[Emi Lu]

Hello Thomas,


Thanks a lot! I just noticed that my postgresql is 8.3(unnest
function is not there by default). Is there a way that I could
download and load only this function from somewhere?



Are you aware that 8.3 will be de-suppported as of next month?

You should really think about an upgrade *now*



http://www.postgresql.org/support/versioning/

Although 8.3 reaches EOL date, it says that "Supported=Yes" for 8.3.

I believe that "Supported" would be fine, wouldn't it?

Emi












--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] How to generate drop cascade with pg_dump

[Emi Lu]

Hello,

May I know how to generate drop table cascade when pg_dump a schema please?

E.g.,
pg_dump -h db_server -E UTF8   -n schema_name  -U schema_owner --clean 
-d db_name >! ~/a.dmp


In a.dmp, I'd like to get:

drop table t1 cascade;
drop table t2 cascade;
... ...

Only dropping constraints within a schema is not good enough since there 
are dependencies on other schema.


Thanks a lot!
Emi


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] SQLMAP IBATIS insert values from web forms to a money type column

[Emi Lu]

Hello,

I am using SQLMAP ibatis to do DB insertion for a webapplication.

In my sqlmap.xml file, I was trying to insert values into a table with 
one col (type is Money), but in java, I could not find Money type.


Does someone knows about ibatis how to make values got from the web form 
to be inserted into money type column through IBATIS ?


Thanks a lot!



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[SQL] The length of the sql query

[Emi Lu]

Hello,

Just curious to know whether postgresql has any length constraint about 
where part, such as



Query =
[
select col1, col2, ... coln
from table 1, table2,
where

constraint1 + constraint2 +constraintN
]

Is there any length arrange for the Query str such as 500M, 1G, etc? Or 
the query can be as long as it is.


Thanks a lot!


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] The length of the sql query

[Emi Lu]

Hello,

It appears in MySql 3.23 the limit is 16 MB.  In 4.0 and later, it is 1 GB

http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html


Could someone tell me where I can find PostgreSQL doc about the query 
length please

Tks a lot!


Hello,

Just curious to know whether postgresql has any length constraint 
about where part, such as



Query =
[
select col1, col2, ... coln
from table 1, table2,
where

constraint1 + constraint2 +constraintN
]

Is there any length arrange for the Query str such as 500M, 1G, etc? 
Or the query can be as long as it is.


Thanks a lot!


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[SQL] Is it possible to left join based on previous joins result

[Emi Lu]

Hello,

Is it possible to do something like:

select ...
from t1
inner join t2 ...
left join t2.colN

When t1 inner join with t2 I got unique result for t2.colN( colN's value 
is table name).


Can I continue to left join with the column "colN" in table t2?

Thanks




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Is it possible to left join based on previous joins result

[Emi Lu]

I tried the example as the following:

create table a(col1);
create table b(col1, col2)

select a.*
from a inner join b using(col2)
left join b.col2 as c on (c.col1 = a.col1)

System notifies me that b is not a schema name.

So, I guess the approach that I tried to do is not acceptable by Pgsql 
grammar.






Is it possible to do something like:

select ...
from t1
inner join t2 ...
left join t2.colN

When t1 inner join with t2 I got unique result for t2.colN( colN's value 
is table name).


Can I continue to left join with the column "colN" in table t2?

Thanks




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] case insensitive regex clause with some latin1 characters

[Emi Lu]

My environment setup as:

 show lc_ctype;
  lc_ctype
-
 fr_CA.UTF-8
(1 row)


fis=> SELECT 'Ä' ~* 'ä';
 ?column?
--
 f
(1 row)


fis=> SELECT 'Ä' ilike 'ä';
 ?column?
--
 f
(1 row)


I got the same result: false





"=?ISO-8859-1?Q?Ragnar_=D6sterlund?=" <[EMAIL PROTECTED]> writes:

I'm not sure if this is a bug or if I'm doing something wrong. I have
a database encoded with ISO-8859-1, aka LATIN1. When I do something
like:



SELECT 'Ä' ~* 'ä';



it returns false.


Check the database's locale setting (LC_CTYPE).  It has to be one that
expects LATIN1 encoding.

The current regex code is generally not able to deal with locale-specific
behaviors in UTF8 encoding, but it should work for single-byte encodings
as long as you've got the locale setting right.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] How to get all users under a group

[Emi Lu]

Hello,

I know \du+ can get all group info for each user.

Could someone tell me how to get all users under each group please?

such as provide the group name, showing all users under the group.

Thanks,
Emi


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] How to get all users under a group

[Emi Lu]

To answer my own question:

SELECT g.groname , u.usename AS "User name"
FROM pg_catalog.pg_user u
left join pg_catalog.pg_group g on(u.usesysid = ANY(g.grolist))
ORDER BY 1, 2;






I know \du+ can get all group info for each user.

Could someone tell me how to get all users under each group please?

such as provide the group name, showing all users under the group.

Thanks,
Emi


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] Get the max(value1, value2, value3) from a table

[Emi Lu]

Greetings,

Version: PostgreSQL 8.0.13 on i686-pc-linux-gnu

I have a table test(col1, col2, col3)

For each row, I'd like to get the "max"(col1, col2, col3).

For example, test(1, 5, 2)
 test(8, 1, 3)
 test(12, 1, 1)


select ?max?(col1, col2, col3) as result;
will return

result
---
5
8
12

(3 rows)

Thanks!
Ly.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Get the max(value1, value2, value3) from a table

[Emi Lu]

select max(col1) from table
union all
select max(col2) from table
union all
select max(col3) from table

No, this is not what I prefer; it makes complicate query.




Would the following work also?

SELECT MAX( GREATEST( col1, col2, col3 ) )
  FROM TABLE;


I would prefer this func. Unfortunately, the current version I have 
8.02(http://www.postgresql.org/docs/8.0/static/functions-conditional.html) 
does not support this func I am afraid :(


Thanks !
Ly.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Get the max(value1, value2, value3) from a table

[Emi Lu]

select ?max?(col1, col2, col3) as result;
will return

result
---
5
8
12

(3 rows)

8.1 (I believe?) introduced GREATEST(), which does precisely what you're
looking for.


How would greatest give him three rows like that?  Maybe I'm
misunderstanding what the OP was asking for...


IF 8.1, "select greatest(col1, col2, col3) from test" is exactly what I 
am looking for.


I would do the optional query by union/or for now.

Thanks!
Ly.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] String function to Find how many times str2 is in str1?

[Emi Lu]

Good morning,

Is there a string function in PSQL to count how many times one str is in 
another string?


For example,
Str1 = "test   test   caa   dtest   testing   EndofString";
Str2 = "   ";

select funcName(Str1, Str2);

return 5

Because Str1 has 5 Str2.

Thanks !
Ly



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] String function to Find how many times str2 is in str1?

[Emi Lu]

Pavel Stehule wrote:

Hello

what about

CREATE OR REPLACE FUNCTION Foobar(text, text)
RETURNS integer AS $$
SELECT array_upper(string_to_array($1,$2),1) - 1;
$$ LANGUAGE SQL IMMUTABLE;

On 15/02/2008, Rodrigo E. De León Plicet <[EMAIL PROTECTED]> wrote:

On Fri, Feb 15, 2008 at 11:09 AM, Emi Lu <[EMAIL PROTECTED]> wrote:
 >  Str1 = "test   test   caa   dtest   testing   EndofString";
 >  Str2 = "   ";
 >
 >  select funcName(Str1, Str2);
 >
 >  return 5


CREATE OR REPLACE FUNCTION
  FOOBAR(TEXT,TEXT)
 RETURNS INT AS $$
  SELECT(LENGTH($1) - LENGTH(REPLACE($1, $2, ''))) / LENGTH($2) ;
 $$ LANGUAGE SQL IMMUTABLE;

 SELECT FOOBAR('test   test   caa   dtest   testing   EndofString', '   ');

  foobar
 
  5
 (1 row)


If there is not a system func for this, I will do this way.
Thank you for all inputs.
Ly

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Documenting a DB schema

[Emi Lu]

Hi,


I'm looking for a systematic way to document the schema for the database
behind our website (www.redfin.com  ), so that
the developers using this database have a better idea what all the
tables and columns mean and what data to expect.  Any recommendations?


I am using Case Studio to document DB structures. I think it is pretty 
good tool.


http://www.casestudio.com/enu/default.aspx

To browse DB objects only, try DbVisualizer:
http://www.minq.se/products/dbvis/

- Ly

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your Subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql

[SQL] query results in XML format?

[Emi Lu]

Hello,

Can someone suggestion some tutorial/hyperlinks/docs about how 
postgresql output query results into xml files?


Thanks a lot!

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql

[SQL] case when... end in update clause?

[Emi Lu]

Hello,

May I know can "case when " used by update clause. If yes, how?

I use one small Example, table: test
=
id
==
5
6
8

try to update test.id


update test

case
 when id =5 then SET id = 6
end
;

Thanks!

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] case when... end in update clause?

[Emi Lu]

Aaron Bono wrote:

On Wed, Mar 12, 2008 at 10:47 AM, Emi Lu <[EMAIL PROTECTED]> wrote:


Hello,

May I know can "case when " used by update clause. If yes, how?

I use one small Example, table: test
=
id
==
5
6
8

try to update test.id


update test

case
 when id =5 then SET id = 6
end
;

<http://www.postgresql.org/mailpref/pgsql-sql>



Is this what you are looking for:


update test
set id = case when id = 5 then 6 else id end;


Exactly what I am looking for!

Thanks a lot!

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] case when... end in update clause?

[Emi Lu]

 I use one small Example, table: test
 =
 id
 ==
 5
 6
 8

 try to update test.id


 update test

 case
  when id =5 then SET id = 6
 end
 ;


would this work:

update test set id=5 where id=6;



No. I provide one small fake example.


I want to know how to use case when in update/set clause as the following:

update test
set id = case when id = 5 then 6 else id end;


Thanks.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] case when... end in update clause?

[Emi Lu]

Hi Scott ,

 No. I provide one small fake example.


 I want to know how to use case when in update/set clause as the following:

 update test
 set id = case when id = 5 then 6 else id end;


Well, I think my point stands, that this stuff really belongs in a
where clause.  The way you're doing it it updates ALL the rows whether
it needs to or not, my way only updates the rows that need it.  How
about a REAL example of what you're trying to do.  There may well be a
more efficient way of doing this than using a case statement.  Or
not...


Ok.


The situation would like this, in one query:


UPDATE tableName
SET
   col1 = val1 when col1 satisfy condition1
   col1 = val2 when col1 satisfy condition2

   ... ...

   col1 = valN when col1 satisfy conditionN

   ... ...

WHERE
   col3 satisfy conditionX;




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] drop table where tableName like 'backup_2007%' ?

[Emi Lu]

Good morning,


Is there a command to drop tables whose name begins a specific string?


For example, all backup tables begins with backup_2007:
Drop table where tableName like 'backup_2007%'?


Thanks a lot!



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] A sys func for a->b, b->c => a->c ?

[Emi Lu]

Good Morning,

Someone has better solution about the following query situation?

table test with two columns with primary key (id1, id2)
id1, id2
=
12
13
12   3
13   5



Query conditions:
=
(1) a->b  => b->a
(2) a->b and a->c => a->c



Expected return:
id1   id2
===
1 2
1 3
112

21
23
212

31
32
312

12   1
12   2
12   3

13   5


I did:

create view v_test AS
select id1 , id2 from test
union
select id2, id1  from test;


(
SELECTa.id1 , b.id2
FROM  v_test AS a
left join v_test AS b
   ON (a.id2 = b.id1)
WHERE a.id1 <> b.id2
)
UNION
(
SELECT id1, id2
FROM   v_test
)
order by id1 ;


The query is a bit complex, do we have a better system func or query for 
this?


Thanks a lot!

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] export CSV file through Java JDBC

[Emi Lu]

Good morning,

Running the following command from command line is ok, but cannot export 
a table into a csv file through java JDBC code.


Please help!


JAVA code:
===
   public static void exec(String command)
   {
  try{
 Process p   = Runtime.getRuntime().exec(command);
 p.waitFor();
 p.destroy();
  }catch(Exception e) {
 System.err.println("exec command Error:  " + e.getMessage());
  }
   }



SQL Command:
=
psql -U username -d dbName -c  "\copy tableName to 'result.csv'  with CSV "

When call exec(commands);

Nothing happens, result.csv was not created at all?

Thanks a lot!



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] trim(both) problem?

[Emi Lu]

Hi,

Isn't this a bug about trim both.

 select trim(both '' from 'ROI Engineering Inc.');
btrim
-
 OI Engineering Inc.
(1 row)


"R" is missing? How?


 version
-
 PostgreSQL 8.0.15 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2

Thank you!







--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] trim(both) problem?

[Emi Lu]

Thanks a lot for all help! I understand how trim work now.


You could probably use instead:
select replace('ROI Engineering Inc.', '', '')



That would zap occurrences in the middle of the string, though.
regexp_replace would be better since it'd allow anchoring the
pattern, eg

select regexp_replace('ROI Engineering Inc.', '^', '');
select regexp_replace('ROI Engineering Inc.', '$', '');


This is exactly I am looking for, but my version
PostgreSQL 8.0.15 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2

does not support this func, and have to think about the other way to 
'trim' the ^ & $


Thank you again!

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] How long - Vacumm full - 10 million to 90,000

[Emi Lu]

Good morning,

A question about VACUUM FULL. The docs say:

VACUUM FULL is recommended for cases where you know you have deleted the 
majority of rows in a table, so that the steady-state size of the table 
can be shrunk substantially with VACUUM FULL's more aggressive approach. 
Use plain VACUUM, not VACUUM FULL, for routine vacuuming for space recovery.


I have a table from around 10 million to 90,000, after deletion, I tried 
to use vacuum full, but it seems that it takes forever to finish. Could 
anyone tell me how long it will take to finish the Recovering disk space 
please?


Thanks a lot!

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Size or efficiency differences "varchar(128) vs. varchar(32)"

[Emi Lu]

Good morning,

I'd like to create a varchar length column.

May I know does varchar(128) and varchar(32) will cause any size or 
efficiency differences?


Thanks a lot!

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Size or efficiency differences "varchar(128) vs. varchar(32)"

[Emi Lu]

Sorry, forgot to replay all.


Emi Lu wrote:

May I know does varchar(128) and varchar(32) will cause any size or  
efficiency differences?


None at all.



Basically, there is no efficiency differences at all, if I know a column 
is now varchar(32) but could be potentially increased to length(col)>32 
in the future, I will setup to varchar(128).


This column will be setup as varchar(128) everywhere so that foreign key 
constraints will work.


I had thought "foreign constraint, query or indexes" on varchar(32) 
could be more efficient than varchar(128) and I was wrong.


I will use varchar(128) for my column.

Thanks a lot!





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Query prepared plan

[Emi Lu]

Good morning,

May I know the "commands" to
. show current session's prepared plans
. see the definition of a prepared plan

E.g., psql> PREPARE fooplan (int, text, bool, numeric) AS INSERT INTO 
foo VALUES($1, $2, $3, $4);


(1) Similar to "\dt", I want to see "fooplan"
(2) Similar to "\d tableName", how to see the plan def?

Thanks a lot!


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] truncate vs. delete

[Emi Lu]

A. Kretschmer wrote:

am  Thu, dem 24.07.2008, um 10:01:46 -0400 mailte Emi Lu folgendes:

A. Kretschmer wrote:

am  Thu, dem 24.07.2008, um  9:47:48 -0400 mailte Emi Lu folgendes:

I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well?
http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html

Not realy, for instance, pg can rollback a truncate, and a sequence are
not reset.



Thank you. I am quite sure that I will not use "delete" now.
Now I a question about how efficient between

(1) truncate a big table (with 200, 000)
vacuum it (optional?)


not required



drop primary key
load new data
load primary ke
vacuum it


analyse it, instead vacuum.


It gets more and more clear to me know!

I guess I need only do analyze(primary key column) after loading data.
The new picture will be:
. truncate table
. drop primary key
. load data
. set primary key
. analyze interesting columns










--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] truncate vs. delete

[Emi Lu]

Good morning,

If I remember correctly, "delete" does not release space, while truncate 
will.


I have an option now

(1) Use object creator(with create/drop permission which I do not need 
in my cronjob script) to truncate table1(>100,000 recs) records


(2) Use user1(has r/w only) to delete from table1, then vacuum it

May I know how inefficient "delete from" comparing to truncate please?

Thanks a lot!



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] truncate vs. delete

[Emi Lu]

I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well?
http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html





Emi Lu wrote:

Good morning,

If I remember correctly, "delete" does not release space, while truncate 
will.


I have an option now

(1) Use object creator(with create/drop permission which I do not need 
in my cronjob script) to truncate table1(>100,000 recs) records


(2) Use user1(has r/w only) to delete from table1, then vacuum it

May I know how inefficient "delete from" comparing to truncate please?

Thanks a lot!






--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] truncate vs. delete

[Emi Lu]

A. Kretschmer wrote:

am  Thu, dem 24.07.2008, um  9:47:48 -0400 mailte Emi Lu folgendes:

I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well?
http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html


Not realy, for instance, pg can rollback a truncate, and a sequence are
not reset.



Thank you. I am quite sure that I will not use "delete" now.
Now I a question about how efficient between

(1) truncate a big table (with 200, 000)
vacuum it (optional?)
drop primary key
load new data
load primary ke
vacuum it

(2) drop table (this table has no trigger, no foreign key)
re-create table (without primary key)
load new data
setup primary key
vacuum it

suggestions PLEASE?

Thanks a lot!


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql