[GENERAL] Query cancellation in JDBC

[rsmogura]

Hi,

I done in JDBC driver barrier preventing of execution of query until 
the cancel will not return, but future queries after cancellation are 
occasionally terminated.


The query cancel sends following 16, 123456789, pid, key, flush, after 
this it tries to read EOF, I got and ignore exception "Connection reset" 
- probably OK, so query cancel finishes. The next query will not be 
executed until cancellation will end.


I think problem can be in backend, it can signal process but it doesn't 
wait till signal is processed. I saw this on Windows 7.


If you want I can only try to attach Wireshark frames.

Kind regards,
Radosław Smogura
http://www.softperience.eu

--
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] Moving from SQL Anywhere to PostGres - First Time

[Jasen Betts]

On 2011-01-20, Robert Paresi  wrote:
> Hello,
>
> We have 700 user install base using Sybase SQL Anywhere 9.02
>
> We are looking at migrating these installations over to PostGres

> 1.  Very Very Short Answer Please - why should we?

postgres is the best database in the universe. :)

> 2.  Does anyone have a utility or migration application to read SQL Anywhere 
> to go to PostGres

sed ?  Any stock utility is likely to make choices that are sub optimal for
your needs.  with 700 changeovers pending I would want complete control over
how each column is handled.

> 3.  Does PostGres handle column descriptions (ie: you can give each column a 
> 50 character description) and then access it via SQL Result Set (like I can 
> do in Sybase)

you can put descriptions on columns, I have not hit a length limit.
(probably several megabytes like other text fields).

Getting descriptions in the same same result set as column data is probably
impractical (see length limit for a possible reason why this has not been
implemented). fetching them separately en-masse and caching them or fetching 
or individually afterwards should not be greatly difficult, resultsets are 
returned with the column and table indicated.

> 4.  Is there any Date/TimeStamp issues and conversions I need to know about. 
> I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp 
> fields together.

time goes upto 24:00:00.99 just incase you come across a day that long.
so unless that's going to cause problems there should be no issues.
the range supported by date is starts in pre-history and continues
for a few million years into the future.

> 5.  What UI/Developer tools (GUI) are available to manage the database as 
> well as add/change columns rather than doing it via SQL commands.

I hear there are several, But I can't understand why someone would want to 
perform non-graphical tasks using a GUI.  

-- 
⚂⚃ 100% natural

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

[GENERAL] Queyring for columns which are exist in table.

[Santosh Bhujbal (sabhujba)]

Hi All,

 

I want to fire a query such that if the particular column does not exist
then query should return some default value.

For that I have tried following experiment.

 

CREATE TABLE tbl (

c1 integer,

c2 integer,

c3 integer

);

 

INSERT INTO tbl VALUES (1, 2, 3);

INSERT INTO tbl VALUES (2, 3, 4);

INSERT INTO tbl VALUES (3, 4, 5);

INSERT INTO tbl VALUES (4, 5, 6);

INSERT INTO tbl VALUES (5, 6, 7);

INSERT INTO tbl VALUES (6, 7, 8);

INSERT INTO tbl VALUES (7, 8, 9);

INSERT INTO tbl VALUES (8, 9, 10);

 

 

CREATE OR REPLACE FUNCTION ColumnAlreadyExists(name, name) RETURNS
INTEGER AS E'

DECLARE columnCount INTEGER;

BEGIN

 

SELECT COUNT (pg_attribute.attname) into columnCount FROM
pg_attribute,pg_class, pg_type WHERE
((pg_attribute.attrelid=pg_class.oid) AND
(pg_attribute.atttypid=pg_type.oid) AND (pg_class.relname = $1) AND
(pg_attribute.attname = $2));

 

IF columnCount = 0 THEN

RETURN 0;

END IF;

RETURN 1;

END;

' LANGUAGE 'plpgsql';

 

 

DROP FUNCTION checkColumn(name,name,name);

CREATE OR REPLACE FUNCTION checkColumn(name, name, name) RETURNS name AS
E'

DECLARE isColumnExist INTEGER;

 

BEGIN

SELECT ColumnAlreadyExists ($1,$2) into isColumnExist;

 

IF isColumnExist = 0 THEN

RETURN name($3);

