[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

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

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]

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

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

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

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

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

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

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

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

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

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

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

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

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

[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

[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

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

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

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

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

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

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

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

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

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

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] 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] 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] 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] Allow column type to change without worrying about view dependencies

[Emi Lu]

ulka salvi wrote:


u can try this
SELECT a,b,c,CAST(d AS float)

  AS N 'd',e,f,g INTO dbo.newtable

Ulka


Did not follow.


Example,
create view v_test as select * from test1;
create table test(col1 varchar(12));

I'd like to:
alter table test alter column col1 type varchar(128);

Tried your suggested:
create or replace view v_test as select CAST( col1 as varchar(128)) from 
test;


Got:
cannot change data type of view column "col1"

--
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] left join where not null vs. inner join

[Emi Lu]

Two tables, each contains more than hundreds of thousands records. Is 
there any efficiency differences between (1) and (2)?


(1) T1 inner join T2 using (c1, c2)


(2) T1 left join T2 using (c1, c2) where c2 is not null


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] Allow column type to change without worrying about view dependencies

[Emi Lu]

Good morning,

When there are lots of views and complicated dependencies, it is not 
easy to alter column from varchar(a) to varchar(b).


Is it possible when defining a view, adding cascade or some other key 
word(s) to allow column type change?


When column1 is changed, all related views' column type is changed as well.

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

Re: [SQL] alter column from varchar(32) to varchar(255) without view re-creation

[Emi Lu]

Emi Lu wrote:

PostgreSQL 8.0.15.
Is there a way that I can easily alter column type from varchar(32) to
varchar(255) but do not have to worry about views dependent on it?



You should test it carefully and it is considered a bad practice -
I'll probably get sued for recommending this :-), but you may try:

SELECT * from pg_attribute where attname = 'colname' and  attrelid =
(SELECT oid FROM pg_class WHERE relname='_tablename');

UPDATE pg_attribute set atttypmod = 4+_new_length_in_your_case_255
where attrelid = _attrelid_from_above_ and attname = 'colname';


Tried this, it worked for table but not view.

E.g.,

T1(col1 varchar(64)... );
create view v1 as select * from T1;


update pg_attribute set atttypmod = 4+ 128 where 
\d T1
   col1  varchar(128) == [OK]

\d v1
   col1  varchar(64)  == [did not change?]

So, it does not really update all dependencies?

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 dump views definition in one schema?

[Emi Lu]

Good morning,

Can pg_dump or other command could dump all views definition in one schema.

I was trying to alter column types for several tables, and I have very 
complicated view dependencies.


What I try to do is:
(1). Back all views def
(2). alter columns
(3). re-create views from (1)

I'd like to know how to dump all views in one schema?

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] view vs. tables used to define that view

[Emi Lu]

May I know is it possible to query dictionary table(s) in order to 
return relationship between a view and table(s) please?


For example, create view v1 as (select ... from T1 left join T2 ... )

Result:
view Name | Table Name

v1| T1
v1| T2

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] pg_stat_activity return all connections

[Emi Lu]

Good morning,

A question about pg_stat_activity:

If believe that "select * from pg_stat_activity where 
usename='db_user1'" returns all connections based on db_user1, no matter 
the connection is through terminal command or jdbc, am I right?



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

[SQL] ibatis with overlaps query

[Emi Lu]

Good morning,

With ibatis, do overlap checking:

(1) select (DATE #begin_date#, DATE #end_date#) overlaps
(DATE '2008-01-01', DATE '2009-01-01')
. #begin_date# is varchar
. #end_date#   is varchar

Always get:

Cause: java.sql.SQLException: ERROR: syntax error at or near "$4"


However, when I updated the query to
(2) select (#begin_date#::DATE, #end_date#::DATE) overlaps
(DATE '2008-01-01', DATE '2009-01-01')

It works. I am bit confused why (1) does not work, but (2) does?

--
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] alter column from varchar(32) to varchar(255) without view re-creation

[Emi Lu]

PostgreSQL 8.0.15.
Is there a way that I can easily alter column type from varchar(32) to
varchar(255) but do not have to worry about views dependent on it?



You should test it carefully and it is considered a bad practice -
I'll probably get sued for recommending this :-), but you may try:

SELECT * from pg_attribute where attname = 'colname' and  attrelid =
(SELECT oid FROM pg_class WHERE relname='_tablename');

UPDATE pg_attribute set atttypmod = 4+_new_length_in_your_case_255
where attrelid = _attrelid_from_above_ and attname = 'colname';


I am afraid that only admin can do this dictionary change :(

If it does work, would it cause any storage or efficiency problem? Or it 
is just permission issues probably?


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

[SQL] Inherits is not encouraged?

[Emi Lu]

Good morning,

I have two questions about inherits under postgreSQL 8.0.15.


(1) Is possible that I could change the column of a table that is 
defined by Inherits?


e.g., t1(col1, col2, col3); create table tx(colx) Inherits (t1)
I'd like to get tx shown as (col1, colx, col2, col3).


(2) Logically, I have a table inherited from two other tables, but no 
more other columns. Will it not be encouraged to do so?


e.g., t1 (col1... coln)
 t2 (col1... coln)

 create t3() Inherits (t1, t2) without OID


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] "union" vs. left join

[Emi Lu]

Good morning,

Would it be possible to know that whether "union" is quicker than "left 
join" in a general sense?


Queries:

(1) union
select a.*, b.value
from  a
left join b
   union
select a.*, c.value
from  a
left join c


(2) left join
select distinct a.*,
  case when ... then
 b.value
  else
 c.value
from  a
left join b
left join c


Will (1) be more efficient than (2) or vice versa? Or it really depends 
on data



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] alter column from varchar(32) to varchar(255) without view re-creation

