Re: [GENERAL] Petition: Treat #!... shebangs as comments

On Fri, Jul 18, 2014 at 08:32:53AM -0700, Adrian Klaver wrote: > >Could we please have the PostgreSQL lexer treat #!... on the first line > >of a file as a comment? This would enable .psql scripts to be run with > >dot-slash notation preferred by many unix users: > > > >./script.psql > > > >While

[GENERAL] pg_dump enhancement ?

Let's assume there's a table dem.address CREATE TABLE address ( id integer NOT NULL, id_street integer NOT NULL, aux_street text, number text NOT NULL, subunit text, addendum text, lat_lon point )

Re: [GENERAL] Need help on triggers - postgres 9.1.2

On Thu, May 22, 2014 at 02:48:48PM +0200, Khangelani Gama wrote: > > CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS > > $center_changed$ > > > > BEGIN > > > > IF (TG_OP = 'UPDATE') THEN > > INSERT INTO center_changed SELECT c_cde, now(); PostgreSQL probab

Re: [GENERAL] Psycopg2 : error message.

On Fri, May 16, 2014 at 11:07:42AM -0400, skeeved wrote: > Looks like you have an invalid connection string: > conn=psycopg2.connect("dbname='busard_test' user='laurent' host='localhost' > password='cactus’") > Try removing the double quotes, they are obscuring the parameter names and > values:

Re: [GENERAL] Psycopg2 : error message.

You need to search the web for how and whether to quote values inside the DSN for psycopg2. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscri

Re: [GENERAL] Psycopg2 : error message.

On Fri, May 16, 2014 at 04:19:25AM -0700, image wrote: > Indeed i have a new error message in my DOS interface : > > pg_test.py > > > SS_dos.JPG > You'll ha

Re: [GENERAL] Psycopg2 : error message.

On Fri, May 16, 2014 at 03:41:15AM -0700, image wrote: > Thanks. Ok i deleted the except. Please find below the 2 updates usefull > liles : > > pg_test.py > You need to learn to write proper Python code, especially try: except

Re: [GENERAL] importing a messy text file

On Wed, Apr 30, 2014 at 10:07:09AM +0200, Willy-Bas Loos wrote: > it would probably not be > feasible to manually add tabs for every record that misses one, although i > don't know how many it would be. Why not a) let a script do that, b) remove tabs from the end of lines as needed ? All in all

Re: [GENERAL] Linux OOM-Killer

On Mon, Mar 17, 2014 at 12:21:30PM +0100, basti wrote: > uname -a > Linux h2085616 3.2.0-3-amd64 #1 SMP Mon Jul 23 02:45:17 UTC 2012 x86_64 > GNU/Linux > > At any time there are not more than 20-30 Connections at once. > > Swap is disabled. > free -m > total used free

Re: [GENERAL] Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

On Sat, Feb 15, 2014 at 10:17:05PM +, Antman, Jason (CMG-Atlanta) wrote: > [...] I see how my original "brilliant" idea > (multiple DBs per postgres instance on one host, [...]) is insane, > without some specific support for it in postgres. "multiple DBs per PostgreSQL instance on one host"

Re: [GENERAL] Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

On Sat, Feb 15, 2014 at 06:15:04PM +, Antman, Jason (CMG-Atlanta) wrote: > I also asked this question on dba.stackexchange.com, where it received a very > detailed enumeration of the associated problems from Craig Ringer: ... > Perhaps there's a postgres internals expert around, someone > i

Re: [GENERAL] Any freeware graphic display of DDL software available?

On Thu, Jan 16, 2014 at 08:45:44AM -0800, Susan Cassidy wrote: > Is there any free or cheap software that will read in DDL and output a > graphic display of it? Preferably showing links for foreign keys. pg_autodoc Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 453

Re: [GENERAL] getting domain information from query results

On Thu, Jan 09, 2014 at 12:10:34PM +0100, Marco Baringer wrote: > assuming i have this schema: > > create domain almost_a_string AS text; > create table object ( name_like_thing almost_a_string ); > > and i'm trying to go from the results, using postgresql's > frontend/backend protocol, of t

