Re: [SQL] need help

on (p.name) p.name, l.name, datetime FROM location l INNER JOIN product_move m ON m.destination_location = l.id INNER JOIN product p ON m.product_id = p.id WHERE m.datetime '2012-12-31' ORDER BY p.name, datetime DESC -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte

Re: [SQL] parsing audit table

functions on it (http://www.postgresql.org/docs/9.0/static/hstore.html) The other one uses arrays to store column names, old values, new values, still more parseable -- Jaime Casanova         www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-sql mailing

Re: [SQL] Howto get a group_number like row_number for groups

On Thu, Apr 8, 2010 at 2:51 PM, Andreas maps...@gmx.net wrote: or rank()  over  (order by  object,   ts)  %  2 use dense_rank() instead -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via

Re: [SQL] Trigger on select :-(

on if the FooKey exists in the ToDo table for the user. you should do this when inserting data (with triggers of course) you can't use rules because a RULE ON SELECT you only can use one SELECT, no INSERT, UPDATE nor DELETE -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría

Re: [SQL] How to start the auto_explain module

On Thu, Nov 26, 2009 at 6:26 AM, aymen marouani marouani.ay...@gmail.com wrote: How can I load the auto_explain module ? http://www.postgresql.org/docs/8.4/static/auto-explain.html -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil

Re: [SQL] postgres time zone settings

in postgresql.conf) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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] Protection from SQL injection

intcol = intcol; set allow_literals all; add any query you want -- regards, Jaime Casanova Soporte de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 -- 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] Protection from SQL injection

query you want How do you inject this? How would the application looks like where this can be injected? ok... point taken -- regards, Jaime Casanova Soporte de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes

Re: [SQL] Cast in PG 8.3

-character data types are no longer automatically cast to TEXT (Peter, Tom) -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe

Re: [SQL] Cast in PG 8.3

On Feb 5, 2008 2:39 PM, Li, Jingfa [EMAIL PROTECTED] wrote: check the typo -- ilike that's not a typo, it's case insensitive LIKE -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying

Re: [SQL] Inserting an IF statement in the middle of a SELECT in pl/pgSQL code

sorry, i resubmit to the list On 7/18/07, Jaime Casanova [EMAIL PROTECTED] wrote: On 7/18/07, Norm Garand [EMAIL PROTECTED] wrote: Thanks Jaime: I tried using the CASE function and it still didn't work. What I need to do is set a conditional, so that if the parameter is NULL

Re: [SQL] Inserting an IF statement in the middle of a SELECT in pl/pgSQL code

$# $$ language sql; CREATE FUNCTION -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning

Re: [SQL] Informix Schema - PostgreSQL ?

? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook

Re: [SQL] Query Problem from FoxPro???

the problem is? maybe m$ knows -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning

[SQL] Fwd: [pgsql-es-ayuda] Ejecutar \copy desde VB

-- Forwarded message -- From: Carlos Alberto Silva [EMAIL PROTECTED] Date: Jun 19, 2007 2:00 PM Subject: Re: [pgsql-es-ayuda] Ejecutar \copy desde VB To: Jaime Casanova [EMAIL PROTECTED] Yo lo haría de otra manera. Una rutina q lea el archivo de texto en VB (nada complicado de

Re: [SQL] Sequence vs. Index Scan

mean stable? maybe this is silly but you can verify what the database thinks of the function selecting from pg_proc select pronamespace, provolatile from pg_proc where proname = 'get_branch_for_zip' -- regards, Jaime Casanova Programming today is a race between software engineers striving

Re: [SQL] dinamic sql

|| 'where ename in (' || quote_literal(user_input) || ')' -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far

Re: [SQL] Unexpected SQL error for UPDATE

the index again? it certainly sounds to an index corruption. -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning

Re: [SQL] Unexpected SQL error for UPDATE

On 7/12/06, aurora [EMAIL PROTECTED] wrote: Yes that helped! reindex index users_email_address_text_key; what version of postgres is this? there have been some bug fixes that involved indexes on text columns. wich locale are you using? -- regards, Jaime Casanova Programming today

Re: [SQL] PL/PGSQL - How to pass in variables?

(*) FROM ' || tablename INTO rowcount; -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning

Re: [SQL] READ COMMITTE without START TRANSACTION?

TRANSACTION before the SELECT, will this READ COMMITTED XA behaviour still be in effect? yes. all statements not executed inside a transaction block are in an implicit transaction Thanks, Otis -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks

Re: [SQL] [PERFORM] Query optimization with X Y JOIN

that someone could shed some light on the basic principal of this JOIN command and its syntax. Most people I ask, don't give me straight answers and what I have already read on the web is not very helpful thus far. http://www.postgresql.org/docs/current/static/sql-select.html -- regards, Jaime

Re: [SQL] Changing the transaction isolation level within the stored procedure?

is not the first statement... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Unable to identify an ordering operator '' for type 'smallint[]'

why is a bad idea to develop in version that is superior to the one you will use in production... my advice, upgrade your production server to 8.1.2... there were some work in arrays in 8.x.x -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast

Re: [SQL] delete from a using b in postgres 8.1.

clear in changes. Peter Childs -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] instead of trigger in pg

