[SQL] Reverse String in sql or pgplsql
Did some searching on forums archives to no avail, I found a PL/Perl example but would like to do this in plpgsql if possible. Does somebody have this sitting around, I just dont want to reinvent the wheel. But if need to I will. Thanks in advance, Oisin
Re: [SQL] Connecting to Postgres from other machines (outside localhost)
Catalin Pitis wrote: Hello I installed PostgreSQL 8.0 on Windows and I can connect from localhost only. How can I configure the server to allow connection from other machines? Thank you Catalin Under Start-> Programs->PostgreSQLXX->Configuration files postgresql.conf pg_hba.conf Are the 2 files I modified. Please be aware my settings are for a dev box and are WIDE OPEN AS SHOWN HERE this might allow alot more people connect than you want. postgresql.conf # - Connection Settings - listen_addresses = '*'# what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all port = 5432 max_connections = 100 pg_hba.conf # TYPE DATABASEUSERCIDR-ADDRESS METHOD # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostall all 192.168.10.1/24 md5 # IPv6 local connections: #hostall all ::1/128 md5 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Am I crazy or is this SQL not possible
Collin Peters wrote: I am having some serious mental block here. Here is the abstract version of my problem. I have a table like this: unique_id (PK) broadcast_id date_sent status 1 1 2005-04-0430 2 1 2005-04-01 30 3 1 2005-05-20 10 4 2 2005-05-29 30 So it is a table that stores broadcasts including the broadcast_id, the date sent, and the status of the broadcast. What I would like to do is simply get the last date_sent and it's status for every broadcast. I can't do a GROUP BY because I can't put an aggregate on the status column. SELECT MAX(date_sent), status FROM broadcast_history GROUP BY broadcast_id How do I get the status for the most recent date_sent using GROUP BY? DISTINCT also doesn't work SELECT DISTINCT ON (email_broadcast_id) * FROM email_broadcast_history ORDER BY date_sent As you have to have the DISTINCT fields matching the ORDER BY fields. I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent I keep thinking am I missing something. Does anybody have any ideas? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster May not be the most efficient but seems to work here. Select broadcast_id,status from broadcast_history bh1 where bh1.date_sent = (select max(date_sent) from broadcast_history bh2 where bh1.broadcast_id=bh2.broadcast_id) order by bh1.broadcast_id; Oisin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Advanced Query
Richard Broersma Jr wrote: Personally: I think your posts are getting annoying. This isn't SQLCentral. Learn to write your own damn queries or even better - buy a book on SQL... Personally: (being a newbie with an interest in developing a strong rdms skillset) I've enjoyed following threads like these. Even when the questions (to some) seems overly simplistic, the courteous respondents often share insightful solutions or nuances that are not found in an "off the self" SQL book. However, if questions like these are *really* off-topic for the pgsql-sql I would be interested in knowing what kind of threads are acceptable and on-topic for this list. Also, if there are other mailing lists (pg or other) that are better suited for threads like this, I would appreciate learning of them. Regards, Richard Broersma ---(end of broadcast)--- TIP 1: 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 I would like to say that I have found the level of patience and help offered by the members of the mailing lists to be a key factor in my choice to use Postgres. And I feel that the response above would have been the sort of thing that would have turned me off., even if it was not in response to one of my own posts. Sometimes I may not understand the questions being asked or the answers being given but it sure is great to be able to search and find them later when you are the one hitting that same wall. If someone on a list like this rubs you the wrong way, personally I would ignore them, hence I expect to get zero responses to this! ;) Just my 2c this is a great resource and I would hope all feel as welcome here as I have, Oisin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Concat two fields into one at runtime
George Handin wrote: Is there a way using built-in PostgreSQL functions to combine two data fields into a single field at runtime when querying data? For example, the query now returns: idfirstlast --- --- -- 1 Goerge Handin 2 Joe Rachin I'd like it to return: idname --- --- 1 George Handin 2 Joe Rachin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings if you want the 2 fields with a space between them select first ||' '||last as name from foo; should do it. Oisin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Help with performance and explain.
I have an issue with a select returning very slowly approx 198 seconds. I took a backup of this DB and restored it on another system and it is returning in 28 seconds. Windows 2000 PG Version 8.0.3 Data is inserted into the table row by row. Table has index on service explain SELECT callrecord."service", callrecord."timequeuing", callrecord."timeconversation", callrecord."timeoffering", callrecord."calltype", callrecord."application" FROM "public"."callrecord" callrecord ORDER BY callrecord."service" ASC "Sort (cost=284032.83..284322.17 rows=115734 width=46)" " Sort Key: service" " -> Seq Scan on callrecord (cost=0.00..270144.34 rows=115734 width=46)" Windows 2000 Version 8.0.1 DB created from backup of other server. explain SELECT callrecord."service", callrecord."timequeuing", callrecord."timeconversation", callrecord."timeoffering", callrecord."calltype", callrecord."application" FROM "public"."callrecord" callrecord ORDER BY callrecord."service" ASC "Sort (cost=17465.33..17754.65 rows=115729 width=46)" " Sort Key: service" " -> Seq Scan on callrecord (cost=0.00..3577.29 rows=115729 width=46)" Any help would be greatly appreciated. Oisin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with performance and explain.
Oisin Glynn wrote: I have an issue with a select returning very slowly approx 198 seconds. I took a backup of this DB and restored it on another system and it is returning in 28 seconds. Windows 2000 PG Version 8.0.3 Data is inserted into the table row by row. Table has index on service explain SELECT callrecord."service", callrecord."timequeuing", callrecord."timeconversation", callrecord."timeoffering", callrecord."calltype", callrecord."application" FROM "public"."callrecord" callrecord ORDER BY callrecord."service" ASC "Sort (cost=284032.83..284322.17 rows=115734 width=46)" " Sort Key: service" " -> Seq Scan on callrecord (cost=0.00..270144.34 rows=115734 width=46)" Windows 2000 Version 8.0.1 DB created from backup of other server. explain SELECT callrecord."service", callrecord."timequeuing", callrecord."timeconversation", callrecord."timeoffering", callrecord."calltype", callrecord."application" FROM "public"."callrecord" callrecord ORDER BY callrecord."service" ASC "Sort (cost=17465.33..17754.65 rows=115729 width=46)" " Sort Key: service" " -> Seq Scan on callrecord (cost=0.00..3577.29 rows=115729 width=46)" Any help would be greatly appreciated. Oisin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I decided to Import a dump into the same server that was displaying the slow response and it was fine on the second database Explain below. So I am even more puzzled but I guess my immediate issue is over. I had vacuumed and analyzed this pre dumping to no avail on the initial db. "Sort (cost=31284.82..31576.38 rows=116622 width=234)" " Sort Key: service" " -> Seq Scan on callrecord (cost=0.00..3605.22 rows=116622 width=234)" Oisin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] how do I check for lower case
Juliann Meyer wrote: I have a table with a column, lets call it identifier, that is defined as varchar(8) that should never contain lower case letters. Its a large table. Is there a way to query the table to see if any values in this column are lower case and to get a list out? The user interface application that users use prevents them from adding an entry in lower case now, but didn't in earlier version. Julie ---(end of broadcast)--- TIP 1: 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 Not sure if this is over simple and perhaps this would be a concern on a very large table but select * from table where identifier <> upper(identifier); would give a list of all where the entry is not all uppercase? Oisin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Evaluation of if conditions
Daniel CAUNE wrote: Hi, How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? The following snippet seems to be invalid, which let me think that PL/PGSQL evaluates all the conditions: IF (TG_OP = 'INSERT') OR (OLD.bar = ...) THEN statement END IF; Should be rewritten as (for example): IF (TG_OP = 'INSERT') THEN statement ELSIF (OLD.bar = ...) THEN statement END IF; Regards, -- Daniel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match The following is working fine for me on 8.1.x on Windows. I am not sure what order it is evaluating the if statement in but it is working correctly. Oisin CREATE OR REPLACE FUNCTION zfunc_testor(bool, bool) RETURNS "varchar" AS $BODY$DECLARE v_1 boolean; v_2 boolean; BEGIN v_1 :=$1; v_2 := $2; if (v_1 = TRUE) OR (v_2 = TRUE) then return 'At least 1 true'; else return 'neither true'; end if; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION zfunc_testor(bool, bool) OWNER TO postgres; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Odbc configuration on Localhost
Ezequias Rodrigues da Rocha wrote: Hi list, Is there any tip to set a localhost connection on the windows odbc configuration to postgresql ? I am using 127.0.0.1 <http://127.0.0.1> and localhost and notthing. Any suggest ? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ <http://ezequiasrocha.blogspot.com/> Are you getting any errors? What have you got set in your pg_hba.conf and postgresql.conf for access rights? Oisin -- Oisin Glynn My status smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] [NOVICE] Windows 2K Installation difficulties...
Neil Bibbins wrote: I'm logging in directly on the machine. The installation gets most of the way through, chokes, and rolls the whole thing back. I've tried altering permissions, but the PostgreSQL installer creates new accounts regardless with just user permissions. It's a mystery. Hmmm... On 1/25/07, *Oisin Glynn* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote: Neil Bibbins wrote: > Hello, > > I'm having difficulty installing PostgreSQL 8.2 on Windows 2000. It > gets most of the way through the installation and fails (I think) > after trying to initialize the database. The log message is: > > The database cluster will be initialized with locale C. > > fixing permissions on existing directory C:/Program > Files/PostgreSQL/8.2/data ... ok > creating subdirectories ... ok > selecting default max_connections ... 10 > selecting default shared_buffers/max_fsm_pages ... 400kB/2 > creating configuration files ... ok > creating template1 database in C:/Program > Files/PostgreSQL/8.2/data/base/1 ... Bad command or file name > could not write to child process: Invalid argument > Initdb: removing contents of data directory "C:/Program > Files/PostgreSQL/8.2.data > > I have removed all virus software, although I hope to reinstall it > after installation. (Can PostgresSQL really not run as an application > with virus protection installed? Ouch...) > > I have tried deselecting the initialization box and initializing > afterward, but this also doesn't work. Possibly I'm using the wrong > command, which is one that I found from a post from Magnus several > years ago. > > I have installed Cygwin, but this also didn't help. > > Can anyone assist? I've done many forum searches but nothing seems to > work. I don't think I'm attempting the impossible! > > Thanks for any insight. Much appreciated. I am running on XP, 2k and 2k3 with various different VirusScanners without seeing issues (Symantec, AVG) Though 8.1.X not 8.2 Might you be hitting some permissions error on the folder/drive? Are you logged on remotely (RDP?) or directly on the machine? Oisin Make sure to copy the list on your responses and in general people prefer to reply at the bottom so someone who looks at this afresh can read it in order going down. Not sure if anyone else has any suggestions? Oisin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
