[SQL] a few Questions about quoted varaibles in psql
Hi! How can I interpret a variable in psql, when the variable has to be quoted? for example: \set myVar myValue \echo :myVar The Result ist then "myValue" ==> ok But: in my case the variable-values have to be in quotes: \set db_username myUsername \set db_password myPassword CREATE USER :db_username WITH PASSWORD :db_password ==> This will not work, because you need quotes CREATE USER :db_username WITH PASSWORD ':db_password' ==> This will create the user with Password :db_password and not myPassword (so it takes the name of the varibale and not den value) I played a bit with excaping, but even '\'':db_password'\'' will not work What can I do, in order to achieve, that psql interprets a variable in quotes? Another Problem: How can I concatenate a variable with a string without having a space in between? \set foo bar \echo :foo test ==> bar test (but there is a space in between) \echo :foo||test ==> this does not work (I want bartest) Is it somehow possible to do this? Thanks for your help! Leo -- GMX ProMail (250 MB Mailbox, 50 FreeSMS, Virenschutz, 2,99 EUR/Monat...) jetzt 3 Monate GRATIS + 3x DER SPIEGEL +++ http://www.gmx.net/derspiegel +++ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] problem w/plpgsql proc
Hi all - This might be the wrong forum for this, but I don't want to cross-post unless someone tells me I should. Anyways, I'm having a problem trying to compile this plpg proc. I'll post the proc and the errors. I'm coming from a T-SQL background, so hopefully when I get the hang of Pl/PgSQL I'll be able to draft up a doc for porting from T-SQL to Pl/PgSQL. proc: /** *editEmail: all vars required - will renumber the sequence if needed - if the email address is not found, it will add it. returns: -1 - the user already has 8 entries 0 - the record was updated, and the table renumbered 1 - the record was added. **/ --DROP FUNCTION editEmail(integer, smallint, varchar, varchar); CREATE FUNCTION editEmail(integer, smallint, varchar, varchar) RETURNS integer AS ' DECLARE ufk ALIAS FOR $1; seq ALIAS FOR $2; em ALIAS FOR $3; emp ALIAS FOR $4; --for renumbering the records rec RECORD; cnt INTEGER; found SMALLINT := 0; BEGIN -- see if the email address exists, then see if renumbering is needed CREATE TEMP TABLE this_user AS SELECT * FROM tblemailadd WHERE emuserfk = ufk; GET DIAGNOSTICS cnt = ROW_COUNT; --equiv. to @@ROWCOUNT -- see if the user exists in the table, then see if the user already -- has 8 entries. If so - return -1 (error)... max 8 entries allowed :) IF (cnt > 7) THEN IF NOT EXISTS (SELECT emseqnum FROM this_user WHERE emailaddr = em; ) THEN RETURN -1; END IF; END IF; --see if renumbering is needed.. IF (cnt > 1) THEN FOR rec IN SELECT * FROM this_user LOOP; --renumber the sequences UPDATE tblemailadd SET emseqnum = rec.emseqnum + 1 WHERE emuserfk = ufk AND emailaddr = rec.emailaddr; IF (em = rec.emailaddr) THEN found = 1; -- looks like we found the email addr. END IF; END LOOP; -- if the emailaddr was found, then update the record. -- if it wasn't, then insert the new record. IF (found = 1) THEN UPDATE tblemailadd SET emseqnum = seq, emailaddr = em, emprettyname = emp 121 >>> WHERE emuserfk = ufk; RETURN 0; ELSE INSERT tblemailadd (emuserfk, emseqnum, emailaddr, emprettyname) VALUES (ufk, seq, em, emp); RETURN 1; END IF; ELSE IF (cnt > 7) THEN RETURN -1; --alas! the user has too many records to proceed! END IF --make sure that the sequencing order begins intact IF (cnt = 1 AND seq = 1) THEN seq := 2; ELSE IF (cnt = 0 AND seq != 1) THEN seq := 1 END IF; END IF; INSERT tblemailadd (emuserfk, emseqnum, emailaddr, emprettyname) VALUES (ufk, seq, em, emp); RETURN 1; --huzahh! the record has been added! END IF; END; 'LANGUAGE 'plpgsql'; errors: psql:edit_procs.sql:121: ERROR: parser: parse error at or near "t" psql:edit_procs.sql:122: ERROR: parser: parse error at or near "return" psql:edit_procs.sql:125: ERROR: parser: parse error at or near "ELSE" psql:edit_procs.sql:126: ERROR: parser: parse error at or near "return" psql:edit_procs.sql:127: ERROR: parser: parse error at or near "if" psql:edit_procs.sql:131: ERROR: parser: parse error at or near "ELSE" psql:edit_procs.sql:136: ERROR: parser: parse error at or near "if" psql:edit_procs.sql:140: ERROR: parser: parse error at or near "ELSE" psql:edit_procs.sql:141: ERROR: parser: parse error at or near "if" psql:edit_procs.sql:144: ERROR: parser: parse error at or near "tblemailadd" psql:edit_procs.sql:146: ERROR: parser: parse error at or near "return" psql:edit_procs.sql:147: ERROR: parser: parse error at or near "if" psql:edit_procs.sql:148: NOTICE: COMMIT: no transaction in progress COMMIT this is part of a larger script, but the function declaration before this works perfectly, so I assume the problem lies here. sorry for the length... TIA leo ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] order by day or month, etc
Hello first time I post something... good morning everyone! short presentation: Leo Xavier, Lisbon - Portugal, 17 years, my home-made site: www.megabenfica.com Sql7, win 2000... The question: How for example do I select all entrys from a certain month (of a certain year, of course) ? Or from a certain day? And how from a certain period, between day X and Y, i.e? The solution i found is to create three columns in the table: one with the day, a second with the month, a thir with the year... but this really is a little bit unprofessional ... doing this: SELECT to_char(field, 'DD/MM/') AS new_date as Francis Solomon said, didnt work... "unrecognized function" ... can anyone help me?? Leo Xavier
[SQL] Array of Arrays of int
Why does
select array(select array[1,2]);
give me an error: "could not find array type for data type integer[]"?
Is there a type-cast missing, or is this generally impossible? I was
expecting something like
{{1,2}}
In my real-world application, the subquery returns more than one pair
of ints, but I think it all boils down to this simpler example. I am
using version 7.4 of PostgreSQL.
Thanks a lot,
Leo
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Array of Arrays of int
Hello Tom. Am 02.05.2005 um 06:11 schrieb Tom Lane: Leo Fink <[EMAIL PROTECTED]> writes: Why does select array(select array[1,2]); give me an error: "could not find array type for data type integer[]"? We don't do arrays of arrays. You seem to be hoping for a 2-D array, which is something fundamentally different (even though some programming languages consider them the same ;-)) Actually, I don't. In my application I can work around the problem by using an array of string representations of these pairs, or by using two arrays of ints. I just thought that was less elegant and I was missing something obvious. Thanks for your quick response. Best, Leo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] error code invalid_input_syntax
I wrote a function which generates SQL statements for INSERTs and UPDATEs, in which I cast the values to the correct datatype. Now I want to catch ERROR: invalid input syntax for integer: "i" but although I tried quite a few I can't find the right error code for this exception. Is it possible that this error can not be caught? Thanks Leo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] uniqueness constraint with NULLs
On Mon, 2009-06-29 at 17:08 +1000, Robert Edwards wrote: > A. Kretschmer wrote: > > In response to Robert Edwards : > >> Can anyone suggest a way that I can impose uniqueness on a and b when > >> c is NULL? > > > > Sure, use a functional index: > > > > test=# create table bobtest (a int, b int, c int); > > CREATE TABLE > > test=*# create unique index idx_bobtest on > > bobtest(a,b,coalesce(c::text,'NULL')); > > CREATE INDEX > > test=*# insert into bobtest (a, b) values (1, 4); > > INSERT 0 1 > > test=*# insert into bobtest (a, b, c) values (1, 4, NULL); > > ERROR: duplicate key value violates unique constraint "idx_bobtest" > > test=!# > > Why changing the datatype of coulumn "c"? Will this not probably disable the use of the index? Why not only: lem=# create table bobtest (a int, b int, c int); CREATE TABLE lem=# create unique index idc_bobtest on bobtest (a, b, coalesce(c, 1)); CREATE INDEX lem=# insert into bobtest (a, b) values (1, 4); INSERT 0 1 lem=# insert into bobtest (a, b) values (1, 4); ERROR: duplicate key value violates unique constraint "idc_bobtest" lem=# insert into bobtest (a, b, c) values (1, 4, null); ERROR: duplicate key value violates unique constraint "idc_bobtest" lem=# > > > > Regards, Andreas > > Beautiful! > > Many thanks, > > Bob Edwards. > -- 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] two records per row from query
John wrote: > mytable > pkid > class_date. > sessionid > > select * from mytable > 1 2009/01/01 2101 > 2 2009/01/02 2101 > > I would like an SQL that would produce > > newtable > pkid, > class_date1, > class_date2, > sessionid1, > sessionid2 > > Select * from newtable > > 1 2009/01/01 2009/01/02 2101 2101 > > I have a list of classes that is perfect for our needs. However, I need to > create the second table (from a query) to feed to a report writer so it can > write out a single line of text for two records. > Like: > > Your class dates are as follows > >Date Date > 01/01/2009 01/02/2009 > 01/08/2009 01/10/2009 > 03/31/2009 04/05/2009 > and will continue until the all the classes are printed. > > The problem of course is the table has a row per class and the report writer > needs two class dates per row. > > I have no idea how to do this using SQL. > > Thanks in advance, > Johnf > Can you give a more precise example please? I don't get what you really need. What I understand is that you want 1 record back for each sessionid with the earliest and latest class_date. I've done the following: lem=# select * from mytable; pkid | class_date | sessionid --+-+--- 1 | 2009-01-01 00:00:00 | 2101 2 | 2009-01-02 00:00:00 | 2101 3 | 2009-01-01 00:00:00 | 2102 4 | 2009-01-02 00:00:00 | 2102 5 | 2009-01-01 00:00:00 | 2103 6 | 2009-01-02 00:00:00 | 2103 7 | 2009-01-03 00:00:00 | 2103 (7 rows) and then: lem=# select min(pkid) as pkid lem-# ,min(class_date) as class_date1 lem-# ,max(class_date) as class_date2 lem-# ,sessionid lem-# from mytable lem-# group by sessionid; pkid | class_date1 | class_date2 | sessionid --+-+-+--- 5 | 2009-01-01 00:00:00 | 2009-01-03 00:00:00 | 2103 3 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2102 1 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2101 (3 rows) Is this what you need or is there something else? Can you give more sample data and the result you expect from it? Cheers, Leo -- 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] two records per row from query
union all select null ,null ,v2.sessionid from ( select sessionid from ( select sessionid ,mod(count(*), 2) as extra_row from mytable group by sessionid ) v1 where v1.extra_row = 1 ) v2 ) v3 order by v3.sessionid, v3.class_date nulls last ) t1 ) t2 ; select r1.class_date as class_date1 ,r1.sessionid as sessionid1 ,r2.class_date as class_date2 ,case when r2.class_date is null then null else r2.sessionid end as sessionid2 from myreport r1 ,myreport r2 where r1.sessionid = r2.sessionid andr1.myrn = r2.myrn - 1 andr1.mycolcount = 1 order by r1.sessionid, r1.class_date ; rollback; Hope this helps or somebody else has a more elegant solution Cheers, Leo -- 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] Hibernate, web application and only one sequence for all primary keys
rawi wrote: > Hello! > > The subject says it... > > Grails/Hibernate wishes per default one sequence for all tables-PKs and all > PKs as BigInt. > > What would you think about a database with some tens of tables and > incidentally low to moderate insert concurrency spread in about the half of > the tables from at most 10 concurrent users with some 10-20 inserts each? Caveat: If you use the standard sequence generator in hibernate, it is not using the postgres-sequence in the "usual" manner. hibernate itself caches 50 ID's as sequence numbers by default. This means, hibernate only does a select on the database sequence every 50 numbers. it multyplies the database sequence by 50 to get the "real" sequence number. it generates the sequence numbers in blocks of 50 numbers or according to the sequence cache size. That said, you would probably not see any performance bottlenecks because of the sequence number generator in the database, even with thousands of inserts per second. > > And (for the eventuality of an unexpected need to scale up in the future - > e.g. integration of multiple databases), from about which size would you > expect performance penalties due to the sole sequence and the BigInt-PKs? > > I first intend to deploy it on an Intel Pentium 2 Duo (2.5-2.8GHz) with 3 GB > RAM and SATA hard disk under Ubuntu Server. > > Thank you very much in advance! > Regards > Rawi cheers, leo -- 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] Hibernate, web application and only one sequence for all primary keys
rawi wrote: > > Leo Mannhart wrote: >> Caveat: If you use the standard sequence generator in hibernate, it is >> not using the postgres-sequence in the "usual" manner. hibernate itself >> caches 50 ID's as sequence numbers by default. This means, hibernate >> only does a select on the database sequence every 50 numbers. it >> multyplies the database sequence by 50 to get the "real" sequence >> number. it generates the sequence numbers in blocks of 50 numbers or >> according to the sequence cache size. >> That said, you would probably not see any performance bottlenecks >> because of the sequence number generator in the database, even with >> thousands of inserts per second. >> > > Hi Leo, thank you for the explanation! > > I don't know if it is that cool to lose up to 50 IDs on each session-end of > Hibernate... What you mean "loose 50 IDs"? Sequences are never meant to be gap-free therefore you are not "loosing" IDs at all. OTOH are you saying, that one session is just inserting one row and then disconnects from the database? Then it would be somewhat a waste to use hibernate and all this caching mechanism, but I highly doubt this. Isn't your app running on a middle tier and hibernate will only be shutdown when the app server will shut down? Then there is no "loosing" of IDs either. > And what do you suppose it would happen, if I set the cache size of > Hibernate's own sequence (after generation) by hand to 1 instead of 50? I > wouldn't need tausends of inserts per second... Why should you do that? You want to know, how much is the network roundtrip adding to the response time? Just let it how it is; it is a good starting point. > > Kind regards, Rawi > -- 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] Fwd: conditional rule not applied
Seb wrote: > Hi, > > Apologies for posting this from postgresql.general, but this failed to > get any follow-ups in that NG. Hopefully someone here can shed some > light on this. [snip] I can give a 'first cut' solution. But I strongly discourage from doing this in a real world application as chances are big, that you'll forget something to implement correctly (nullable fields come to mind immediately as an example). Your example is also simplified as it makes no sense for instance, that sh_name is nullable... here we go: --- cut create or replace function footwear_upd( in p_sh_id_old integer ,in p_sh_name_old varchar ,in p_sh_avail_old integer ,in p_sl_name_old varchar ,in p_sh_id_new integer ,in p_sh_name_new varchar ,in p_sh_avail_new integer ,in p_sl_name_new varchar ) returns void as $$ declare l_anzinteger := 0; begin if p_sh_id_old <> p_sh_id_new then select count(*) into l_anz from shoelaces sl where sl.sh_id = p_sh_id_old; if l_anz > 0 then raise exception 'Cannot update shoes.sh_id referenced by shoelace.sh_id'; else raise notice 'updating sh_id in shoes (but this doesn''t make sense'; update shoes sh set sh.sh_id = p_sh_id_new where sh.sh_id = p_sh_id_old; end if; end if; if p_sh_name_old <> p_sh_name_new then update shoes sh set sh.sh_name = p_sh_name_new where sh.sh_id = p_sh_id_old; end if; if p_sh_avail_old <> p_sh_avail_new then update shoes sh set sh.sh_avail = p_sh_avail_new where sh.sh_id = p_sh_id_old; end if; if p_sl_name_old <> p_sl_name_new then update shoelaces sl set sl_name = p_sl_name_new where sl.sh_id = p_sh_id_new; end if; if p_sl_name_old is null and p_sl_name_new is not null then insert into shoelaces(sh_id, sl_name) values(p_sh_id_new, p_sl_name_new); end if; if p_sl_name_old is not null and p_sl_name_new is null then delete from shoelaces where sl_name = p_sl_name_old; end if; end; $$ language plpgsql; CREATE RULE footwear_newshoelaces_upd AS ON UPDATE TO footwear do instead select footwear_upd(old.sh_id, old.sh_name, old.sh_avail, old.sl_name, new.sh_id, new.sh_name, new.sh_avail, new.sl_name); --- cut this works nicely although the feedback is not really nice: lem=# SELECT * FROM footwear; sh_id | sh_name | sh_avail | sl_name ---+-+--+- 1 | sh1 |2 | sl1 2 | sh2 |0 | 3 | sh3 |4 | sl2 4 | sh4 |3 | (4 rows) lem=# UPDATE footwear SET sl_name = 'sl3' WHERE sh_name = 'sh2'; footwear_upd -- (1 row) UPDATE 0 lem=# SELECT * FROM footwear; sh_id | sh_name | sh_avail | sl_name ---+-+--+- 1 | sh1 |2 | sl1 2 | sh2 |0 | sl3 3 | sh3 |4 | sl2 4 | sh4 |3 | (4 rows) lem=# update footwear set sl_name=null where sh_name='sh2'; footwear_upd -- (1 row) UPDATE 0 lem=# SELECT * FROM footwear; sh_id | sh_name | sh_avail | sl_name ---+-+--+- 1 | sh1 |2 | sl1 2 | sh2 |0 | 3 | sh3 |4 | sl2 4 | sh4 |3 | (4 rows) lem=# UPDATE footwear SET sl_name='sl3' WHERE sh_name='sh2' OR sh_name='sh4'; footwear_upd -- (2 rows) UPDATE 0 lem=# SELECT * FROM footwear; sh_id | sh_name | sh_avail | sl_name ---+-+--+- 1 | sh1 |2 | sl1 2 | sh2 |0 | sl3 3 | sh3 |4 | sl2 4 | sh4 |3 | sl3 (4 rows) lem=# Cheers, Leo -- 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] combine SQL SELECT statements into one
msi77 wrote: > Hi, > > SELECT > (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') > as count1, > (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') > as count2, > (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01') > as count3 But this statement will seq scan the table inventory three times as an explain analyze easily will show, while the solution from Andreas will do only one seq scan. This can be a big difference, depending on the size of the table. > > Serge > >> Good Evening, Good Morning Wherever you are whenever you may be reading >> this. >> I am new to this email group and have some good experience with SQL and >> PostgreSQL database. >> I am currently working on a PHP / PostgreSQL project and I came upon >> something I could not figure out in SQL. I was wondering if anyone here >> could take a look and perhaps offer some guidance or assistance in helping >> me write this SQL query. >> Please Consider the following information: >> --- >> I have a postgresql table called 'inventory' that includes two fields: >> 'model' which is a character varying field and 'modified' which is a >> timestamp field. >> So the table inventory looks something like this: >> model modified >> --- >> I7782881762010-02-01 08:27:00 >> I778288176 2010-01-31 11:23:00 >> I778288176 2010-01-29 10:46:00 >> JKLM112345 2010-02-01 08:25:00 >> JKLM112345 2010-01-31 09:52:00 >> JKLM112345 2010-01-28 09:44:00 >> X22TUNM7652010-01-17 10:13:00 >> V8893456T6 2010-01-01 09:17:00 >> Now with the table, fields and data in mind look at the following three >> queries: >> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01'; >> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20'; >> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01'; >> All three of the above queries work and provide results. However, I want to >> combine the three into one SQL Statement that hits the database one time. >> How can I do this in one SQL Statement? Is it possible with sub select? >> Here is what result I am looking for from one SELECT statement using the >> data example from above: >> count1 | count2 | count3 >> --- >> 2 2 4 >> Can this be done with ONE SQL STATEMENT? touching the database only ONE >> time? >> Please let me know. >> Thanx> :) >> NEiL >> > > Здесь спама нет http://mail.yandex.ru/nospam/sign > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] problem with the Index
I have a problem with the index of 1 table.
I hava a table created :
CREATE TABLE "acucliart" (
"cod_pto" numeric(8,0) NOT NULL,
"cod_cli" varchar(9) NOT NULL,
"mes" numeric(2,0) NOT NULL,
"ano" numeric(4,0) NOT NULL,
"int_art" numeric(5,0) NOT NULL,
"cantidad" numeric(12,2),
"ven_siv_to" numeric(14,2),
"ven_civ_to" numeric(14,2),
"tic_siv_to" numeric(14,2),
"tic_civ_to" numeric(14,2),
"visitas" numeric(2,0),
"ult_vis" date,
"ven_cos" numeric(12,2),
"ven_ofe" numeric(12,2),
"cos_ofe" numeric(12,2),
CONSTRAINT "acucliart_pkey"
PRIMARY KEY ("cod_cli")
);
if i do this select:
explain select * from acucliart where cod_cli=1;
postgres use the index
NOTICE: QUERY PLAN:
Index Scan using cod_cli_ukey on acucliart (cost=0.00..4.82 rows=1
width=478)
and this select
explain select * from acucliart where cod_cli>1;
Postgres don't use the index:
NOTICE: QUERY PLAN:
Seq Scan on acucliart (cost=0.00..22.50 rows=333 width=478)
why?
tk
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] prosgrees + java + trasnacciones
Hola a todos! Intento hacer una aplicacion en java que va contra pgsql y me gustaria bloquear registros. Un jps llama a un metodo de una clase para modificar un registro. Lo primero que hace es una consulta del registro (aquí quiero bloquear el registro) y despues se muestra en el navegador el resultado, un usuario lo modifica, pulsa el botón de actualizar y lo actualiza, desbloqueo. He probado con select for update y lo hace bien, solo que si un usuario quiere consultarlo para modificar tambien el mismo registro, se queda esperando a que el otro acabe la actualización. Hay alguna manera de detectar que ese registro se está consultando en select for update para que no se quede esperando o hay alguna forma de hacerlo? Gracias por todos y un saludo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
