Re: [SQL] how to do a find and replace

-matching.html), it isn't mentioned in section '9.4. String Functions and Operators' (http://www.postgresql.org/docs/8.1/interactive/functions-string.html). Hélder M. Vieira ---(end of broadcast)--- TIP 6: explain analyze is your friend

[SQL] max() unexpected type conversion

x27;); insert into test (testcol) values ('B'); insert into test (testcol) values ('B'); insert into test (testcol) values ('C'); insert into test (testcol) values ('C'); select max(testcol) from test; Thank you Hélder M. Vieira

Re: [SQL] max() unexpected type conversion

underlying data is of type 'varchar', one has to be always very cautious about the 'text' conversion, otherwise all readings after the first will return null instead of valid data. Helder M. Vieira ---(end of broadcast)--- TIP 4: H

[SQL] quote_literal() fails with unmatched quotes

error returns from postgres 8.1 ERROR: unterminated quoted string at or near "'2009-10-03" at character 122 QUERY: insert into scenario_inv (inv_id,record_date,scenario_type,mostlikemat,mktpr,mktyld ) values ( '405832','2005-06-30','1','2009-10-03

[SQL] Quote_literal()

declare SQL text; NSQLtext; SQL := ''10/3/2009''; NSQL := quote_literal(SQL); The result is '10/3/2009 with no closing quote. Jim James M Doherty Principal JMD CONSULTING 411 Thunderbay Dr Georgetown, TX 78626 A reputation is something

Re: [SQL] quote_literal() fails with unmatched quotes

Nmostlikemat := ''10/3/2009''; What happens is that quote_literal works for everyone in the example but this one. Jim James M Doherty Principal JMD CONSULTING 411 Thunderbay Dr Georgetown, TX 78626 A reputation is something others give you HONOR is something you give your

Re: [SQL] Quote_literal()

the one of the functions. Any way thanks for taking the time to look at my issue. Jim James M Doherty Principal JMD CONSULTING 411 Thunderbay Dr Georgetown, TX 78626 A reputation is something others give you HONOR is something you give yourself! -Original Message- From: Tom Lane [mai

[SQL] Locale and pattern matching

users: Until now, I've been selecting LATIN1 encoding, but after a few tests, I came to think that LATIN9 is a better option (the euro sign...). For those who regularly use LATIN9, what is your opinion ? Is it indeed a better option ? Thank you. Hélder M. Vieira LATIN CAPITA

[SQL] References NULL field

FROM registrars, notes WHERE regname = 'blah' no matter that I put after the 'blah' (or nothing), I get no results. As soon as I add notes into the tables being queried, I get nothing. There's some special way I need to shape this query, but I don't know what it is. Any

[SQL] Re: [SQL] Problema com função UPPER

Eduardo, creio que o problema decorre do jogo de caracteres usado... No meu caso, uma vez que vivo em Portugal, uso o jogo LATIN9 quando crio uma nova base de dados. Simplificando: CREATE DATABASE xpto WITH OWNER = postgres ENCODING = 'LATIN9' TABLESPACE = pg_default;

Re: [SQL] distinct with sum

sum there. The query: select distinct on (id_prod) id_prod, id_unit, sum(quantity) as result from products where determinant = false group by id_prod, id_unit select id_prod, id_unit, sum(quantity) as result from products where determinant = false group by id_prod, id_unit Hélder M. Vieira

Re: [SQL] backup and document views and user functions

carefully if this is waht you're looking for. Regards, Helder M. Vieira -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Cursor names in a self-nested function

s. Any other solutions are of cause welcome. Many thanks, - David David M. Kidd Research Associate Center for Population Biology Silwood Park Campus Imperial College London 0207 594 2470

Re: [SQL] Looping recordset

selected rows or, perhaps, use the SELECT in the FOR..LOOP and subsequently perform the DELETE. Hélder M. Vieira ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command

Re: [SQL] Removing duplicate rows

eate temporary table mytesttable (like mymaintable excluding constraints excluding defaults); Regards, Hélder M. Vieira ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [SQL] Having the sum of two queries

ect status from imported_users ) as fff group by status; Regards, ´ Helder M. Vieira ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Problemas com Procedure no PostgreSQL

caso continue a ter problemas, volte a publicar aqui o script na sua nova redacção. Já agora, quando publicar mensagens nesta lista, caso lhe seja possível escreva em inglês. Cumprimentos, Helder M. Vieira -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make chan

Re: [SQL] Problemas com Procedure no PostgreSQL

ias nos select e update, uma vez que não há join envolvidos Cumprimentos, Hélder M. Vieira create or replace function trava_inadimplentes ( pdias_vencido integer, pund_local varchar(3), pcnpj varchar(18) ) returns boolean as $$ declare spermite_bloqueio varchar(1); inro_re

[SQL] Savepoints and SELECT FOR UPDATE in 8.2

I have a client application that needs: SELECT a set of records from a table and lock them for potential updates. for each record make some updates to this record and some other records in other tables call some call a function that does some application logic that does not access the da

[SQL] LIMIT question

I need to use a LIMIT count in a query but I also need to know how many rows the query itself would yield without the limit. I can do this inside a transaction like this BEGIN SELECT COUNT(*) from table1 where blah; select * from table1 where blah LIMIT 1000; COMMIT Now I can give some feedback l

[SQL] looking for example of inserting into a view

I can't seem to find an example I vaguely remember seeing when I was originally learning about INSERT rules and views. This example features a view that is an outer join of several tables. The example shows how to generate a CSV file of the data in the view and then loading the data into the origi

Re: [SQL] random rows

fetch the n desired rows example SELECT * FROM test_table LIMIT 100 OFFSET 100; this will skip to row number 100 and the fetch the next 100 rows []´s - Joao Pedro M. F. Monoo Infortrade Information Systems #183816 Linux Registered User Slackware 7.1 r

Re: [SQL] create unique index schema.index_name on table (column)?

I'm working with a closed source application that reads an ASCII file and as it does it (via ODBC) creates tables and indexes, inserting as it goes. Like so: CREATE TABLE bgsuser.CAXCTRLD ( CTRLS INTEGER, INTVL INTEGER); CREATE UNIQUE INDEX bgsuser.PRIMARY_CAXCTRLD ON bgsuser.CAXCTRLD (CTRLS,IN

Re: [SQL] left outer join on more than 2 tables? (UNCLASSIFIED)

Classification: UNCLASSIFIED Caveats: NONE Pardon my lameness, I have JUST dipped my toes in PostgreSQL and want to try this out! I'm probably wrong but here goes my very first PostgreSQL join attempt! SELECT region_name, count(complaint.id) FROM region LEFT JOIN city ON (region.id = city.reg

Re: [SQL] left outer join on more than 2 tables? (UNCLASSIFIED)

Classification: UNCLASSIFIED Caveats: NONE Maybe I am way of base here, but I see a reference to region in this query. However, I think count(cm.id) is correct because some would have a count of 0. Count(*) would produce counts when there are no complaints. (If I understand the logic, again, I

Re: [SQL] left outer join on more than 2 tables? (UNCLASSIFIED)

Classification: UNCLASSIFIED Caveats: NONE My problem with that is that you are counting rows per region and you have done a left jion on region. That means there will be at least one row per region even if there are 0 compliants. It might yield the same result now, but if you even have a p

<    1   2