[GENERAL] a PostgreSQL slogan misused

I suppose the PostgreSQL Publice Relations people might be interested in this misuse of PostgreSQL phrase: http://www.computerweekly.com/blogs/open-source-insider/2013/11/why-elephants-never-forget-big-data.html Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD

Re: [GENERAL] pg_upgrade ?deficiency

> Bruce Momjian writes: > > Not sure about backpatching. default_transaction_read_only has been > > around since 7.4. Setting it to true would cause pg_dump to fail unless > > you changed the database setting, and pg_dumpall would fail completely > > as there is no way to turn off the database s

Re: [GENERAL] pg_upgrade ?deficiency

On Thu, Nov 21, 2013 at 06:22:50AM -0800, Kevin Grittner wrote: > I would be happy to supply a patch to treat > default_transaction_read_only the same as statement_timeout or > standard_conforming_strings in pg_dump and related utilities. > Since it causes backup/restore failure ... (and pg_upgr

Re: [GENERAL] pg_upgrade ?deficiency

> On Wed, Nov 20, 2013 at 02:36:08PM +0100, Karsten Hilbert wrote: > > > Karsten Hilbert wrote: > > > > Let me try to rephrase: > > > > > > > > Fact: pg_upgrade can NOT properly upgrade clusters which contain > > > >

Re: [GENERAL] pg_upgrade ?deficiency

> Karsten Hilbert wrote: > > Let me try to rephrase: > > > > Fact: pg_upgrade can NOT properly upgrade clusters which contain > > databases that are set to "default_transaction_read_only on" > > > > Question: Is this intended ? > >

Re: [GENERAL] pg_upgrade ?deficiency

> On Tue, Nov 19, 2013 at 11:22:47AM +0100, Karsten Hilbert wrote: > > ERROR: transaction is read-only > > > > Now, this is quite understandable since one of the databases > > is set to > > > > ALTER DATABASE ... SET DEFAULT_TRANSACTION_READ_ONLY T

[GENERAL] pg_upgrade ?deficiency

Hello all, I am upgrading a 8.4 cluster to 9.1 and am seeing the following: SQL command failed CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnames

Re: [GENERAL] Stop execution without ERROR

On Wed, Oct 16, 2013 at 12:04:53PM +1100, James Sewell wrote: > Let's say I'm running the following SQL script via psql -f > > BEGIN > \set ON_ERROR_STOP > SELECT myFunction(); > CREATE TABLE x(id int); > END; > > Is there anything I can do in myFunction which will: > > a) Stop execution of the

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 10:40:36AM -0700, Steve Crawford wrote: > >>>The reason for that is that in PostgreSQL there is no time zone > >>information stored along with a "timestamp with time zone", > >>it is stored in UTC. > >A better name might perhaps been "timezone aware timestamp". > > > >Karst

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 02:09:23PM +, Albe Laurenz wrote: > Karsten Hilbert wrote: > > Whatever became of the 2011 intent to implement > > the above that's linked to in the blog post ? > > You'd have to ask Alvaro. I figured he'd maybe read this on-list

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 11:48:02AM +, Albe Laurenz wrote: >> Or does that still need some C sprinkling (for operator support, say) ? > > Exactly. If you want "<" to work right for this data type > that's the road you have to go. I see. Whatever became of the 2011 intent to implement the abo

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 09:18:30AM +, Albe Laurenz wrote: > > Maybe the question I need to ask is "how can I store the time zone along > > with the timestamp" > > Store an additional field "offset". > If you want to invest more energy and don't mind writing C, > you could create your own dat

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 09:49:38PM +1300, Tim Uckun wrote: > >The reason for that is that in PostgreSQL there is no time zone > information stored along with a "timestamp with time zone", > it is stored in UTC. A better name might perhaps been "timezone aware timestamp". Karsten -- GPG key ID E

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 09:49:38PM +1300, Tim Uckun wrote: > Maybe the question I need to ask is "how can I store the time zone along > with the timestamp" You need an extra field, say, of type interval. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9

