[GENERAL] Odd behaviour of timestamptz

Hi, I've been recently pointed out an issue with timestamptz on a fedora box and no one was able to replicate it on other machines. After a quick chat on the IRC at least another two people could replicate the issue and all of them were using an RPM package. PostgreSQL 8.2.3 on i686-redhat-linux

Re: [GENERAL] Odd behaviour of timestamptz

On Feb 21, 2007, at 17:15 , Matteo Beccati wrote: PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3) test=# create TABLE test (data timestamp with time zone); CREATE TABLE test=# INSERT into test values ('1910-01-10'); INSERT 0 1 test=# INSE

Re: [GENERAL] Odd behaviour of timestamptz

Matteo Beccati ha scritto: > Hi, > > I've been recently pointed out an issue with timestamptz on a fedora box > and no one was able to replicate it on other machines. After a quick > chat on the IRC at least another two people could replicate the issue > and all of them were using an RPM package.

Re: [GENERAL] Odd behaviour of timestamptz

Hi, Michael Glaesemann wrote: >> test=# SELECT * from test; >>data >> >> 1910-01-10 00:00:00+00:19:32 >> 1990-01-10 00:00:00+01 >> (2 rows) > > I suspect your RPMs build PostgreSQL without --enable-integer-datetimes. > Without this configure flag, timestam

Re: [GENERAL] Error upgrading on W2K

On Wed, Feb 21, 2007 at 12:11:07PM +0900, Paul Lambert wrote: > I have postgres running on W2K, version 8.2.1 which I am upgrading to > 8.2.3 but when I run the upgrade I get an error as follows: > > "The installer has encountered an unexpected error > installing this package. This may indicate a

Re: [GENERAL] Installing support for python on windows

On Tue, Feb 20, 2007 at 05:15:38PM -0500, Rhys Stewart wrote: > > > Ok so i am having trouble installing plpython, and found this thread. > Howevre, after adding postgresql/bin to the path and the python lib > directory to the path i still get: > createlang: language installation failed: ERROR:

Re: [GENERAL] REVOKE ALL

David Legault wrote: Hello, Is there a way to revoke all privileges of a role without actually specifying the whole list of items. Like if a role has privileges on FUNCTIONs, is there a REVOKE all FUNCTIONS. There's no GRANT/REVOKE ON public.* command format, but there are plenty of plpgsq

Re: [GENERAL] number of tables limited over time (not simultaneous)?

dave crane wrote: We've settled upon a method for gathering raw statistics from widely scattered data centers of creating one sequence per-event, per minute. Aside from security concerns, did we miss something? Should I be worried we're going through ~60,000 sequences per day? Well, you'll

Re: [GENERAL] Odd behaviour of timestamptz

On 2/21/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: I suspect your RPMs build PostgreSQL without --enable-integer- datetimes. Without this configure flag, timestamps are represented as floats, with all of the imprecision that implies. See the second note below the Date/Time Types table:

[GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

Hello list, I've been testing tsearch2 for a while and I recently noticed some really slow queries. This is a quite big document so the times are maybe accurate. The document has about 194 000 words. I put all the data in tbl_fulltext.fulltext_text and then did a update tbl_fulltext set ve

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

Henrik Zagerholm wrote: The document has about 194 000 words. update tbl_fulltext set vectors = to_tsvector(fulltext_text); Which takes about 80 seconds to complete. The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory. Is this normal? What can I tweak in postgresql.conf to speed up big

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

Hi, Henrik Zagerholm wrote: Which takes about 80 seconds to complete. The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory. Is this normal? What can I tweak in postgresql.conf to speed up big to_tsvector()? Hm.. seems not too unreasonable to me. Take a look at the stemmers or dictionar

Re: [GENERAL] postgresql vs mysql

On 2/20/07, gustavo halperin <[EMAIL PROTECTED]> wrote: I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to much technical reasons. Can you give help me please ? How abo

Re: [GENERAL] REVOKE ALL

In which table pg_* are stored the GRANT options? As I can do a cross-check with a SELECT to see if the user has any grants on functions using the pg_proc table. At the same time, I need to know exactly the names of the functions to be able to REVOKE them which in my opinion, there should be a wil

Re: [GENERAL] pg_dump: [tar archiver] write error appending to tar archive

Where do I find the man page. Regards Michaela - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "MG" <[EMAIL PROTECTED]> Sent: Tuesday, February 13, 2007 3:46 PM Subject: Re: [GENERAL] pg_dump: [tar archiver] write error appending to tar archive "MG" <[EMAIL PROTECTED

Re: [GENERAL] postgresql vs mysql

This can (I discovered yesterday) be fixed by adding ONLY_FULL_GROUP_BY to the sql_mode setting. As Ron mentioned though that can be happily overridden on a per-session basis so it's not as 'strict' as it makes out... Chad Wagner wrote: On 2/20/07, *gustavo halperin* <[EMAIL PROTECTED]

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

On Wed, 21 Feb 2007, Henrik Zagerholm wrote: Hello list, I've been testing tsearch2 for a while and I recently noticed some really slow queries. This is a quite big document so the times are maybe accurate. The document has about 194 000 words. I put all the data in tbl_fulltext.fulltext_t

Re: [GENERAL] REVOKE ALL

David Legault wrote: In which table pg_* are stored the GRANT options? As I can do a cross-check with a SELECT to see if the user has any grants on functions using the pg_proc table. At the same time, I need to know exactly the names of the functions to be able to REVOKE them which in my opinion,

Re: [GENERAL] WARNING: some databases have not been vacuumed in 1953945422 transactions

Yes there are changes. db1, db2 and template1 has been vacuumed over night, not template0. 20.02.07 datname - (12) age - (4) 1 db1 1.075.878.187 2 db2 1.075.847.556 3 template1 1.976.569.889 4 template0 1.976.569.889 21.02.07 datname - (12) age - (4) 1 db1 1.074.7

Re: [GENERAL] WARNING: some databases have not been vacuumed in 1953945422 transactions

MG wrote: Yes there are changes. db1, db2 and template1 has been vacuumed over night, not template0. 4 template0 1.976.569.889 4 template0 1.978.965.587 Not necessarily - the age is the count of how many transactions have occurred ACROSS ALL DATABASES since the "datfrozenxid" i

Re: [GENERAL] REVOKE ALL

Concerning the pg_hba.conf file, I don't want to prevent external connections to the DB as I need all my web apps to connect to them. I was referring the fact that ROLE A "belongs" to DB G so that I don't want him to access anything in DB H for example. I'd like to be able to create roles that ca

Re: [GENERAL] REVOKE ALL

David Legault wrote: Concerning the pg_hba.conf file, I don't want to prevent external connections to the DB as I need all my web apps to connect to them. I was referring the fact that ROLE A "belongs" to DB G so that I don't want him to access anything in DB H for example. I'd like to be able

Re: [GENERAL] Odd behaviour of timestamptz

On Wed, Feb 21, 2007 at 09:40:05AM +0100, Matteo Beccati wrote: > > test=# SELECT * from test; > >data > > --- > > 1910-01-10 00:00:00+01:24 > > 1990-01-10 00:00:00+01 > > (2 rows) > > Actually this one was built from source (CVS HEAD from yesterday), so > it'

Re: [GENERAL] REVOKE ALL

From the docs db_user_namespace (boolean) This parameter enables per-database user names. It is off by default. This parameter can only be set in the postgresql.conf file or on the server command line. If this is on, you should create users as [EMAIL PROTECTED] When username is passed by a con

Re: [GENERAL] REVOKE ALL

David Legault wrote: From the docs db_user_namespace (boolean) It doesn't talk about this in the CREATE ROLE docs though so it's a bit ambiguous and the note saying this is a temp measure means they are thinking of something better for the future I assume. Well, it's clearly not ideal, b

Re: [GENERAL] REVOKE ALL

I only have a local root user for DBA pruposes, the rest will be DB specific roles (If I can do it properly). Users have no privileges except CONNECT to the DB. All the privileges are granted to group roles. Users are assigned groups to have access to DB functionality which are all stored in fun

Re: [GENERAL] WARNING: some databases have not been vacuumed in 1953945422 transactions

MG wrote: >> I don't think that there were any changes to the template databases. >> >> You detected a difference in age(datfrozenxid) - try selecting >> datfrozenxid itself and you will probably see that it does not >> change over time. > > Yes there are changes. > db1, db2 and template1 has been

Re: [GENERAL] REVOKE ALL

David Legault wrote: I'll test all that [EMAIL PROTECTED] stuff and see what happens and if it works properly and report back on it. Excellent - never used it myself. Is there a place where I can see what features were suggested and where I could suggest my own? Full list, and what people a

Re: [GENERAL] postgresql vs mysql

On Wednesday 21 February 2007 1:10:41 am Tom Lane wrote: > "Adam Rich" <[EMAIL PROTECTED]> writes: > > I'm not apologizing for their past mistakes.. But the issue > > you cite is no longer true: > > "As of 5.0.2, the server requires that month and day values > > be legal, and not merely in the rang

Re: [GENERAL] Odd behaviour of timestamptz

Matteo Beccati <[EMAIL PROTECTED]> writes: > test=# INSERT into test values ('1910-01-10'); > INSERT 0 1 > test=# INSERT into test values ('1990-01-10'); > INSERT 0 1 > test=# SELECT * from test; >data > > 1910-01-10 00:00:00+00:19:32 > 1990-01-10 00:00:00

Re: [GENERAL] postgresql vs mysql

On Wed, Feb 21, 2007 at 08:54:30AM -0500, Jan de Visser wrote: > It gets better: The problem is not just feb 35, it's also that it doesn't > warn > you that it didn't like the input format: Actually it did, sort of. > mysql> insert into test values ('35-Feb-2007'); > Query OK, 1 row affected, 1

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

Markus Schiltknecht <[EMAIL PROTECTED]> writes: > Henrik Zagerholm wrote: >> Is this normal? What can I tweak in postgresql.conf to speed up big >> to_tsvector()? > Hm.. seems not too unreasonable to me. > Take a look at the stemmers or dictionaries involved. What do you use there? Also, I wonde

Re: [GENERAL] REVOKE ALL

Will report on it, There isn't anything in the FAQ about changes to the ROLES that I've seen though this line may be interesting: %Allow GRANT/REVOKE permissions to be applied to all schema objects with one command The proposed syntax is: GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

21 feb 2007 kl. 15:44 skrev Tom Lane: Markus Schiltknecht <[EMAIL PROTECTED]> writes: Henrik Zagerholm wrote: Is this normal? What can I tweak in postgresql.conf to speed up big to_tsvector()? Hm.. seems not too unreasonable to me. Take a look at the stemmers or dictionaries involved. What

[GENERAL] tsearch2: word position

Hi, I'm fiddling with to_tsvector() and parse() from tsearch2, trying to get the word position from those functions. I'd like to use the tsearch2 parser and stemmer, but I need to know the exact position of the word as well as the original, unstemmed word. What I came up with so far is prett

[GENERAL] How can you tell if a function is immutable from psql?

Am I just missing it or is there no way to tell if a function is noted as immutable from the \df or \df+ output in psql? -- Mike Nolan

Re: [GENERAL] Views: having a rule call a function vs. using a before trigger

"Karl O. Pinc" <[EMAIL PROTECTED]> wrote: > Hi, > > Postgresql 8.1. > > I'm trying to come up with a generic way > of inserting into a view, particularly regards > error testing and the generation of complicated > foreign keys. I don't seem to be having much luck. > > (I also want to update an

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

Le mercredi 21 février 2007 15:44, Tom Lane a écrit : > Markus Schiltknecht <[EMAIL PROTECTED]> writes: > > Henrik Zagerholm wrote: > >> Is this normal? What can I tweak in postgresql.conf to speed up big > >> to_tsvector()? > > > > Hm.. seems not too unreasonable to me. > > Take a look at the stem

Re: [GENERAL] REVOKE ALL

David Legault escribió: > It's marked with an % saying it's easy to implement, but isn't with a "-" so > it won't be in 8.3. Note that having a "-" in front means "somebody already coded it and the patch has been committed". Not having it does not mean it won't be in 8.3; it means nobody has don

Re: [GENERAL] tsearch2: word position

I'm fiddling with to_tsvector() and parse() from tsearch2, trying to get the word position from those functions. I'd like to use the tsearch2 parser and stemmer, but I need to know the exact position of the word as well as the original, unstemmed word. It's not supposed usage... Why do you nee

Re: [GENERAL] tsearch2: word position

Hello Teodor, Teodor Sigaev wrote: It's not supposed usage... Why do you need that? Well, long story... I'm still using my own indexing on top of the tsearch2 parsers and stemming. However, two obvious cases come to mind: - autocompletion, where I want to give the user one of the possible

Re: [GENERAL] postgresql vs mysql

On Tue, 2007-02-20 at 15:25, gustavo halperin wrote: > Hello > > I have a friend that ask me why postgresql is better than mysql. > I personally prefer posgresql, but she need to give in her work 3 or 4 > strong reasons for that. I mean not to much technical reasons. Can you > give help me

Re: [GENERAL] postgresql vs mysql

Scott Marlowe wrote: You can't change a table in any way without rewriting the whole thing, resulting in a very long wait and a complete table lock on any alter table action on big tables. Don't forget that if you've got a really big table, you need that much space free on the drive to alter the

Re: [GENERAL] postgresql vs mysql

On Wed, 2007-02-21 at 10:12, Jack Orenstein wrote: > Scott Marlowe wrote: > > You can't change a table in any way without rewriting the whole thing, > > resulting in a very long wait and a complete table lock on any alter > > table action on big tables. Don't forget that if you've got a really > >

Re: [GENERAL] postgresql vs mysql

At 07:31 PM 2/21/2007, Chad Wagner wrote: On 2/20/07, gustavo halperin <[EMAIL PROTECTED]> wrote: I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not

Re: [GENERAL] postgresql vs mysql

At 12:02 AM 2/22/2007, Scott Marlowe wrote: You can't change a table in any way without rewriting the whole thing, resulting in a very long wait and a complete table lock on any alter table action on big tables. Don't forget that if you've got a really Oh yeah, that reminds me. "rewriting the

Re: [GENERAL] postgresql vs mysql

> It's got a query parser that's dumb as a brick. While we're on this topic... I have a question on these series of queries: -- Query A select count(*) from customers c where not exists ( select 1 from orders o where o.customer_id = c.customer_id ) -- Query B select count(*) from customers c

Re: [GENERAL] pg_dump: [tar archiver] write error appending to tar archive

On 2/21/07, MG <[EMAIL PROTECTED]> wrote: Where do I find the man page. Try 'man pg_dump' or browse www.postgresql.org for Documentation if you can't get to any, Google is your best friend. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "MG" <[EMAIL PROTECTED]> Sent:

Re: [GENERAL] postgresql vs mysql

On Wed, 2007-02-21 at 10:54, Adam Rich wrote: > > It's got a query parser that's dumb as a brick. > > While we're on this topic... I have a question on these series > of queries: > > -- Query A > select count(*) from customers c > where not exists ( select 1 from orders o > where o.customer_id

[GENERAL] Expanding the crosstab function to extra rows

Finally figured out what was wrong with my crosstab that I posted for help yesterday. I was really close, just need to set the right types. I have it working using the crosstab(text sql, int N) function. This displays a crosstab from my view below for units sold by each sales rep under each month..

[GENERAL] Quering 4 or more physicale different PostgreSQL server at once

Hello, My current PostgreSQL Database has arround 560 GByte without indices. If not more then 3-5 user query it it works fast like Google... :-) But I have over 600 Users now which all query it over the Net and hit a heavy performance problem. The biggest problem is the "main" table which is ar

Re: [GENERAL] postgresql vs mysql

Adam Rich wrote: > -- Query A > select count(*) from customers c > where not exists ( select 1 from orders o > where o.customer_id = c.customer_id ) > > -- Query B > select count(*) from customers c > where customer_id not in ( select customer_id from orders) > > -- Query C > select count(*) from c

Re: [GENERAL] Recursive Left Joins Causing Trouble in 8.2.3 RESOLVED (kind of)

OK, here's the deal I had views that used syntax like WHERE datecol < current_date and (otherdatecol is null or otherdatecol current_date) Kind of a hillbilly version of BETWEEN but it assumes null is INFINITY (except I use date, not timestamp) Suddenly, this is ungodly inefficient in 8.2.3.

Re: [GENERAL] Recursive Left Joins Causing Trouble in 8.2.3 RESOLVED (kind of)

OK, here's the deal I had views that used syntax like WHERE datecol < current_date and (otherdatecol is null or otherdatecol current_date) Kind of a hillbilly version of BETWEEN but it assumes null is INFINITY (except I use date, not timestamp) Suddenly, this is ungodly inefficient in 8.2.3.

Re: [GENERAL] postgresql vs mysql

On 2/21/07, Lincoln Yeoh wrote: At 07:31 PM 2/21/2007, Chad Wagner wrote: >On 2/20/07, gustavo halperin ><[EMAIL PROTECTED]> wrote: >I have a friend that ask me why postgresql is better than mysql. >I personally prefer posgresql, but she need to give in her work 3 or 4

Re: [GENERAL] PG Email Client

Hello Sim, Am 2007-01-29 14:29:27, schrieb Sim Zacks: > For example, instead of saving a copy of an email in 1 folder, the same > email could be indexed to multiple folders. Current email clients mimic > file cabinets too much in that the system considers an email to be a > physical entity that

Re: [GENERAL] PG Email Client

Am 2007-01-30 07:56:32, schrieb Sim Zacks: > In the traditional imap server the mail is stored in folders on the server > that are accessed by the client. Therefore you are limited to one indexed > location per email. Lets say you want to look at your email by customer. > You would want to have

Re: [GENERAL] Any Plans for cross database queries on the same server?

Am 2007-01-30 15:40:23, schrieb Mark Walker: > Maybe that's just my experience with my customers. I have seen signs of > dysfunctional computer systems lately. I was in a fast food restaurant > in San Francisco a few months back and they were manually taking > orders. I think the only reason

[GENERAL] Sockets in perl (Db transaction ends abnormally at client when server closes client socket)

Hi, I have a table on which there is a trigger fired after insert. This trigger opens a socket with another server. Server, after executing what it has to, closes the client socket and at the client end (DB side) the whole transaction rolls back saying that server closed the connection abnormally

Re: [GENERAL] Odd behaviour of timestamptz

On 2/21/07, Tom Lane <[EMAIL PROTECTED]> wrote: I'll bet you are running in Europe/Amsterdam time zone? The above is what about me? i'm in poland, and runing in europe/warsaw time zone. i assume we also had some issues lie this - where can i read about it? best regards, depesz -- http://w

[GENERAL] change data type int4 to serial

Hi, I am experimenting a migration of an M$ Access database to postgresql in a Debian GNU/Linux system. Based on some reading, I've approached it as follows: 1. Using mdbtools, export the tables from Access to *.csv files, sanitizing file names and column names. 2. Using unixodbc and GNU R's

Re: [GENERAL] Odd behaviour of timestamptz

On Wed, 2007-02-21 at 13:21, hubert depesz lubaczewski wrote: > On 2/21/07, Tom Lane <[EMAIL PROTECTED]> wrote: > I'll bet you are running in Europe/Amsterdam time zone? The > above is > > what about me? i'm in poland, and runing in europe/warsaw time zone. i > assume we also had

Re: [GENERAL] massive memory allocation until machine crashes

Hello. Richard Huxton schrieb: Alexander Elgert wrote: Hello, given is a postgres database in version PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 Upgrade to 7.4.16 as soon as is convenient - y

Re: [GENERAL] postgresql vs mysql

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/21/07 08:42, Michael Fuhr wrote: > On Wed, Feb 21, 2007 at 08:54:30AM -0500, Jan de Visser wrote: >> It gets better: The problem is not just feb 35, it's also that it doesn't >> warn >> you that it didn't like the input format: > > Actually it

Re: [GENERAL] massive memory allocation until machine crashes

On Wed, Feb 21, 2007 at 08:35:40PM +0100, Alexander Elgert wrote: > Yes, there are five FOREIGN keys in this table: There's your problem. You've got a trigger set to run after every delete, and you've got them set to wait until the end of the transaction. So postgres has to delete all the tuples

Re: [GENERAL] postgresql vs mysql

IMX, the only things going for MySQL are: 1. It's fast. 2. It's easy to install and administer. 3. It's cheap and cross-platform. 4. It's popular. The problem is: 1. It's fast because fsync is off by default, and MyISAM is not transactional and doesn't support basic features like foreign keys. Tha

Re: [GENERAL] change data type int4 to serial

On Wed, Feb 21, 2007 at 11:20:38 -0600, Seb <[EMAIL PROTECTED]> wrote: > > Checking the results in pgadmin, this proceeded fine, but now that I want > to specify the primary and foreign keys in the tables, I see that the > columns needed for this were imported as int4 data type. I would like >

Re: [GENERAL] Quering 4 or more physicale different PostgreSQL server at once

You could look at contrib/dblink but if you plan to join between your four servers a lot it seems like you might want cleaner solution, like, say, slony-I or pgpool, and maybe some data partitioning on that main table as well. On Wed, 21 Feb 2007, Michelle Konzack wrote: Hello, My current P

[GENERAL] (no subject)

---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

[GENERAL] Row-Level Access Control via FK to pg_catalog.pg_authid

Folks, I'm working on a way to do row-level access via VIEWs and ROLEs. The idea: Given a table foo with pk foo_id, which is to be the subject of these row-level permissions, I'd make another table, say can_read_foo, which looks like: CREATE TABLE can_read_foo ( foo_id INTEGER NOT NULL REFE

Re: [GENERAL] postgresql vs mysql

How would you like to use a database that has nuances like these -- http://forums.mysql.com/read.php?20,141120,141120#msg-141120 ep ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] postgresql vs mysql

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/21/07 18:09, Erick Papadakis wrote: > How would you like to use a database that has nuances like these -- > http://forums.mysql.com/read.php?20,141120,141120#msg-141120 Huh? A blank string (does that mean '' or ' '?) is not NULL, so of *course*

Re: [GENERAL] postgresql vs mysql

Chad Wagner wrote: On 2/20/07, *gustavo halperin* <[EMAIL PROTECTED] > wrote: I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to muc

Re: [GENERAL] postgresql vs mysql

Adam Rich wrote: It's got a query parser that's dumb as a brick. While we're on this topic... I have a question on these series of queries: -- Query A select count(*) from customers c where not exists ( select 1 from orders o where o.customer_id = c.customer_id ) -- Query B select count(*) f

Re: [GENERAL] postgresql vs mysql

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/21/07 19:01, Brusser, Michael wrote: > >>> How would you like to use a database that has nuances like these -- >>> http://forums.mysql.com/read.php?20,141120,141120#msg-141120 > > --- > >> Huh? >> A blank string (does that mean '' or ' '?) i

Re: [GENERAL] postgresql vs mysql

>> How would you like to use a database that has nuances like these -- >> http://forums.mysql.com/read.php?20,141120,141120#msg-141120 --- > Huh? > A blank string (does that mean '' or ' '?) is not NULL, so of > *course* it should pass the NOT NULL constraint. > Or am I missing something? ---

Re: [GENERAL] postgresql vs mysql

Erick Papadakis wrote: How would you like to use a database that has nuances like these -- http://forums.mysql.com/read.php?20,141120,141120#msg-141120 Err - an empty string is not the same as null, so that is perfectly valid. Null means unknown, an empty string is not unknown - it's a known v

Re: [GENERAL] postgresql vs mysql

On Wed, Feb 21, 2007 at 01:45:08PM -0600, Ron Johnson wrote: > On 02/21/07 08:42, Michael Fuhr wrote: > > Not as good as "ERROR: hey bonehead, there ain't no such date" but > > But it *inserts the "data"*! I didn't say otherwise and I'm not defending MySQL's behavior. I was simply refuting t

Re: [GENERAL] postgresql vs mysql

So how should I make a database rule in MySQL to not allow blank strings. Basically to REQUIRE a value for that column, whether it is NULL or NADA or VOID or whatever you wish to call it. I just want to make sure that something, some value, is entered for a column. Would appreciate any thoughts or

Re: [GENERAL] postgresql vs mysql

Erick Papadakis wrote: So how should I make a database rule in MySQL to not allow blank strings. Basically to REQUIRE a value for that column, whether it is NULL or NADA or VOID or whatever you wish to call it. I just want to make sure that something, some value, is entered for a column. Would ap

Re: [GENERAL] postgresql vs mysql

On Thu, 22 Feb 2007, Erick Papadakis wrote: Why should a NULL be different from an empty string, what's the big mysterious difference? Long ago and far away, when I was in the Army, we had quite a few soldiers whose name took the form 'John NMI Doe.' That's because the Army -- even before co

Re: [GENERAL] postgresql vs mysql

Erick Papadakis wrote: So how should I make a database rule in MySQL to not allow blank strings. Basically to REQUIRE a value for that column, whether it is NULL or NADA or VOID or whatever you wish to call it. I just want to make sure that something, some value, is entered for a column. Would ap

Re: [GENERAL] How can you tell if a function is immutable from psql?

On Wed, Feb 21, 2007 at 09:20:19AM -0600, Michael Nolan wrote: > Am I just missing it or is there no way to tell if a function is noted as > immutable from the \df or \df+ output in psql? Apparently not. You could examine pg_proc.provolatile. http://www.postgresql.org/docs/8.2/interactive/catalo

Re: [GENERAL] postgresql vs mysql

> Does PostgreSQL suffer from this oddity as well? This distinction > between an empty string and a NULL? Could you also please give me an > example of where this would be useful from a business logic > standpoint? Why should a NULL be different from an empty string, > what's the big mysterious dif

Re: [GENERAL] Row-Level Access Control via FK to pg_catalog.pg_authid

David Fetter wrote: > Folks, > > I'm working on a way to do row-level access via VIEWs and ROLEs. The > idea: > > Given a table foo with pk foo_id, which is to be the subject of these > row-level permissions, I'd make another table, say can_read_foo, which > looks like: > > CREATE TABLE can_rea

Re: [GENERAL] postgresql vs mysql

CaT wrote: On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: In postgres, to stop an empty blank string: create table a(a text not null check (char_length(a) > 0)); What's wrrong with using a <> '' sd the check? Or is this just a flavour thing? Nothing, I just thought of the other wa

Re: [GENERAL] postgresql vs mysql

On 2/21/07, Lincoln Yeoh wrote: MySQL: the PHP of databases. 'd appreciate if you stick to the subject. jzs ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PR

Re: [GENERAL] postgresql vs mysql

Chris wrote: It's not an oddity. An empty string is a KNOWN value. You know exactly what that value is - it's an empty string. A NULL is UNKNOWN - it doesn't have a value at all. Just to expand on that (and to drag this thread out a little longer), i find that a pretty good way to get ac

[GENERAL] what compression is used in on disk bitmap index implementation

i want to know 1.what compression technique used in on disk implementation of bitmap index. 2.if we want add a new thing to the development how we can test that with the database

Re: [GENERAL] Recursive Left Joins Causing Trouble in 8.2.3 RESOLVED (kind of)

"Ian Harding" <[EMAIL PROTECTED]> writes: > I had views that used syntax like > WHERE datecol < current_date and (otherdatecol is null or otherdatecol > > current_date) > Suddenly, this is ungodly inefficient in 8.2.3. It worked just fine in 8.1.3. This complaint is pretty much content-free (espe

Re: [GENERAL] postgresql vs mysql

John Smith wrote: > On 2/21/07, Lincoln Yeoh wrote: >> MySQL: the PHP of databases. > > 'd appreciate if you stick to the subject. Oops he probably should not have used MySQL because it is trademarked... mysql: The PHP of databases ;) Sincerely, Joshua D. Drake > jzs > > -

Re: [GENERAL] Odd behaviour of timestamptz

Scott Marlowe <[EMAIL PROTECTED]> writes: > On Wed, 2007-02-21 at 13:21, hubert depesz lubaczewski wrote: >> On 2/21/07, Tom Lane <[EMAIL PROTECTED]> wrote: >>> I'll bet you are running in Europe/Amsterdam time zone? The >>> above is >> >> what about me? i'm in poland, and runing in europe/warsaw

Re: [GENERAL] Row-Level Access Control via FK to pg_catalog.pg_authid

David Fetter <[EMAIL PROTECTED]> writes: > I'm working on a way to do row-level access via VIEWs and ROLEs. You sure you're not re-inventing the wheel? http://pgfoundry.org/projects/veil/ regards, tom lane ---(end of broadcast)-