Re: [GENERAL] coalesce function
Torsdag 20. juni 2013 21.45.02 skrev itishree sukla: Hi All, I am using coalesce(firstname,lastname), to get the result if first name is 'NULL' it will give me lastname or either way. I am having data like instead of NULL, blank null ( i mean something like '' ) for which coalesce is not working, is there any workaround or other function available in postgresql, please do let me know. CASE WHEN firstname NOT IN (NULL, '') THEN firstname ELSE lastname END; regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Storing Special Characters
Tirsdag 14. mai 2013 18.05.05 skrev Rebecca Clarke:
Hi there.
This may be the wrong forum to inquire in, but I'd be grateful if I could
directed in the right direction if that is the case.
I am currently using Postgresql 9.1.
I have a table in which I want to store shop names. Some of the shop names
contain 'é' and '£'.
The query below works when I run it through pg_admin:
insert into retail(storename) values ('£'::character varying)
However, when I run the same query through pg_query in PHP, I get:
PHP Warning: pg_query(): Query failed: ERROR: invalid byte sequence for
encoding UTF8: 0xa3
It's possibly a client encoding problem. See the PHP documentation on
http://php.net/manual/en/function.pg-set-client-encoding.php
regards, Leif
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Money casting too liberal?
Søndag 31. mars 2013 18.45.10 skrev ajmcello : unsubscribe On Sun, Mar 31, 2013 at 3:31 AM, Gavan Schneider pg-...@snkmail.com wrote: On 30/3/13 at 11:09 PM, D'Arcy J.M. Cain wrote: I am formulating Cain's Law. Something like If a discussion lasts long enough, someone will mention Godwin's Law. +1 More formally: As an online discussion grows longer, the probability of Godwin's Law being mentioned approaches one. First corollary to Cain's Law: As an online discussion grows longer, the probability of someone trying to unsubscribe by posting a reply to the discussion approaches one. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using composite types within PLPGSQL Function
Tirsdag 8. januar 2013 16.10.03 skrev Graeme Hinchliffe : My example code is : CREATE TYPE testtype AS ( a INTEGER, b INTEGER ); CREATE OR REPLACE FUNCTION test() RETURNS INTEGER AS $$ DECLARE x testtype; BEGIN (x).a:=1; RETURN 1; END $$ LANGUAGE plpgsql; This throws up syntax errors for the (x).a:=1; line.. I have also tried SELECT INTO (x).a 1; Any help much appreciated, version of PostgreSQL is 8.4 under Debian. It should work with x.a := 1; without the parentheses. See http://solumslekt.org/blog/?p=91 for an example of composite types and functions. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Where is 'createdb'?
Torsdag 1. november 2012 16.32.42 skrev Kevin Burton : This is probably a question for the authors of a book I have been reading but it may be faster to get an answer here. I was trying to follow along in a book 'Seven Databases in Seven Weeks' and chapter 2 deals with PostgreSQL. One of the first things it does is issue a command 'createdb book'. The text before this command says, Once you have Postgres installed, create a schema called book using the following command: $ createdb book' But when I tried to issue this command (at a Linux command prompt) I get 'createdb command not found'. Are the authors out of date? What is the current command? leif@balapapa ~ $ which createdb /usr/bin/createdb regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Great site for comparing databases (or anything else)
Søndag 28. oktober 2012 01.17.45 skrev Gavin Flower : Also note that for features that are obviously complicated or advanced, Postgres tends to a lot better than MySQL. It's like comparing BASIC to C. BASIC has a low threshold, but you will very quickly bump your head against the wall. MySQL, the BASIC of db engines? regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database corruption questions
Lørdag 13. oktober 2012 23.53.03 skrev Heine Ferreira : Hi Are there any best practices for avoiding database corruption? In my experience, database corruption always comes down to flaky disk drives. Keep your disks new and shiny eg. less than 3 years, and go for some kind of redundancy in a RAID configuration. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] insert ... returning in plpgsql
Tirsdag 2. oktober 2012 15.01.08 skrev Willy-Bas Loos :
Hi,
(postgres 9.1)
I was doing something like this in a plpgsql function, but i got a Syntax
Error.
t_var:=(insert into table1(field2) values ('x') returning field1);
Is there no support for using RETURNING in insert, update, delete queries
to fill a variable in plpgsql?
Try
insert into table1(field2) values ('x') returning field1 into t_var
regards, Leif
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with recreating database with export
Torsdag 27. september 2012 16.55.15 skrev Dennis Gearon : note to future To anyone reading this in the future, if you have problems importing a plain text database export, it is usually impossible to do: psql -d some_dbase -f the_backup.sql. I don't know why. What works is doing 'cd ./the_files_directory', going INTO psql command line, then issuing '\i the_backup.sql', and it's really fast. 8 seconds for 128 mbyte file. PS,do this as user 'postgres' on the system. /note to future FWIW, here are a few relevant lines from my reload.sh script, which I have been using since version 7.4, and which is working perfectly: dropdb $DB createdb --encoding=UNICODE $DB psql -U postgres -d $DB -f $INFILE restore.log 21 Note that I'm running this as a regular postgres user, whose only privilege is to create new databases. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need to run a job in PgAdmin-III
Mandag 24. september 2012 11.06.32 skrev pavithra : I have a scheduled a sql query as select sysdate from dual and i have given as Data Export. That's an Oraclism. Have you actually tested the query in psql? postgres= select sysdate from dual; ERROR: relation dual does not exist LINE 1: select sysdate from dual; ^ postgres= Maybe this is what you want? postgres= select current_date; date 2012-09-24 (1 row) postgres= regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using psql -f to load a UTF8 file
Torsdag 20. september 2012 19.27.22 skrev Alan Millington :
Thank you for the link. I am using Notepad, which inserts the byte order
mark. Following the links a bit further, I gather that the version of
Notepad that I am using may not identify a UTF8 file correctly if the byte
order mark is omitted. Also, as I mentioned, Python makes use of it. (From
the Python documentation on Encoding declarations: If the first bytes of
the file are the UTF-8 byte-order mark ('\xef\xbb\xbf'), the declared file
encoding is UTF-8 (this is supported, among others, by Microsoft’s
Notepad).)
The conclusion seems to be that I must use one editor for Python, and
another for Postgres.
It's been a long time since I last wrote a Python script, but I've always used
the explicit encoding directive:
#! /usr/bin/env python
# -*- encoding: utf-8 -*-
See http://docs.python.org/release/2.5.1/ref/encodings.html which also
mentions the BOM method as an alternative.
regards, Leif
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using psql -f to load a UTF8 file
Torsdag 20. september 2012 16.56.16 skrev Alan Millington : psql. But how am I supposed to remove the byte order mark from a UTF8 file? I thought that the whole point of the byte order mark was to tell programs what the file encoding is. Other programs, such as Python, rely on this. http://en.wikipedia.org/wiki/Byte_order_mark While the Byte Order Mark is important for UTF-16, it's totally irrelevant to the UTF-8 encoding. Still you'll find several editors that automatically input BOMs in every text file. There is usually a setting Insert Byte Order Mark somewhere in the configuration, and it may be on by default. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trying to execute several queries involving temp tables in a PHP script
Onsdag 13. juni 2012 15.12.33 skrev Alexander Farber : Any ideas please on how to handle this situation in PHP scripts, do I really have to encapsulate my calls into a pl/PgSQL function? I believe that Misa Simic's idea that you can do it all in a single query without temp tables is correct. But anyway, it's always a good idea to encapsulate multiple interdependent queries in a single pl/pgsql function. I tend to keep my PHP code as simple as possible, and do most of the work inside the database. regards, Leif http://code.google.com/p/yggdrasil-genealogy/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedural Languages
Torsdag 31. mai 2012 17.07.19 skrev Merlin Moncure : pl/pgsql is unique in that it has 'first class queries' -- sql is intermixed freely with procedural code and it uses the same type system and error handling mechanisms (although the syntax is different). this directly translates into direct, impactful coding as long as you are not trying to do things that are awkward for the language like heavy computation or string processing. I'm using plpgsql for string processing all the time, mostly with regexes, and don't find it particularly awkward. That may of course be an example of the If all you've got is a hammer, all problems look like nails syndrome. But I've never felt a need for installing another pl language. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what Linux to run
Lørdag 3. mars 2012 01.43.29 skrev Gavin Flower : I think if you are going to select a member of the Debian family, I would strongly recommend Debian itself. I have the impression that the Debian community is more serious about quality than Canonical (the company behind Ubuntu). I haven't run Debian for ten years, when I had a headless old PC running with a LAMP stack. Since I discovered Gentoo, that has been my preferred distro. However, I'm currently in the process of setting up a dedicated Web server with Debian as it may one day be another person's responsibility to admin this box, and I would consider it cruel to leave a Gentoo box to anyone but the most devoted Linux fans. My current gripe is this: The «stable» version of Postgres on Debian is 8.4. In order to install 9.1, I added this line to /etc/apt/sources.list: deb http://ftp.debian.org/debian unstable main contrib non-free Then I did an apt-get update and apt-get install postgresql-9.1 postgresql-client-9.1 Finally I commented out the added line of /etc/apt/sources.list. This seems a rather roundabout way, is there a better one? regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what Linux to run
Lørdag 3. mars 2012 12.34.27 skrev Raymond O'Donnell : You can get Postgres 9.1 from backports.debian.org: deb http://backports.debian.org/debian-backports squeeze-backports main Ah, sweet, thank you! regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Philosophical question
Onsdag 14. desember 2011 22.21.04 skrev Chris Angelico : The biggest problem with PHP, imho, is actually that it's so easy to use. Anyone can get a WYSIWYG editor, save as HTML, and have a web page... and then all you need to do is rename it to .php and put some special tags in it, and look! You have a dynamic web page and it's so awesome! At least, it is until you try to go further, and you start adding mess on top of mess on top of mess. In my opinion, that's a pretty elitistic view. Certainly, that's one way of writing PHP, but it isn't the only one. Quite a few of us have started with something like what you've outlined here, but have long ago moved on to more maintainable coding practices. The good thing about PHP is the low threshold, and you can start using it doing exactly what you outlined in your first paragraph. But somebody coming to PHP from any old procedural language, will soon find that PHP lends itself well to building function upon function, until you can really write the code you need to express anything you want. There are a few more fundamental issues with the language, but mainly, it gets the blame for myriad bad PHP programmers. Yes there's a lot of bad programmers out there. Most of them code in Java or Visual Basic. I prefer Pike. It's designed for writing servers, performance is pretty decent, it's a high level language, and it has great database support (including Postgres-specific features, some of which are quite handy). I'd like to look at this Pike. I don't think that my Web host supports it, but it might still be a fun experience. regards, Leif The Yggdrasil project: http://code.google.com/p/yggdrasil-genealogy/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I/O error on data file, can't run backup
On Thursday 6. October 2011 07.07.11 Craig Ringer wrote: On 10/06/2011 03:06 AM, Leif Biberg Kristensen wrote: I seemingly fixed the problem by stopping postgres and doing: balapapa 612249 # mv 11658 11658.old balapapa 612249 # mv 11658.old 11658 And the backup magically works. Woo! That's ... interesting. I'd be inclined to suspect filesystem corruption, a file system bug / kernel bug (not very likely if you're on ext3), flakey RAM, etc rather than a failing disk ... though a failing disk _could_ still be the culprit. Use smartmontools to do a self-test; if 'smartctl -d ata -t long /dev/sdx' (where 'x' is the drive node) is reported by 'smartctl -d ata -a /dev/sdx' as having passed, there are no pending or uncorrectable sectors, and the disk status is reported as 'HEALTHY' your disk is quite likely OK. Note that a 'PASSED' or 'HEALTHY' report by its self doesn't mean much, disk firmwares often return HEALTHY even when the disk can't even read sector 0. I strongly recommend making a full backup, both a pg_dump *and* a file-system level copy of the datadir. Personally I'd then do a test restore of the pg_dump backup on a separate Pg instance and if it looked OK I'd re-initdb then reload from the dump. Craig, Thank you very much for the tip on smartmontools, which I didn't know about. There indeed appears to be some problems with this disk: 8--- balapapa ~ # smartctl -d ata -a /dev/sdb -s on smartctl 5.40 2010-10-16 r3189 [x86_64-pc-linux-gnu] (local build) Copyright (C) 2002-10 by Bruce Allen, http://smartmontools.sourceforge.net === START OF INFORMATION SECTION === Model Family: Seagate Barracuda 7200.11 family Device Model: ST31000340AS Serial Number:9QJ1ZMHY Firmware Version: SD15 User Capacity:1 000 204 886 016 bytes Device is:In smartctl database [for details use: -P show] ATA Version is: 8 ATA Standard is: ATA-8-ACS revision 4 Local Time is:Thu Oct 6 07:46:19 2011 CEST == WARNING: There are known problems with these drives, AND THIS FIRMWARE VERSION IS AFFECTED, see the following Seagate web pages: http://seagate.custkb.com/seagate/crm/selfservice/search.jsp?DocId=207931 http://seagate.custkb.com/seagate/crm/selfservice/search.jsp?DocId=207951 SMART support is: Available - device has SMART capability. SMART support is: Disabled === START OF ENABLE/DISABLE COMMANDS SECTION === SMART Enabled. === START OF READ SMART DATA SECTION === SMART overall-health self-assessment test result: PASSED General SMART Values: Offline data collection status: (0x82) Offline data collection activity was completed without error. Auto Offline Data Collection: Enabled. Self-test execution status: ( 25) The self-test routine was aborted by the host. Total time to complete Offline data collection: ( 650) seconds. Offline data collection capabilities:(0x7b) SMART execute Offline immediate. Auto Offline data collection on/off support. Suspend Offline collection upon new command. Offline surface scan supported. Self-test supported. Conveyance Self-test supported. Selective Self-test supported. SMART capabilities:(0x0003) Saves SMART data before entering power-saving mode. Supports SMART auto save timer. Error logging capability:(0x01) Error logging supported. General Purpose Logging supported. Short self-test routine recommended polling time:( 1) minutes. Extended self-test routine recommended polling time:( 236) minutes. Conveyance self-test routine recommended polling time:( 2) minutes. SCT capabilities: (0x103b) SCT Status supported. SCT Error Recovery Control supported. SCT Feature Control supported. SCT Data Table supported. SMART Attributes Data Structure revision number: 10 Vendor Specific SMART Attributes with Thresholds: ID# ATTRIBUTE_NAME FLAG VALUE WORST THRESH TYPE UPDATED WHEN_FAILED RAW_VALUE 1 Raw_Read_Error_Rate 0x000f 114 099 006Pre-fail Always - 61796058 3 Spin_Up_Time0x0003 094 092 000Pre-fail Always - 0 4 Start_Stop_Count0x0032 100 100 020Old_age Always - 46 5 Reallocated_Sector_Ct 0x0033 100 100 036Pre-fail Always - 1 7 Seek_Error_Rate
[GENERAL] I/O error on data file, can't run backup
Running postgresql 9.0.5 on balapapa ~ # uname -a Linux balapapa 2.6.39-gentoo-r3 #1 SMP Sun Jul 17 11:22:15 CEST 2011 x86_64 Intel(R) Core(TM) i7 CPU 930 @ 2.80GHz GenuineIntel GNU/Linux I'm trying to run pg_dump on my database, and get an error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not read block 1 in file base/612249/11658: Inn/ut-feil pg_dump: The command was: SELECT tableoid, oid, opfname, opfnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = opfowner) AS rolname FROM pg_opfamily I have tried to stop postgresql and take a filesystem backup of the data directory with a cp -ax, but it crashes on the same file. I've looked at the directory with ls -l, and the file looks pretty normal to me. I've also rebooted from a live CD and run fsck on my /var partition, and it doesn't find any problem. The database is still working perfectly. The backup script overwrote my previous backup with a 40 byte file (yes silly me I know that's bloody stupid - I'm gonna fix that) and now I haven't got a recent backup anymore. Is this fixable? regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I/O error on data file, can't run backup
On Wednesday 5. October 2011 20.42.00 Tom Lane wrote: Postgres can't magically resurrect data that your drive lost, if that's what you were hoping for. However, you might be in luck, because that file is probably just an index and not original data. Try this: select relname from pg_class where relfilenode = 11658; On my 9.0 installation I get pg_opclass_am_name_nsp_index. If you get the same (or any other index for that matter) just reindex that index and you'll be all right ... or at least, you will be if that's the only file your drive has lost. Tom, this is what I get: postgres@balapapa ~ $ psql pgslekt psql (9.0.5) Type help for help. pgslekt=# select relname from pg_class where relfilenode = 11658; relname - pg_opfamily (1 row) regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I/O error on data file, can't run backup
I seemingly fixed the problem by stopping postgres and doing: balapapa 612249 # mv 11658 11658.old balapapa 612249 # mv 11658.old 11658 And the backup magically works. I'm gonna move the data to another disk right now. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I/O error on data file, can't run backup
On Wednesday 5. October 2011 22.41.49 Tom Lane wrote: Leif Biberg Kristensen l...@solumslekt.org writes: I'm gonna move the data to another disk right now. Good plan. Couple of things I forgot to mention, in case it matters: The disk is a 1 TB Seagate Barracuda S-ATA, and it has been in use for about a year. I've been using this brand since way back around 1998 without any problems, but have never used any disk more than 3 years. The file system is ext3. I had a hang on the machine a few hours earlier that required a power-off reboot. That has been a problem with this rig since I built it about a year ago, it's probably a funky connection somewhere. This may be the direct cause of the I/O error, which also may mean that the disk is not to blame. I'm so used to postgres and everything else coming up without a hiccup after a power-off that I don't usually pay much attention to it. But I'm certainly going to rework my backup strategy, and keep several generations. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I/O error on data file, can't run backup
On Thursday 6. October 2011 00.17.38 Steve Crawford wrote: I'm thinking perhaps a funky memory problem - you are having odd crashes after all. I've been thinking about the memory myself, but it passes memtest86plus with flying colors. Or at least it did the last time I checked which is a few months ago. The problems got a lot better after I replaced a monster Radeon XFX video card with a very basic fanless NVidia card (with the added bonus that I can now actually watch Flash videos in full screen), which may point to overheating issues. In other news: I discovered that injecting `date +%u` into the backup file name at an appropriate place will number it by weekday, which is great for keeping daily backups for a week. regards, Leif. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find freak UTF-8 character?
On Sunday 2. October 2011 15.53.50 pasman pasmański wrote: Its simple to remove strange chars with regex_replace. True, but first you have to know how to represent a «strange char» in Postgresql :P It isn't all that obvious, and it's difficult to search for the solution. I tried a lot of different search terms in Google, and none of them turned up anything near what I needed. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find freak UTF-8 character?
On Sunday 2. October 2011 16.34.27 Cédric Villemain wrote: you may have miss this one : http://tapoueh.org/blog/2010/02/23-getting-out-of-sql_ascii-part-2.html That's an, uh, interesting article, but as far as I can see, it doesn't tell anything about how to find a perfectly legal three-byte UTF-8 character that doesn't have a counterpart in LATIN1, given that all I know about it is its hexadecimal value. I know how to do it now, and I consider the problem solved. Hopefully, this thread may help others who stumbles upon the same issue. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find freak UTF-8 character?
On Sunday 2. October 2011 17.54.52 Raymond O'Donnell wrote: I may have missed it upthread, but if you haven't already would you consider writing up your solution for the benefit of the archives? I did, in my own first reply to the original message: SELECT * FROM foo WHERE bar LIKE E'%\xe2\x80\x8e%'; The trick is obviously to escape each byte in the sequence. Maybe I'll write a blog post about it. It appears to be weakly documented, or at least very hard to find. Or maybe it's just me being dense. regards, Leif. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to find freak UTF-8 character?
I've somehow introduced a spurious UTF-8 character in my database. When I try to export to an application that requires LATIN1 encoding, my export script bombs out with this message: psycopg2.DataError: character 0xe2808e of encoding UTF8 has no equivalent in LATIN1 I figure that it should be easy to find the offending character in the database by doing a SELECT * FROM foo WHERE bar like '%\0xe2808e%' or something like that, but I can't find the correct syntax, I can't find a relevant section in the manual, and I can't figure out how to google this problem. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find freak UTF-8 character?
On Saturday 1. October 2011 07.55.01 Leif Biberg Kristensen wrote: I've somehow introduced a spurious UTF-8 character in my database. When I try to export to an application that requires LATIN1 encoding, my export script bombs out with this message: psycopg2.DataError: character 0xe2808e of encoding UTF8 has no equivalent in LATIN1 I finally figured it out, with a little help from maatb's unicode database (http://vazor.com/unicode/c200E.html): SELECT * FROM foo WHERE bar LIKE E'%\xe2\x80\x8e%'; regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find freak UTF-8 character?
On Saturday 1. October 2011 21.29.45 Andrew Sullivan wrote: I see you found it, but note that it's _not_ a spurious UTF-8 character: it's a right-to-left mark, ans is a perfectly ok UTF-8 code point. Andrew, thank you for your reply. Yes I know that this is a perfectly legal UTF-8 character. It crept into my database as a result of a copy-and-paste job from a web site. The point is that it doesn't have a counterpart in ISO-8859-1 to which I regularly have to export the data. The offending character came from this URL: http://www.soge.kviteseid.no/individual.php?pid=I2914ged=Kviteseid.GEDtab=0 and the text that I copied and pasted from the page looks like this in the source code: Aslaug Steinarsdotter Fjågesundnbsp;nbsp;lrm;(I2914)lrm; I'm going to write to the webmaster of the site and ask why that character, represented in the HTML as the lrm; entity, has to appear in a Norwegian web site which never should have to display text in anything but left-to-right order. If you need a subset of the UTF-8 character set, you want to make sure you have some sort of constraint in your application or your database that prevents insertion of anything at all in UTF-8. This is a need people often forget when working in an internationalized setting, because there's a lot of crap that comes from the client side in a UTF-8 setting that might not come in other settings (like LATIN1). I don't want any constraint of that sort. I'm perfectly happy with UTF-8. And now that I've found out how to spot problematic characters that will crash my export script, it's really not an issue anymore. The character didn't print neither in psql nor in my PHP frontend, so I just removed the problematic text and re-entered it by hand. Problem solved. But thank you for the idea, I think that I will strip out at least any lrm; entities from text entered into the database. By the way, is there a setting in psql that will output unprintable characters as question marks or something? regards, Leif. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] max_stack_depth error, need suggestion
On Thursday 18. August 2011 12.39.31 AI Rumman wrote: I am using Postgresql 9.0.1 in Centos 5. Yesterday, I got the error inlog: 2011-08-16 10:57:34 EDT [3868]: [1-1] user=vcrm,db=vtigercrm504 ERROR: stack depth limit exceeded 2011-08-16 10:57:34 EDT [3868]: [2-1] user=vcrm,db=vtigercrm504 HINT: Increase the configuration parameter max_stack_depth, after ensuring the platform's stack depth limit is adequate. I found that I need to increase max_stack_depth. But doc says that it is a bit risky increasing it. Could any one please suggest me what the maximum safe value I may set in my environment? My Server RAM is 32 GB. That error message is usually caused by an infinite recursion. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Postgresql as application server
On Monday 15. August 2011 16.36.23 Merlin Moncure wrote: Postgres is not just a database -- it's a language hosting platform if you want to use it as such. Now, you can continue to do things as you've always done (database 'here', code 'here', web server 'here'), but why discourage people from trying out different things? Somebody's probably going to do it -- for no other reason why than because you can. Sometimes I'll write functions like CREATE OR REPLACE FUNCTION dpp(INTEGER) RETURNS SETOF TEXT AS $$ SELECT 'p class=packed' || ss_link_expand(source_text) || '/p' FROM sources WHERE parent_id=$1 ORDER BY sort_order $$ LANGUAGE SQL STABLE; for dumping thext that I'll copy and paste right into a static Web page. It's a lot easier to do this in psql than a lot of other methods that I can think of. BTW, the mentioned ss_link_expand() function will generate hyperlinks on the fly from a compact format stored in the database. The concept is explained here: http://solumslekt.org/blog/?p=151 I'm working with Postgres and PHP in tandem, and frequently write functions in sql or pl/pgsql that will output text directly in HTML format, mostly because I've found text transformation (particularly regexp_replace) in Postgres to be far superior to the equivalent methods of doing it in PHP. Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] announcements regarding tools
On Tuesday 19. July 2011 18.44.46 Scott Ribe wrote: I'm not sure to whom this specifically should be addressed, but something that's been bugging me for a while: announcements like this morning's AnySQL Maestro 11.7 released, where the announcement mentions nothing about platform support. And it's not just the lack of that info in announcements; on many web sites you have to dig for a while to find info about platform support, and I do not enjoy finding what looks like a nice tool, only to have to spend 5-10 minutes to figure out that it is Windows only. My suggestion: all such announcements should include information about supported platforms. Any announcement submitted without that info should be rejected, and the vendor instructed to add it before re-submission. I totally agree. In particular, Windows users seems to think that everybody else is using their platform, much as right-handers seem to unconsciously deny the existence of left-handers. The corollary being that if the announcement doesn't explicitly say otherwise, you can assume with about 99% confidence that the touted product is Windoze only. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] An amusing MySQL weakness--not!
On Sunday 26. June 2011 16.11.06 Vincent Veyron wrote: Le dimanche 26 juin 2011 à 00:05 -0700, Darren Duncan a écrit : Michael Nolan wrote: Having real BOOLEAN is just one of the reasons I like Postgres the most. Would you mind giving an example of where a boolean field would be a win over an integer one? I'm asking this because I frequently wonder what is best for my use; I normally query postgres via Perl modules, which don't care about boolean (the driver converts t/f to 0/1), but I like to tune my fields properly. PHP has its own Boolean values TRUE/FALSE, but reads Postgresql Booleans as 't'/'f'. You always have to rely on kludgy konstructs like if ($pg_bool == 't') then $my_bool = TRUE; elseif ($pg_bool == 'f') then $my_bool = FALSE; else $my_bool = NULL; It's of course much easier to use integer values, but it sucks not to use a bool when you want a variable that can't be anything but TRUE, FALSE or NULL. It obfuscates the code to use a var that evaluates to an integer, but in reality is a Boolean in disguise. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle / PostgreSQL comparison...
On Friday 24. June 2011 03.14.39 Rodrigo E. De León Plicet wrote: Here: http://cglendenningoracle.blogspot.com/2011/06/oracle-vs-postgres-postgresq l.html Any comments? I think he got a point in «Oracle as the second largest software company in the world» which is a killer argument from the PHB point of view. They're big because they're big. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle / PostgreSQL comparison...
On Friday 24. June 2011 06.01.31 Greg Smith wrote: The idea that PostgreSQL is reverse engineered from Oracle is ridiculous. Maybe he believes that SQL was invented by Oracle? regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what is the best way of storing text+image documents in postgresql
On Friday 10. June 2011 09.04.18 Arash pajoohande wrote: Actually, my word files consists of many exam questions. for each user, a random subset of questions must selected and displayed. it seem's that it would be nice if I have each question as an easy to handle document section (e.g. html div) and display them without need of any other application. That is a very trivial task in eg. PHP. Like John and Craig has said, just store the questions as plain text in the database. (Watch out for special MS characters.) Let the PHP script pick a random subset of predefined size and display it in the browser. Click on File - Print. Sounds like less than a day's work to me. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Passing parameters into an in-line psql invocation
On Thursday 2. June 2011 18.58.23 Gauthier, Dave wrote:
Hi:
I'd like to pass a parameter into an inline psql call that itself calls an
sql script, something like...
psql mydb -c \i thesqlscript foo
Wherefoo is the value I want to pass in.
Just as good would be the ability to sniff out an environment variable from
within the sql script (thesqlscript in the example above). In perl, I
would use $ENV{VARNAME}. Is there something like that in Postgres SQL?
V8.3.4 on Linux (upgrading to v9 very soon).
Thanks for any ideas !
Personally I prefer to write a small wrapper in Perl for interaction with
Postgres from the command line. Here's a boilerplate:
#! /usr/bin/perl
use strict;
use DBI;
my $val = shift;
if ((!$val) || !($val =~ /^\d+$/)) {
print Bad or missing parameter $val\n;
exit;
}
my $dbh = DBI-connect(dbi:Pg:dbname=mydb, '', '',
{AutoCommit = 1}) or die $DBI::errstr;
my $sth = $dbh-prepare(SELECT foo(?));
while (my $text = STDIN) {
chomp($text);
$sth-execute($val);
my $retval = $sth-fetch()-[0];
if ($retval 0) {
$retval = abs($retval);
print Duplicate of $retval, not added.\n;
}
else {
print $retval added.\n;
}
}
$sth-finish;
$dbh-disconnect;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] wnat ot edit pg_hba.conf file from command prompt
On Monday 02 May 2011 16:28:48 Sim Zacks wrote: sed for windows - http://gnuwin32.sourceforge.net/packages/sed.htm Whatever happened to edlin? regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 10 missing features
On Monday 25 April 2011 10:41:36 Linos wrote: Hi all, only want to link this blog post http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-features , i think he may have any good points. Miguel Angel. Maybe the best point is the one between the lines: That PostgreSQL is being compared to Oracle on a feature-by-feature basis. I don't know if his views are representative for Oracle DBAs, but it's an indication of PostgreSQL being considered in the same league as Oracle. The «features» perceived to be missing from PostgreSQL by the Oracle DBA is the icing on the cake. The cake itself (in terms of data integrity, performance, scalability) is obviously regarded as totally edible. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using column aliasses in the same query
On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote: Hi, I am not sure if there ever was a feature request for using defined column aliases in the rest of a query. This would make queries with a lot of logic in those aliased columns a lot smaller and this easier to write/debug. I already know you can use the following syntax: SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and lots of logic here) as col2, col3 FROM table) s WHERE col2 aValue But when you need to use (calculated) values from the actual record and or have sub-selects in your main select that also need to use these values things get really hairy. I don't know if the SQL specification allows it but I know that RDBMS's like Sybase already support this. Any thoughts? It's easy to define a view or an SQL function and stash the hairy logic there. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..
On Monday 04 April 2011 21:07:38 Martin Gainty wrote: ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language.. to whom might you be alluding to ??? Probably something starting with P. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..
On Monday 04 April 2011 21:20:51 John R Pierce wrote: On 04/04/11 12:07 PM, Martin Gainty wrote: ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language.. to whom might you be alluding to I only used a few of those adjectives, and prefixed them by hypothetical. to be honest, I would expect most languages commonly used in web service environments to be more efficient at string processing than pl/pgsql, and I really can't think of a counterexample off the top of my head. I had to move a piece of regexp/replace logic from PHP into pl/pgsql because PHP couldn't handle more than abt. 50 replacements in one text unit, instead it just dumped the text in the bit bucket. It was probably a memory allocation problem. On the other hand pl/pgsql has had no problem with the logic. Documentation here: http://solumslekt.org/blog/?p=23 regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UUID column as pimrary key?
On Wednesday 5. January 2011 16.05.29 Bill Moran wrote: Beyond that, the namespace size for a UUID is so incomprehensibly huge that the chance of two randomly generated UUIDs having the same value is incomprehensibly unlikely ... it is, however, not a 100% guarantee. I can't help thinking of the «Birthday Paradox»: http://en.wikipedia.org/wiki/Birthday_problem regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
On Wednesday 29. December 2010 13.18.40 Alban Hertroys wrote: Learning Vim is probably time well-spent, but until you do it's probably not that good a tool for fixing your problem. Although Vim is indeed a very powerful editor, it's not particularly easy to use. Unlike your usual editors like Notepad and friends, it's a command-based editor, meaning you have to execute a command before you can input or change data. It's an entirely different paradigm than what you're probably used to (I may assume wrongly here). Back when I used Windows, my favorite editor was EditPlus (http://www.editplus.com/). It isn't free, but well worth the 35 bucks. As a rather casual coder, I'm very satisfied with the simple editor Kwrite in KDE. It's a sheer delight compared to Notepad. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Root user commands
On Wednesday 22. December 2010 20.03.23 Bob Pawley wrote: Hi I am attempting to see if my Postgresql installation is running. I’ve found this - [root user only] ./database_service.pl status I don’t understand what is meant by root user. I also don’t know how “./database_service.pl status” is used. Bob You don't tell where you found this information. If you don't know what a root user is, you're probably on Windows. On *nix systems, root is the privileged user who can do all the things a normal user isn't allowed to do, like modifying system files. The 'database_service.pl' is a Perl script. Perl is a fairly default installation on *nix systems, there also exist builds for Windows. I used the Komodo flavor way back when. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copy From suggestion
On Monday 20. December 2010 15.24.58 Jorge Godoy wrote: With OpenOffice.org that 65K limit goes away as well... I don't know why it is still like that today for MS Office... It is almost 2011 and they still think 64K is enough? :-) Maybe there's an uncrippled «Professional» or «Enterprise» version costing an arm and a leg? ;) regards, Leif B. Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Adding data from mysql to postgresql periodically
On Sunday 14. November 2010 13.44.53 franrtorres77 wrote: Hi there I need to add periodically some data from a remote mysql database into our postgresql database. So, does anyone know how to do it having in mind that it must be runned every minute or so for adding new records to the postresql? It should be trivial to write a Perl script that pulls the data from MySQL, inserts them into PostgreSQL, and then goes to sleep for 60 seconds. regards, Leif B. Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Adding data from mysql to postgresql periodically
On Sunday 14. November 2010 14.33.39 franrtorres77 wrote: well, I know how to query to mysql but what i dont know is how to then write that data on the fly to the postgresql The DBD::Pg package has an excellent documentation: http://search.cpan.org/dist/DBD-Pg/Pg.pm regards, Leif B. Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Adding data from mysql to postgresql periodically
On Sunday 14. November 2010 14.33.39 franrtorres77 wrote: well, I know how to query to mysql but what i dont know is how to then write that data on the fly to the postgresql I'd also like to say that it's an interesting question, and a lot of people (including me) might want to take a stab at the solution. If you can tell what the data looks like coming from MySQL, and the corresponding table structure in PostgreSQL, you may well get a much more detailed reply. regards, Leif B. Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full Vacuum/Reindex vs autovacuum
On Monday 8. November 2010 20.06.13 Jason Long wrote: I currently have Postgres 9.0 install after an upgrade. My database is relatively small, but complex. The dump is about 90MB. Every night when there is no activity I do a full vacuum, a reindex, and then dump a nightly backup. Is this optimal with regards to performance? autovacuum is set to the default. I've got a database about the same size order (65 MB on disk, 5 MB dump.tgz) and I never bother with neither full vacuum nor reindexing. I run the default autovacuum, and if the db becomes bloated for some reason, I just do a dump/drop/reload cycle. It's done in a few seconds. regards, Leif B. Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do you control IMMUTABLE PG PROC results?
On Friday 5. November 2010 01.24.14 Carlo Stonebanks wrote: We have procs that would benefit from returning IMMUTABLE results. The procs are dependent on external tables that rarely change, but when they DO change, it would be great if we could expire the cache that the procs read from so that the procs are forced to re-evaluate the results. A function declared as IMMUTABLE can't, by definition, do database lookups. Then it has to be declared as STABLE. http://www.postgresql.org/docs/8.4/static/xfunc-volatility.html «An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever.» regards, Leif B. Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?
On Tuesday 2. November 2010 22.37.33 Carlos Mennens wrote: On Tue, Nov 2, 2010 at 10:53 AM, Steve Clark scl...@netwolves.com wrote: mv /var/lib/postgres/data /var/lib/postgres/data.old Before I move or rename '/var/lib/postgres/data', what version of PostgreSQL should I be at? 8.4 or 9.0? You should be at 9.0. The important thing is that you must start the new installation initdb with an empty data directory, or you'll inevitably get the complaints about incompatibility with the old files. It's quite logical, really, if you think about it. You will then have to do an initdb to create the basic 9.x databases. You can then use psql or pg_restore depending on how you dumped your data to restore your databases. I simply ran the following command: /usr/bin/pg_dump finance finance.sql That's only the first part of the upgrade process. Then you must prepare an empty directory to acommodate your data, do an initdb, and then populate the new directory from your dump file. I think that the Gentoo method works great: In the /var/lib/postgres directory, we just create a new subirectory named /8.3, /8.4, /9.0, and so on, and then a new /data directory under each one. That way, you can safely remove the old data when you've confirmed that the new version actually works. regards, Leif B. Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Printing command string passed to EXECUTE command in plpgsql (after argument resolution)
On Thursday 28. October 2010 16.25.47 Allan Kamau wrote: I am debugging a plpgsql function which contains a long sql query consisting of several parameters which is executed using EXECUTE command. I would like to output this command string including the actual values of the parameters contained within it so I can obtain the actual query and run it directly (manually) in psql (or other client such as pgAdmin3). Or at least is possible to output the command string as is, followed by the parameter resolution details such as $1='2', $2='abc' and so on. If I understand you correctly, you can assign the SQL string to a variable x, and then do a RAISE NOTICE 'Query is: %', x regards, Leif B. Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Front End Application (OFF-TOPIC)
On Friday 24. September 2010 19.15.39 Carlos Mennens wrote: I am using PostgreSQL to hold my entire I.T. inventory for my company. I am manually entering the data in via hand through the Linux command line and the 'psql' utility which is great for me but other co-workers in I.T. have no understanding of how to view the schema and I wanted to see if anyone had any recommendations for software or applications I could use that would display all my PostgreSQL data to them over the web of sorts? Something that lets them view the warranty expiration dates I entered in the Dell table. I don't want an administrative tool like 'phppgadmin' or anything but more like a utility that users can go in and view the database schema and data for reference only. Sorry if this is not the correct forum to ask this kind of question but I figured with everyone on the list using this in so many different varieties, I would find something. Thanks for your support! With PHP, it's very easy to build a Web interface to a PostgreSQL database. Even I have managed that :D regards, Leif Biberg Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trade Study on Oracle vs. PostgreSQL
On Friday 24. September 2010 20.04.26 Lincoln Yeoh wrote: Then again, does Tom ever sleep? Maybe he sleeps in the afternoon? Or is that when he does intensive coding? Once there was a suggestion on this list to give Tom every other week off to level the competition with the other RDBMSes. That being said, the PostgreSQL mail lists is an excellent and totally free support channel. Speaking for myself, I've never needed any support outside the mail lists. And Tom is of course a great big part of that. regards, Leif Biberg Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trade Study on Oracle vs. PostgreSQL
On Friday 24. September 2010 23.03.21 Joshua D. Drake wrote: DUDE! Are you trying to kill the postgresql company's business? :P Huh? Is there a company? :P The REALLY great thing about PostgreSQL is the free sharing. Back in 2002 when I discovered that all the software that I considered cool was Freenix based, I left the world of proprietary software and never looked back. This is my world. Thanks to every one of you who makes this possible. regards, Leif Biberg Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
On Thursday 12. August 2010 08.29.13 Ma Sivakumar wrote: What does a migrating PHP/MySQL user do? If MySQL performs fast just out of box (I have not used MySQL), what is different there? Do MySQL defaults give better performance? How do they arrive at those defaults? Or is it a completely different system, which can not be adapted in PostgreSQL? For the vast majority of use cases, ie. with db size 100 MB, PostgreSQL performs well enough out of the box. I guess that the same goes for MySQL. For the relatively few high-profile, high-performance installations some manual tuning obviously is needed, and I guess that applies whatever the name of system is. That is of course one of the major reasons why such installations need qualified DBAs. But for the long-tail segment where most of us belong, this isn't a problem. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MESSAGE ERROR
On Saturday 17. July 2010 15.14.51 Cornelio Royer Climent wrote: Hi I want to create this table, but i can't, Look this error. CREATE TABLE security_info2 ( window character varying(64) NOT NULL ); ERROR: syntax error at or near window LINE 2: window character varying(64) NOT NULL window is a reserved word in PostgreSQL since version 8.3, I believe. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cognitive dissonance
On Thursday 10. June 2010 17.24.00 Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Peter Eisentraut's message of jue jun 10 02:50:14 -0400 2010: As I said back then, doing this is straightforward, but we kind of need more than one user who asks for it before we make it part of a regular service, which comes with maintenance costs. Hey, count me as another interested person in a single-file plain-text doc output format. Well, there are two separate things here: * providing a Makefile target to build plain-text output. * shipping prebuilt plain text docs in standard distributions. I am for #1, not so much for #2, mainly on the grounds of size. But given #1 it would be possible for packagers to make their own choices about whether to include plain-text docs. Wouldn't it suffice to make it downloadable, like the pdf doc? regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] uppdate from postgersql 8.3.7 to 8.4.4
On Tuesday 25. May 2010 12.15.14 Alban Hertroys wrote: I know it's totally unrelated, but when did it become popular to send (HTML) messages in a very small blue font? I find this rather hard to read and usually fall back to the plain text alternative (which is included, thankfully). At least there's a plain text fallback. Messages in HTML only are totally unreadable in a plaintext MUA, and should IMO be bounced from the list. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fwd: Google Alert - postgresql 8.4
On Monday 17. May 2010 17.13.05 Thom Brown wrote: Is this announcement premature? I don't see where to download 8.4.4. -- Forwarded message -- From: Google Alerts googlealerts-nore...@google.com Date: Mon, May 17, 2010 at 5:18 AM Subject: Google Alert - postgresql 8.4 To: richard.broer...@gmail.com Google News Alert for: postgresql 8.4 PostgreSQL developers fix vulnerabilities The H PostgreSQL versions 8.0 to 8.4 and 7.4 are affected by the update. Installation packages and source code are available online. 8.4.4, 8.3.11, 8.2.17, ... Erk... yeah, that does appear to be premature. The site doesn't reflect that announcement. I'm on Gentoo Linux, and am compiling PostgreSQL 8.4.4 from portage (~x86) as I'm writing. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Resetting serial type after delete from table
On Saturday 8. May 2010 10.11.32 John Gage wrote: If I delete from table, which table contains a serial type field, and then insert new rows into the table excluding the [serial] column from the list of columns in the INSERT statement, the numbers in the serial column resume where they left off prior to the delete from table: 639, 640, 641, 642 for example. This behavior is totally acceptable, but is it possible to have the serial column reset itself to 1 following delete from table (i.e. following flushing all the rows from the table)? The only way I can think to do this is by altering the table by dropping the serial column and then altering it again by adding a new serial column before doing the insert. That is only a couple of more lines of script, so I don't do the work, but is there an easier way? http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE- SERIAL http://www.postgresql.org/docs/current/static/functions-sequence.html regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GeSHi module for Postgresql?
On Tuesday 4. May 2010 08.20.56 Yeb Havinga wrote: Leif Biberg Kristensen wrote: Now I only need a list of the built-in functions. There doesn't seem to be a pg_get functions()? Try psql -E postgres (-E shows all queries psql does on the catalog) then \df Thanks, Yeb. I'm starting to get second thoughts about including a full function list; there's a myriad of functions of which I guess nobody (except for Tom Lane of course) has a full overview. It's probably better to provide a short list of the most common functions, and then let users add to it according to their own domain of usage. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GeSHi module for Postgresql?
On Tuesday 4. May 2010 16.31.20 Tom Lane wrote: Leif Biberg Kristensen l...@solumslekt.org writes: Now I only need a list of the built-in functions. There's an awful lot of them, many of which aren't really intended to be called by users anyway. Can't you just do if it looks syntactically like a function call, assume it is one? As the FAQ points out, «GeSHi is not a lexical parser, unlike other highlighting solutions.» http://qbnz.com/highlighter/faq.php So, that's not really feasible the way GeSHi is built. That said, I've already compiled a list of most of the functions from the documentation. I only omitted system administration functions which may be postponed for a later version. I've submitted a preliminary draft of a Postgresql language file, and am waiting for a reply. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GeSHi module for Postgresql?
On Tuesday 4. May 2010 16.31.20 Tom Lane wrote: Leif Biberg Kristensen l...@solumslekt.org writes: Now I only need a list of the built-in functions. There's an awful lot of them, many of which aren't really intended to be called by users anyway. Can't you just do if it looks syntactically like a function call, assume it is one? Another point: I'm uncertain how to define a «user» in this context. Someone who wants to highlight plpgsql code snippets is most likely to be some kind of developer. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GeSHi module for Postgresql?
On Tuesday 4. May 2010 18.05.02 Tom Lane wrote: Leif Biberg Kristensen l...@solumslekt.org writes: Another point: I'm uncertain how to define a «user» in this context. Someone who wants to highlight plpgsql code snippets is most likely to be some kind of developer. Sure, but what I meant was not intended to be called directly from SQL. Lots of those functions underlie operators, for example, and you're really expected to use the operator instead. Stuff like btree support functions likewise not really intended to be called manually. Yes, I think we are agreed. See also my earlier reply to Yeb Havinga, where I expressed some second thoughts about including a full function list. I think that the «public» functions included in the docs are sufficient for most purposes. There aren't an awful lot of them; I compiled a list by hand in a couple of hours. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GeSHi module for Postgresql?
GeSHi http://qbnz.com/highlighter/images/geshi.png is a system for highlighting code. I've just installed the WP-Syntax plugin, which utilizes GeSHi, in my WP blog because I like code highlighting and am tired of doing it more or less by hand. However, I was disappointed when I didn't find PostgreSQL on the list of supported languages. I wonder if any of you have at least started writing a PostgreSQL module, in which case I'll be happy to contribute. From a peek at the plsql module, it doesn't look like rocket science. If I have to write one from scratch, I wonder if there' s a compiled list somewhere of just the PostgreSQL keywords and function names. That would be of great help. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GeSHi module for Postgresql?
On Monday 3. May 2010 22.49.21 Leif Biberg Kristensen wrote: GeSHi http://qbnz.com/highlighter/images/geshi.png is a system for Sorry about that link. It's of course http://qbnz.com/highlighter/index.php. *blush* -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GeSHi module for Postgresql?
On Monday 3. May 2010 23.05.54 Richard Broersma wrote: On Mon, May 3, 2010 at 1:49 PM, Leif Biberg Kristensen l...@solumslekt.org wrote: I wonder if there' s a compiled list somewhere of just the PostgreSQL keywords and function names. That would be of great help. These are what I know of: Richard, thank you. http://www.postgresql.org/docs/9.0/static/sql-keywords-appendix.html That list is a table with descriptions etc, but I can extraxt the keywords with a little Perl magic. http://www.postgresql.org/docs/9.0/static/functions.html That's not very useful. I see that I can get a list of the functions in public with a \df, put can I get a corresponding one for the system functions? http://www.postgresql.org/docs/9.0/static/reference.html That list is probably superfluous, as I suspect those words are already in the keywords list. But thanks anyway. I've also received a private message from a guy with an «under work» project, and along with your hints it's a good start. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GeSHi module for Postgresql?
On Monday 3. May 2010 23.02.05 Christophe Chauvet wrote: hi i have begin this work, you can find it at http://svn.postgresqlfr.org/repos/tools/geshi/trunk/ Christophe, thank you very much! I've started to look at it, and have already taken your postgresql.php for a spin. You can see the first test at my blog under the title «Regular expression fun in PostgreSQL». I don't want to post the direct link here. As soon as I've got some real progress, I'll send you an updated file. regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GeSHi module for Postgresql?
On Monday 3. May 2010 23.50.55 Alvaro Herrera wrote: Why wouldn't you simply ask Postgres? Because I didn't know how :D That's why we've got this wonderful list, isn't it? select * from pg_get_keywords(); Cool! Thank you! I really only need the 'word' column. I'll separate the data types from the rest of the keywords, though. Now I only need a list of the built-in functions. There doesn't seem to be a pg_get functions()? regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fuzzy string matching of product names
On Monday 5. April 2010 22.00.41 Peter Geoghegan wrote: similar they sound. How can that actually be applied to get the functionality that I've described? I've got a similar problem in my 18th century research, when clerks usually took pride in being able to spell a name in any number of ways. I've landed on a solution where I'm sending search strings to SIMILAR TO. I usually get far too many hits, but it's much easier to browse through 100 hits than the entire dataset which is approaching 60,000 records. Optimizing the search strings is based upon a lot of experience. It would probably be better to add a column with normalized names, but the amount of work involved with that is staggering. I eventually associate most of the records to «persons» with normalized names, but the search process can sometimes be very frustrating, and it would really help with some kind of fuzzy search. Just in case anyone should suggest it: Both Soundex and Metaphone are useless for Norwegian 18th century names. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice on webbased database reporting
On Friday 2. April 2010 14.43.48 Davor J. wrote: I need to make certain views from the database visible online (on our webpage) and I wonder if there is any reasonably quick solution for this that works with Postgres? At best, a query should be specified and the user should be able to select the layout on certain columns (like stepped, or outlined). I don't mind running a whole CMS on our Apache server as long as it allows me to make reports and is free to use. Has anyone any suggestions? PHP works great with Postgres and Apache. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] insert into test_b (select * from test_a) with different column order
On Monday 29. March 2010 16.51.35 Ole Tange wrote:
I have 2 tables that have the same column names but in different
order. Similar to this:
create table test_a (col_a text, col_b int);
create table test_b (col_b int, col_a text);
insert into test_a values ('abc', 2),( 'def', 3);
I would like to do this:
insert into test_b (select * from test_a);
This fails because the columns in test_b are not in the same order as
test_a. For my use case the tables may get more columns or have
columns removed over time og be recreated in a different order, the
only thing that is given is that the column names in test_a and test_b
always are the same and that the datatype of the named columns are the
same.
Is there a general solution I can use to do the insert?
Per the SQL standard, there's no inherent order between columns. That said,
you'll usually get the columns in the order that they were created, but
there's no guarantee for it. Actually, when you do a SELECT * FROM ... you
make a totally unwarranted assumption that the columns will come out in any
specific order. So, the answer to your question is to specify the columns
explicitly in your query, as
insert into test_b (select col_b, col_a from test_a);
regards,
--
Leif Biberg Kristensen
http://solumslekt.org/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Self-referential records
On Sunday 24. January 2010 14.43.10 Ovid wrote: Assuming I have the following table: CREATE TABLE refers ( idSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) ); I need to insert two records so that select * from refers looks like this: =# select * from refers; id | name | parent_id +--+--- 1 | | 1 2 | yyy | 2 The first record can't be inserted because I don't yet know the parent_id. I've got a similar structure. I just declared the root node with both id and parent_id=0. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Self-referential records
On Sunday 24. January 2010 16.22.00 Wayne E. Pfeffer wrote: If you do not use null to represent a root node, when you go to unwind the data from the table to generate a hierarchy tree, you could end up with an infinite loop. The query will always be looking for the next parent in the hierarchy. Meaning, you will want to find the parent of a node using the given parent_id, the query will find the parent of 1 to be 1, then it will look again for the parent of 1 it will find 1, etc. etc. ad nauseum. I enjoy using recursion as much as the next guy, but this could cause some serious issues with the PostgreSQL query engine eating up system resources. It doesn't really matter if the root node is NULL or 0 or whatever. You just have to realize that the root node is a special case and program accordingly. An adjacency tree is not a normalized structure, and will never be. It's the programmer's responsibility to ensure that circular references can't occur. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configuration Optimisation
On Thursday 14. January 2010 14.31.07 Howard Cole wrote: Test setup: pgbench -i -s 5 Test run: pgbench -T 120 You may think this is a short test, but running it for much longer does not seem to make a significant difference. Now running on windows I get ~ 700 TPS, but on linux I am getting ~70 TPS. The hardware configuration is Dual Opteron, 8GB Ram and 4 sata disks in a Hardware Raid 10 configuration. For what it's worth, on my aging Gentoo Linux workstation with a Pentium 4 @ 3 GHz, 1 GB RAM and a single 400 GB SATA Seagate Barracuda disk, default Postgres 8.4.2 installation, I get: l...@balapapa ~ $ pgbench -i -s 5 ... l...@balapapa ~ $ pgbench -T 120 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 5 query mode: simple number of clients: 1 duration: 120 s number of transactions actually processed: 29990 tps = 249.694058 (including connections establishing) tps = 249.700935 (excluding connections establishing) regards, -- Leif Biberg Kristensen http;//solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configuration Optimisation
On Thursday 14. January 2010 16.02.12 Andy Colson wrote: I think an important question is: are both os's really flushing all the way to disk, or is someone lying to you? Assuming your workload is IO bound, I'd bet windows is write caching and linux is not. See my reply above. My 250 tps seems in line with the 700 tps on a modern system that the OP gets on his Windows setup. To me it seems like something is broken on his Ubuntu setup. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configuration Optimisation
On Thursday 14. January 2010 16.55.07 Howard Cole wrote: Leif Biberg Kristensen wrote: On Thursday 14. January 2010 16.02.12 Andy Colson wrote: See my reply above. My 250 tps seems in line with the 700 tps on a modern system that the OP gets on his Windows setup. To me it seems like something is broken on his Ubuntu setup. regards, I am hoping your assumptions are correct. I have reinstalled ubuntu from scratch as I had been tinkering with the power management on the server. Once the RAID has rebuilt, I shall try again. What kind of file system are you running? regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to get DATE in server locale format
On Sunday 10. January 2010 22.57.38 Andrus wrote: Server lc_times contains non-US locale. SELECT CURRENT_DATE::TEXT still returns date in format -MM-DD How to get date in server locale format ? Pg doesn't care about lc_times. http://www.postgresql.org/docs/current/static/datatype-datetime.html#datatype- datetime-output2-table pgslekt= SELECT CURRENT_DATE::TEXT; text 2010-01-11 (1 row) pgslekt= set datestyle to german; SET pgslekt= SELECT CURRENT_DATE::TEXT; text 11.01.2010 (1 row) regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Gentoo, 8,2 --- 8.4, and /var/run/postgresql in mode 770
On Saturday 9. January 2010 22.20.36 fe...@crowfix.com wrote: I just upgraded my home gentoo system's postgresql from 8.2.14 to 8.4.2. I use it mostly for fooling around and keeping smatterings of personal data, so it was simple laziness which kept me from upgrading sooner, triggered by the gentoo switch back in 8.2.mumble in how they manage postgresql. Everything went smoothly except the permissions of the directory /var/run/postgresql with the domain socket .s.PGSQL.5432. This dir had permissions of 770, owned by postgres.postgres, so no mere mortals could access it. I have changed this to 775 and can now access it. Didn't 8.2 put these in /tmp? Maybe this was a gentoo thing. What should the permissions be for this? Or does gentoo do their own thing and there is a different standard way of handling this? In Gentoo, you must add the database owner (probably your own username) to the group Postgres. This was changed with 8.3, and if you had read the message from emerge, you should have noticed. And, yes, I fumbled a lot with this myself. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rules and conditions
On Wednesday 9. December 2009 09.06.12 Guillaume Lelarge wrote: Le mercredi 9 décembre 2009 à 01:52:03, George Silva a écrit : [...] I can't seem to understand why a simples if is not working on the creation of rules. I tried both ways (am i missing something?): Sure. IF does not exist in SQL, and you can only put SQL statements in a rule. Instead, you can call a stored function written in PL/pgsql which supports IF structure. Or you can look up the CASE WHEN ... THEN ... ELSE .. END, construct, which works very much like IF / THEN / ELSE: http://www.postgresql.org/docs/current/static/functions-conditional.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