Re: [GENERAL] Call for design: PostgreSQL mugs

> ...I wonder how long it will be before we have mugs where you can > actually tap the logo with a finger and get send to the website! A QR code is as close as it gets these days. Karsten Hilbert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes t

Re: [GENERAL] Call for design: PostgreSQL mugs

- freshly painting bitbuckets - indexing/filing (paper) charts - ... Karsten Hilbert -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] question about age()

On Thu, Aug 29, 2013 at 09:11:07PM +0200, Andreas Kretschmer wrote: > test=*# select * from birthday where age(birthday) != age (current_date-1, > birthday); > id | birthday > +-- > (0 rows) > > '3 years' != '2 years 11 mons 30 days', but i got 0 rows, why? What does selec

Re: [GENERAL] pg 9.2.4 dblink

On Tue, Jul 09, 2013 at 03:55:47PM +0200, Peter Kroon wrote: > I want to talk to multiple db's in one session. You'll have to define "session" to get meaningful answers. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-gen

Re: [GENERAL] Semi-Pseudo Data Types & Procedure Arguments

On Wed, Jun 26, 2013 at 01:50:46AM -0400, Joshua Burns wrote: > Example #1: > -- A stored procedure which can accept two arguments, each of which could > be text, character varying, character varying(any length) or character(any > length). > SELECT * FROM my_fn('val1'::text, 'val2'::character(4));

Re: [GENERAL] regex help wanted

On Thu, Apr 25, 2013 at 03:40:51PM +0100, Thom Brown wrote: > On 25 April 2013 15:32, Tom Lane wrote: > > Karsten Hilbert writes: > >> What I don't understand is: Why does the following return a > >> substring ? > > > >&

Re: [GENERAL] regex help wanted

On Thu, Apr 25, 2013 at 10:32:26AM -0400, Tom Lane wrote: > Karsten Hilbert writes: > > What I don't understand is: Why does the following return a > > substring ? > > > select substring ('junk $$ junk' from > > '\$<[^<]+?::[^

[GENERAL] regex help wanted

Hi, I am in the process of converting some TEXT data which I try to identify by regular expression. What I don't understand is: Why does the following return a substring ? select substring ('junk $$ junk' from '\$<[^<]+?::[^:]+?>\$'); I would have thought the '::[^:]+?>' part should ha

Re: [GENERAL] Need solution for weekly database "snapshot"

On Mon, Apr 22, 2013 at 03:16:19PM -0400, Moshe Jacobson wrote: > > > Not bad, but the transaction logs would fill up the file system. > > > > I'm not sure I understand that comment. Why would the transaction logs be > > particularly voluminous in this case? > > > > I assumed the logs would be s

Re: [GENERAL] Need solution for weekly database "snapshot"

On Mon, Apr 22, 2013 at 11:58:58AM -0400, Moshe Jacobson wrote: > We have a report in our system that is pulled many times each week, but > needs to be based off data from Sunday at noon. So every Sunday at noon, we > back up our main database and restore it into a new reporting snapshot > databas

Re: [GENERAL] PostgreSQL registry entries for apt-get/yum/rpm install

On Mon, Apr 15, 2013 at 01:21:55PM +0530, dinesh kumar wrote: > Would like to request you to share your valuable inputs on this. I would > like to know the PostgreSQL registry entries when we install it through > apt-get/yum/rpm. I mean, when we install the EnterpriseDB PostgreSQL one > click inst

Re: [GENERAL] How to store version number of database layout

> It's more like a question of best practice: > How do you managing different version of database layout for e.g. > software releases? > We are planing to do an application based on postgres and want to store > some version number of database layout to ensure correct upgrade-scripts > can be applie

Re: [GENERAL] Database schema

> I have created a database name '"ofbiz". then the default schema name > "public " created automatically. I want to create schema name ofbiz in the > database "ofbiz" >  when I create database name "ofbiz" then the schema name "ofbiz" will > create automatically. > how it is possible if yes, Pre

Re: [GENERAL] Four issues why "old elephants" lack performance: Explanation sought Four issues why "old elephants" lack performance: Explanation sought

On Sun, Feb 26, 2012 at 08:37:54AM -0600, Andy Colson wrote: >> 3. WAL logging > > PG writes a transaction twice. Once to WAL and once to > the DB. WAL is a simple and quick write, and is only ever > used if your computer crashes and PG has to re-play > transactions to get the db into a good/kn

Re: [GENERAL] Extraneous Files

On Thu, Oct 20, 2011 at 02:32:18PM -0400, Scott Mead wrote: > On Thu, Oct 20, 2011 at 1:12 PM, Ian Harding wrote: > > > If someone happened to accidentally end up with a lot of files that > > were NOT part of their database in the data/base/X directory, how > > could they go about getting a

Re: [GENERAL] PG 9.1 on Debian Squeeze

On Sat, Oct 08, 2011 at 03:54:31PM +0100, Raymond O'Donnell wrote: Oh, wait, you said Squeeze - which doesn't have: > Thanks everyone - I installed dpkg-dev, and it now produces a > different message: > > rod@simecom:~$ psql -U postgres -h localhost > DEB_HOST_MULTIARCH is not a supported variab

Re: [GENERAL] PG 9.1 on Debian Squeeze

On Sat, Oct 08, 2011 at 12:19:22PM +0100, Raymond O'Donnell wrote: > I'm not sure if this is the correct place for this, but here goes: I've > just installed Postgres 9.1.1 from backports.debian.org on a fresh > installation of Debian Squeeze, and when I run psql here's what I get: > > rod@simeco

Re: [GENERAL] (another ;-)) PostgreSQL-derived project ...