ELSE

RETURN name($2);

END IF;

END;

' LANGUAGE 'plpgsql';

 

 

 

Function checkColumn should return proper column name (second parameter)
if column exist and third parameter if column not exist.

 

NOW when I try to execute following command it returns improper result.

I expect proper column values as a output of query.

 

SELECT(checkColumn('tbl','c2','0'))::name FROM tbl;

 

mydb=# SELECT (checkColumn('tbl','c2','0'))::name FROM tbl;

 checkcolumn

-

 c2

 c2

 c2

 c2

 c2

 c2

 c2

 c2

(8 rows)

 

mydb=#

 

 

Above query should return actual values present for c2 column in tbl.

But it's not working as desired.

Please help me in this.

 

Thanks in advance,

Santosh.

Re: [GENERAL] iPad and Pg revisited...

[John DeSoi]

On Jan 24, 2011, at 3:25 PM, Jerry LeVan wrote:

> I assume that if I were to jump to Pg 9.x.x that phpPgAdmim would die, yes?

I have not tried it, but my guess is it will work. I don't recall seeing that 
there were any major protocol changes for version 9, so I suspect whatever 
libpq version is linked to PHP should work just fine with Postgres 9.




John DeSoi, Ph.D.





-- 
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] Postgresql as a dictionary coder backend?

[Cédric Villemain]

2011/1/23 Attila Nagy :
>  Hello,
>
> I'm looking for a database backend for a dictionary coder project. It would
> have three major tasks:
> - take a text corpus, get their words and substitute each word by a 64 bit
> integer (the word:integer is always constant) and store the result
> (encoding)

ok. PostgreSQL allow to do that easily.

> - take the previous result and substitute the integers with words (decoding)

idem.

> - the words should be reference counted, so if a word can be no longer found
> in any of the encoded messages, delete it (and optionally free it's integer
> ID, but 64 bit is believed to be enough for a long time, although having
> smaller IDs result smaller encoded files). This could be achieved by
> informing the database of the words of a deleted message, so it could
> decrement those refcounts and delete the records if needed.

Yes, like what despez do :
http://www.depesz.com/index.php/2009/07/10/getting-list-of-unique-elements/

>
> I can easily do this with any RDBMS, with a table of three columns: auto
> incremented ID, word and refcount, with a unique index on word.
> The challenge could be:
> - that it should scale to several TBs of size and several (hundred) billion
> of records. One scenario would be to store about 40 TBs of words and the
> average word length would be about 50-60 bytes (that's about 800*10^9
> records). It should work well both for inserting and searching (encoding and
> decoding) words.

I strongly suggest you to have a look at intarray contrib (it is
provided with PostgreSQL.

> - I need atomicity and durability, but having these on a word (record) level
> takes too much IOPS and have no use, so it would be good to have an
> interface for inserting about 1000-50 words in one call, assign a unique
> ID to each unique words and store them (if the word has had already an ID,
> increment its refcount) and give back the IDs for each words. This
> transaction could be committed as one, so the transactions could be big,
> sparing IOPS.

Array allow a very good compression of the data per row. (still it is
not a RDBMS way to use array for that, but it is good for
performances)

> - I need concurrency, so when the above happens from two sources at the same
> time, the same word in the two transactions must get the same ID

one transaction will finish before the other to allow that. (but they
can start at the same time)

>
> Is postgresql a good choice for doing this and if yes, what would be the
> optimal (for both time and space efficiency at encoding and decoding) use
> case?

PostgreSQL should work for that, yes. You'll have to compensate the
size with good hardware and good SQL (and probably some optimization
like using arrays)

-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

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

[GENERAL] Store base64 in database. Use bytea or text?

[Andre Lopes]

Hi,

I need to put some images on Base64 in a PostgreSQL database. Wich
type should I use and what is the difference between using bytea or
text to store Base64?

Best Regards,

-- 
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] Store base64 in database. Use bytea or text?

[Bill Moran]

In response to Andre Lopes :
> 
> I need to put some images on Base64 in a PostgreSQL database. Wich
> type should I use and what is the difference between using bytea or
> text to store Base64?