://messenger.yahoo.de ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 9: In versions below 8.0

Re: [SQL] Sub-query as function argument

On 12/20/05, Michael Burke [EMAIL PROTECTED] wrote: Is it possible to execute a SELECT query as an argument to a function? have you tried? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9

Re: [SQL] DB design and foreign keys

, ^^ [...unnecesary...] ERROR: there is no unique constraint matching given keys for referenced table orders this is because the PK in the orders table has two fields not one... so it founds no unique index on orders(order_code) -- Atentamente, Jaime Casanova (DBA

Re: [SQL] select count of distinct rows

, Otto -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] select count of distinct rows

On 12/10/05, Foster, Stephen [EMAIL PROTECTED] wrote: Well this should work but I tried it and it didn't. SELECT DISTINCT COUNT(*) FROM mytable; No, it shouldn't work... actually is a non-sense, count will return just one value so there is nothing to be distinct with... -- regards, Jaime

Re: [SQL] update question

and if the row doesn't exist then insert? if that is you can do a function in plpgsql and use exceptions for that... there is an example in the manuals -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you

Re: [SQL] update question

On 12/5/05, Matthew Peter [EMAIL PROTECTED] wrote: it's in a loop so there's an extra comma at the end so i was thinking i could put in a throw away value to keep the update from breaking if there's an additional comma Jaime Casanova [EMAIL PROTECTED] wrote: On 12/5/05, Matthew Peter wrote

Re: [SQL] APPEND INTO?

the FOR UPDATE OF tablename clause is for?) Mark INSERT INTO table SELECT -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] 'AS' in 'DELETE/UPDATE'

from TODO --- o Allow an alias to be provided for the target table in UPDATE/DELETE This is not SQL-spec but many DBMSs allow it. --- end extracting --- -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have

Re: [SQL] DEFAULT Constraint based on table type?

(animals); ALTER TABLE birds ALTER COLUMN type SET DEFAULT 'BIRD'; -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] sysid

---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ

Re: [SQL] can UNIQUEness of TEXT datatype really be guaranteed?

(and in a text field you can) you will get an error... you have to control within your application that... -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?

rules (INSERT/UPDATE/DELETE), 4 if you want SELECT as well... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your

Re: [SQL] Extract date from week

-01-01 is part of the 52nd week of year 2005. SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 7 --- end extracted text --- -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze

Re: [SQL] combination of function to simple query makes query slow

... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] combination of function to simple query makes query slow

; maybe you can show us an EXPLAIN of your select: EXPLAIN select_statement -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet

Re: [SQL] Tigger

FUNCTION session_update() RETURNS trigger AS $session_update$ [..function body..] $session_update$ LANGUAGE plpgsql; I think it should be: CREATE FUNCTION session_update() RETURNS trigger AS $$ [..function body..] $$ LANGUAGE plpgsql; -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator

Re: [SQL] UPDATEABLE VIEWS ... Examples?

, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Putting an INDEX on a boolean field?

if you know wich value will be less frequent... on the other value a sequential scan will be a win, isn't it? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: you can get off all lists at once

Re: [SQL] [GENERAL] index row size 2728 exceeds btree maximum, 27

this is the case. If this a log he will need a timestamp field to be usefull, making that field part of the primary key and letting the data out of the primary has more sense to me. -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast

Re: [SQL] Release feature

! 8.0.x and later -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] trying to do an update a bit confused.

= tblcase.clientnum::text) -- Regards, DBA* Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] A SQL Question About distinct, limit, group by, having, aggregate

, Jaime Casanova ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Consecutive row count query

the following results: Apple 2 Orange 1 Banana 1 Apple 1 A function? regards, Jaime Casanova ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so