On Sun, Sep 25, 2011 at 09:41:19PM +, Albretch Mueller wrote: > I am amazed to read that you/the PC community were still running regression > tests > > *in ASCII*: > > http://www.postgresql.org/docs/9.1/static/release-9-1.html (E.1.3.12. Source > Code) > * Run regression tests > (postgr

Re: [GENERAL] md5 of table

On Thu, Sep 01, 2011 at 11:47:24AM +0300, Sim Zacks wrote: > Is there a way to get an md5 or other hash of an entire table? > > I want to be able to easily compare 2 tables in different databases. > > I thought about using dblink and the EXCEPT query, but then I need to > know the field list of

Re: [GENERAL] Implementing "thick"/"fat" databases

> I wonder which other languages have first class support for these areas of > Pg? While already supporting most if not all standard PG datatypes the psycopg2 interface lets you write in/out wrappers of arbitray complexity mapping PG <-> Python datatypes and insert them into the driver at runtime.

Re: [GENERAL] Implementing "thick"/"fat" databases

Hello Chris, > > In LedgerSMB, we take this a step further by making the procedures > > into discoverable interfaces, how do you do that ? Karsten -- NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie! Jetzt informieren: http://www.gmx.net/de/go/freephone -- Sent

Re: [GENERAL] Streaming replication on 9.1-beta2 after pg_restore is very slow

On Thu, Jul 07, 2011 at 11:14:05AM -0400, Tom Lane wrote: > Looking back, I notice that you built with gcc 4.6.0. At least on Red > Hat machines, that gcc has a rather nasty optimization bug that breaks > WAL replay, with symptoms that seem to match what you have here --- > namely, the replay pro

Re: [GENERAL] rationale behind quotes for camel case?