bytea is overkill if you know for sure that the data will always be
base64.  Aside from that, I don't know of any particular advantage
either way.  Since the data isn't really text, your locale isn't
really going to come into play (i.e., it's not like you're going to
sort the data or anything).  Since the data isn't binary, you won't
have to worry about escaping anything.

Personally, I'd use text.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] Store base64 in database. Use bytea or text?

[Andy Colson]

On 1/25/2011 4:27 PM, Andre Lopes wrote:

Hi,

I need to put some images on Base64 in a PostgreSQL database. Wich
type should I use and what is the difference between using bytea or
text to store Base64?

Best Regards,



If they are encoded in base64, use text.  Use bytea if you dont want to 
encode them.


-Andy

--
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] Store base64 in database. Use bytea or text?

[Dmitriy Igrishin]

2011/1/26 Andy Colson 

> On 1/25/2011 4:27 PM, Andre Lopes wrote:
>
>> Hi,
>>
>> I need to put some images on Base64 in a PostgreSQL database. Wich
>> type should I use and what is the difference between using bytea or
>> text to store Base64?
>>
>> Best Regards,
>>
>>
> If they are encoded in base64, use text.  Use bytea if you dont want to
> encode them.
>
Or *store* in bytea by using decode() function and retrieve
in base64 by using encode() function.
http://www.postgresql.org/docs/9.0/static/functions-string.html

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



-- 
// Dmitriy.

Re: [GENERAL] Store base64 in database. Use bytea or text?

[Peter Geoghegan]

On 25 January 2011 22:27, Andre Lopes  wrote:
> Hi,
>
> I need to put some images on Base64 in a PostgreSQL database. Wich
> type should I use and what is the difference between using bytea or
> text to store Base64?

I really don't think you want to do that. Base64 is used to make
binary data 7-bit safe for compatibility with legacy systems (i.e. to
embed arbitrary binary data within ASCII). Sometimes people escape
binary data as base64 to store it in their DB, but they typically
store it as bytea. Base64 probably isn't even a particularly good
choice for escaping binary, let alone storing it.

You should just use a generic escaping function. libpq has
PQescapeByteaConn(), for example.

-- 
Regards,
Peter Geoghegan

-- 
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] Store base64 in database. Use bytea or text?

[Bill Moran]

In response to Peter Geoghegan :

> On 25 January 2011 22:27, Andre Lopes  wrote:
> > Hi,
> >
> > I need to put some images on Base64 in a PostgreSQL database. Wich
> > type should I use and what is the difference between using bytea or
> > text to store Base64?
> 
> I really don't think you want to do that. Base64 is used to make
> binary data 7-bit safe for compatibility with legacy systems (i.e. to
> embed arbitrary binary data within ASCII). Sometimes people escape
> binary data as base64 to store it in their DB, but they typically
> store it as bytea. Base64 probably isn't even a particularly good
> choice for escaping binary, let alone storing it.
> 
> You should just use a generic escaping function. libpq has
> PQescapeByteaConn(), for example.

A warning: last I checked, PHP's pg_escape_bytea() was broken, so be
cautious if you're using PHP.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] Store base64 in database. Use bytea or text?

[Dmitriy Igrishin]

2011/1/26 Peter Geoghegan 

> On 25 January 2011 22:27, Andre Lopes  wrote:
> > Hi,
> >
> > I need to put some images on Base64 in a PostgreSQL database. Wich
> > type should I use and what is the difference between using bytea or
> > text to store Base64?
>
> I really don't think you want to do that. Base64 is used to make
> binary data 7-bit safe for compatibility with legacy systems (i.e. to
> embed arbitrary binary data within ASCII). Sometimes people escape
> binary data as base64 to store it in their DB, but they typically
> store it as bytea. Base64 probably isn't even a particularly good
> choice for escaping binary, let alone storing it.
>
> You should just use a generic escaping function. libpq has
> PQescapeByteaConn(), for example.
>
It is better to use PQexecParams and binary data transfer instead
of escaping via PQescapeByteaConn.

>
> --
> Regards,
> Peter Geoghegan
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Store base64 in database. Use bytea or text?

[Andre Lopes]

Thanks for all the reply's.

I will be using PHP for now to insert data.

So I shouldn't use base64 to store images or any other kind of files.
I'm new to storing files in the database. This will be my first
experience.

