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
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
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
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
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
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
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
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
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:
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
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
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
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
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
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
:
Something like
ALTER TABLE t_yourtable DROP CONSTRAINT
and then
ALTER TABLE t_yourtable ADD FOREIGN KEY
?
Best,
Oliveiros
- Original Message - From: Emi Lu em...@encs.concordia.ca
To: pgsql-sql@postgresql.org
Sent: Friday, October 21, 2011 2:36 PM
Subject: [SQL] how to temporally disable
On 08/31/2011 03:16 AM, Emre Hasegeli wrote:
2011/8/30 Emi Luem...@encs.concordia.ca:
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
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:
On 08/30/2011 11:24 AM, Tom Lane wrote:
Emi Luem...@encs.concordia.ca 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
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 ~~*
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)
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
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
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
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)
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
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
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,
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
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
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
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=
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
-
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
, 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
version...
Best,
Oliveiros
- Original Message - From: Emi Lu em...@encs.concordia.ca
To: pgsql-sql@postgresql.org
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
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:
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
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,
: 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
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:
- From: Emi Lu em...@encs.concordia.ca
To: pgsql-sql@postgresql.org
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
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
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?,
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
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
, AND and CAD, and as
TRUE for all other currencies. When the records are sorted the false
are placed on the top because falsetrue;.
On Thu, Jul 30, 2009 at 10:51 PM, Emi Lu em...@encs.concordia.ca
mailto:em...@encs.concordia.ca wrote:
Good morning,
I have a currency table (code, description
Good morning,
I googled to find that on commit drop does not support:
(a) create temp table as select * from table1 where 12;
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, .
Got it. Thank you Tom!
Scott Marlowe scott.marl...@gmail.com 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.
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
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'
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
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
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
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
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
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:
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)
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
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
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!
--
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
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' ;
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
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
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
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:
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
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
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
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
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
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
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:
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
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,
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
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
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
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
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
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:
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
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
Thanks a lot for all help! I understand how trim work now.
You could probably use instead:
select replace('BR/ROI Engineering Inc.', 'BR/', '')
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
Hi,
Isn't this a bug about trim both.
select trim(both 'BR/' from 'BR/ROI Engineering Inc.');
btrim
-
OI Engineering Inc.
(1 row)
R is missing? How?
version
-
PostgreSQL 8.0.15 on
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 =
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
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
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
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
Hi,
I'm looking for a systematic way to document the schema for the database
behind our website (www.redfin.com http://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
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
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
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
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
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
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]
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
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
1 - 100 of 118 matches
Mail list logo