Re: [SQL] datatype conversion on postgresql 7.4.1
Timo Roessner wrote: and if i try something like: alter table fragment alter column x type numeric(15,2) i get an syntax error, so this seems to be no feature in 7.4.1 (didnt find anything like that in the docs too) what can i do to solve this? there must be some way in postgresql 7.4.1, if there is no way to do that, i have to build up the whole database from scratchthere must be a way to convert float to numeric values in 7.4.1 , but i dont have a single clue... There isn't a way to directly convert the type of a column in 7.4.x but you can duplicate the effect. BEGIN; ALTER TABLE fragment RENAME COLUMN x TO old_x; ALTER TABLE fragment ADD COLUMN x numeric(10,2); UPDATE fragment SET x = old_x; ALTER TABLE fragment DROP COLUMN old_x; COMMIT; Make sure you check any functions/application code that might be expecting floating-point rather than numeric before you do this. Oh, and upgrade from 7.4.1 to 7.4.7 as soon as possible. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Transaction in plpgslq
2005/5/20, Andrew Hammond <[EMAIL PROTECTED]>: > The solution to your problem is locking (or concurrency control if you > prefer). While we're at it, we might as well optimize your statement a > little too using ORDER BY with LIMIT instead of min(). > > SELECT id INTO _contacto_id > FROM contactos > WHERE contactos.operadora_id IS NULL > AND contactos.actividad_id > = _actividad_id > ORDER BY id LIMIT 1 > FOR UPDATE; > > Take a look at the "FOR UPDATE" section of the SELECT description for an > explanation of how this works. I understand "FOR UPDATE" clause is locking while is selecting rows only. It does not secure atomic execution from 'SELECT' to 'UPDATE' in next statement. Is not it? -- Rafa Couto (caligari) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] could not devise a query plan
Dear Gurus, Sorry for upping a 13-month-old thread; please tell if I should've opened another one. Here I come again, with another silly join. Please forgive me, but our queries are built from blocks :) VERSION: 7.4.6, 7.4.8, 8.0.0rc4 (sorry, no newer installed right now) ABSTRACT: The following query fails. SELECT * FROM (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa NATURAL FULL JOIN (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb WHERE a+b = 3; DETAILS: It seems it's something about the redundant WHERE clauses. If I comment (replace with "WHERE true") any of the three WHERE clauses, it works. Session log: See below. TIA, -- G. serv1:tir=> \d cdqp Table "pg_temp_20.cdqp" Column | Type | Modifiers +-+--- a | integer | b | integer | serv1:tir=> SELECT * FROM serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa serv1:tir-> NATURAL FULL JOIN serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb serv1:tir-> WHERE a+b = 3; ERROR: could not devise a query plan for the given query serv1:tir=> SELECT * FROM serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa serv1:tir-> NATURAL FULL JOIN serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb serv1:tir-> WHERE true; a | b ---+--- 1 | 2 (1 row) ---(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
Re: [SQL] Transaction in plpgslq
Rafa Couto wrote: 2005/5/20, Andrew Hammond <[EMAIL PROTECTED]>: The solution to your problem is locking (or concurrency control if you prefer). While we're at it, we might as well optimize your statement a little too using ORDER BY with LIMIT instead of min(). SELECT id INTO _contacto_id FROM contactos WHERE contactos.operadora_id IS NULL AND contactos.actividad_id > = _actividad_id ORDER BY id LIMIT 1 FOR UPDATE; Take a look at the "FOR UPDATE" section of the SELECT description for an explanation of how this works. I understand "FOR UPDATE" clause is locking while is selecting rows only. It does not secure atomic execution from 'SELECT' to 'UPDATE' in next statement. Is not it? After the SELECT FOR UPDATE command locked some rows, other concurrent changes to the database could be made, but changes, which require to lock that rows will be deferred. The lock will be hold until the end of the transaction (that means at least until the function returns). ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] could not devise a query plan
Dear Gnanavel, (please reply to the lists...) Indeed it works! Still, I think it's a bug. As for this solution being a workaround, it's a bit of pain, since the subselect names (effectively, the included subselects) are not constant. As for my workaround, I used a condition to not include the last WHERE clause in the query when the inner WHERE clauses are present (since it does nothing). -- G. On 2005.05.24. 13:14, Gnanavel Shanmugam wrote: replace WHERE a+b = 3; with WHERE aa.a+aa.b = 3; (or) WHERE bb.a+bb.b = 3; with regards, S.Gnanavel -Original Message- From: [EMAIL PROTECTED] Sent: Tue, 24 May 2005 12:39:04 +0200 To: pgsql-bugs@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] could not devise a query plan Dear Gurus, Sorry for upping a 13-month-old thread; please tell if I should've opened another one. Here I come again, with another silly join. Please forgive me, but our queries are built from blocks :) VERSION: 7.4.6, 7.4.8, 8.0.0rc4 (sorry, no newer installed right now) ABSTRACT: The following query fails. SELECT * FROM (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa NATURAL FULL JOIN (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb WHERE a+b = 3; DETAILS: It seems it's something about the redundant WHERE clauses. If I comment (replace with "WHERE true") any of the three WHERE clauses, it works. Session log: See below. TIA, -- G. serv1:tir=> \d cdqp Table "pg_temp_20.cdqp" Column | Type | Modifiers +-+--- a | integer | b | integer | serv1:tir=> SELECT * FROM serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa serv1:tir-> NATURAL FULL JOIN serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb serv1:tir-> WHERE a+b = 3; ERROR: could not devise a query plan for the given query serv1:tir=> SELECT * FROM serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa serv1:tir-> NATURAL FULL JOIN serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb serv1:tir-> WHERE true; a | b ---+--- 1 | 2 (1 row) ---(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 ---(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] Transaction in plpgslq
Rafa Couto wrote: I have got a plpgsql function: -- BEGIN; SELECT min(id) INTO _contacto_id FROM contactos WHERE contactos.operadora_id IS NULL AND contactos.actividad_id = _actividad_id; UPDATE contactos SET operadora_id = _operadora_id WHERE id = _contacto_id; -- COMMIT; and it works right, but I need atomic execution from --BEGIN and --COMMIT, and manual says it is not possible to have transactions in PL/pgSQL procedures :-( OK - the WHOLE FUNCTION takes place within one transaction. So, in that sense all changes are atomic. However, if you want to prevent any changes to "contactos" in-between those two statements you'll want additional locks. Read the chapter on "concurrency control" for details. You might well want SELECT FOR UPDATE (and also just ORDER BY id LIMIT 1 rather than using min(id)). -- Richard Huxton Archonet Ltd ---(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] Transaction in plpgslq
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafa Couto wrote: > I understand "FOR UPDATE" clause is locking while is selecting rows > only. It does not secure atomic execution from 'SELECT' to 'UPDATE' in > next statement. Is not it? Locks adhere until the transaction ends. I included links to relevant documentation in my original post. If you read up on locking, you'll find the answer to your problem. Please post any further questions you have after reading the documentation. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFCk1INgfzn5SevSpoRAjVlAJ4kGMlTQFaI1BW+9O9GT8He19TyLACcCYtU Tucg2FuGiDggsAwV7JA2HHs= =9rus -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] DROP IF ...
PostgreSQL 7.4 ...
I'm trying to find a way to drop a table via SQL if it contains 0 rows. Here
was my thought:
CREATE OR REPLACE FUNCTION dropif(text, bool)
RETURNS bool AS
'DECLARE
tblname ALIAS FOR $1;
condition ALIAS FOR $2;
BEGIN
IF (condition) THEN
EXECUTE(\'DROP TABLE "\' || tblname || \'";\');
END IF;
RETURN \'t\'::bool;
END;'
LANGUAGE 'plpgsql' VOLATILE;
... then ...
BEGIN;
CREATE TABLE testtbl (i int4);
SELECT dropif('testtbl',(SELECT count(*)::int4 FROM testtbl) = 0);
ERROR: relation 286000108 is still open
CONTEXT: PL/pgSQL function "dropif" line 6 at execute statement
... It makes sense. The select is still open when the table is going to be
dropped. I need a different strategy.
Please advise!
CG
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(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
Re: [SQL] Help: Function for splitting VARCHAR column and migrating its data to 2 new tables
On 5/23/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I am restructuring my DB schema and need help migrating data from 1
> column of an existing table to two new tables. I have some Java code
> that can do this for me, but it's very slow, and I am now hoping I can
> migrate this data with some clever SQL instead.
>
> Here are my 3 tables:
>
> user_data (existing, old table)
> -
> id (PK),
> user_id (FK)
> keywords VARCHAR(256)
> -- this contains comma separated keywords
> -- e.g. "new york,san francisco, dallas, food"
> -- also "keywords without strings are really just 1 keyword"
> add_date TIMESTAMP
>
>
> So now I'm trying to migrate this "keywords" VARCHAR column to a more
> normalized schema:
>
> user_data_keyword (new lookup table to populate)
> -
> id (PK) -- I may change PK to PK(user_data_id, keyword_id)
> user_data_id (FK)
> keyword_id (FK)
>
>
> keyword (new table to populate)
> ---
> id (PK)
> name VARCHAR(64) NOT NULL UNIQUE
> add_date TIMEZONE
>
>
> I just found
> http://www.postgresql.org/docs/current/static/functions-string.html ,
> but if anyone could lend me a hand by getting me started with writing a
> function for this, I'd really appreciate it.
>
> Thanks,
> Otis
>
I am not aware of any extremely clever SQL to make this ALL happen.
However you can do everything with pl/pgsql. My contribution below
will build the keyword table for you. Once you have this working and
you understand it, you could extend it to build your user_data_keyword
table.
CREATE OR REPLACE FUNCTION split_on_commas(TEXT) RETURNS SETOF TEXT
LANGUAGE 'plpgsql' AS '
DECLARE
mystring ALIAS FOR $1;
incomma BOOLEAN := FALSE; -- ## Catch the first word
endposINTEGER; -- end character
startpos INTEGER := 0;
pos INTEGER;
outstring TEXT;
BEGIN
SELECT INTO endpos CHAR_LENGTH(mystring);
FOR pos IN 1 .. endpos
LOOP
-- There are 2 single quotes, a space, and 2 single quotes below:
IF SUBSTRING(mystring,pos,1) = '','' THEN
incomma := TRUE;
outstring := SUBSTRING(mystring,startpos,pos-startpos);
RETURN NEXT outstring;
ELSE
IF incomma IS TRUE THEN
incomma := FALSE;
startpos := pos;
END IF;
END IF;
END LOOP;
IF incomma IS FALSE THEN -- ## Catch the last phrase
outstring := SUBSTRING(mystring,startpos,endpos);
RETURN NEXT outstring;
END IF;
RETURN;
END;
';
-- try it! You will get a set of keyword all split out
SELECT * FROM split_on_commas('foo1,bar2,foo3');
From here, we need a migration function that will loop through row(s)
in your table. I also noticed that some of your words may have leading
and trailing spaces. The function drop_first_and_last_space should
take care of that. Notice that I use this function below in the
build_keyword_table function.
CREATE OR REPLACE FUNCTION drop_first_and_last_space(TEXT) RETURNS
TEXT LANGUAGE 'plpgsql' AS '
DECLARE
in_string ALIAS FOR $1;
out_msg TEXT;
BEGIN
out_msg := in_string;
IF SUBSTRING(out_msg,1,1) = '' '' THEN
RAISE NOTICE ''dropping leading space'';
out_msg := substring(out_msg,2,length(out_msg));
END IF;
--and the last space
IF SUBSTRING(out_msg,length(out_msg),1) = '' '' THEN
RAISE NOTICE ''dropping trailing space'';
out_msg := substring(out_msg,1,length(out_msg)-1);
END IF;
RETURN out_msg;
END
';
-- This function uses the INSERT ((SELECT)EXCEPT(SELECT)); syntax
mentioned in http://www.varlena.com/varlena/GeneralBits/19.htm. Note
that I am unsure why I didn't need to use FOR r in EXECUTE ''SELECT...
syntax. But this did work in my small scale test.
CREATE OR REPLACE FUNCTION build_keyword_table(TEXT) RETURNS TEXT
LANGUAGE 'plpgsql' AS '
DECLARE
match ALIAS FOR $1;
rec RECORD;
r RECORD;
out TEXT := ''done'';
BEGIN
FOR rec IN SELECT keywords
FROM user_data
WHERE id SIMILAR TO match
ORDER BY id
LOOP
RAISE NOTICE '' working on %'',rec.keywords;
FOR r IN SELECT drop_first_and_last_space(split_on_commas) AS
kw FROM split_on_commas(rec.keywords)
LOOP
RAISE NOTICE ''trying to insert - %'',r.kw;
INSERT INTO keyword (name) ( (SELECT r.kw) EXCEPT (SELECT
name FROM keyword WHERE name=r.kw));
END LOOP;
END LOOP;
RETURN out;
END;
';
Then try it all together, like so:
SELECT build_keyword_table('%');
The input goes to a SIMILAR TO on id - and % means all elements. You
can match a single row by using something like SELECT
build_keyword_table('123');
I also used this as my test data... It worked for me!
CREATE TABLE user_data (
id SERIAL,
user_id INTEGER,
keywords VARCHAR(256) NOT NULL,
add_date TIMESTAMP,
PRIMARY KEY(id)
);
INSERT INTO user_data (keywords) VALUES ('new york,san francis
[SQL] Tip ?
This tip was at the end of a message (from Szűcs Gábor). TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match it looks very important, but I cannot understand it. Sound as a small and easy mistake that can make things go sour... Can someone explain it please? thanks, Alain ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Tip ?
On Tue, 2005-05-24 at 13:26, Alain wrote: > This tip was at the end of a message (from Szűcs Gábor). > > > TIP 9: the planner will ignore your desire to choose an index scan if your > > joining column's datatypes do not match > > it looks very important, but I cannot understand it. Sound as a small > and easy mistake that can make things go sour... > > Can someone explain it please? Note that this is mostly fixed in V 8.0 What it means is that if you have a table with a field of type numeric, and you join another table against it with a field of type integer, the query planner won't know it can use the indexes on those two fields (assuming you DO have indexes on them) when joining and use a sequential scan all the time. Casting one field to the other's type, or changing the type of one to the other's type will allow the planner to use the index. ---(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
Re: [SQL] could not devise a query plan
=?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <[EMAIL PROTECTED]> writes:
> ABSTRACT: The following query fails.
> SELECT * FROM
> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa
> NATURAL FULL JOIN
> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb
> WHERE a+b = 3;
Thanks for the report! Seems I overlooked a case when fixing the
original report last year. Patch for 8.0 attached (it's the same in
7.4 too).
regards, tom lane
Index: joinpath.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v
retrieving revision 1.91.4.1
diff -c -r1.91.4.1 joinpath.c
*** joinpath.c 23 Jan 2005 02:22:27 - 1.91.4.1
--- joinpath.c 24 May 2005 17:54:15 -
***
*** 498,512
* nestloop path, but since mergejoin is our only join type that
* supports FULL JOIN, it's necessary to generate a clauseless
* mergejoin path instead.
-*
-* Unfortunately this can't easily be extended to handle the
case
-* where there are joinclauses but none of them use
mergejoinable
-* operators; nodeMergejoin.c can only do a full join correctly
if
-* all the joinclauses are mergeclauses.
*/
if (mergeclauses == NIL)
{
! if (jointype == JOIN_FULL && restrictlist == NIL)
/* okay to try for mergejoin */ ;
else
continue;
--- 498,507
* nestloop path, but since mergejoin is our only join type that
* supports FULL JOIN, it's necessary to generate a clauseless
* mergejoin path instead.
*/
if (mergeclauses == NIL)
{
! if (jointype == JOIN_FULL)
/* okay to try for mergejoin */ ;
else
continue;
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Duplicated records
Hi. How can I delete the duplicated records with "DELETE FROM TABLE WHERE..." clause?? The problem is becouse I have imported data from Dbase (dbf) file, and this function have not built the Constraint (unique, primary key, ...), and this function is usually executed. select * from table1; --id may be primary key - Table1 - id | field 2 0 | 'aaa' 1 | 'bbb' 2 | 'ccc' 0 | 'aaa' <<== The data is duplicated 1 | 'bbb' 2 | 'ccc' 0 | 'aaa' 1 | 'bbb' 2 | 'ccc' Is there a way to delete the duplicated data without build another table with constraints and copy those data to the new table? Something like "delete from table1 where ...???" Thanks, Lucas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Duplicated records
How can I delete the duplicated records with "DELETE FROM TABLE WHERE..." clause?? The problem is becouse I have imported data from Dbase (dbf) file, and this function have not built the Constraint (unique, primary key, ...), and this function is usually executed. If you have no primary key how can you reference a record in order to delete it ? I'd say use a temporary table... If you have complete row dupes (ie. the entire row is duplicated) use SELECT * FROM table GROUP BY * (or select distinct) If only the primary key is duplicated but other fields change, then you have to decide which one you wanna keep ! ---(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
Re: [SQL] DROP IF ...
The following function takes a table name as a parameter and drops
the table and returns true if there are zero rows (otherwise, it
returns false):
CREATE OR REPLACE FUNCTION dropzero( varchar ) RETURNS bool AS '
DECLARE
zerotable ALIAS FOR $1;
zerocurs refcursor;
rowcount int;
BEGIN
OPEN zerocurs FOR EXECUTE ''SELECT COUNT( * ) FROM '' ||
zerotable;
FETCH zerocurs INTO rowcount;
CLOSE zerocurs;
IF rowcount = 0 THEN
EXECUTE ''DROP TABLE '' || zerotable;
RETURN true;
ELSE
RETURN false;
END IF;
END;
' LANGUAGE 'plpgsql';
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On May 24, 2005, at 12:44 PM, CG wrote:
PostgreSQL 7.4 ...
I'm trying to find a way to drop a table via SQL if it contains 0
rows. Here
was my thought:
CREATE OR REPLACE FUNCTION dropif(text, bool)
RETURNS bool AS
'DECLARE
tblname ALIAS FOR $1;
condition ALIAS FOR $2;
BEGIN
IF (condition) THEN
EXECUTE(\'DROP TABLE "\' || tblname || \'";\');
END IF;
RETURN \'t\'::bool;
END;'
LANGUAGE 'plpgsql' VOLATILE;
... then ...
BEGIN;
CREATE TABLE testtbl (i int4);
SELECT dropif('testtbl',(SELECT count(*)::int4 FROM testtbl) = 0);
ERROR: relation 286000108 is still open
CONTEXT: PL/pgSQL function "dropif" line 6 at execute statement
... It makes sense. The select is still open when the table is
going to be
dropped. I need a different strategy.
Please advise!
CG
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [despammed] [SQL] Duplicated records
am 24.05.2005, um 17:59:31 -0300 mailte [EMAIL PROTECTED] folgendes: > Hi. > How can I delete the duplicated records with "DELETE FROM TABLE WHERE..." > clause?? Please read http://www.gtsm.com/oscon2003/deletetid.html Its a very good article about this problem. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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