I will research about PQescapeByteaConn.

Thanks for the help.

Best Regards,


On Tue, Jan 25, 2011 at 10:46 PM, Bill Moran  wrote:
> In response to Peter Geoghegan :
>
>> On 25 January 2011 22:27, Andre Lopes  wrote:
>> > Hi,
>> >
>> > I need to put some images on Base64 in a PostgreSQL database. Wich
>> > type should I use and what is the difference between using bytea or
>> > text to store Base64?
>>
>> I really don't think you want to do that. Base64 is used to make
>> binary data 7-bit safe for compatibility with legacy systems (i.e. to
>> embed arbitrary binary data within ASCII). Sometimes people escape
>> binary data as base64 to store it in their DB, but they typically
>> store it as bytea. Base64 probably isn't even a particularly good
>> choice for escaping binary, let alone storing it.
>>
>> You should just use a generic escaping function. libpq has
>> PQescapeByteaConn(), for example.
>
> A warning: last I checked, PHP's pg_escape_bytea() was broken, so be
> cautious if you're using PHP.
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>

-- 
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] Store base64 in database. Use bytea or text?

[Dmitriy Igrishin]

2011/1/26 Andre Lopes 

> Thanks for all the reply's.
>
> I will be using PHP for now to insert data.
>
> So I shouldn't use base64 to store images or any other kind of files.
> I'm new to storing files in the database. This will be my first
> experience.
>
You may want to use large objects to store files instead of using
bytea data type. With large objects you can achieve streaming
data transfer and as of PostgreSQL 9.0 it is possible to control
access permissions on large objects via GRANT.

But I don't know about support of large objects in PHP.

> I will research about PQescapeByteaConn.
>
> Thanks for the help.
>
> Best Regards,
>
>
> On Tue, Jan 25, 2011 at 10:46 PM, Bill Moran 
> wrote:
> > In response to Peter Geoghegan :
> >
> >> On 25 January 2011 22:27, Andre Lopes  wrote:
> >> > Hi,
> >> >
> >> > I need to put some images on Base64 in a PostgreSQL database. Wich
> >> > type should I use and what is the difference between using bytea or
> >> > text to store Base64?
> >>
> >> I really don't think you want to do that. Base64 is used to make
> >> binary data 7-bit safe for compatibility with legacy systems (i.e. to
> >> embed arbitrary binary data within ASCII). Sometimes people escape
> >> binary data as base64 to store it in their DB, but they typically
> >> store it as bytea. Base64 probably isn't even a particularly good
> >> choice for escaping binary, let alone storing it.
> >>
> >> You should just use a generic escaping function. libpq has
> >> PQescapeByteaConn(), for example.
> >
> > A warning: last I checked, PHP's pg_escape_bytea() was broken, so be
> > cautious if you're using PHP.
> >
> > --
> > Bill Moran
> > http://www.potentialtech.com
> > http://people.collaborativefusion.com/~wmoran/
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Store base64 in database. Use bytea or text?

[Andre Lopes]

Thanks for the reply.

I will mainly store files with 100kb to 250kb not bigger than this.

PQescapeByteaConn is not available in a default installation of
PostgreSQL? My hosting account hava a standard installation of
PostgreSQL. There are other options for escaping binary files?

Best Regards,