[Emi Lu]

Good morning,

I am using PostgreSQL 8.0.15.

Is there a way that I can easily alter column type from varchar(32) to 
varchar(255) but do not have to worry about views dependent on it?


If Psql could support column alter without views' dependencies, that 
will be great!


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] pg_dump more than one table in one command?

[Emi Lu]

Good morning,

Under postgreSQL 8.0, could someone tell me how to pg_dump more than one 
tables at the same time please?


I tried to do something like:

pg_dump -h machineName -U username --inserts --column-inserts 
--file=dump.sql --table=t1 t2 ... ...tN -d databaseName ;


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] prepared query plan did not update

[Emi Lu]

Stephan Szabo wrote:

On Wed, 17 Sep 2008, Emi Lu wrote:


Good morning,

I tried to use prepared query plan to update columns, but it did not
update at all.

PREPARE pname(varchar) AS
UPDATE t1
SETcol1 = false
WHERE  col1 AND
col2 = '$1' ;


I don't think you want those quotes in the second part of the where
clause. I'm pretty sure that means you're comparing against the literal
string with a dollar sign and one rather than the value given at execute
time for $1.


Do you mean:

PREPARE pname(varchar) AS
UPDATE t1
SET   col1 = false
WHERE col1 AND
  col2 = $1 ;

But still does not work? Strange, right?


--
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] prepared query plan did not update

[Emi Lu]

I tried to use prepared query plan to update columns, but it did not
update at all.

PREPARE pname(varchar) AS
UPDATE t1
SETcol1 = false
WHERE  col1 AND
col2 = '$1' ;
EXECUTE pname( 'value' )
Could someone tell me where I did wrong please?





WHERE  col1 AND
col2 = '$1' ;
Are you looking for both: col1 and col2 - to be equal to '$1'?
Then it should be:
WHERE  col1 = '$1' AND
col2 = '$1';


Not exactly, col1 is boolean value

WHERE col1 = true AND
  col2 = '$1';




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

[SQL] prepared query plan did not update

[Emi Lu]

Good morning,

I tried to use prepared query plan to update columns, but it did not 
update at all.


PREPARE pname(varchar) AS
UPDATE t1
SETcol1 = false
WHERE  col1 AND
   col2 = '$1' ;
EXECUTE pname( 'value' )



Could someone tell me where I did wrong please?

Thanks alot!



--
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] pg_restore in java connection (auto_commit = false)

[Emi Lu]

> See:
> http://www.postgresql.org/docs/8.3/interactive/explicit-locking.html
>
> where it says that ALTER TABLE obtains the ACCESS EXCLUSIVE lock. You
> can confirm this by issuing the command of interest then running:
>
>   SELECT * from pg_catalog.pg_locks;
>
> With that transaction still open, and that lock still held, you then
> execute a new process (pg_restore) that establishes its own unrelated
> connection to the database and tries to get a ROW EXCLUSIVE lock (if
> using INSERT, and presumably COPY though the docs don't say so) on the
> table. It can't do so, because your Java program holds an ACCESS
> EXCLUSIVE lock on the table that conflicts with the requested lock mode.
>
> Your java code won't release the lock until pg_restore finishes, and
> pg_restore won't finish until your java code releases the lock.
>
> Deadlock.
>
>
> There is no way you can "pass" your connection to pg_restore when you
> invoke it from Java. Thus, you must either not hold any locks that would
> prevent pg_restore from acting on the table, or you must do all the work
> within Java using your existing JDBC connection.

This is exactly what I was trying to make it clear to myself.
Thank you Craig!


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

[SQL] pg_restore in java connection (auto_commit = false)

[Emi Lu]

Good afternoon,

I have a question about pg_restore in JAVA and need your help.

I planed to do:
==
1. pg_dump each tables (done successfully)
2. start a java connection
   auto_commit = false

3.for(Table1... ... TableN)
  {
 drop primary key

 pg_restore table  (dead lock here)

 add primary key

 Connection.commit
  }

It seems that the above steps caused a lock for the tableX. Could 
someone give me some ideas 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] How to provide password to pg_dump command ?

[Emi Lu]

Good morning,

Is there a way that I can pass "pwd" to pg_dump command please?


I tried to pg_dump in java, but do not know how to pass password.

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] RE: [SQL] Why *no* ambig.uous complain in select part?

[Emi Lu]

Hi Luigi,


you will have problems if table 1 and table 2 have the same names to col1 e
col2. For example, table1 has col1=parcel and col2=area and table 2 has
col1=country and col2=area then, in that case you will have ambiguity.