On Tue, Jun 28, 2011 at 11:09:52AM -0500, dennis jenkins wrote: > Any suggestions on how to name tables when table names contain both > multi-word nouns and mutli-table "many-many" mappings? > > Example: Suppose that I have a table called "foo" and another table called > "barBiz" (or "bar_biz" i

Re: [GENERAL] Per-query local timezone

On Tue, Jun 14, 2011 at 09:40:20AM +, Mark Morgan Lloyd wrote: > Is it possible to incorporate SET TIMEZONE into a query, so that > to_char(...'TZ') etc. is appropriately localised? You seem to want "AT TIME ZONE". Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD

Re: [GENERAL] determine client os

On Tue, Jun 14, 2011 at 09:35:52AM +0300, Sim Zacks wrote: > >Sim Zacks writes: > >>All the suggestions given are for the server OS :-( > >>My purpose is to be able to return a correct file path to the client > >>without it specifying the OS. > >File path? Seems to me that even if you knew the c

Re: [GENERAL] Need suggestion

On Fri, Jun 03, 2011 at 07:15:40AM +0200, to...@tuxteam.de wrote: > but you wouldn't have large blobs of data clobbering your "regular" queries. You would want to write better queries than select * from my_table_with_bytea_column; anyway. > You could pass the scans and pics piecemeal b

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Wed, May 04, 2011 at 09:33:57AM -0400, David Johnston wrote: > “Hello - person born in Liverpool London, St. Whatever > hospital, Room 101 @ 13:14:57AM on the 5th of March 2001 – > how may I direct your call?” (I guess you could use the > conception date as well That will rarely be known to an

Re: [GENERAL] [ADMIN] Can we Flush the Postgres Shared Memory ?

On Tue, May 03, 2011 at 03:33:34PM +0100, Simon Riggs wrote: > > The contents of this email may not be copied or forwarded in part or in > > whole without the express written consent of the author. > > Pleased to meet you Mark. > > If you post here, the above disclaimer is not effective. Right n

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Tue, May 03, 2011 at 10:52:23AM +0800, Craig Ringer wrote: > ... and that's before we get into the horror of "what is someone's > name". Which name? Which spelling? Do they even have a single canonical > name? - people have, at least over time, several compound names - they have, at any one ti

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Fri, Apr 29, 2011 at 10:14:07AM -0500, Merlin Moncure wrote: > I took a quick look at the gnumed schema and found it to be generally > very thorough and excellent. If you're going to use surrogate keys, > that's they way to do it. Good to know since I'm only a lowly medical doctor not having

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Thu, Apr 28, 2011 at 03:39:19PM -0500, Merlin Moncure wrote: > They are fairly pervasive, and increasingly so, which I find to be > really unfortunate. Personally I think rote use of surrogate keys is > terrible and leads to bad table designs, especially if you don't > identify the true natura

Re: [GENERAL] plpython module import errors

On Thu, Apr 28, 2011 at 09:15:06AM -0400, Martin Gainty wrote: > mv python python5 (for python version 5 binary) > mv python python6 (for python version 6 binary) Do you happen to mean 2.5 and 2.6 ? Given that, say, our Electronic Medical Record solution happily runs on Python 2.5, 2.6, and 2.7

Re: [GENERAL] problem with parent/child table and FKs

On Thu, Apr 21, 2011 at 07:15:38PM +0200, Alban Hertroys wrote: > > What is the suggested approach for this situation ? (there > > will be more tables like "icd10" holding other coding > > systems of fairly diverse nature but all of them sharing > > .code and .term: LOINC, ATC, ICPC-2, ICD-9, ...)

Re: [GENERAL] problem with parent/child table and FKs

On Thu, Apr 21, 2011 at 07:15:38PM +0200, Alban Hertroys wrote: > BTW, do you really need those artificial PK's? If not, you > may well be better off dropping them. That way (code, term) > could be your PK instead. I don't know enough about your > data to make more than a guess though, I just get

Re: [GENERAL] problem with parent/child table and FKs

On Thu, Apr 21, 2011 at 07:53:04AM -0700, Adrian Klaver wrote: > On Thursday, April 21, 2011 4:36:51 am Karsten Hilbert wrote: > > Does anyone have any suggestions regarding the below ? > > The only thing I can come up with is to eliminate the FK : > fk_cod

Re: [GENERAL] problem with parent/child table and FKs

On Thu, Apr 21, 2011 at 01:36:51PM +0200, Karsten Hilbert wrote: > Does anyone have any suggestions regarding the below ? If you guys happen to think this could be a "please-do-my-class-assignment-for-me" question -- I'd be glad to read up on things if someone clues me

Re: [GENERAL] problem with parent/child table and FKs

Does anyone have any suggestions regarding the below ? Thanks, Karsten On Mon, Apr 18, 2011 at 03:53:16PM +0200, Karsten Hilbert wrote: > Hello all, > > since (according to the docs) PostgreSQL does not propagate > INSERTs from child tables unto parent tables the below doe

[GENERAL] problem with parent/child table and FKs

Hello all, since (according to the docs) PostgreSQL does not propagate INSERTs from child tables unto parent tables the below does not work, unfortunately. What is the suggested approach for this situation ? (there will be more tables like "icd10" holding other coding systems of fairly diverse na

Re: [GENERAL] adding years to a date field

On Wed, Apr 06, 2011 at 09:18:15AM -0700, Christine Penner wrote: > This still gave me a sytax error. The other suggestion to multiply > the interval field by 1 year also gave me a syntax error. ... > Any other suggestions? ... > Christine Penner > Ingenious Software Live up to to it ? Karst

Re: [GENERAL] Auto Adjust Age

On Wed, Apr 06, 2011 at 11:15:31AM -0400, Jerry Sievers wrote: > I'd suggest not storing age but instead wrapping with a view that calls > date_trunc('year', now() - dob). Or put that in the query: SELECT ..., date_trunc('year', now() - dob) as age FROM users ; Karsten -

Re: [GENERAL] Database recovery.

On Thu, Mar 24, 2011 at 12:37:25PM +0100, Karsten Hilbert wrote: > > More help is available by typing NET HELPMSG 3534. ... > > any clue? And the second Google result has this: http://archives.postgresql.org/pgsql-bugs/2009-05/msg5.php Karsten -- GPG key ID E4

Re: [GENERAL] Database recovery.

On Thu, Mar 24, 2011 at 04:31:19PM +0500, Waqar Azeem wrote: > The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. > > The service did not report an error. > > More help is available by typing NET HELPMSG 3534. > > The start command returned an error (2) > > Press to cont

Re: [GENERAL] problematic view definition

On Sun, Feb 20, 2011 at 02:31:46PM -0500, Tom Lane wrote: > Karsten Hilbert writes: > > On Wed, Feb 09, 2011 at 11:12:01PM +0100, Karsten Hilbert wrote: > >> Unfortunately I do not understand why PostgreSQL says > >> > >> psql:xx.sql:14: ERROR: could not i

Re: [GENERAL] problematic view definition

For the record: On Wed, Feb 09, 2011 at 11:12:01PM +0100, Karsten Hilbert wrote: > Attached find some table and view definitions from the > GNUmed (www.gnumed.de) database. > > Unfortunately I do not understand why PostgreSQL says > > psql:xx.sql:14: ERROR: could n

Re: [GENERAL] problematic view definition

I should have mentioned this is on PostgreSQL 8.4.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-4) 4.4.5, 32-bit Karsten On Wed, Feb 09, 2011 at 11:12:01PM +0100, Karsten Hilbert wrote: > Date: Wed, 9 Feb 2011 23:12:01 +0100 > From: Karsten Hilbert >

[GENERAL] problematic view definition

Hi all ! Attached find some table and view definitions from the GNUmed (www.gnumed.de) database. Unfortunately I do not understand why PostgreSQL says psql:xx.sql:14: ERROR: could not implement UNION DETAIL: Some of the datatypes only support hashing, while others only support

Re: [GENERAL] Displaying text appears as hex data

On Tue, Feb 08, 2011 at 02:19:49PM +0100, Michael wrote: > >> The arrow in the last line indicates that 'convert_from' is not > >> correctly parsed. > >> > >My understanding was that your msg column was of type bytea. Is this > >not the case? Or is it a different column which needs converting? >

Re: [GENERAL] Displaying text appears as hex data

On Tue, Feb 08, 2011 at 11:39:04AM +0100, Michael wrote: > I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) > as BLOB I take it you mean BYTEA. > and PostgreSQL is displaying it in hex format like so: > > $ TERM=vt100 /pfx/bin/psql opensips opensips > psql (9.0.2) > Type "help

Re: [GENERAL] Are there any projects interested in object functionality? (+ rule bases)

> 2. Inheritance > > This feature is now used almost exclusively for physical partitioning > rather than logical design. GNUmed uses it for logical design (albeit not OO) a lot. Karsten -- NEU: FreePhone - kostenlos mobil telefonieren und surfen! Jetzt informieren: http

Re: [GENERAL] Case Sensitivity

> you should to use a citext datatype > > http://www.postgresql.org/docs/9.0/interactive/citext.html Or: - use a functional index with lower() to ensure uniqueness - use a BEFORE trigger to lower() the inserted data Karsten -- GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit gratis Handy-Fl

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

On Thu, Nov 18, 2010 at 10:30:46AM +0100, Karsten Hilbert wrote: > That is why I argued for options: > > - alter database dump_unlogged_tables to on/off > > default on: better safe than sorry, point the gun but don't pull the > trigger (I agree, however, that the

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

On Thu, Nov 18, 2010 at 08:49:12AM +0100, Alban Hertroys wrote: > From the discussion so far it appears to me that > "unlogged" should probably be split into various gradations > of unlogged. There appear to be a number of popular > use-cases for such tables, with different requirements, That's p

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

On Tue, Nov 16, 2010 at 10:25:13PM -0500, Tom Lane wrote: > 4. The last bit of discussion on -hackers concerned what to do in > the case where the server got shut down cleanly. If it was shut > down cleanly, then any data for unlogged tables would have been > written out from shared buffers ... b

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

> PostgreSQL 9.1 is likely to have, as a feature, the ability to create > tables which are "unlogged", meaning that they are not added to the > transaction log, and will be truncated (emptied) on database restart. > Such tables are intended for highly volatile, but not very valuable, > data, such a

Re: [GENERAL] Linux

On Thu, Nov 04, 2010 at 11:10:24AM -0400, Bill Moran wrote: > Beyond that, I think that any Linux distro that caters to a server > environment will work well for you. > > The thing (in my experience) that's going to make you happy or angry > is how well the packaging system works. Find a distro

Re: [GENERAL] Internationalisation of database content (text columns)

On Fri, Oct 08, 2010 at 12:12:54PM +0200, Wolfgang Keller wrote: > I'm working on a database schema which contains lots of > "type code lookup" tables. The entries of these tables are > also hierarchically related among themselves > (subtype/supertype), to store rather large and quite complex > ta

Re: [GENERAL] Preserving order through an inner join

On Sun, Sep 26, 2010 at 07:32:01PM +, Kevin Jardine wrote: > Hi Pavel, > > I'm not really interested in a "my database is better than your database" > discussion. Pavel did not say that his database is better than yours. What he said was that your query is wrong (with respect to what you s

Re: [GENERAL] where does postgres keep the query result until it is returned?

On Mon, Sep 20, 2010 at 03:37:58PM +0200, Willy-Bas Loos wrote: > > Rows are sent back in the entireity, so the PG instance would need > > enough memory to work with that row.  When you're running a 32bit > > version of PG, values whose size is beyond ~100MB are a bit touch and go > > whether it w

Re: [GENERAL] Getting FATAL: terminating connection due to administrator command

On Wed, Sep 15, 2010 at 02:55:39PM +0200, Peter Hopfgartner wrote: > Where could I start to troubleshoot this problem. First with staff, then with unauthorized access, then with failover software. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 134

Re: [GENERAL] Inheritance and trigger/FK propagation

On Thu, Jul 29, 2010 at 10:50:02AM +0200, Davor J. wrote: > For completeness, I think this link > (http://projects.nocternity.net/index.py/en/psql-inheritance) provides some > scripts you mention. Very interesting. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 453

Re: [GENERAL] Inheritance and trigger/FK propagation

On Tue, Jul 27, 2010 at 10:33:19AM +0200, Davor J. wrote: > Well... I found it out the hard way :). There are some extra caveats I have > come along. There is the very clumsy ALTER TABLE table_name > INHERIT(parent_table) which simply presupposes the parent's columns, but > doesn't enforce it t

Re: [GENERAL] Inheritance and trigger/FK propagation

On Tue, Jul 27, 2010 at 10:36:16AM +0200, Davor J. wrote: > For me Vick's question just proves that inheritance in relational databases > is a complex issue. It shows that trigger propagation is not always desired, Now that's for sure :-) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167

Re: [GENERAL] Inheritance and trigger/FK propagation

> On Thu, Jul 15, 2010 at 4:05 AM, Davor J. wrote: > > It seems no secret that a child table will not fire a trigger defined on > > it's parent table. Various posts comment on this. But nowhere could I > find a > > reason for this. > > Do you want your trigger that redirects insert on parent tabl

Re: [GENERAL] copy/duplicate database schemas

> > This adds significant complexity to your code, especially since (AFAIK) > > there aren't really any good i18n tools for Pg's SQL, PL/PgSQL, etc. > > But there is - whether good or not: Go to > http://gitorious.org/gnumed and browse the tree under > gnumed/server/SQL/. Look at the i18n schema

Re: [GENERAL] copy/duplicate database schemas

On Thu, Jun 24, 2010 at 11:29:48AM +0800, Craig Ringer wrote: > You might want to investigate internationalization options instead, > where you can process your "master" sources to produce a list of > strings, and have translators translate those strings. Your code loads > the string lists, and de

Re: [GENERAL] [offtopic] How do you name a table...

On Thu, Apr 08, 2010 at 10:16:57PM +0900, Ian Barwick wrote: > > How do you name a table which sole purpose is to store a list of values? > (...) > > Is this: > > a. Lookup table > > b. Classifier > > c. Cypher(er)? lookup (*_lu, lu_*) or enum or just what it is (gender, document_type, ...) Kars

Re: [GENERAL] PostgreSQL licence

On Tue, Feb 02, 2010 at 02:30:47PM +, Thom Brown wrote: > I guess it's not a major point considering BSD and MIT are so similar, but > people may become confused when Wikipedia says one thing, and the official > site says another. Then it seems prudent to add clarification (as to the ambiguit

Re: [GENERAL] Research and EAV models

On Fri, Oct 23, 2009 at 11:53:26PM +0200, Leif B. Kristensen wrote: > I'm a researcher type, and I've made an EAV model that suits me well in > my genealogy research. How can you associate an essentially unknown > number of sundry "events" to a "person" without an EAV model? create table person

Re: [GENERAL] Re: Unit conversion database (was: multiple paramters in aggregate function)

On Thu, Aug 20, 2009 at 12:06:19PM +, Jasen Betts wrote: > what is "vv" Vice versa, I'd assume. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to you

Re: [GENERAL] multiple paramters in aggregate function

On Thu, Aug 20, 2009 at 03:30:00PM +0300, Sim Zacks wrote: > In other words, I have a conversion table of all different units. If > there is no conversion between 2 units (such as volume and area) then > the sum returns null. > > > Shouldn't that return NULL IOW unkno

Re: [GENERAL] Unit conversion database (was: multiple paramters in aggregate function)

On Wed, Aug 19, 2009 at 08:31:17PM +0200, Alban Hertroys wrote: > He he, all right then! There certainly are some things left to > improve. One thing I noticed from the links you sent is that I > ignored a few units used in medicine assuming they were deprecated > ages ago - apparently not... Ah,

Re: [GENERAL] multiple paramters in aggregate function

On Thu, Aug 20, 2009 at 10:36:37AM +0300, Sim Zacks wrote: > In other words, I have a conversion table of all different units. If > there is no conversion between 2 units (such as volume and area) then > the sum returns null. Shouldn't that return NULL IOW unknown ? Karsten -- GPG key ID E40713

Re: [GENERAL] Unit conversion database (was: multiple paramters in aggregate function)

Alban, I think having an installable schema for units of measure with definitions and functions would be a great addition to PostgreSQL. I for one know we would use it in GNUmed (wiki.gnumed.de). A few points: Would these guys be of use as a source for reference data ? http://unitsofmeasur

<    1   2   3   4   5   6   7   >