On Tue, Jan 25, 2011 at 10:58 PM, Dmitriy Igrishin  wrote:
>
>
> 2011/1/26 Andre Lopes 
>>
>> Thanks for all the reply's.
>>
>> I will be using PHP for now to insert data.
>>
>> So I shouldn't use base64 to store images or any other kind of files.
>> I'm new to storing files in the database. This will be my first
>> experience.
>
> You may want to use large objects to store files instead of using
> bytea data type. With large objects you can achieve streaming
> data transfer and as of PostgreSQL 9.0 it is possible to control
> access permissions on large objects via GRANT.
>
> But I don't know about support of large objects in PHP.
>>
>> I will research about PQescapeByteaConn.
>>
>> Thanks for the help.
>>
>> Best Regards,
>>
>>
>> On Tue, Jan 25, 2011 at 10:46 PM, Bill Moran 
>> wrote:
>> > In response to Peter Geoghegan :
>> >
>> >> On 25 January 2011 22:27, Andre Lopes  wrote:
>> >> > Hi,
>> >> >
>> >> > I need to put some images on Base64 in a PostgreSQL database. Wich
>> >> > type should I use and what is the difference between using bytea or
>> >> > text to store Base64?
>> >>
>> >> I really don't think you want to do that. Base64 is used to make
>> >> binary data 7-bit safe for compatibility with legacy systems (i.e. to
>> >> embed arbitrary binary data within ASCII). Sometimes people escape
>> >> binary data as base64 to store it in their DB, but they typically
>> >> store it as bytea. Base64 probably isn't even a particularly good
>> >> choice for escaping binary, let alone storing it.
>> >>
>> >> You should just use a generic escaping function. libpq has
>> >> PQescapeByteaConn(), for example.
>> >
>> > A warning: last I checked, PHP's pg_escape_bytea() was broken, so be
>> > cautious if you're using PHP.
>> >
>> > --
>> > Bill Moran
>> > http://www.potentialtech.com
>> > http://people.collaborativefusion.com/~wmoran/
>> >
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
> // Dmitriy.
>
>
>

-- 
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] Store base64 in database. Use bytea or text?

[Dmitriy Igrishin]

2011/1/26 Andre Lopes 

> Thanks for the reply.
>
> I will mainly store files with 100kb to 250kb not bigger than this.
>
> PQescapeByteaConn is not available in a default installation of
> PostgreSQL? My hosting account hava a standard installation of
> PostgreSQL. There are other options for escaping binary files?
>
> Best Regards,
>
PQescapeByteConn is a function of libpq - native C client library.
In you case (PHP) you should use its functions to encode binary
data before including it into a query (e.g., in base64) and use
built-in decode() function of Postgres:
-- Pseudo-code
INSERT INTO img (dat) VALUES (decode(BASE64_INPUT_FROM_PHP, 'base64'));

where dat column of table img of type bytea.

Or you can use PHP's function which escapes binary data for bytea
textual representation (now hex by default) and omit decode(). In this
case you query will like that:
-- Pseudo-code
INSERT INTO img (dat) VALUES (BYTEA_ESCAPED_FROM_PHP);

>
>
> On Tue, Jan 25, 2011 at 10:58 PM, Dmitriy Igrishin 
> wrote:
> >
> >
> > 2011/1/26 Andre Lopes 
> >>
> >> Thanks for all the reply's.
> >>
> >> I will be using PHP for now to insert data.
> >>
> >> So I shouldn't use base64 to store images or any other kind of files.
> >> I'm new to storing files in the database. This will be my first
> >> experience.
> >
> > You may want to use large objects to store files instead of using
> > bytea data type. With large objects you can achieve streaming
> > data transfer and as of PostgreSQL 9.0 it is possible to control
> > access permissions on large objects via GRANT.
> >
> > But I don't know about support of large objects in PHP.
> >>
> >> I will research about PQescapeByteaConn.
> >>
> >> Thanks for the help.
> >>
> >> Best Regards,
> >>
> >>
> >> On Tue, Jan 25, 2011 at 10:46 PM, Bill Moran 
> >> wrote:
> >> > In response to Peter Geoghegan :
> >> >
> >> >> On 25 January 2011 22:27, Andre Lopes 
> wrote:
> >> >> > Hi,
> >> >> >
> >> >> > I need to put some images on Base64 in a PostgreSQL database. Wich
> >> >> > type should I use and what is the difference between using bytea or
> >> >> > text to store Base64?
> >> >>
> >> >> I really don't think you want to do that. Base64 is used to make
> >> >> binary data 7-bit safe for compatibility with legacy systems (i.e. to
> >> >> embed arbitrary binary data within ASCII). Sometimes people escape
> >> >> binary data as base64 to store it in their DB, but they typically
> >> >> store it as bytea. Base64 probably isn't even a particularly good
> >> >> choice for escaping binary, let alone storing it.
> >> >>
> >> >> You should just use a generic escaping function. libpq has
> >> >> PQescapeByteaConn(), for example.
> >> >
> >> > A warning: last I checked, PHP's pg_escape_bytea() was broken, so be
> >> > cautious if you're using PHP.
> >> >
> >> > --
> >> > Bill Moran
> >> > http://www.potentialtech.com
> >> > http://people.collaborativefusion.com/~wmoran/
> >> >
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
> >
> >
> > --
> > // Dmitriy.
> >
> >
> >
>