Would you please give me an example?

I have two tables like the following:
T1 (col1 varchar, col2 varchar, primary key (col1, col2))
T2 (col1 varchar, col2 varchar, primary key (col1, col2))


Query I have is:
===
select col1, col2
from  T1
left join T2 using (T1, T2);

Thanks a lot!









2008/8/22 Emi Lu <[EMAIL PROTECTED]>


Hi Edward,

 Just a guess, but it seems to me that since the join is using col1 and

col2
there is no ambiguity. They should be the same no matter which table it
comes from.


Not always the same; "Left join" may return:

table2.col1,col2 = null,
while table1.col1,col2 is not null





 -Original Message-

From: [EMAIL PROTECTED] [mailto:
[EMAIL PROTECTED]
On Behalf Of Emi Lu
Sent: Friday, August 22, 2008 4:12 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Why *no* ambig.uous complain in select part?

Good morning,

Just notice one small thing, and need your information about select

select col1, col2
from table1
left join table2
using (col1, col2)

;

This query never complain about ambiguous columns of col1 and col2 in the
select part.

My guess is:
(1) col1, col2 always return table1.col1, table1.col2
(2) because using (col1, col2)
that's why, table name is not necessary in select part

Am I wrong? Please advise?

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








--
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] RE: [SQL] Why *no* ambig.uous complain in select part?

[Emi Lu]

Hi Edward,


Just a guess, but it seems to me that since the join is using col1 and col2
there is no ambiguity. They should be the same no matter which table it
comes from.


Not always the same; "Left join" may return:

table2.col1,col2 = null,
while table1.col1,col2 is not null





-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Emi Lu
Sent: Friday, August 22, 2008 4:12 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Why *no* ambig.uous complain in select part?

Good morning,

Just notice one small thing, and need your information about select

select col1, col2
from table1
left join table2
using (col1, col2)

;

This query never complain about ambiguous columns of col1 and col2 in 
the select part.


My guess is:
(1) col1, col2 always return table1.col1, table1.col2
(2) because using (col1, col2)
 that's why, table name is not necessary in select part

Am I wrong? Please advise?

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

[SQL] Why *no* ambig·uous complain in select part?

[Emi Lu]

Good morning,

Just notice one small thing, and need your information about select

select col1, col2
from table1
left join table2
using (col1, col2)

;

This query never complain about ambiguous columns of col1 and col2 in 
the select part.


My guess is:
(1) col1, col2 always return table1.col1, table1.col2
(2) because using (col1, col2)
that's why, table name is not necessary in select part

Am I wrong? Please advise?

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] Check a column value not in Array.

[Emi Lu]

select *
from   test
where  test.col not in ARRAY['val1', 'val2'];



select * from test where test.col <> ALL ( ARRAY['val1', 'val2'] );

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

be careful with NULLs in this type of comparisons.


Thanks a lot for all your inputs!



For efficiency, I guess :

(1) "... where test.col <> all ARRAY['val1', 'val2']"
and
(2) "... where test.col not in ('var1', 'var2')"

provide the same query efficiency right?







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

[SQL] Check a column value not in Array.

[Emi Lu]

Greetings,

May I know the command to check whether a column value is in array please?

For example, I am looking  for sth like:

select *
from   test
where  test.col not in ARRAY['val1', 'val2'];

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] Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date

[Emi Lu]

I am expecting something like:

sql> select data_part('day name', current_date);
sql> Monday

sql> select data_part('day number', current_date);
sql> 1

(Mon =1 ... Sun =7?)

Thanks a lot!



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



This is exactly what I am looking for.

> select to_char(current_date,'Day');
> select to_char(current_date,'D');

1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday

Thank you very much for all your inputs!

--
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 day name(Mon, Tue... Sun) and day number (1, 2...7) from a date

[Emi Lu]

Good morning,

Could someone tell me the command to get the weekly day name and day 
number please.



I am expecting something like:

sql> select data_part('day name', current_date);
sql> Monday

sql> select data_part('day number', current_date);
sql> 1

(Mon =1 ... Sun =7?)

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] Query prepared plan

[Emi Lu]

Similar to \dt to show all tables, within one session, may I know the  
command to list all prepared query plan please?


select * from pg_prepared_statements;


Thank you! I think this is the command.

Too bad that I could not use it under 8.0x.
select * from pg_prepared_statements;
ERROR:  relation "pg_prepared_statements" does not exist
fis=> select version();
 version
-
 PostgreSQL 8.0.15 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2



--
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] Query prepared plan

[Emi Lu]

Tom Lane wrote:

Emi Lu <[EMAIL PROTECTED]> writes:

Somebody know about how to find prepared query plan through command line?


PREPARE fooplan(...)
EXPLAIN EXECUTE fooplan(...)


Thank you Tom.

Similar to \dt to show all tables, within one session, may I know the 
command to list all prepared query plan please?


For example,
[1]\dX
   show all defined prepared query plan
[2]\dX planName
   show the definition of the named plan.

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



Somebody know about how to find prepared query plan through command line?

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

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

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

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

[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

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

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

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

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]

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

[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

[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

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

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