Re: [GENERAL] Table with differerent Data Types
hi, i haven´t found the explication about my problem... On 24 abr, 03:04, [EMAIL PROTECTED] (Klint Gore) wrote: > xaviergxf wrote: > > Hi, > > > I´m trying to do the follow: create a table like: > > create table t( > > cod serial, > > data_type char(10), > > value ??? > > ); > > > I would like to do the follow the table would tell me what data type > > its the value. For instance: > > > insert into t values(1, 'Integer', 12); > > insert into t values(2, 'String', 'test'); > > insert into t values(3, 'Float', 1.23); > > > How can i do that? Its that possible? How Can i solve this problem? > > seehttp://www.varlena.com/varlena/GeneralBits/110.php > > klint. > > -- > Klint Gore > Database Manager > Sheep CRC > A.G.B.U. > University of New England > Armidale NSW 2350 > > Ph: 02 6773 3789 > Fax: 02 6773 3266 > EMail: [EMAIL PROTECTED] > > -- > Sent via pgsql-general mailing list ([EMAIL PROTECTED]) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general- Ocultar texto > entre aspas - > > - Mostrar texto entre aspas - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql installation - cannot initdb 1
Hi, I had the postgresql version 8.2, but i´ve updated it to the 8.3. During the installation, postresql show this message "Failed to run initdb: 1". I already tried to remove and reinstall, but it does not works. Did anyone know how can i solve this problem? Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] timestamp with time zone output incorrect
Hi, We are having trouble with the output of timestamp with time zone with versions 8.1.10 and 8.3.1. It seems reversed, and change over times are incorrect. timezone for both is: => show timezone ; TimeZone - NZST-12NZDT (1 row) Note, change over times for this year is: Sun Apr 06 02:59:59 NZDT 2008 --> Sun Apr 06 02:00:00 NZST 2008 Sun Sep 28 01:59:59 NZST 2008 --> Sun Sep 28 03:00:00 NZDT 2008 On both versions: => select timestamp with time zone '2008-01-01 00:00:00'; timestamptz 2008-01-01 00:00:00+12 => select timestamp with time zone '2008-05-01 00:00:00'; timestamptz 2008-05-01 00:00:00+13 (1 row) It seems that the time zone off set is reversed. Also it seems to be using the old change over times. => select timestamp with time zone '2008-03-09 01:00:00'; timestamptz 2008-03-09 01:00:00+12 (1 row) => select timestamp with time zone '2008-03-09 03:00:00'; timestamptz 2008-03-09 03:00:00+13 (1 row) Checked "postgresql-8.3.1/src/timezone/data/australasia" and the information here seems correct. The date on the system (HPUX 11.23) is correct, e.g. % date Thu Apr 24 18:22:42 NZST 2008 % echo $TZ NZST-12NZDT The database seems to know we are using the New Zealand time zone. It seems to think summer is coming it is winter. Any ideas anyone? Thanks Steve Martin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] status on pgiomonitor
I'm re-writing documentation I have used in the past to give PG courses, and at this moment I upgrading the information in my docs to fit PG 8.3. What I'm stuck with now is pgiomonitor, which seems to not work due to the changes that happened between 8.1 (last time I used it) and 8.3. Does anybody know if development is still going to be done, at least to make it fit new versions of PostgreSQL? thanks. -- 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] status on pgiomonitor
Martin Marques escribió: I'm re-writing documentation I have used in the past to give PG courses, and at this moment I upgrading the information in my docs to fit PG 8.3. What I'm stuck with now is pgiomonitor, which seems to not work due to the changes that happened between 8.1 (last time I used it) and 8.3. Does anybody know if development is still going to be done, at least to make it fit new versions of PostgreSQL? If anybody is interested (I'm not a Perl expert), I changed the line where is asks if stats_block_level is on, to track_counts. Now it works for PG 8.3. -- 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] timestamp with time zone output incorrect
On Thu, Apr 24, 2008 at 06:30:27PM +1200, Steve Martin wrote: > Hi, > > We are having trouble with the output of timestamp with time zone with > versions 8.1.10 and 8.3.1. > It seems reversed, and change over times are incorrect. > > timezone for both is: > => show timezone ; > TimeZone > - > NZST-12NZDT > (1 row) I have no idea what timezone that it. Presumably it switches between daylight savings and non-daylight savings based on the US rules? I can't find this timezone anywhere on my system. Perhaps you should try the timezone Pacific/Auckland to get closer to what you want. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Postgresql installation - cannot initdb 1
On Thu, Apr 24, 2008 at 05:04:38AM -0700, xaviergxf wrote: > I had the postgresql version 8.2, but i´ve updated it to the 8.3. > During the installation, postresql show this message "Failed to run > initdb: 1". I already tried to remove and reinstall, but it does not > works. > > Did anyone know how can i solve this problem? I'm afraid my mind reading device is offline today, could you provide the following information: - What OS? - How did you try to install the 8.3 version? Packaged or compiled yourself? - What exactly did you type? - What was the exact error message? - How was your 8.2 installation installed? - Which documentation/google searches have you already tried to resolve this? Thanks in advance, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] timestamp with time zone output incorrect
Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > On Thu, Apr 24, 2008 at 06:30:27PM +1200, Steve Martin wrote: >> => show timezone ; >> TimeZone >> - >> NZST-12NZDT >> (1 row) > I have no idea what timezone that it. Presumably it switches between > daylight savings and non-daylight savings based on the US rules? Yeah, that's a POSIX zone spec. See http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#DATATYPE-TIMEZONES As noted there, if the OP really really wants to spell his zone name that way, he could fool with the "posixrules" file in the timezone database. But Pacific/Auckland is probably better. (I don't remember whether 8.1 would honor changes in "posixrules".) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Regular expression
Hello, Case insensitive pattern matching gives strange results for non-ascii character (such as UTF-8 encoded cyrillic letters): test=# select 'б' ~* 'Б' ; ?column? -- f (1 row) ( 'б' and 'Б' are lower and upper case variants of cyrillic 'B') at the same time: test=# select 'б' ilike 'Б' ; ?column? -- t (1 row) (PG 8.3 on Linux, UTF-8 locale) Also, what could be the reason for that cyrillic letters are not treated by regexp engine as the part of [:alpha:], [:alnum:], \w etc. classes? Or they never meant to be?
Re: [GENERAL] Regular expression
"Vyacheslav Kalinin" <[EMAIL PROTECTED]> writes: > Case insensitive pattern matching gives strange results for non-ascii > character (such as UTF-8 encoded cyrillic letters): Yeah, the regex locale support doesn't work well in multibyte character sets --- it basically will not recognize that non-ASCII characters have any case variants. Fixing this has been on the TODO list for awhile ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] taking actions on rollback (PHP)
I've a set of statements...
pg_query('begin;');
pg_query('do stuff');
pg_query('do other stuff');
if(!pg_query('commit;')) {
pg_query('rollback;');
// DO SOME CLEANUP HERE
}
if something goes wrong the statement actually get rolled back... but
the pg_query('commit;') statement always succede so the cleanup never
get executed.
How am I going to see if the transaction succeeded without checking
what happens for each statement and getting the cleanup code execute?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
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] taking actions on rollback (PHP)
On Sat, Apr 26, 2008 at 10:52:12PM +0200, Ivan Sergio Borgonovo wrote:
> How am I going to see if the transaction succeeded without checking
> what happens for each statement and getting the cleanup code execute?
You basically actually check for the errors in the earlier pg_query()
calls, since they will tell you. If you can't be bothered, you could
just do at the end of the transaction:
if( !pg_query("select 1") )
pg_query("rollback');
// DO CLEANUP HERE
}
else
{ pg_query("commit"); }
Have a nice day,
--
Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
signature.asc
Description: Digital signature
Re: [GENERAL] taking actions on rollback (PHP)
On Sat, 26 Apr 2008 23:14:07 +0200
Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> On Sat, Apr 26, 2008 at 10:52:12PM +0200, Ivan Sergio Borgonovo
> wrote:
> > How am I going to see if the transaction succeeded without
> > checking what happens for each statement and getting the cleanup
> > code execute?
>
> You basically actually check for the errors in the earlier
> pg_query() calls, since they will tell you. If you can't be
> bothered, you could just do at the end of the transaction:
> if( !pg_query("select 1") )
> pg_query("rollback');
> // DO CLEANUP HERE
> }
> else
> { pg_query("commit"); }
I can guess the logic... but I'd like to be sure I got it.
Why does
if( !pg_query("commit;") )
doesn't report any error back?
So an error stays around and after a failure of any statement after
the begin all the following statement (on the same connection???) will
fail. Is it?
BTW your code had to be changed to
if( [EMAIL PROTECTED]("select 1") )
pg_query("rollback');
// DO CLEANUP HERE
}
else
{ pg_query("commit"); }
Otherwise php get nervous, fail and the cleanup code doesn't get
executed and furthermore it seems that with a pending failed
transaction other statement fail too.
That was curious since I opened 2 pages from the same browser just to
check if the cleanup code was going to be executed and keep the form
I was submitting easy to be refilled with a back.
Form -> several errors +
ERROR: current transaction is aborted, commands ignored until end of
transaction block
Testing page that give me a view on the tables that should be
cleaned up -> refresh ->
ERROR: current transaction is aborted, commands ignored until end of
transaction block
With the added @ everything seemed to be OK.
I had to refresh a second time to get rid of the error.
I'd like to be sure I've understood how it works since I wouldn't
like the error went away just by chance and under heavy load I may
have troubles.
I'm using pg_connect not pg_pconnect.
Why did I get the
ERROR: current transaction is aborted, commands ignored until end of
transaction block
from 2 different pages?
> Have a nice day,
thanks, you've been very helpful.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
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 modify ENUM datatypes?
On Friday 25 April 2008 14:56, Merlin Moncure wrote:
> On Thu, Apr 24, 2008 at 3:01 PM, Tino Wildenhain <[EMAIL PROTECTED]> wrote:
> > Merlin Moncure wrote:
> > > I think you're being a little too hard on enums here. I was actually
> > > in the anti-enum camp until it was demonstrated to me (and in my own
> > > testing) that using enum for natural ordering vs. fielding the
> > > ordering of the type out to a join is can be a huge win in such cases
> > > where it is important. Relational theory is all well and good, but in
> > > practical terms things like record size, index size, and query
> > > performance are important.
> >
> > Uhm. Sorry what? Can you demonstrate this particular use?
> > When I first saw discussion about enumns I kinda hoped they
> > will be implemented as kind of macro to really map to a table.
> > But here you go. I'm still looking for a good example to
> > demonstrate the usefullness of enums (same for arrays for that
> > matter)
>
> You must not be aware that enums are naturally ordered to make that
> statement. Suppose your application needs to order a large table by
> a,b,c where b is the an 'enum' type of data. With an enum, the order
> is inlined into the key order, otherwise it's out of line, meaning
> your you key is larger (enum is 4 bytes, varchar is guaranteed to be
> larger), and you need to join out to get the ordering position, use a
> functional index, or cache it in the main table.
>
I think one of the best examples of this is the movie rating system (which I
blogged about at
http://people.planetpostgresql.org/xzilla/index.php?/archives/320-PostgreSQL-8.3-Features-Enum-Datatype.html
)
It's a good example of setting pre-defined values that really can leverage the
enum types custom ordering. It also showcases the idea of data definitions
that "should never change", but that do changes every half dozen years or so.
Now you can argue that since it is expected that the ratings might change in
some way every few years that an enum type is not a good choice for this, but
I feel like some type of counter-argument is that this is probably longer
than one would expect thier database software to last. :-)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
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] taking actions on rollback (PHP)
On Sat, Apr 26, 2008 at 4:19 PM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > > With the added @ everything seemed to be OK. No, the @ is just making php quietly swallow the postgresql errors that are being returned. It changes nothing in operation. > I had to refresh a second time to get rid of the error. > I'd like to be sure I've understood how it works since I wouldn't > like the error went away just by chance and under heavy load I may > have troubles. I'm pretty sure you're not undestanding how it works. With postgresql, ANY error in a transaction results in the whole transaction being rolled back. Any commands thrown at the database after that result in this error message: > Why did I get the > ERROR: current transaction is aborted, commands ignored until end of > transaction block > from 2 different pages? because each page got an error in a statement inside its transaction. It then issued the above error over and over as you attempted to execute the next statement. In postgresql, without using savepoints, any error in the transaction will cause the whole transaction to fail, whether you type commit or rollback at the end. All the changes are lost either way. So, there's no "cleanup" to do for the transaction, it's already cleaned up. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql functions and the planner
Do SQL statements inside of plpgsql functions get planned upon every execution, only when the function is first executed/defined, or something else entirely? For example, suppose I have a table foo and a function bar. Function bar executes some SQL statements (select/insert/update) against table foo using various indexed columns. When the function is created and first executed, table foo is near empty so the statements in function bar use seqscans. Then table foo is filled with many rows. Now, when bar is executed again, will PG (8.3.1) know that a seqscan is no longer reasonable?