-- 
// Dmitriy.

[GENERAL] Install Pgadmin3 1.12 on ubuntu 10.4 lucid client without postgresql server install

[MargaretGillon]

Using these instructions http://wiki.flexive.org/confluence/display/FX/Postgres+9.x+installation+in+Ubuntu?focusedCommentId=4915206#comment-4915206
I am trying to install current pgadmin3 1.12 on an ubuntu lucid client. Using the instructions above I have an Ubuntu 10.4 Postgresql 9 server up and 
running successfully with pgadmin3 working, too. If I try this server install on the ubuntu client it fails. Because there is no local server the pgadmin3 install fails. Is there a way to install just pgadmin3 from the 
tar on an ubuntu 10.4 client?*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***Margaret GillonSenior Programmer-AnalystChromalloy Los Angeles2100 West 139th StreetGardena, CAUnited States 310-532-6100 extension 297fax 310-329-2228margaretgil...@chromalloy.com
This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain company proprietary, privileged or confidential information.   If you are not the intended recipient(s), please contact the sender by reply e-mail, advise them of the error and destroy this message and its attachments as well as any copies. The review, use or distribution of this message or its content by anyone other than the intended recipient or senior management of the company is strictly prohibited."This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain company proprietary, privileged or confidential information.   If you are not the intended recipient(s), please contact the sender by reply e-mail, advise them of the error and destroy this message and its attachments as well as any copies. The review, use or distribution of this message or its content by anyone other than the intended recipient or senior management of the company is strictly prohibited."

Re: [GENERAL] Moving from SQL Anywhere to PostGres - First Time

[Robert Paresi]

I hear there are several, But I can't understand why someone would want to 
perform non-graphical tasks using a GUI.


Because it is easier.

I've managed to do everything I need - as well as write a full query 
editor/report writer for PostGreSQL.


It is being beta tested now, and I plan on releasing it within the next 2 or 
3 weeks.


It adds about 8 extensions to the PostGreSQL language - and does many 
things.


You'll see why it is nice to use a nice/pleasant GUI interface.

ie:

 /* Check to make sure all email addresses have valid domain names */
 select lastname,firstname,email  from profiles
   domain check on email

OR

 /* return a list, subtotalling adults and child by state */
 select state,lastname,firstname,email,adults,child from profile
   order by state
   subtotal adults,child on state

OR

/* highlight all credits in red */
select company,balance from accounts
  highlight balance where balance < 0 bgcolor=COLOR:RED


-Robert

"Jasen Betts"  wrote in message news:ihmf86$98d$1...@reversiblemaps.ath.cx...

On 2011-01-20, Robert Paresi  wrote:

Hello,

We have 700 user install base using Sybase SQL Anywhere 9.02

We are looking at migrating these installations over to PostGres



1.  Very Very Short Answer Please - why should we?


postgres is the best database in the universe. :)

2.  Does anyone have a utility or migration application to read SQL 
Anywhere

to go to PostGres


sed ?  Any stock utility is likely to make choices that are sub optimal for
your needs.  with 700 changeovers pending I would want complete control over
how each column is handled.

3.  Does PostGres handle column descriptions (ie: you can give each column 
a
50 character description) and then access it via SQL Result Set (like I 
can

do in Sybase)


you can put descriptions on columns, I have not hit a length limit.
(probably several megabytes like other text fields).

Getting descriptions in the same same result set as column data is probably
impractical (see length limit for a possible reason why this has not been
implemented). fetching them separately en-masse and caching them or fetching
or individually afterwards should not be greatly difficult, resultsets are
returned with the column and table indicated.

4.  Is there any Date/TimeStamp issues and conversions I need to know 
about.
I use simply a DATE field and a TIME field - but do not use DATE/TIME 
stamp

fields together.


time goes upto 24:00:00.99 just incase you come across a day that long.
so unless that's going to cause problems there should be no issues.
the range supported by date is starts in pre-history and continues
for a few million years into the future.