[SQL] select with a function

per row in academico.aca_t_alumnocurso that matches the where. AndrewSN but I don't know any easy way of doing that for each row in a query. any ideas? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina

Re: [SQL] Query from different Database

it, please help me. Thanks. I believe that the contrib module dblink will do what you want, but I've never used it. What about using schemas not databases. Selects between schemas are fully supported. Regards, Jaime Casanova _ Do

Re: [SQL] feature request ?

Hi all, I ask: "why not to disallow nulls in boolean fields?". It was a question not a proposal. The explanation was clear to me. Nulls are not values but the absence of a known value. It is comparable to the state of a c (or almost any other programming language) variable that had not been

Re: [SQL] feature request ?

Hi all, Tri-valued boolean?? that's not against boolean concept?? i'm not saying that SQL is wrong nor Postgresql has to go beyond standard, i'm just trying to understand this stuff. Why not disallow the ability of boolean fields to be null? thanx in advance, Jaime CasanovaMichael Glaesemann

[SQL] case stement when null

end ' LANGUAGE 'sql' but when $4 is null the function executes the else part and what i want to do is to execute the when null. Can you help me??? Thanx in advance, Jaime Casanova _ The new MSN 8: advanced junk mail

[SQL] query with =ALL

that its result is 'A', 'B', 'C' any idea, is something wrong in my thinking? thanx in advance, Jaime Casanova _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail

[SQL] a query with = ALL

AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'A ' then it brings the result that it would but when i do the same with =ALL it doesn't work thanx in advance, Jaime Casanova

Re: [SQL] query with =ALL

Jaime Casanova wrote: Hi all, i have an strange result here, i'm using 7.4.2 on redhat 8 i have a query like this [snip] this query proves that its result is 'A', 'B', 'C' any idea, is something wrong in my thinking? Do you have any null values involved? That might well interfere (though I admit

Re: [SQL] query with =ALL

On Mon, 14 Jun 2004, Jaime Casanova wrote: On Mon, 14 Jun 2004, Jaime Casanova wrote: i have an strange result here, i'm using 7.4.2 on redhat 8 i have a query like this SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento

Re: [SQL] a query with = ALL

Jaime Casanova [EMAIL PROTECTED] writes: brings 'A ', 'B ', 'C ' well when i cHange the real query Given that you spelled it like that, I wonder whether you aren't confused about the behavior of cross-data-type comparisons. If one column is char(n) and the other is text or varchar(n) then you

Re: [SQL] query with =ALL

certainly fails at every row of CPA. Perhaps you meant = ANY? Not really becuase ANY has the same efect that IN and what i want is all the results that are equal in all the rows in any of the cur_paralelo values. But must be in all the cur_paralelo or nothing. thanx in advance, Jaime Casanova

Re: [SQL] query with =ALL

You're not expressing yourself clearly, because as far as I can understand you there are guaranteed to be no such results. regards, tom lane ok, the output i want is equivalent to: SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA,

[SQL] a query question

up with duplicate key error then i replace the(select (nvl(max(orp_numero), 0) + 1) from rec_t_ordenpago) part with nextval('seq1') and that work. So my question is, is this a postgreSQL limitation or is the way it have to happen? why? thanx in advance, Jaime Casanova

[SQL] joining a table with a function

hi all, i have a function of a user defined type it returns a record of values but just a row. select * from rec_f_consultar_alumno(a.ent_codigo, a.alu_codigo) what i want to do now is a select in wich i can do a seq scan or an index scan of a table and pass the values i retrieve into the

[SQL] isolation level

Hi all, is there a way to set the isolation level to something like the sql standard dirty read. Thanx in advance, Jaime Casanova _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features

[SQL] \d in 7.4.2

, RI_ConstraintTrigger_46769 that is the same i got from 7.2. I think 7.3 version is better and much informative. why the hackers go back in this? thanx in advance, Jaime Casanova _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http

[SQL] Ignore my last message

Sorry it was my mistake, i was using psql from 7.2.2 _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of

[SQL] \d in 7.4

Sorry it was my mistake, i was using psql from 7.2.2 _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)---

Re: [SQL] lifetime of temp schema versus compiled image of plpgsql proc

Hi all, I have a similar problem, i'm connecting to postgresql with th psql-odbc, create a temp table outside the transaction, insert into the temp, commit then i try to use the temp and it isn't there. Maybe an odbc problem?? are you using the odbc?