[SQL] PL/SQL trouble
Hi,
I really don't understand following PostgreSQL 7.2.3 behaviour:
$ psql mydb
mydb=> CREATE FUNCTION MONDAY(timestamp) RETURNS DATE AS '
DECLARE
var1 date;
BEGIN
select into var1 to_date($1::date-(case when extract(DOW from
timestamp $1) = 0 then 6 else (extract(DOW from timestamp $1)-1) end));
RETURN var1;
END'
language 'plpgsql';
CREATE
mydb=> select MONDAY('now'::timestamp);
NOTICE: Error occurred while executing PL/pgSQL function MONDAY
NOTICE: line 4 at select into variables
ERROR: parser: parse error at or near "$2"
mydb=> \q
But I've not inserted any $2 there.
I've rewritten the same function in other ways but I've got the same error.
I thank you in advance for any hints.
Bye, \fer
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Are sub-select error suppressed?
Greetings, I'm not sure what the correct behavior is here but the observed behavior seems "wrong" (or at least undesirable). I have a few tables and a view on one of the tables selecting entries that may be purged. My delete statement uses the view to delete data from one of the tables. Like so: delete from tab1 where id1 in ( select id from view1 ); Assume that the view doesn't have a field named "id". The select statement alone would cause an error. However, in this context it doesn't and the delete statement deletes everything from tab1. Is this a bug in PostgreSQL or an "As Designed" feature? Best Regards, sidster -- They who would sacrifice freedom for security will have neither. -Ben Franklin Working example (with comments) follows: I don't yet have access to a newer PostgreSQL build. begin; create table ttab_title ( title_id int4 primary key, can_deletebool ); create sequence tseq_title_id; insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); insert into ttab_title values ( nextval( 'tseq_title_id' ), true ); -- rm able insert into ttab_title values ( nextval( 'tseq_title_id' ), true ); -- rm able insert into ttab_title values ( nextval( 'tseq_title_id' ), true ); -- rm able insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); create view tview_title as select ttab_title.title_id as title_number from ttab_title where ttab_title.can_delete = true ; -- -- Notice the column/field rename from title_id to title_number create table ttab_title_selection ( title_id int4 references ttab_title( title_id ), ranking int4 -- some other fields ... ); create sequence tseq_title_rank; insert into ttab_title_selection select ttab_title.title_id, nextval( 'tseq_title_rank' ) from ttab_title; end; -- Now lets look at this delete statement. delete from ttab_title_selection where title_id in ( select title_id from tview_title ); -- -- Notice how instead of title_number we made the mistake and used -- title_id. -- -- We intended to only delete titles with ids: 3, 4 and 5 but this -- delete statement deletes all 9 titles! -- Drop statements for clean up /* drop tablettab_title_selection; drop sequence tseq_title_rank; drop view tview_title; drop sequence tseq_title_id; drop tablettab_title; */ -- -- Local variables: -- c-basic-offset: 2 -- indent-tabs-mode: nil -- End: -- -- ex: ai et sw=2 ts=2 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Are sub-select error suppressed?
On Tuesday 26 Nov 2002 9:43 am, patrick wrote: > Greetings, > > I'm not sure what the correct behavior is here but the observed > behavior seems "wrong" (or at least undesirable). > > I have a few tables and a view on one of the tables selecting > entries that may be purged. > > My delete statement uses the view to delete data from one of the > tables. Like so: > > delete from tab1 where id1 in ( select id from view1 ); > > Assume that the view doesn't have a field named "id". The select > statement alone would cause an error. However, in this context it > doesn't and the delete statement deletes everything from tab1. > > Is this a bug in PostgreSQL or an "As Designed" feature? Don't look right to me, and I still see it here in 7.2 and the 7.3 beta I've got (note - not most recent). I don't think it's in the subselect itself - what's happening is when you do DELETE FROM ttab_title_selection WHERE tsel_id IN (SELECT xxx FROM tview_title); the xxx is binding to the outside query (the DELETE). If you change your definition of ttab_title_selection to use tsel_id rather than title_id this will be clearer. You can get the same with ranking: DELETE FROM ttab_title_selection WHERE ranking IN (SELECT ranking FROM tview_title); I'm guessing it gets parsed down to: DELETE FROM ttab_title_selection WHERE ranking IN (ranking); which of course matches everything. -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PL/SQL trouble
On Tuesday 26 Nov 2002 8:56 am, Ferruccio Zamuner wrote: > CREATE FUNCTION MONDAY(timestamp) RETURNS DATE AS ' > DECLARE > var1 date; > BEGIN > select into var1 to_date($1::date-(case when extract(DOW from > timestamp $1) = 0 then 6 else (extract(DOW from timestamp $1)-1) end)); > RETURN var1; > END' > language 'plpgsql'; The problem is the to_date(...) - the value is already a date so there isn't a to_date that takes a date. You can also remove the timestamp casts: select into var1 ($1::date - (case when extract(DOW from $1) = 0 then 6 else (extract(DOW from $1) - 1 ) end ) ); If you put your function in a text file and create it with psql -f you can pinpoint errors more easily. In this case, the $2 was complaining about the second (expected) paramater to to_date I think. -- Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] PL/SQL trouble
> I really don't understand following PostgreSQL 7.2.3 behaviour:
>
> $ psql mydb
> mydb=> CREATE FUNCTION MONDAY(timestamp) RETURNS DATE AS '
> DECLARE
> var1 date;
> BEGIN
> select into var1 to_date($1::date-(case when extract(DOW from
> timestamp $1) = 0 then 6 else (extract(DOW from timestamp $1)-1)
end));
> RETURN var1;
> END'
> language 'plpgsql';
>
> CREATE
> mydb=> select MONDAY('now'::timestamp);
> NOTICE: Error occurred while executing PL/pgSQL function MONDAY
> NOTICE: line 4 at select into variables
> ERROR: parser: parse error at or near "$2"
> mydb=> \q
>
> But I've not inserted any $2 there.
> I've rewritten the same function in other ways but I've got the same
error.
>
Something like the following works (as Richard already pointed out):
CREATE OR REPLACE FUNCTION MONDAY(timestamp) RETURNS DATE AS '
DECLARE
ts_paramALIAS FOR $1;
var1 date;
BEGIN
select into var1 to_date(ts_param::date-
(case when extract(DOW from ts_param) = 0
then 6 else (extract(DOW from ts_param)-1) end),''DD'');
RETURN var1;
END'
language 'plpgsql';
Me personally would prefer another approach:
CREATE OR REPLACE FUNCTION MONDAY(timestamp) RETURNS DATE AS '
DECLARE
ts_paramALIAS FOR $1;
var1 date;
var2 double precision;
BEGIN
var2 := extract(DOW from ts_param);
IF var2 = 0 THEN
var2 := 6;
ELSE
var2 := var2 - 1;
END IF;
var1 := to_date(ts_param::date - var2,''DD'');
RETURN var1;
END'
language 'plpgsql';
because it's easier to read, but that's only a matter of taste I
suppose.
Regards, Christoph
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Inheritance in SQL99 ?
Hello, is the inheritance of tables specified in the SQL99 standard, or is this a postgresql "add-on" ? Does anybody know when the primary key bug, which is documented in the docs, of this feature will be fixed ? Thx berger ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Two TIMESTAMPs in one pl/sql function
Hi, I'm trying to time a pl/sql function using a rougn and ready method, basically: print a TIMESTAMP at the begining of the function, print a TIMESTAMP at the end of the function. So...: CREATE OR REPLACE FUNCTION timer() RETURNS INTEGER AS ' DECLARE timer1 TIMESTAMP; timer2 TIMESTAMP; num_operators INTEGER; BEGIN timer1 := ''now''; -- As suggested in 23.4 of programmer guide RAISE NOTICE ''Start: %'', timer1; /* Some function which takes time. Here, a select from a pg catalogue */ SELECT INTO num_operators COUNT(*) FROM pg_operator; timer2 := ''now''; RAISE NOTICE ''End: %'', timer2; RETURN(num_operators); END;' LANGUAGE 'plpgsql'; Gives me: testdb2=# select timer(); NOTICE: Start: 2002-11-26 13:40:14.116605+00 NOTICE: End: 2002-11-26 13:40:14.116605+00 timer --- 623 (1 row) I've tried all sorts of variations but I hit one of two problems; either the TIMESTAMP is fixed to the function compile time or, as above, the timer1 and timer2 TIMESTAMPs are always identical. Any help/pointers/suggestions appreciate... well of course a working solution would be the best ;) Cheers, Stuart. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Date trunc in UTC
Hi Richard
Ok, I'll do my best to explain clearer ;)
I have to make some monthly reports about some service requests
activity. So, I'm keeping in a table the monthly traffic.
TABLE traffic
+-+++
| service | month | visits |
+-+++
| chat| 2002-11-01 00:00:00+01 | 37002 |
| video | 2002-11-01 00:00:00+01 | 186354 |
| chat| 2002-10-01 00:00:00+01 | 41246 |
| video | 2002-10-01 00:00:00+01 | 86235 |
So, when I have a new visit on any service, I increase the counter for
that month. The problems are:
- As you see, the month includes timezone information (+01), which
corresponds to the CET beggining of the month.
- Whenever a new month starts, I have to create a new entry in the table.
I have done a plpgsql procedure 'increase_counter' that increases the
counter 'visits = visits + 1' every time it gets called. But, I have to
check if I went into the next month, so basically I do
UPDATE traffic SET visits = visits + 1 WHERE service = 'chat' AND
month = DATE_TRUNC (''month'', ''now''::timestamp);
If there was no row updated, then I create the new entry as
INSERT INTO traffic VALUES
('chat', DATE_TRUNC (''month'', ''now''::timestamp), 1);
So, as I can see in the traffic table, the DATE_TRUNC is, in fact,
equivalent to
2002-11-01 00:00:00+01 (CET) == 2002-09-30 23:00:00+00 (UTC)
If we think that I will work in an international environment, I would
rather to have in the table as the result of the DATE_TRUNC the right
UTC value, so, the right begginning of the month in UTC.
2002-11-01 00:00:00+00 (UTC)
So, if I'm working in the CET timezone, what I would like to see is
2002-11-01 01:00:00+01 (CET)
Or, if I'm working with another time zone,
2002-10-31 16:00:00-08 (dunno timezone name)
TABLE traffic
+-+++
| service | month | visits |
+-+++
| chat| 2002-11-01 01:00:00+01 | 37002 |
| video | 2002-11-01 01:00:00+01 | 186354 |
| chat| 2002-10-01 01:00:00+01 | 41246 |
| video | 2002-10-01 01:00:00+01 | 86235 |
In fact, DATE_TRUNC is returning the beggining of the month FOR THE
WORKING TIME ZONE, but I need to know, in my timezone, what is the
begginning of the UTC month.
Another more problem is that if I set the time zone in the session, I'm
not able to recover to its previous state. In plpgsql,
client preferences -> SET TIME ZONE 'PST8PDT';
... calling to my wrapper function
CREATE FUNCTION date_trunc_utc (TEXT, TIMESTAMP) RETURN TIMESTAMP AS '
DECLARE
st_month TIMESTAMP;
BEGIN
SET TIME ZONE ''UTC'';
st_month = DATE_TRUNC ($1, $2);
RESET TIME ZONE;
END
' LANGUAGE 'plpgsql';
-> SHOW TIME ZONE
NOTICE: Time zone is 'CET'
so basically, I cannot change to UTC because I'm not able no more to
recover to the client timezone preferences.
I hope I explained well ;)
Thanks for everything
Richard Huxton wrote:
On Wednesday 20 Nov 2002 10:37 am, Thrasher wrote:
No I cannot use SET TIME ZONE.
SET TIME ZONE will be set by any client backend. But what I want to get
is that DATE_TRUNC('month', ) = DATE_TRUNC('month',
).
Sorry, I've obviously misunderstood. Are you just looking to discard the
timezone so they look the same?
select date_trunc('month', CAST(CURRENT_TIMESTAMP AS timestamp without time
zone));
date_trunc
-
2002-11-01 00:00:00
I'd have thought that would give you some problems around local/utc midnight
on the first of the month.
Or is it that you want to know what time it was in UTC zone at the start of
the month local time?
If I'm still being a bit slow (quite likely) can you explain what you're using
this for?
=# select date_trunc ('month', now ());
date_trunc
2002-11-01 00:00:00+01
Instead, I would like to have as a result
2002-11-01 01:00:00+01
which is correct, but I cannot set the whole server to UTC. Any way to
get this ?
--
Juan A. FERNANDEZ-REBOLLOS - [EMAIL PROTECTED]
Mobile Dept.
_
ELECTRONIC GROUP INTERACTIVE - www.electronic-group.com
World Trade Center, Moll de BARCELONA
Edificio Norte 4 Planta
08039 BARCELONA SPAIN
Tel : +34 93600 23 23 Fax : +34 93600 23 10
_
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems invoking psql. Help please.
Thank you Tom Lane and Oliver Elphick. Here is the latest shell dialogue. postgres@biko:/home/hesco$ ./usr/lib/postgresql/bin/psql -d template1 sh: ./usr/lib/postgresql/bin/psql: No such file or directory postgres@biko:/home/hesco$ /usr/lib/postgresql/bin/psql -d template1 sh: /usr/lib/postgresql/bin/psql: No such file or directory postgres@biko:/home/hesco$ cd /usr/lib/postgresql/bin postgres@biko:/usr/lib/postgresql/bin$ ls -al | grep psql postgres@biko:/usr/lib/postgresql/bin$ Returned a blank prompt. My copy of psql is in /usr/bin. Is that a problem? Its where apt-get install postgresql put it. Should I move it? Someone earlier suggested building this from source, which I guess would allow me to do that. I don't know. postgres@biko:/usr/lib/postgresql/bin$ ./usr/bin/psql -d template1 sh: ./usr/bin/psql: No such file or directory postgres@biko:/usr/lib/postgresql/bin$ /usr/bin/psql -d template1 Could not execv /usr/lib/postgresql/bin/psql postgres@biko:/usr/lib/postgresql/bin$ Same error again. postgres@biko:/usr/lib/postgresql/bin$ cd /usr/bin postgres@biko:/usr/bin$ ls -al | grep psql lrwxrwxrwx1 root root 10 Oct 10 16:24 psql -> pg_wrapper postgres@biko:/usr/bin$ ls -al | grep pg_wrapper -rwxr-xr-x1 root root 6584 Sep 11 04:30 pg_wrapper lrwxrwxrwx1 root root 10 Oct 10 16:24 psql -> pg_wrapper postgres@biko:/usr/bin$ So, no. to answer Mr. Elphick's question below. I can not run it directly. I'm not sure what I would do with /sbin/ldconfig to let it know where /usr/lib/libpq.so.2 is located at. My attempts to run it return a blank shell prompt. I assume that Mr. Elphick's demonstration of the error generated by renaming the file shows there is no cheese down that hole, anyway. -- Hugh Esco At 02:46 PM 11/20/02 +, Oliver Elphick wrote: On Wed, 2002-11-20 at 14:23, Hugh Esco wrote: > Everything in the path is executable for others. > That is true for: > /usr/lib/postgresql/bin > and for: > /usr/bin > where psql is located. So can you run the executable directly? /usr/lib/postgresql/bin/psql -d template1 -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land." II Chronicles 7:14 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] can i decrease the query time?
i created index already. how can i decrease the query time more. number of record is over 1 million. the following is the query plan. Group (cost=34082.59..34085.62 rows=61 width=112) -> Sort (cost=34082.59..34082.59 rows=607 width=112) -> Nested Loop (cost=0.00..34054.51 rows=607 width=112) -> Nested Loop (cost=0.00..125.64 rows=2 width=108) -> Nested Loop (cost=0.00..8.84 rows=1 width=22) -> Index Scan using tbl_member_pkey on tbl_member d (cost=0.00..5.14 rows=1 width=18) -> Index Scan using tbl_company_pkey on tbl_company c (cost=0.00..3.68 rows=1 width=4) -> Index Scan using tbl_adpage_pkey on tbl_adpage b (cost=0.00..112.65 rows=332 width=86) -> Index Scan using tbl_showlog_adpage_id_idx on tbl_showlog a (cost=0.00..20370.75 rows=5466 width=4) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] select for update
I'm pretty new to databases in general, and would like to find a spiffy way to do something. I want to use two columns from one table to populate three columns in two other tables. Assuming t1 is the table I want to take the values from, here is the structure of what I want to insert into t2 and t3. t2.id = t1.id t2.groupname = t1.username t2.owner = t1.username t3.id = t3.groupid = t1.id t3.username = t1.username t3.writeperms = 31 PS - I'm not subscribed to the list, so please CC my email with responses. -- ; Justin Georgeson ; http://www.lopht.net ; mailto:[EMAIL PROTECTED] ; "Free the mallocs, delete the news" ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] retrieving specific info. from one column and locating it in another
I'm trying to retrieve some info from one column and put it in another. I have a column that has a bunch of information in it called 'Route'. I don't need to show all of that information. Instead I need divide that single column into two seperate columns called 'Sender' and 'Receiver'. How do I divide this information up into these two columns. I know of methods called charindex and patindex. I need to do something like that but instead of returning the position of the info, to just return the selected info. Ex) I have a column named Routewith info in it similar to 'UPS NS Ground' How do I create a second column called 'Delivery' and pull only the 'NS' out of the Route column and put it into the 'Reciever' column? Similarly how would I pull just the UPS part out of Route and put it into 'Sender'? thanks, Marc __ Do you Yahoo!? Yahoo! Mail Plus Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] escape single quote in INSERT command
Hi Hunter, From my xbase++ program suite, sqlcontext class. * cValue := strtran(cValue,['],[\']) * Its called masquarading, simply replace the single quote with back_slash + single quote. Regards Mark Carew Brisbane Australia ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] escape single quote in INSERT command
Hi Group - I have a perl application for a registration form. I'd like to put escape characters in my insert command to accommodate for ' (i.e. O'Brien, O'Malley, etc). I've tired double quotes, single quotes, back tick, forward ticks, curly bracket, round brackets - no success. Thanks, dave ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] escape single quote in INSERT command
Woops should have been masquerading ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] join question
Hi all.
I have a table with members and a table with payments. Each payment is
related to a member by memberID and each payment has (among other things) a
year paid.
I can create a join to find a list of members who have paid for a given year
(2002 in this case):
select member.memberID,member.name from member, payment where
payment.memberID = member.memberID and payment.yearPaid = 2002
I would like to be able to get a list of members who have not paid for a
given year.
I would also like to combine the two criteria, for example to generate a list
of members who have paid for 2002 but not 2003.
Thanks in advance!
--->Nathan
---
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
---
Nathan Young
N. C. Young Design
(530)629-4176
http://ncyoung.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] help on sql query
Suppose you have a table T: -- A B -- 1 '111' 2 '222' -- How do you select A and B of the record with A maximum? The desirable result therefore is (2, '222') The two variants I could grow with are: 1) SELECT A, B FROM T ORDER BY A DESC LIMIT 1 2) SELECT A, B FROM T WHERE A IN (SELECT MAX(A) FROM T) What do yo think of the queries above? And give me the better implementation if possible. __ Dmitry Vitalievitch ICQ#: 1108 Current ICQ status: + More ways to contact me __ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL query help!
Hello! I hope that someone here could help. I'm using PostgreSQL7.1.3 I have 3 tables in my DB: the tables are defined in the following way: CREATE TABLE category( id SERIAL NOT NULL PRIMARY KEY, // etc etc ) ; CREATE TABLE subcategory( id SERIAL NOT NULL PRIMARY KEY, categoryid int CONSTRAINT subcategory__ref_category REFERENCES category (id) // etc etc ) ; CREATE TABLE entry( entryid SERIAL NOT NULL PRIMARY KEY, isapproved CHAR(1) NOT NULL DEFAULT 'n', subcategoryid int CONSTRAINT entry__ref_subcategory REFERENCES subcategory (id) // atd , ) ; I have the following SQL query : "SELECT * FROM entry where isapproved='y' AND subcategoryid IN (SELECT id FROM subcategory WHERE categoryid='"+catID+"') ORDER BY subcategoryid DESC"; For a given categoryid( catID), the query will return all entries in the "entry" table having a corresponding subcategoryid(s)[returned by the inned subquery]. But I want to return only a limited number of entries of each subcategory. let's say that I want to return at most 5 entries of each subcategory type ( for instance if the inner subquery returns 3 results, thus I will be having in total at most 15 entries as relust) How can this be achieved? I'm aware of postgreSQL "LIMIT" and "GROUP BY" clause. but so far, I'm not able to put all this together... Thanks in advance. Arcadius. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] copy from command - quotes and header lines
a) First an observation about populating
tables.
The standard syntax for literal values is to put
single quotes around all the strings and not around the numbers eg
insert into table values ('London', 12,
15.7);
However when bulk loading from files it needs an
extra step to find out which fields to put the quotes around. By
experimenting, I have found that the INSERT command will allow quotes around all
the fields eg
insert into table values ('London', '12', '15.7');
but not the converse
insert into table values (London, 12,
15.7);
On the other hand the COPY FROM file command has it
the other way round - if the separators are semi-colons then the source
file
London;12;15.7
Paris;13;22.2
will work
but
'London'; '12'; '15.7'
'Paris';'13';'22.2'
will not!
Any comments? If this behaviour works it is
certainly convenient as well as surprising, provided one knows what to
do!
b) My source files for populating the database
tables have three header lines. It would be convenient if the COPY FROM
command had another parameter eg "HEADERS n" meaning skip first n lines of input
file. Assuming I have not missed something in the manual, would it be
possible to request an enhancement along these lines? The R
statistical package allows one to skip n header lines so this must be a common
situation. My current workaround uses a perl pipe but I would prefer
a cleaner solution.
Regards
Kenneth Evans
[SQL] LIMIT and/or GROUP BY help!
Hello! I hope that someone here could help. I'm using PostgreSQL7.1.3 I have 3 tables: entry, subcategory and category. The table "entry" has a foreign key "subcategoryid" with reference to the table "subcategory", and the "subcategory" table has a foreign key "categoryid" with reference to the table "category" I have the following SQL query : "SELECT * FROM entry where isapproved='y' AND subcategoryid IN (SELECT id FROM subcategory WHERE categoryid='"+catID+"') ORDER BY subcategoryid DESC"; For a given categoryid, this will return all entries in the "entry" table having a corresponding subcategoryid(s). But I want to return only a limited number of entries of each subcategory. let's say that I want to return at most 5 entries of each subcategory type ( for instance if the inner subquery returns 3 results, thus I will be having in total at most 15 entries ) How can this be achieved? I'm aware of postgreSQL "LIMIT" and "GROUP BY" clause. but so far, I'm not able to put all this together... Thanks in advance. Arcadius. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How to recover Data
I had a m/c with Postgres 7.2 and huge amount of data. on Power failure and restart of the m/c pgsql refused connect to any of the database. Being an invoice i took a tar of the data dir. I tried reinstalling PGSQL and copied the data dir to the same dir where the new data is being stored. ie cp /backup/pgsql/data /var/lib/pgsql/data When i connect to the old database i am able to connect but when i do a \d to list the tables i get no relations found. Also when i do select * from a table i am able to get the structure but it has no records in it. WHen i check the same info in pg_database to check if the old database entry is present i dont find it. Can someone help me as to how to recover my data??? Basically what i feel is update the pg_tables. Thanks and rgds Padmanab ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] How does postgres handle non literal string values
Hi All,
I have some jsp code that should insert a user name and password into
a table called login.
Instead of inserting the values given by the client, it insert the
literal string 'username' and 'password. The problem is somewhere in
the INSERT statement.
Here is the code:
<%@page contentType="text/html"%>
<%@page import="java.io.*" %>
<%@page import="java.sql.*" %>
<%@page import="java.util.*" %>
JSP login
<%-- --%>
<%-- --%>
<%
String username = request.getParameter("username");
String password = request.getParameter("password");
String confirmpw = request.getParameter("password2");
String dbName = "storedb";
Connection conn = null;
Statement stmt = null;
String usr = "postgres";
String passwd = "Wimdk12";
if (username != null)
username = username.trim();
if (password != null)
password = password.trim();
if(confirmpw != null)
confirmpw = confirmpw.trim();
if (username != null &&
username.length() > 0) {
if (password != null &&
password.length() > 0) {
if (confirmpw != null &&
confirmpw.length() > 0) {
if (password.equals(confirmpw)) {
%>
Loading the driver
<%
String url = "jdbc:postgresql:" + dbName;
// Load the driver
Class.forName("org.postgresql.Driver");
// Connect to database
conn = DriverManager.getConnection(url, usr,
passwd);
stmt = conn.createStatement();
%>
Connecting to the data base
<%
String insertString =
"INSERT INTO \"login\" (\'user\', \'password\')
VALUES ('username', 'password')";
%>
Updating table
<%
stmt.executeUpdate(insertString);
%>
Checking result
<%
ResultSet rset = stmt.executeQuery("SELECT *
FROM login");
while (rset.next()) {
System.out.println(
rset.getString("user") + ":" +
rset.getString("password"));
}
%>
Closing connection
<%
rset.close();
stmt.close();
conn.close();
%>
Congratulations <%= username %>! your account has been created
<%
} else { %>
Sorry! Account not created. passwords do
not match
<%
}
} else { %> Sorry! Account not
created. passwords do not match
<%
}
} else { %>
Sorry! Account not created. Please enter a
confirmation password
<%
}
} else { %>
Sorry! Account not created. Please enter a
password
<%
}
} else { %>
Sorry! Account not created. Please enter a
username
<%
} %>
Any help on this is greatly appreciated.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Problems invoking psql. Help please.
> Here are the results from reversing the arguments. > > >hesco@biko:~$ su postgres > >Password: > >postgres@biko:/home/hesco$ cd > >postgres@biko:~$ cd /usr/bin > >postgres@biko:/usr/bin$ psql tempate1 -U postgres > >Could not execv /usr/lib/postgresql/bin/psql > >postgres@biko:/usr/bin$ psql template1 -U postgres > >Could not execv /usr/lib/postgresql/bin/psql > >postgres@biko:/usr/bin$ Check the permissions. Psql is only a symbolic link to pg_wrapper. You should have: ls -al /usr/bin/pg_wrapper -rwxr-xr-x1 root root 6584 sie 25 23:55 /usr/bin/pg_wrapper > If I compile from source, will the apt-get database know what I've > done? Or will I have to do the updates from source as well? No. If you want to create package .deb from your sources, look at debian packages source site. There is special debian patch in postgres directory. Apply it, compile your sources and create .deb package. The last step is to install this package with dpkg. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SQL -select count-
when I do : select count(column_name) from table_name should I get the count for all columns or just those which are not null? __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How does postgres handle non literal string values
Title: RE: [SQL] How does postgres handle non literal string values
I'm guessing it would have to be more like:
<%
String insertString =
"INSERT INTO \"login\" (\'user\', \'password\')
VALUES ('"+username+"', '"+password+"')";
%>
to actually concatonate a string including the username & password variables, however I've not really used Java much so you might want to ask on the [EMAIL PROTECTED] list.
hth,
- Stuart
[EMAIL PROTECTED] wrote:
> Hi All,
>
> I have some jsp code that should insert a user name and password into
> a table called login.
>
> Instead of inserting the values given by the client, it insert the
> literal string 'username' and 'password. The problem is somewhere in
> the INSERT statement.
>
> Here is the code:
>
> <%@page contentType="text/html"%>
> <%@page import="java.io.*" %>
> <%@page import="java.sql.*" %>
> <%@page import="java.util.*" %>
>
>
> JSP login
>
>
> <%--
> class="package.class" /> --%> <%--
> name="beanInstanceName" property="propertyName" /> --%>
>
> <%
> String username = request.getParameter("username");
> String password = request.getParameter("password");
> String confirmpw = request.getParameter("password2"); String dbName =
> "storedb";
>
>
> Connection conn = null;
> Statement stmt = null;
>
>
> String usr = "postgres";
> String passwd = "Wimdk12";
>
> if (username != null)
> username = username.trim();
> if (password != null)
> password = password.trim();
> if(confirmpw != null)
> confirmpw = confirmpw.trim();
> if (username != null &&
> username.length() > 0) {
> if (password != null &&
> password.length() > 0) {
> if (confirmpw != null &&
> confirmpw.length() > 0) {
> if (password.equals(confirmpw)) {
> %>
> Loading the driver <%
> String url = "" + dbName;
>
> // Load the driver
> Class.forName("org.postgresql.Driver");
> // Connect to database
> conn = DriverManager.getConnection(url, usr,
> passwd); stmt = conn.createStatement(); %>
>
> Connecting to the data base <%
> String insertString =
> "INSERT INTO \"login\" (\'user\',
> \'password\') VALUES ('username', 'password')";
> %>
>
> Updating table
> <%
> stmt.executeUpdate(insertString);
>
> %>
> Checking result
> <%
> ResultSet rset = stmt.executeQuery("SELECT *
> FROM login");
>
>
> while (rset.next()) {
> System.out.println(
> rset.getString("user") + ":" +
> rset.getString("password"));
> } %>
> Closing connection <%
> rset.close();
> stmt.close();
> conn.close();
>
> %>
> Congratulations <%= username %>! your account has been created
>
>
> <%
> } else { %>
> Sorry! Account not created. passwords do
> not match <%
> }
> } else { %> Sorry! Account not
> created. passwords do not match
> <%
> }
> } else { %>
> Sorry! Account not created. Please enter a
> confirmation password <%
> }
> } else { %>
> Sorry! Account not created. Please enter a
> password <%
> }
> } else { %>
> Sorry! Account not created. Please enter a
> username <%
> } %>
>
>
>
Re: [SQL] subselect instead of a view...
On 26 Nov 2002 at 0:29, Tom Lane wrote: > "Dan Langille" <[EMAIL PROTECTED]> writes: > > SELECT E.name, EP.pathname, E.id, EP2.pathname, CL.id > > ... > > and EP2.pathname like EP.pathname || '/%' > > > I am still suspicous of that like. It seems to be the performance > > killer here. There is an index which can be used: > > It won't be, though. The LIKE-to-indexscan transformation happens at > plan time, and that means it can only happen if the pattern is a > constant. Which it surely will not be in your example. Thanks. I'll see if I can come up with something else to do this. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Two TIMESTAMPs in one pl/sql function
On Tuesday 26 Nov 2002 1:54 pm, Rison, Stuart wrote: > Hi, > > I'm trying to time a pl/sql function using a rougn and ready method, > basically: print a TIMESTAMP at the begining of the function, print a > TIMESTAMP at the end of the function. [snip] > Gives me: > > testdb2=# select timer(); > NOTICE: Start: 2002-11-26 13:40:14.116605+00 > NOTICE: End: 2002-11-26 13:40:14.116605+00 > I've tried all sorts of variations but I hit one of two problems; either > the TIMESTAMP is fixed to the function compile time or, as above, the > timer1 and timer2 TIMESTAMPs are always identical. Try timeofday() not now(). Quite often you want the time to stay fixed for the length of a transaction (what now() does). In this case you don't - see the Functions : date/time section of the manual for details. -- Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] help on sql query
On Tuesday 26 Nov 2002 1:14 pm, Zuev Dmitry wrote: > Suppose you have a table T: > > A B > 1 '111' > 2 '222' > > How do you select A and B of the record with A maximum? > 1) SELECT A, B FROM T ORDER BY A DESC LIMIT 1 > > 2) SELECT A, B FROM T WHERE A IN (SELECT MAX(A) FROM T) > > What do yo think of the queries above? And give me the better > implementation if possible. If you have an index on A the first option will be faster. All PG has to do in this case is check the end of the index and fetch one row. -- Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL -select count-
On Tuesday 19 Nov 2002 5:06 pm, Giannis wrote: > when I do : > > select count(column_name) from table_name > > should I get the count for all columns or just those which are not null? Just "not null" - use count(*) or count(0) for a count of rows. -- Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How does postgres handle non literal string values
On Monday 25 Nov 2002 12:57 pm, javaholic wrote:
> Hi All,
>
> I have some jsp code that should insert a user name and password into
> a table called login.
>
> Instead of inserting the values given by the client, it insert the
> literal string 'username' and 'password. The problem is somewhere in
> the INSERT statement.
> String insertString =
> "INSERT INTO \"login\" (\'user\', \'password\')
> VALUES ('username', 'password')";
You're creating an insertString with the literal words "username" and
"password" in them. This is a java issue, not a PG issue. You'll want
something like (sorry if syntax is wrong, but I don't do Java):
String insertString = "Insert INTO ... VALUES ('" + sql_escaped(username) +
"')..."
You *will* want to escape the username and password otherwise I'll be able to
come along and insert any values I like into your database. I can't believe
the JDBC classes don't provide
1. Some way to escape value strings
2. Some form of placeholders to deal with this
--
Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Are sub-select error suppressed?
On Tue, 26 Nov 2002, Richard Huxton wrote: > On Tuesday 26 Nov 2002 9:43 am, patrick wrote: > > Greetings, > > > > I'm not sure what the correct behavior is here but the observed > > behavior seems "wrong" (or at least undesirable). > > > > I have a few tables and a view on one of the tables selecting > > entries that may be purged. > > > > My delete statement uses the view to delete data from one of the > > tables. Like so: > > > > delete from tab1 where id1 in ( select id from view1 ); > > > > Assume that the view doesn't have a field named "id". The select > > statement alone would cause an error. However, in this context it > > doesn't and the delete statement deletes everything from tab1. > > > > Is this a bug in PostgreSQL or an "As Designed" feature? > > Don't look right to me, and I still see it here in 7.2 and the 7.3 beta I've > got (note - not most recent). I don't think it's in the subselect itself - > what's happening is when you do I think it's standard behavior. The column reference is an outer reference I believe, IIRC all the names from the outer query are in scope in the subselect (although if there's an equivalent name in the subselect from tables you'd have to qualify it). ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] can i decrease the query time?
On 20 Nov 2002, [EMAIL PROTECTED] wrote: > i created index already. how can i decrease the query time more. > > number of record is over 1 million. > the following is the query plan. > > Group (cost=34082.59..34085.62 rows=61 width=112) > -> Sort (cost=34082.59..34082.59 rows=607 width=112) > -> Nested Loop (cost=0.00..34054.51 rows=607 width=112) > -> Nested Loop (cost=0.00..125.64 rows=2 width=108) > -> Nested Loop (cost=0.00..8.84 rows=1 width=22) > -> Index Scan using tbl_member_pkey on tbl_member d (cost=0.00..5.14 > rows=1 width=18) > -> Index Scan using tbl_company_pkey on tbl_company c (cost=0.00..3.68 > rows=1 width=4) > -> Index Scan using tbl_adpage_pkey on tbl_adpage b (cost=0.00..112.65 > rows=332 width=86) > -> Index Scan using tbl_showlog_adpage_id_idx on tbl_showlog a > (cost=0.00..20370.75 rows=5466 width=4) What's the actual query and the table layout for the tables in question and if you're running 7.2, what does explain analyze show? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to recover Data
Sridhar such questions shud be posted to pgsql-admin list. generally in case of power failure pgsql is unable to remove its pid file., if u are using RPM based installation the file shud be in /var/lib/pgsql/ in the name of postmaster.pid If such a file exists postmaster will refuse to start. in such case first remove that file. then start postmaster using # /etc/rc.d/init.d/postgresql start or better # su - postgres $ pg_ctl -l logfile -D /var/lib/pgsql/data observe the recovery messages in logfile using $tail -f logfile in general such recoveries mostly succeeds and in no case shud be inturrupted to avoid further (grave) complications. Good Luck with your data, regds mallah. PS: its not easy to loose data with pgsql ;-) > I had a m/c with Postgres 7.2 and huge amount of data. on Power > failure and restart of the m/c pgsql refused connect to any of the database. Being >an invoice i > took a tar of the data dir. > I tried reinstalling PGSQL and copied the data dir to the same dir where the new >data is being > stored. ie > cp /backup/pgsql/data /var/lib/pgsql/data Only data directory is not sufficeint u need the pg_xlog directory as well. In any case such backups in prociple are not ok unless postmaster is shut down during the cp. > > When i connect to the old database i am able to connect but when i do a \d to list >the tables i > get no relations found. Also when i do > select * from a table i am able to get the structure but it has no records in it. > WHen i check the same info in pg_database to check if the old database entry is >present i dont > find it. > Can someone help me as to how to recover my data??? > Basically what i feel is update the pg_tables. > Thanks and rgds > Padmanab > > ---(end of broadcast)--- TIP 6: Have >you > searched our list archives? > > http://archives.postgresql.org - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] celko nested set functions -- tree move
I think you should take a closer look at Greg's function. It is uses lfts as parameters in the function mainly just to make the function implementation independent; I was able to easily adapt it to my schema which uses unique id's for each object in the tree hierarchy. After looking your function over, I also have some concerns about moving children to new parents with lft and rgt smaller than the child, because the math is different depending on which way your moving in the tree. It's possible that your use of treeid's and universe id's makes up for this, though it's a little hard to discern without seeing the schema, perhaps you can post schema and some test data? I'm also curious how many nodes you have in your tree, and at how many levels. It seems like your function would have performance issues over large trees since it requires 3 select statements, 3 updates, and a lock table. Compare this with Greg's function which requires 2 selects and 1 update, with no lock. As a final note, you might want to rewrite your select statements like: SELECT rgt, universeid, treeid FROM list_objects WHERE objid_auto=t_newparent INTO newparentrgt, newparentuid, newparenttid; I think it's more readable and probably a little more efficient since you are doing less variable assignment. Robert Treat On Tue, 2002-11-26 at 00:13, Martin Crundall wrote: > I'm not sure that keying off lft is safe in a multi-user environment. I > opted to create and use an objid on the tree definition table, since its > identity is static. I also found that when trees get active, allowing for > tree IDs increased operation speed quite a bit (i actually push this to > two levels--a 'universe id' and then a 'tree id'). Here's my version. > Clearly not as elegantly written, but nothing's gone awry yet. > > -- > --- > --Title: trackmyproject_tree_move() > -- Function: moves a tree branch in the hierarchy from one parent to > -- another. > --parms: srcobj the branch/object to be moved > -- newparentthe new parent for the object to be moved > -- Returns: zero > -- > --- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] select for update
On Wed, 20 Nov 2002, Justin Georgeson wrote: > I'm pretty new to databases in general, and would like to find a spiffy > way to do something. I want to use two columns from one table to > populate three columns in two other tables. Assuming t1 is the table I > want to take the values from, here is the structure of what I want to > insert into t2 and t3. > > t2.id = t1.id > t2.groupname = t1.username > t2.owner = t1.username > > t3.id = > t3.groupid = t1.id > t3.username = t1.username > t3.writeperms = 31 If you're trying to populate the entire table and t3.id is a serial, I think you could do: insert into t2 (id, groupname, owner) select id, groupname, username from t1; insert into t3 (groupid, username, writeperms) select id, username, 31 from t1; If you mean that on inserts to t1 you want to make rows in the other tables then you probably want a simple trigger function like (untested): create function t1_make_t2_and_t3() returns OPAQUE as ' BEGIN INSERT INTO t2 (id, groupname, owner) values (NEW.id, NEW.username, NEW.username); INSERT INTO t3 (groupid, username, writeperms) values (NEW.id, NEW.username, 31); return NEW; END;' language 'plpgsql'; create trigger t1_make_t2_and_t3_trig after insert on t1 for each row execute procedure t1_make_t2_and_t3(); ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How does postgres handle non literal string values
Actually, we use JDBC Prepared Statements for this type of work. You put a query with '?' in as placeholders and then add in the values and the library takes care of the encoding issues. This avoids the double encoding of (encode X as String, decode string and encode as SQL X on the line). There was a good article about a framework that did this in JavaReport about a 18 months ago. We have gleaned some ideas from that article to create a framework around using PreparedStatements as the primary interface to the database. I'd suggest looking at them. They really make your code much more robust. Charlie "')..." You *will* want to escape the username and password otherwise I'll be able to come along and insert any values I like into your database. I can't believe the JDBC classes don't provide 1. Some way to escape value strings 2. Some form of placeholders to deal with this -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] join question
On Fri, 22 Nov 2002, Nathan Young wrote: > Hi all. > > I have a table with members and a table with payments. Each payment is > related to a member by memberID and each payment has (among other things) a > year paid. > > I can create a join to find a list of members who have paid for a given year > (2002 in this case): > > select member.memberID,member.name from member, payment where > payment.memberID = member.memberID and payment.yearPaid = 2002 > > I would like to be able to get a list of members who have not paid for a > given year. Well, I believe either of these two will do that: select member.memberId, member.name from member where not exists (select * from payment where payment.memberId=member.memberID and payment.yearPaid=2002); select member.memberId, member.name from member left outer join (select * from payment where yearPaid=2002) as a using (memberId) where yearPaid is null; > I would also like to combine the two criteria, for example to generate a list > of members who have paid for 2002 but not 2003. I think these would do that: select member.memberID,member.name from member, payment where payment.memberID = member.memberID and payment.yearPaid = 1999 and not exists (select * from payment where payment.memberId=member.memberId and yearPaid=2002); select member.memberId, member.name from member inner join (select * from payment where yearPaid=2002) as a using (memberId) left outer join (select * from payment where yearPaid=2003) as b using (memberId) where b.yearPaid is null; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Are sub-select error suppressed?
> On Tue, 26 Nov 2002, Richard Huxton wrote: > >> On Tuesday 26 Nov 2002 9:43 am, patrick wrote: >> > Greetings, >> > >> > I'm not sure what the correct behavior is here but the observed >> > behavior seems "wrong" (or at least undesirable). >> Don't look right to me, and I still see it here in 7.2 and the 7.3 beta >> I've >> got (note - not most recent). I don't think it's in the subselect itself >> - >> what's happening is when you do > > I think it's standard behavior. The column reference is an outer > reference I believe, IIRC all the names from the outer query are in scope > in the subselect (although if there's an equivalent name in the subselect > from tables you'd have to qualify it). Ah - of course. Otherwise you couldn't do a subselect where foo=outer_foo. It tries to bind within the subselect, fails, then binds to the outer clause. Obvious now Stephan's pointed it out. Also reminds me why I like table aliases for any complicated queries. - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How does postgres handle non literal string values
Hi,
On 25 Nov 2002 at 4:57, javaholic wrote:
Your problem is really a JSP one rather than a postgres problem, and probably doesn't
really belong on this list. That said, I know much more java/jsp than I do postgres,
so I'll
try and help.
> I have some jsp code that should insert a user name and password into
> a table called login.
>
> Instead of inserting the values given by the client, it insert the
> literal string 'username' and 'password. The problem is somewhere in
> the INSERT statement.
Yup, your INSERT statement is doing exactly what you've asked it to do, inserting the
literal strings 'username' and 'password' into the table.
> <%
> String insertString =
> "INSERT INTO \"login\" (\'user\', \'password\')
> VALUES ('username', 'password')";
> %>
To do it correctly using JSP, try the following:
<%
String insertString = "INSERT INTO \"login\" (\'user\', \'password\')
VALUES ('" + username + "', '" + password + "')";
%>
However, you would probably be better off using a PreparedStatement object rather
than a Statement for various reasons, but especially to avoid trying to get the
single-
and double-quotes right in the above statement.
HTH,
Rob Hills
MBBS, Grad Dip Com Stud, MACS
Senior Consultant
Netpaver Web Solutions
Tel:(0412) 904 357
Fax:(08) 9485 2555
Email: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Are sub-select error suppressed?
On Tue, 26 Nov 2002 [EMAIL PROTECTED] wrote: > > On Tue, 26 Nov 2002, Richard Huxton wrote: > > > >> On Tuesday 26 Nov 2002 9:43 am, patrick wrote: > >> > Greetings, > >> > > >> > I'm not sure what the correct behavior is here but the observed > >> > behavior seems "wrong" (or at least undesirable). > > >> Don't look right to me, and I still see it here in 7.2 and the 7.3 beta > >> I've > >> got (note - not most recent). I don't think it's in the subselect itself > >> - > >> what's happening is when you do > > > > I think it's standard behavior. The column reference is an outer > > reference I believe, IIRC all the names from the outer query are in scope > > in the subselect (although if there's an equivalent name in the subselect > > from tables you'd have to qualify it). > > Ah - of course. Otherwise you couldn't do a subselect where foo=outer_foo. > It tries to bind within the subselect, fails, then binds to the outer > clause. > > Obvious now Stephan's pointed it out. Also reminds me why I like table > aliases for any complicated queries. Yeah, they could have (or at least if they did I couldn't find it this morning) required at least table qualifying outer references. That would have let the same functionality at the cost of only a few extra characters while being more obvious. It'd mean that you'd have to table alias things in subselects where you wanted to get to the same tablename in a higher scope, but it wouldn't have been that bad. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] escape single quote in INSERT command
> Hi Group -
>
> I have a perl application for a registration form.
Same Here,
Why dont' you use prepare and execute in case you are using DBI
same program is like this.
$dbh = DBI -> connect ( "..");
$sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
$sth -> execute($a , $b );
$sth -> finish();
$dbh -> commit();
$dbh -> disconnect();
regds
mallah.
I'd like to put escape characters in my
> insert command to accommodate for '
> (i.e. O'Brien, O'Malley, etc). I've tired double quotes, single
> quotes, back tick, forward ticks, curly bracket, round brackets - no success.
>
>
> Thanks, dave
>
> ---(end of broadcast)--- TIP 6: Have
>you
> searched our list archives?
>
> http://archives.postgresql.org
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] escape single quote in INSERT command
On 27 Nov 2002 at 0:01, [EMAIL PROTECTED] wrote:
> > Hi Group -
> >
> > I have a perl application for a registration form.
>
> Same Here,
>
> Why dont' you use prepare and execute in case you are using DBI
> same program is like this.
>
> $dbh = DBI -> connect ( "..");
> $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
> $sth -> execute($a , $b );
> $sth -> finish();
> $dbh -> commit();
> $dbh -> disconnect();
IIRC, there is a dbi->quote() function as well. That should properly
escape anything.
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] escape single quote in INSERT command
On Wed, 27 Nov 2002 [EMAIL PROTECTED] wrote:
> Why dont' you use prepare and execute in case you are using DBI
> same program is like this.
>
> $dbh = DBI -> connect ( "..");
> $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
> $sth -> execute($a , $b );
> $sth -> finish();
> $dbh -> commit();
> $dbh -> disconnect();
> I'd like to put escape characters in my
> > insert command to accommodate for '
$dbh->quote() will do the escaping for DBI but be careful with dates
as the variable binding does not always behave as expected.
You can esc the single with another single, ala ANSI SQL: ''
This works in Oracle, PG and MySQL for sure.
In perl: $name =~ s/\'/\'\'/g;
$query = qq |insert into x values ('$name')|;
and so on...
Now, can some kind soul tell me how to do an 'insert into x select y;'
where x is a numeric(19,2) and y is a money type???
---
Thomas Good e-mail: [EMAIL PROTECTED]
Programmer/Analyst phone: (+1) 718.818.5528
Residential Services fax: (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359
--Geistiges Eigentum ist Diebstahl! --
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] escape single quote in INSERT command
On Wed, 27 Nov 2002 [EMAIL PROTECTED] wrote:
> Why dont' you use prepare and execute in case you are using DBI
> same program is like this.
>
> $dbh = DBI -> connect ( "..");
> $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
> $sth -> execute($a , $b );
> $sth -> finish();
> $dbh -> commit();
> $dbh -> disconnect();
> I'd like to put escape characters in my
> > insert command to accommodate for '
$dbh->quote() will do the escaping for DBI but be careful with dates
as the variable binding does not always behave as expected.
You can esc the single with another single, ala ANSI SQL: ''
This works in Oracle, PG and MySQL for sure.
In perl: $name =~ s/\'/\'\'/g;
$query = qq |insert into x values ('$name')|;
and so on...
Now, can some kind soul tell me how to do an 'insert into x select y;'
where x is a numeric(19,2) and y is a money type???
---
Thomas Good e-mail: [EMAIL PROTECTED]
Programmer/Analyst phone: (+1) 718.818.5528
Residential Services fax: (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359
--Geistiges Eigentum ist Diebstahl! --
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] celko nested set functions -- tree move
Hi Robert; The math actually works either way; if it goes negative, the offset is positive, which is okay. Your selects are way more elegant. I guess I was just raising the point that using a key other than lft (which tends to move around in an active tree), is probably safer. The table lock just keeps the lft and rgt values static while the two updates are done. If I combined the two updates, I could probably loose the lock; I took the conservative route. The node list is around 10k, though using the universe id and tree id to separate trees, I try to keep trees to around 2k or thereabouts so that tree manip functions remain reasonably fast. I've found that a vacuum full analyze is needed at least once a day. Looking over the tree, I don't see too many nodes that are indented further than ten levels, although some are as deep as 20. I really like Celko's model for this app; it makes navigation a snap. Modifying the core functions to deal with sub-trees, however, was a logic nightmare for my feeble brain. Got it on the INTO clause; tks for the tip. I know I have work to do to tighten up the core code in various places. The schema's quite large; I'll post it somewhere soon. Martin > I think you should take a closer look at Greg's function. It is uses > lfts as parameters in the function mainly just to make the function > implementation independent; I was able to easily adapt it to my schema > which uses unique id's for each object in the tree hierarchy. > > After looking your function over, I also have some concerns about moving > children to new parents with lft and rgt smaller than the child, because > the math is different depending on which way your moving in the tree. > It's possible that your use of treeid's and universe id's makes up for > this, though it's a little hard to discern without seeing the schema, > perhaps you can post schema and some test data? > > I'm also curious how many nodes you have in your tree, and at how many > levels. It seems like your function would have performance issues over > large trees since it requires 3 select statements, 3 updates, and a lock > table. Compare this with Greg's function which requires 2 selects and 1 > update, with no lock. > > As a final note, you might want to rewrite your select statements like: > SELECT > rgt, universeid, treeid > FROM > list_objects > WHERE > objid_auto=t_newparent > INTO > newparentrgt, newparentuid, newparenttid; > > I think it's more readable and probably a little more efficient since > you are doing less variable assignment. > > Robert Treat > > On Tue, 2002-11-26 at 00:13, Martin Crundall wrote: >> I'm not sure that keying off lft is safe in a multi-user environment. >> I opted to create and use an objid on the tree definition table, since >> its identity is static. I also found that when trees get active, >> allowing for tree IDs increased operation speed quite a bit (i >> actually push this to two levels--a 'universe id' and then a 'tree >> id'). Here's my version. Clearly not as elegantly written, but >> nothing's gone awry yet. >> >> -- >> --- >> --Title: trackmyproject_tree_move() >> -- Function: moves a tree branch in the hierarchy from one parent to >> -- another. >> --parms: srcobj the branch/object to be moved >> -- newparentthe new parent for the object to be moved -- >> Returns: zero >> -- >> --- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Casting Money To Numeric
Hi All. Having perused all the online docs I can find it appears there is no SQL solution for casting the dread money type to numeric. Is this true? select rent::numeric(9,2) from x; ERROR: Cannot cast type 'money' to 'numeric' I guess my cash ain't nothing but trash... ;-) TIA! --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 --Geistiges Eigentum ist Diebstahl! -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] copy from command - quotes and header lines
Kenneth Evans writes: > On the other hand the COPY FROM file command has it the other way round - if the >separators are semi-colons then the source file > London;12;15.7 > Paris;13;22.2 > will work > > but > 'London'; '12'; '15.7' > 'Paris';'13';'22.2' > will not! The COPY input data is a separate data format, so you canno expect quoting and other features of SQL to work. Read the documentation if you're curious about details. > b) My source files for populating the database tables have three header > lines. It would be convenient if the COPY FROM command had another > parameter eg "HEADERS n" meaning skip first n lines of input file. > My current workaround uses a perl pipe but I would prefer a cleaner > solution. What's unclean about that? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL -select count-
On Tue, 19 Nov 2002, Giannis wrote: > when I do : > > select count(column_name) from table_name > > should I get the count for all columns or just those which are not null? &Ggr;&igr;&aacgr;&ngr;&ngr;&eegr;, &thgr;&agr; &pgr;&aacgr;&rgr;&egr;&igr;&sfgr; &tgr;&ogr; &pgr;&lgr;&eeacgr;&thgr;&ogr;&sfgr; &tgr;&ohgr;&ngr; &kgr;&ogr;&lgr;&oacgr;&ngr;&ohgr;&ngr; !! &pgr;&ogr;&ugr; &dgr;&egr;&ngr; &egr;&iacgr;&ngr;&agr;&igr; null. &Ggr;&igr;&agr;&tgr;&igr; &dgr;&egr;&ngr; &kgr;&aacgr;&ngr;&egr;&igr;&sfgr; &eacgr;&ngr;&agr; &tgr;&eacgr;&sgr;&tgr; &mgr;&oacgr;&ngr;&ogr;&sfgr;? > __ > Do you Yahoo!? > Yahoo! Web Hosting - Let the expert host your site > http://webhosting.yahoo.com > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] [SQL/BUGS] (possible) inconsistent behaviour of timestamp?
Here's an "interesting" timestamp related postgreSQL quirk:
testdb2=# select "timestamp"('now');
timestamp
2002-11-26 13:47:12.454157
(1 row)
testdb2=# select 'now'::timestamp;
timestamptz
--
2002-11-26 13:47:34.88358+00
(1 row)
testdb2=# select timestamp 'now';
timestamptz
---
2002-11-26 13:47:47.701731+00
(1 row)
The first SELECT returns a 'timestamp', but the next two return a
'timestamptz' (presumably with timezone); is this inconsitent behaviour?
Cheers,
Stuart.
Royal Veterinary College
London, UK
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] [SQL/BUGS] (possible) inconsistent behaviour of timestamp?
"Rison, Stuart" <[EMAIL PROTECTED]> writes: > The first SELECT returns a 'timestamp', but the next two return a > 'timestamptz' (presumably with timezone); is this inconsitent behaviour? Yes. It's a transitional behavior in 7.2: timestamp without any quotes is translated by the parser to timestamptz (ie, timestamp with time zone). Quotes suppress the renaming, however. We did that deliberately for one release to ease updating, because PG's old "datetime" datatype corresponds to timestamptz, not timestamp-without- time-zone. As of 7.3 that renaming doesn't happen any more, and undecorated timestamp means timestamp without time zone, per spec. Confused yet? I'm not sure that this update plan was really a great idea, but we agreed to it a release or two back, and we're sticking to it... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [HACKERS] [GENERAL] Bug with sequence
On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > Of course, those would be SQL purists who _don't_ understand > concurrency issues. ;-) Or they're the kind that locks the entire table for any given insert. -- Rod Taylor <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [HACKERS] [GENERAL] Bug with sequence
On 21 Nov 2002, Rod Taylor wrote: > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > > Of course, those would be SQL purists who _don't_ understand > > concurrency issues. ;-) > > Or they're the kind that locks the entire table for any given insert. Isn't that what Bruce just said? ;^) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [HACKERS] [GENERAL] Bug with sequence
On 21 Nov 2002, Rod Taylor wrote: > On Thu, 2002-11-21 at 15:09, scott.marlowe wrote: > > On 21 Nov 2002, Rod Taylor wrote: > > > > > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > > > > Of course, those would be SQL purists who _don't_ understand > > > > concurrency issues. ;-) > > > > > > Or they're the kind that locks the entire table for any given insert. > > > > Isn't that what Bruce just said? ;^) > > I suppose so. I took what Bruce said to be that multiple users could > get the same ID. > > I keep having developers want to make their own table for a sequence, > then use id = id + 1 -- so they hold a lock on it for the duration of > the transaction. I was just funnin' with ya, but the point behind it was that either way (with or without a lock) that using something other than a sequence is probably a bad idea. Either way, under parallel load, you have data consistency issues, or you have poor performance issues. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
