[SQL] How to generate drop cascade with pg_dump

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 wi

Re: [SQL] Split a string to rows?

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 upgr

Re: [SQL] Split a string to rows?

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

Re: [SQL] Split a string to rows?

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

[SQL] Split a string to rows?

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

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

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 st

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

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

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

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

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

[SQL] Simple method to format a string

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

Re: [SQL] Simple way to get missing number

d 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 r

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

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

[SQL] Simple way to get missing number

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

Re: [SQL] Change Ownership Recursively

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 m

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

evedo 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 co

[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

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

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 i

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

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['str

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

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 c

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

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

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

;, '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?

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

Re: [SQL] pagination problem in postgresql need help

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

[SQL] ANY for Array value check

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 pgsq

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

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 a

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

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

Re: [SQL] column type for pdf file

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,

Re: [SQL] column type for pdf file

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

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

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 do

[SQL] column type for pdf file

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`

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

Re: [SQL] Dates and NULL's`

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 every

Re: [SQL] Sorting Issue

[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

Re: [SQL] Sorting Issue

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 VehicleTr

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

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

QL, 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 sel

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

Re: [SQL] quotes etc

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 a

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

IM& 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

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

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

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

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

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

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

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 c

[SQL] how to get row number in select query

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?, lnam

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

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

[SQL] Fuzzy match under PostgreSQL 8.0.15

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

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

;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>&g

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

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

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

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

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

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

Re: [SQL] Allow column type to change without worrying about view dependencies

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 su

[SQL] left join where not null vs. inner join

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

[SQL] Allow column type to change without worrying about view dependencies

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' colum

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

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 :-)

[SQL] How to dump views definition in one schema?

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 li

[SQL] view vs. tables used to define that view

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 Yi

[SQL] pg_stat_activity return all connections

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 mail

[SQL] ibatis with overlaps query

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" Howeve

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

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: SEL

[SQL] Inherits is not encouraged?

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) Logica

[SQL] "union" vs. left join

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.*, ca

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

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

[SQL] pg_dump more than one table in one command?

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! -- Sen

Re: [SQL] prepared query plan did not update

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 thos

Re: [SQL] prepared query plan did not update

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

[SQL] prepared query plan did not update

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 vi

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

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

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

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

[SQL] How to provide password to pg_dump command ?

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

Re: [SQL] RE: [SQL] Why *no* ambig.uous complain in select part?

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

Re: [SQL] RE: [SQL] Why *no* ambig.uous complain in select part?

s 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 ab

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

Re: [SQL] Check a column value not in Array.

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 in

[SQL] Check a column value not in Array.

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 subsc

Re: [SQL] Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date

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 look

[SQL] Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date

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! -- Se

Re: [SQL] Query prepared plan

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: rela

Re: [SQL] Query prepared plan

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 comm

Re: [SQL] Query prepared plan

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 table

Re: [SQL] truncate vs. delete

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

Re: [SQL] truncate vs. delete

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)

[SQL] truncate vs. 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 fro

Re: [SQL] truncate vs. delete

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

[SQL] Query prepared plan

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 tableNa

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

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

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

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

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

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,

Re: [SQL] trim(both) problem?

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_re

[SQL] trim(both) problem?

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

[SQL] export CSV file through Java JDBC

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

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

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 i

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

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

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

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

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

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 w

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

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

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

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

[SQL] query results in XML format?

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

Re: [SQL] Documenting a DB schema

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 usi

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

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,

  1   2   >