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

Hello all, Does anyone have available plpgsql codes to update all capital letters in a column to "the first character is capital and the rest is small" ? For example, in tableA(id, description) 001, 'ZHANG ZHE XIN' => 'Zhang Zhe Xin' 002, 'LIU

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

I got the answer from the docs. |initcap|(text) thanks anyway, Ying Hello all, Does anyone have available plpgsql codes to update all capital letters in a column to "the first character is capital and the rest is small" ? For example, in tableA(id, description) 001, 'ZHANG ZHE XIN'

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

I saw it from the docs as well. Thank you Alvaro :) Alvaro Herrera wrote: Emi Lu wrote: Hello all, Does anyone have available plpgsql codes to update all capital letters in a column to "the first character is capital and the rest is small" ? I don't know about plp

[SQL] how to get the size of array?

Hello, Is there a way that I can get the size of one array ? For example, create table test (id varchar[]); insert into test values('{}'); insert into test values('{1, 2, 3}'); I am looking for something like : select sizeOf(id) as size from test; so that I can get results like: size --

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

Hello, I am using PostgreSQL 8.0.1. In a function, I try to use exception to catch sql errors: begin begin exception WHEN ??? THEN end; ... end; The place where I have ???, what I should put there please? e.g., 1. WHEN sqlcode = '02000' THEN 2. WHEN no_data then 3

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

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

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

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

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

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

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

Hello, In pl/pgsql (postgresql 8.01), how to use variables in select .. into .. command CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$ DECLARE var1 ALIAS FOR $1; cm_tableName tableA.col1%TYPE; T1

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

WHERE col1 = $2 CONTEXT: PL/pgSQL function "test" line 8 at select into variables LINE 1: SELECT col2 FROM $1 WHERE col1 = $2 Does it mean I have to use the cursor ? Thanks, Ying I think it is SELECT INTO cm_tableName col2 FROM ... WHERE ... 2006/3/17, Emi Lu <[EMAIL PRO

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

Does not work either, the whole function is: create table t1(col1 varchar(3), col2 varchar(100)); insert into t1 values('001', 'Result 1'); insert into t1 values('002', 'Result 2'); insert into t1 values('003', 'Result 3'); CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$ DECLARE

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

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

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

If it is for multiple columns' foreign key constraint. Try this query: SELECT DISTINCT n.nspname AS from_schema_name, c.relname AS from_table_name, toSchemaName.nspname AS to_schema_name, toTable.relname as to_table_name, fk_col.attname FROM pg_catalog.pg_class AS c LEFT JOIN pg_namespace AS

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

Hello, I tried "select greatest(max(a), max(b)) from public.test", but I got the following errors: ERROR: function greatest(integer, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. May I know where I can read t

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

Thank you Tom. Emi Lu <[EMAIL PROTECTED]> writes: ERROR: function greatest(integer, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. We added greatest/least in 8.1, but before that you can just use

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

Hello, I am trying to insert one column to a specific position in a table. In mysql, I can do: . create table test(id varchar(3), name varchar(12)); . alter table test add column givename varchar(12) after id; I am looking for similar things in postgresql to add a new column to the correct po

Re: [SQL] Like with special character

I'm using postGre with tables which contain French character (?...). Is there a fonction which performs a like in replacing ? (e cute) by e ? to_ascii() should helps you I got the following error: select to_ascii('ê'); ERROR: encoding conversion from UNICODE to ASCII not supported

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

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

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

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

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

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

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

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

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

[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

[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

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

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,

[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

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

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

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

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

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

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

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

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

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

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

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?

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

[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

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

Hello, I am using SQLMAP ibatis to do DB insertion for a webapplication. In my sqlmap.xml file, I was trying to insert values into a table with one col (type is Money), but in java, I could not find Money type. Does someone knows about ibatis how to make values got from the web form to be in

[SQL] The length of the sql query

Hello, Just curious to know whether postgresql has any length constraint about where part, such as Query = [ select col1, col2, ... coln from table 1, table2, where constraint1 + constraint2 +constraintN ] Is there any length arrange for the Query str such as

Re: [SQL] The length of the sql query

Hello, It appears in MySql 3.23 the limit is 16 MB. In 4.0 and later, it is 1 GB http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html Could someone tell me where I can find PostgreSQL doc about the query length please Tks a lot! Hello, Just curious to know whether postgresql has

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

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

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

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 g

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

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 PROTECT

[SQL] How to get all users under a 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 fo

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

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 plea

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

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

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

select max(col1) from table union all select max(col2) from table union all select max(col3) from table No, this is not what I prefer; it makes complicate query. Would the following work also? SELECT MAX( GREATEST( col1, col2, col3 ) ) FROM TABLE; I would prefer this func. Unfortunat

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

select ?max?(col1, col2, col3) as result; will return result --- 5 8 12 (3 rows) 8.1 (I believe?) introduced GREATEST(), which does precisely what you're looking for. How would greatest give him three rows like that? Maybe I'm misunderstanding what the OP was asking for... IF 8.1, "sel

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

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

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,

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

[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

[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

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

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?

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

[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

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

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

[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

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

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)

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

  1   2   >