Re: Converting empty input strings to Nulls

Hi Ken-

Have you looked at encode ?
http://www.postgresql.org/docs/8.3/interactive/functions-string.html

Anyone else?
Martin
----- Original Message ----- 
From: "Ken Winter" <ken <at> sunward.org>
To: "PostgreSQL pg-general List" <pgsql-general <at> postgresql.org>
Sent: Saturday, May 31, 2008 1:40 PM
Subject: [GENERAL] Converting empty input strings to Nulls

Applications accessing my PostgreSQL 8.0 database like to submit no-value
date column values as empty strings rather than as Nulls.  This, of course,
causes this PG error:

SQL State: 22007
ERROR: invalid input syntax for type date: ""

I'm looking for a way to trap this bad input at the database level, quietly
convert the input empty strings to Null, and store the Null in the date
column.  I tried a BEFORE INSERT OR UPDATE trigger evoking this function ...

CREATE OR REPLACE FUNCTION "public"."empty_string_to_null"()
RETURNS trigger AS
$BODY$
BEGIN
    IF CAST(NEW.birth_date AS text) = '' THEN
        NEW.birth_date = Null;

Defining character sets for indicidual fields

Re: Defining character sets for indicidual fields

On May 31, 2008, at 6:22 PM, Ram Ravichandran wrote:

> Hi,
>
> By default, my postgresql server is set to use UTF8 character set. I  
> was wondering if there is any way to make sure that certain fields  
> like url etc. only makes use of ascii. My main aim is to save space  
> by using only 1 byte / character for urls  (some of the urls are  
> over 200 characters long). Is this possible? Or are all characters  
> eventually converted to UTF8 during storage?

An ascii string and the UTF8 representation of it will take exactly  
the same number of bytes, so if space used is your concern it's not an  
issue.

Cheers,
   Steve

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Defining character sets for indicidual fields

Hi,

Steve Atkins wrote:
> 
> On May 31, 2008, at 6:22 PM, Ram Ravichandran wrote:
> 
>> Hi,
>>
>> By default, my postgresql server is set to use UTF8 character set. I 
>> was wondering if there is any way to make sure that certain fields 
>> like url etc. only makes use of ascii. My main aim is to save space by 
>> using only 1 byte / character for urls  (some of the urls are over 200 
>> characters long). Is this possible? Or are all characters eventually 
>> converted to UTF8 during storage?
> 
> An ascii string and the UTF8 representation of it will take exactly the 
> same number of bytes, so if space used is your concern it's not an issue.

Even more, if you convert URLs from urlencoding to clear text, you can
quickly leave the ASCII char range (think punicode for the fqdn, think 
utf-8 for the path)

Cheers
Tino

Multiple result sets

I need that 5 queries, fired from the same ajax request to a web
python application, see the same database snapshot. The driver is
psycopg2.

Since postgresql 8.2 functions can't return multiple result sets what
would be the best aproach?

All 5 queries return 2 columns but they are different types in each
query. I could just do a union casting everything to text, adding one
column identifying each query and then recast when receiving the
result set. But that could be costly (i guess the union is) and not
elegant at all. Those queries will fill select boxes and the ajax
refresh should be very fast.

Regards, Clodoaldo Pinto Neto

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Multiple result sets

Clodoaldo <clodoaldo.pinto.neto <at> gmail.com> writes:
> I need that 5 queries, fired from the same ajax request to a web
> python application, see the same database snapshot.

serializable transaction?

			regards, tom lane

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Multiple result sets

> I need that 5 queries, fired from the same ajax request to a web
> python application, see the same database snapshot. The driver is
> psycopg2.
> 
> Since postgresql 8.2 functions can't return multiple result sets what
> would be the best aproach?
> 

You want to set your transaction isolation to "Serializable".
Then execute your 5 queries via the same connection, and the same
Transaction.

You can do that with this command:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Multiple result sets

2008/6/1 Adam Rich <adam.r <at> sbcglobal.net>:
>
>> I need that 5 queries, fired from the same ajax request to a web
>> python application, see the same database snapshot. The driver is
>> psycopg2.
>>
>> Since postgresql 8.2 functions can't return multiple result sets what
>> would be the best aproach?
>>
>
> You want to set your transaction isolation to "Serializable".
> Then execute your 5 queries via the same connection, and the same
> Transaction.
>
> You can do that with this command:
>
> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

I'm not sure i got it. You mean like this?:

import psycopg2 as db
dsn = 'host=localhost dbname=dbname user=user password=passwd'
connection = db.connect(dsn)
cursor = connection.cursor()

cursor.execute('BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;');
rs1 = cursor.execute(query_1, (param1,))
rs2 = cursor.execute(query_2, (param2,))
cursor.execute('commit;');

cursor.close()
connection.close()

I tested it and it raises no exception. I just don't understand if a
transaction persists between execute() calls.

Regards, Clodoaldo

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Multiple result sets

> >> I need that 5 queries, fired from the same ajax request to a web
> >> python application, see the same database snapshot. The driver is
> >> psycopg2.
> >>
> >> Since postgresql 8.2 functions can't return multiple result sets
> >> what would be the best aproach?
> >>
> > You want to set your transaction isolation to "Serializable".
> > Then execute your 5 queries via the same connection, and the same
> > Transaction.
> >
> > You can do that with this command:
> >
> > BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> 
> I'm not sure i got it. You mean like this?:
> 
> import psycopg2 as db
> dsn = 'host=localhost dbname=dbname user=user password=passwd'
> connection = db.connect(dsn)
> cursor = connection.cursor()
> 
> cursor.execute('BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;');
> rs1 = cursor.execute(query_1, (param1,))
> rs2 = cursor.execute(query_2, (param2,))
> cursor.execute('commit;');
> 
> cursor.close()
> connection.close()
> 
> I tested it and it raises no exception. I just don't understand if a
> transaction persists between execute() calls.
> 

I am not familiar with the python library, but that looks correct to me.
You can always test it by adding a sleep between your two queries and
modifying the database from a console connection during the sleep.

Note that I'm assuming your 5 queries are all read-only selects. 
If you're modifying data during your queries, and another concurrent
database connection modifies the same data during your transaction,
the later modifications will fail under serializable isolation.

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Multiple result sets

On Sun, 1 Jun 2008 15:36:14 -0500
"Adam Rich" <adam.r <at> sbcglobal.net> wrote:

> I am not familiar with the python library, but that looks correct
> to me. You can always test it by adding a sleep between your two
> queries and modifying the database from a console connection
> during the sleep.

> Note that I'm assuming your 5 queries are all read-only selects. 
> If you're modifying data during your queries, and another
> concurrent database connection modifies the same data during your
> transaction, the later modifications will fail under serializable
> isolation.

Which one will fail? the second query or the serializable
transaction.

My understanding was that the serializable transaction will fail.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general