[GENERAL] Query cancellation in JDBC
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
On 2011-01-20, Robert Paresi firstn...@lastname.net 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.
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...
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?
2011/1/23 Attila Nagy b...@fsn.hu: 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?
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?
In response to Andre Lopes lopes80an...@gmail.com: 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?
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?
2011/1/26 Andy Colson a...@squeakycode.net 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?
On 25 January 2011 22:27, Andre Lopes lopes80an...@gmail.com 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?
In response to Peter Geoghegan peter.geoghega...@gmail.com: On 25 January 2011 22:27, Andre Lopes lopes80an...@gmail.com 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?
2011/1/26 Peter Geoghegan peter.geoghega...@gmail.com On 25 January 2011 22:27, Andre Lopes lopes80an...@gmail.com 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?
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 wmo...@potentialtech.com wrote: In response to Peter Geoghegan peter.geoghega...@gmail.com: On 25 January 2011 22:27, Andre Lopes lopes80an...@gmail.com 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?
2011/1/26 Andre Lopes lopes80an...@gmail.com 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 wmo...@potentialtech.com wrote: In response to Peter Geoghegan peter.geoghega...@gmail.com: On 25 January 2011 22:27, Andre Lopes lopes80an...@gmail.com 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/http://people.collaborativefusion.com/%7Ewmoran/ -- 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?
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 dmit...@gmail.com wrote: 2011/1/26 Andre Lopes lopes80an...@gmail.com 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 wmo...@potentialtech.com wrote: In response to Peter Geoghegan peter.geoghega...@gmail.com: On 25 January 2011 22:27, Andre Lopes lopes80an...@gmail.com 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?
2011/1/26 Andre Lopes lopes80an...@gmail.com 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 dmit...@gmail.com wrote: 2011/1/26 Andre Lopes lopes80an...@gmail.com 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 wmo...@potentialtech.com wrote: In response to Peter Geoghegan peter.geoghega...@gmail.com: On 25 January 2011 22:27, Andre Lopes lopes80an...@gmail.com 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/http://people.collaborativefusion.com/%7Ewmoran/ -- 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
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?MargaretGillonSeniorProgrammer-AnalystChromalloyLosAngeles2100West139thStreetGardena,CAUnitedStates310-532-6100extension297fax310-329-2228margaretgil...@chromalloy.com Thise-mailmessageandanyattachment(s)areforthesoleuseoftheintendedrecipient(s)andmaycontaincompanyproprietary,privilegedorconfidentialinformation.Ifyouarenottheintendedrecipient(s),pleasecontactthesenderbyreplye-mail,advisethemoftheerroranddestroythismessageanditsattachmentsaswellasanycopies.Thereview,useordistributionofthismessageoritscontentbyanyoneotherthantheintendedrecipientorseniormanagementofthecompanyisstrictlyprohibited.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
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 firstn...@lastname.net 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?
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
2011/1/25 Jasen Betts ja...@xnet.co.nz On 2011-01-20, Robert Paresi firstn...@lastname.net 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
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
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