5.  What UI/Developer tools (GUI) are available to manage the database as
well as add/change columns rather than doing it via SQL commands.


I hear there are several, But I can't understand why someone would want to
perform non-graphical tasks using a GUI.

--
⚂⚃ 100% natural

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



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

[GENERAL] archive_command not enabled after finshing recovery?

[hubert depesz lubaczewski]

hi
was testing an idea with pg 8.2.19
generally, I had master and slave, wal archiving set in both of them,
but when slave was slave, it didn't archive any wal segments. which is
fine.

*but*
when I finished slave recovery, it went online, but it didn't start
automatically the archiving process.

is there any way to make sure that it will start archiving before it
will overwrite any wal segments?

Best regards,

depesz

-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

-- 
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] Moving from SQL Anywhere to PostGres - First Time

[Dmitriy Igrishin]

2011/1/25 Jasen Betts 

> On 2011-01-20, Robert Paresi  wrote:
> > Hello,
> >
> > We have 700 user install base using Sybase SQL Anywhere 9.02
> >
> > We are looking at migrating these installations over to PostGres
>
> > 1.  Very Very Short Answer Please - why should we?
>
> postgres is the best database in the universe. :)
>
> > 2.  Does anyone have a utility or migration application to read SQL
> Anywhere
> > to go to PostGres
>
> sed ?  Any stock utility is likely to make choices that are sub optimal for
> your needs.  with 700 changeovers pending I would want complete control
> over
> how each column is handled.
>
> > 3.  Does PostGres handle column descriptions (ie: you can give each
> column a
> > 50 character description) and then access it via SQL Result Set (like I
> can
> > do in Sybase)
>
> you can put descriptions on columns, I have not hit a length limit.
> (probably several megabytes like other text fields).
>
> Getting descriptions in the same same result set as column data is probably
> impractical (see length limit for a possible reason why this has not been
> implemented). fetching them separately en-masse and caching them or
> fetching
> or individually afterwards should not be greatly difficult, resultsets are
> returned with the column and table indicated.
>
> > 4.  Is there any Date/TimeStamp issues and conversions I need to know
> about.
> > I use simply a DATE field and a TIME field - but do not use DATE/TIME
> stamp
> > fields together.
>
> time goes upto 24:00:00.99 just incase you come across a day that long.
> so unless that's going to cause problems there should be no issues.
> the range supported by date is starts in pre-history and continues
> for a few million years into the future.
>
> > 5.  What UI/Developer tools (GUI) are available to manage the database as
> > well as add/change columns rather than doing it via SQL commands.
>
> I hear there are several, But I can't understand why someone would want to
> perform non-graphical tasks using a GUI.
>
I can't understand how to develop and maintain even small
data model without diagramming tool which can represent
entities and relationships between them.
Using pure psql and paper with a pen - is nothing more than LOL.

I recommend dbWrench as a diagramming tool for Postgres.

>
> --
> ⚂⚃ 100% natural
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Moving from SQL Anywhere to PostGres - First Time

[Gary Chambers]

I recommend dbWrench as a diagramming tool for Postgres.


I can also recommend SchemaSpy (http://schemaspy.sourceforge.net/), a superb
command line-based schema browser and ERD generator.

-- Gary Chambers

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

[GENERAL] Subselect AS and Where clause

[Uwe Schroeder]

Google being useless tonight - now that's new :-)

What I'm trying to do is the following and I'm certain there is a simple 
solution which eludes me:

I have a query like this:

SELECT a,b,c, (select problem from other_table where id=a) as problem FROM 
mytable WHERE a=1

So far so good. Actually "problem" always resolves to one record, so it's not 
the "multiple records returned" problem.

What I try to do is this:

SELECT a,b,c, (select problem from other_table where id=a) as problem FROM 
mytable WHERE a=1 and problem = 3

see the "problem=3" part in the where clause? The error I get is 

 SQLError: (ProgrammingError) column "problem" does not exist

Do I miss something? Shouldn't the "as" assignment make this virtual column 
available to the where clause? I think this should work and I know it works if 
I'd make a view out of the query. However, the query is built dynamically, so 
turning it into a view isn't really an option.

Any pointer will be greatly appreciated.

Uwe

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