Re: [GENERAL] Use index for upper(customername) like 'ABC%'
On 4 fev, 18:13, Andrus [EMAIL PROTECTED] wrote: I need to perform case insensitive search by first some characters (ABC) of name like SELECT ... FROM customer WHERE upper(customername) like 'ABC%' My database cluster locale is non-C Database encoding is UTF-8 Which index I must create so that this query can use it to speed up data retrieval ? Andrus. CREATE INDEX ie_cust_upper_name ON customer ( UPPER( customername ) ); If you want to perform better performance for like 'ABC%' statement, you can define it on WHERE clause: CREATE INDEX ie_cust_upper_name ON customer ( UPPER( customername ) ) WHERE ( UPPER(customername) LIKE 'ABC%' ); ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Order of SUBSTR and UPPER in statement
On 13 fev, 12:19, Hermann Muster [EMAIL PROTECTED] wrote: Hi, I encountered something I can't really explain. I use the following statement in my application: COALESCE(UPPER(SUBSTR(Y.Firma,1,7)),'') This returns ERROR: syntax error at end of input However, using the following statement is fine: COALESCE(SUBSTR(UPPER(X.Firma), 1, 7), '') The fieldtype of Firma is character varying. The only difference is the order of UPPER and SUBSTR. Is it possible that this changed during some PostgreSQL version update? By the way, right now I'm using 8.2. Regards, Hermann Please post the entire SQL statement and table structure, and even posting here, did you notice in the first SQL you wrote you're using Y.firma, and on the second you typed X.firma. Is there any difference between X and Y aliases? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Vertical and Horizontal Table Partition
I know that using tablespaces I can create a vertical partition by creating two tables with the same primary key wich stores different columns in each table. However, I need to do some horizontal partition that receives about 2,000,000 records per month. What the best way to perform this horizontal partition in a single table? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Converting from MySQL...need book ideas
On 29 jan, 18:42, cbmeeks [EMAIL PROTECTED] wrote: I love MySQL but I'm concerned about it's future. And, I'm always looking to learn something new. I love the licensing model of Postgres. Anyway, using online documentation, I've managed to get a server running but I find myself constantly having to google little things that I haven't learned yet. Does anyone out there have any book recommendations? I am looking for both administration and development books for medium to advanced levels. My SQL skills are pretty strong as I am a full time C# / T-SQL developer during the day. My apps will be web/django/python based if that makes a difference. Thanks cbmeekshttp://codershangout.com There is a lot of content over the entire WWW. I suggest you to do specific searches on google, that's more productive than get ride on a book when you need the basics. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgres startup method for ubuntu
On 30 jan, 04:14, Jon Hancock [EMAIL PROTECTED] wrote: I have compiled postgres 8.3rc2 on ubuntu 7.04 and seem to a have working install. To start postgres, I do the following: su postgres /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data ok, that works for dev mode. But what is the recommended approach to launch postgres at system boot? Sure, I can hack the above command in an init.d script and if it seems to work, thats nice. But since I'm a nube here, I think I should ask for the refined approach for a startup script. For example, in the mysql world, we have a safe_mysql script which takes care of startup best practices. thanks, Jon I am also searching for a more 'professional' way to startup and shutdown postgresql on ubuntu and debian systems. Everytime I deploy a PostgreSQL server in a ubuntu or debian based server, I create some scripts called pg_start, pg_stop and pg_reload, save them on default postgresql home dir. Then I create symbolic links on /usr/bin, and so on I put pg_start on /etc/init.d/bootmisc.sh. However, I want to deploy it as a native linux service, as 'cron' or 'network', and really I dunno how to do it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Recover by Log
How I can recover data (or replicate data to another) based on postgresql logs? Where they are, and how I do it? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] PostgreSQL 8.3 RC2 Install problems on Windows Vista Home Premium PT_BR
I'm trying to install PostgreSQL 8.3 RC2 for Windows on my Windows Vista Home Premium (Brazillian Portuguese), and thus I'm getting an error on 'initdb' after copy of all files into destination path. The error is this: Failed to run initdb: 1! Please see the logfile in 'C:\Program Files\PostgreSQL\8.3RC2\tmp \initdb.log' Note! You must read/copy this logfile before you click OK, or it will be automatically removed. And on this file, I get a portuguese message, even using English as language for the installer: Falha na inicialização do aplicativo devido a configuração lado a lado incorreta. Consulte o log de eventos do aplicativo para obter mais informações. The translation looks like: Failed on application initialization due to side by side missconfiguration. Check the application's event log for more information. Notice that I haven't PostgreSQL installed on this PC, and after trying to install 8.3 RC2 many times I installed 8.2.6 with no one error. This appears to be an error on installer. Where and how I can report it to the development group efficiently? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgres startup method for ubuntu
On 31 jan, 12:24, [EMAIL PROTECTED] (Daniel Verite) wrote: T.J. Adami wrote: I am also searching for a more 'professional' way to startup and shutdown postgresql on ubuntu and debian systems. The source comes with a startup script that can be installed by running: # cp contrib/start-scripts/linux /etc/init.d/postgresql # update-rc.d postgresql defaults (update-rc.d is to debian-based systems what chkconfig is to Redhat-based systems). -- Daniel PostgreSQL-powered mail user agent and storage:http://www.manitou-mail.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Debian does it for me nicely on bootup. I wonder why you think you need to start it manually ? Karsten I always compile my own version from sources for Python and Perl support, and also because of newest versions that are update with a lack of time (for reasonable test reasons). The source comes with a startup script that can be installed by running: # cp contrib/start-scripts/linux /etc/init.d/postgresql # update-rc.d postgresql defaults (update-rc.d is to debian-based systems what chkconfig is to Redhat-based systems). -- Daniel This adds shutdown scripts too? I need the server shutdown by 'pg_ctl stop', not by linux killing the process (init 0 or shutdown commands are very useful sometimes ;) ). I know this is a specific distro question, however we started this thread yet... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Installing PGSQL
On 22 jan, 22:46, [EMAIL PROTECTED] (Jamiil Abduqadir) wrote: I am trying to install PosgreSQL using postgresql-8.3-dev1 on my WindowsXP machine, but I get a message that reads Fail to create a temporary directory Does anyone know why I am getting this error message? Thank in advance -- Happiness has many doors, and when one of them closes another opens, yet we spent so much time looking at the one that is shut that we don't see the one that just opened. It seems to be a windows error, maybe in your temporary path. 1) Verify your TMP and TEMP environment variables (values must reference valid directory entries); 2) Is there sufficient disk space for extract files from the PostgreSQL package into TMP and TEMP path reference on the environment vars? 3) Also check out your user settings. For installation, you must log in with an administrative role (in Windows). ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql source build instructions for ubuntu 7.04
On 22 jan, 05:02, [EMAIL PROTECTED] (Julio Cesar Sánchez González) wrote: Jon Hancock wrote: The INSTALL file for postgresql 8.3rc1 lists the following install instructions: ./configure gmake su gmake install adduser postgres mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test I get to the 4th step and see I need gmake. I use make instead. Things seem ok. Then all is well, until line 10: /usr/local/pgsql/bin/initdb -D /usr/ local/pgsql/data The bin directory is not accessible to anyone but root due to the permissions of the install. Should I have used a different umask for the make/install process? As I'm new to pg, what should I change the permissions to? Should I make the entire bin directory executable by anyone? Does anyone have experience with installing from source on ubuntu 7.04? thanks, Jon By the way, if you want compile PostgreSQL and all of its features on Ubuntu, you'll need to do some sets and install some extra packages (make sure your universe and multiverse repositories are working fine). 1) Installing all necessary packages for PostgreSQL: sudo apt-get install g++ libreadline5-dev flex bison libzzip-dev libio- zlib-perl zlib1g-dev zlib-bin zlibc zziplib-bin libperl-dev python-dev build-essential 2) Expect libperl to work fine (assume your right version of installed libperl, mine is 5.8); sudo ln -s /usr/lib/libperl.so.5.8 libperl.so 3) Expand files sudo tar -xjvf postgresql-8.2.5.tar.bz2 4) Access the source dir cd postgresql-8.2.5 5) Adding group and user sudo groupadd postgres sudo adduser -g postgres postgres 6) Configure (please fill the options with your preferences) sudo ./configure --prefix=/usr/local/postgres --bindir=/usr/bin -- sysconfdir=/etc/postgres --with-perl --with-python 7) Do make! sudo make sudo make install 8) Create postgresql folder and set the owner to postgres group and user sudo mkdir /usr/local/postgres/data sudo chown postgres:postgres /usr/local/postgres/data 9) Change the current user to postgres sudo su postgres 10) Start a new cluster (choose your enconding, mine is latin1) initdb -E latin1 -D /usr/local/postgres/data And there you go! If you get any errors, please fell free to contact me on (adamitj at gmail dot com). ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] COUNT() with ORDER BY
On 14 jan, 17:02, Andrus [EMAIL PROTECTED] wrote: create temp table test (col char); select count(col) from test order by col; causes ERROR: column test.col must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803 How to fix this ? This statement is generated by DbLinq driver and it is difficult to re-qrite the driver. Query looks quite resonable to me and should be accepted by PostgreSQL 8.3 Beta. Andrus. Give a name to the count column and create a subquery: select * from( select count(col) as count_col fromtest ) subquery_tmp_name_as_you_wish order by count_col; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] backup and restore
On 14 jan, 10:36, hiddenhippo [EMAIL PROTECTED] wrote: Having recently jumped ship from MSSQL to Postgres I'm finding it a little difficult getting onto the 'correct path.' Basically I have a live database and I want to take a copy of that and then restore it onto a backup server. Firstly I'm using Postgres 8.2 and pgAdmin 1.6.3 Within the pgAdmin interface I've selected the database I wish to backup, given it a filename, selected 'Compress' and have chosen blobs and OIDs. From the documentation it appears that if you use foreign keys then you should use OIDs. Anyway, the backup appears to work fine and a file is created. No errors are displayed at any point. When I come to restore the database I fistly create a blank one, and then right click and choose restore. The problem is that errors are thrown because of foreign-key constraints. With a little more investigation I changed the backup output to 'plain' so that I could see the script generated. It appears that the backup process is created tables and their associated data in the wrong order. For example, at the top of the generated file it attempts to insert data into a given table, however the table dictates that some entries must have a corresponding entry in another, for example a user id (a foreign key saying that UID value must exist on the user tables primary key). After detailing the data for the first table it then details the data that should go into the parent table, or in my example above, it then attempts to populate the user table. When you run the restore process this it fails because the user table doesn't have the corresponding entries. Could someone please suggest why this is happening and how, if it can be done, I fix it? Thanks It's quite simple to have problems with pgAdmin's backup procedure. Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could report many errors when did backups and/or restores from it. I suggest you to use pg_dump command line utility into a plain SQL file, so you can compress it with bzip2 to maximum compress rates. When you restore, decompress the file (if you have packed it) and use the psql to load the script file into the new and clean database. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ERROR: shared buffer hash table corrupted
On 14 jan, 09:08, [EMAIL PROTECTED] (Ashish Karalkar) wrote: Hello list members, I hav a table with 140M rows. While I am trying to select the count from the table I am getting following error ERROR: shared buffer hash table corrupted Can anybody please suggest me wht had gone wrong and how to fix it? PostgreSQL 8.2.4 OS:Suse 10.3 With Regards Ashish...Save all your chat conversations.Find them online. I had too many problems with transaction log corruption and table corruption in a linux 2.6 kernel server with bad memory banks.. It does not showed the same error message on shared buffers, but I could fix it by changing the memory banks to ones of same vendor, speed and latency, and after this, I did the following steps (each one in the exactly order): 1- Dropped out every database object that was part of DDL (Views, Indexes, Functions, etc). Of course you'll need the scripts to recreate it later; 2- executed REINDEX DATABASE on each database of cluster; 3- executed a '$ vacuumdb -vfz' against the databases; 4- pg_dumpall into a backup script file of all databases (steps 2 and 3 are only for validation); 5- removed the data path of postgres cluster (PGDATA); 6- recreated a new postgres cluster and restore the pg_dumpall script on it; 7- Re-run the schema definition to create database objects. As you can see, I was tightly lucky for the corruption stay on indexes and other objects. If the table data got corrupted... the story could be another, and you get errors on steps 2 and 3. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] backup and restore
On 15 jan, 12:29, [EMAIL PROTECTED] (Richard Huxton) wrote: It's quite simple to have problems with pgAdmin's backup procedure. Hmm - shouldn't be, and if so then please let the pgAdmin people know! They're always working to improve the package and it's I think the same. We (company I work for) develop an ERP software with more than 200 customers, and each one have a individual and confidential database with PostgreSQL. Using pgAdmin version 1.6.x or lower with those databases causes errors when the database is restored from a .backup file created with compression from pgAdmin. However, to kill the doubt, I realize those errors with pg_restore utility from terminal as well. Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could report many errors when did backups and/or restores from it. Did you keep any notes, and can you reproduce them? In fact, I do not keep notes from those errors because they're always on database schema. The table and its data can be restored very well. So we have a utility written in Visual Foxpro wich can read postgres database schema and do the database diff to upgrade the restored db with the schema needed (PKs, FKs, views, triggers, etc). I will study these cases most extensively in our labs, and will take notes to post them here (once I can prove it's not a pgAdmin problem but pg_dump issue when using compressing - if not proved, I send it to pgAdmin developers.). I suggest you to use pg_dump command line utility into a plain SQL file, so you can compress it with bzip2 to maximum compress rates. When you restore, decompress the file (if you have packed it) and use the psql to load the script file into the new and clean database. You're better off using the compressed mode imnsho. The flexibility is well worth any minor loss vs bzip2's compression. Check out the -l / -L options to let you pick and choose what you restore. The only way I found to dump and restore databases with no errors is using pg_dump creating a plain text file. Compression is a good way, but I prefer create a plain text SQL file and compress it my way after... And maybe bzip2 couldn't be the best pratice, it was just a suggestion ;) P.S: I have to trace exactly these pgAdmin issues. The issues really exists (I'll not post any fake message here if it was not true), but I can't send databases dump to reproduce them according the contract with our customers (confidential data). ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] ERROR: shared buffer hash table corrupted
with extraordinary success in his ministry, in the conversion of many souls. He had five harvests, as he called them. The first was about 57 years ago; the second about 53; the third about 40; the fourth about 24; the fifth and last about 18 years ago. Some of these times were much more remarkable than others, and the ingathering of souls more plentiful. Those about 53, and 40, and 24 years ago, were much greater than either the first or the last: but in each of them, I have heard my grandfather say, the greater part of the young people in the town, seemed to be mainly concerned for their eternal salvation. After the last of these, came a far more degenerate time (at least among the young people), I suppose, than ever before. Mr. Stoddard, indeed, had the comfort, before he died, of seeing a time where there were no small appearances of a divine work among some, and a considerable ingathering of souls, even after I was settled with him in the ministry, which was about two years before his death; and I have reason to bless God for the great advantage I had by it. In these two years there were nearly twenty that Mr. Stoddard hoped to be savingly converted; but there was nothing of any general awakening. The greater part seemed to be at that time very insensible of the things of religion, and engaged in other cares and pursuits. Just after my grandfather's death, it seemed to be a time of extraordinary dullness in religion. Licentiousness for some years prevailed among the youth of the town; there were many of them very much addicted to night-walking, and frequenting the tavern, and lewd practices, wherein some, by their example, exceedingly corrupted others ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] COUNT() with ORDER BY
fervent with love. Princes abandoned their pomp; maidens suffered martyrdom. Whence came this influence? The Messiah was come. These were the effect and sign of His coming. 773. Destruction of the Jews and heathen by Jesus Christ: Omnes gentes venient et adorabunt eum.156 Parum est ut,157 etc. Postula a me.158 Adorabunt eum omnes reges.159 Testes iniqui.160 Dabit maxillam percutienti.161 Dederunt fel in escam.162 774. Jesus Christ for all, Moses for a nation. The Jews blessed in Abraham: I will bless those that bless thee. But: All nations blessed in his seed. Parum est ut,163 etc. Lumen ad revelationem gentium.164 Non fecit taliter omni nationi, said David, in speaking of the Law. But, in speaking of Jesus Christ, we must say: Fecit taliter omni nationi.165 Parum est ut, etc., Isaiah. So it belongs to Jesus Christ to be universal. Even the Church offers sacrifice only for the faithful. Jesus Christ offered that of the cross for all. 775. There is heresy in always explaining omnes by all, and heresy is not explaining it sometimes by all. Bibite ex hoc omnes;166 The Huguenots are heretics in explaining it by all. In quo omnes peccaverunt,167 the Huguenots are heretics in excepting the children of true believers. We must, then, follow the Fathers and tradition in order to know when to do so, since there is heresy to be feared on both sides. 776. Ne timeas pusillus grex.168 Timore et tremore.169--Quid ergo? Ne timeas modo timeas. Fear not, provided you fear; but if you fear not, then fear. Qui me recipit, non me recipit, sed eum qui me misit.170 Nemo scit, neque Filius.171 Nubes lucida obumbravit.172 Saint John was to turn the hearts of the fathers to the children, and Jesus Christ to plant division. There is not contradiction. 777. The effects in communi and in particulari. The semi-Pelagians err in saying of in communi what is true only in particulari; and the Calvinists in saying in particulari what is true in communi. (Such is my o ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] backup and restore
shall be sprinkled on his garments, so as to stain all his raiment. He will not only hate you, but he will have you in the utmost contempt: no place shall be thought fit for you, but under his feet to be trodden down as the mire of the streets. The misery you are exposed to is that which God will inflict to that end, that he might show what that wrath of Jehovah is. God hath had it on his heart to show to angels and men, both how excellent his love is, and also how terrible his wrath is. Sometimes earthly kings have a mind to show how terrible their wrath is, by the extreme punishments they would execute on those that would provoke them. Nebuchadnezzar, that mighty and haughty monarch of the Chaldean empire, was willing to show his wrath when enraged with Shadrach, Meshach, and Abednego; and accordingly gave orders that the burning fiery furnace should be heated seven times hotter than it was before; doubtless, it was raised to the utmost degree of fierceness that human art could raise it. But the great God is also willing to show his wrath, and magnify his awful majesty and mighty power in the extreme sufferings of his enemies. Rom. 9:22. What if God, willing to show his wrath, and to make his power known, endured with much long-suffering the vessels of wrath fitted to destruction? And seeing this is his design, and what he has determined, even to show how terrible the unrestrained wrath, the fury and fierceness of Jehovah is, he will do it to effect. There will be something accomplished and brought to pass that will be dreadful with a witness. When the great and angry God hath risen up and executed his awful vengeance on the poor sinner, and the wretch is actually su ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] backup and restore
the others in their vastness. For who will not be astounded at the fact that our body, which a little while ago was imperceptible in the universe, itself imperceptible in the bosom of the whole, is now a colossus, a world, or rather a whole, in respect of the nothingness which we cannot reach? He who regards himself in this light will be afraid of himself, and observing himself sustained in the body given him by nature between those two abysses of the Infinite and Nothing, will tremble at the sight of these marvels; and I think that, as his curiosity changes into admiration, he will be more disposed to contemplate them in silence than to examine them with presumption. For, in fact, what is man in nature? A Nothing in comparison with the Infinite, an All in comparison with the Nothing, a mean between nothing and everything. Since he is infinitely removed from comprehending the extremes, the end of things and their beginning are hopelessly hidden from him in an impenetrable secret; he is equally incapable of seeing the Nothing from which he was made, and the Infinite in which he is swallowed up. What will he do then, but perceive the appearance of the middle of things, in an eternal despair of knowing either their beginning or their end. All things proceed from the Nothing, and are borne towards the Infinite. Who will follow these marvellous processes? The Author of these wonders understands them. None other can do so. Through failure to contemplate these Infinites, men have rashly rushed into the examination of nature, as though they bore some proportion to her. It is strange that they have wished to understand the beginnings of things, and thence to arrive at the knowledge of the whole, with a presumption as infinite as their object. For surely this design cannot be formed without presumption or without a ca ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] to realise the dark side of Microsoft SQL Server...?
On Dec 19, 4:23 pm, [EMAIL PROTECTED] (Geoffrey) wrote: Joshua D. Drake wrote: Geoffrey wrote: Johan van Zyl wrote: Please elaborate! (Many a true word spoken in jestg) That is until I can convince my new employer to realise the dark side of Microsoft SQL Server. It's the product of an evil company? Let's see: 1. they are virtually a monopoly 2. they use their monopoly to tie folks to their product upgrade merry-go-round. 3. They purchase companies to destroy competition. 4. They generate FUD rather then innovate. 5. The Novell agreement? 6. They coerce computer makers to not only preload their operating system, but coerce them into promoting it. 7. the OOXML debacle? The list continues, but I suspect you get my point. You are assuming most people care. No, I'm trying to educate people. I was answering the question of the previous poster. If people begin to understand the issues, they will begin to care. At least some of them. 1. You have to pay for the the OS. 2. You have to pay for the database. 3. You have to pay for any extra feature. That is where you start. Agreed. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly The point is certification. As the number of softwarehouses grows up, and so on the number of software solutions, companies are seeking from mature and professional solutions. But most of them does not know postgres because it have not a commercial certification. Even here (Brazil), middle-size companies are changing their PostgreSQL databases and migrating to IBM DB2 Community Edition. That's all for certified employees, who can guarantee virtually the security and functioning of host applications. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Timestamp without timezone
I'm using Microsoft Visual Foxpro 9 developing an ERP application, using PostgreSQL 8.2.5 and ODBC connection in version 7. If I upgrade my ODBC drivers to use PostgreSQL ANSI, becomes an error like this: function saldo_estoque(unknown, unknown, unknown, unknown, timestamp without time zone) does not exist However, the unknown types are BPCHAR on function saldo_estoque, and timestamp without time zone is DATE type. How can I solve this? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Timestamp without timezone
On 20 nov, 12:35, [EMAIL PROTECTED] (Reg Me Please) wrote: Il Tuesday 20 November 2007 15:01:53 T.J. Adami ha scritto: I'm using Microsoft Visual Foxpro 9 developing an ERP application, using PostgreSQL 8.2.5 and ODBC connection in version 7. If I upgrade my ODBC drivers to use PostgreSQL ANSI, becomes an error like this: function saldo_estoque(unknown, unknown, unknown, unknown, timestamp without time zone) does not exist However, the unknown types are BPCHAR on function saldo_estoque, and timestamp without time zone is DATE type. How can I solve this? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ It's very likely that you have to do some explici casting on all other paramter types, as the only one the DB has recognised is the last one, aka TIMESTAMPTZ. -- Reg me Please Non quietis maribus nauta ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ It appears to be a VFP handling error, once I use parametrized calls with memvars. This means that VFP will create SQL statement for me translating variables into SQL values (something like PreparedStatement on Java). It's quite simple to make a explicit cast, but due to the size of application, this will be not worth. ODBC driver version 7 is working perfectly, but it's very old and I'm afraid of get some errors. Assuming that PostgreSQL will increase it's versions (now on 8.3 beta 2), there is a need to upgrade the Windows ODBC driver to version 8? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] configure password
On 31 out, 07:33, [EMAIL PROTECTED] (João Paulo Zavanela) wrote: Hi, How I configure password to postgres user in Linux? I'm using trust, but I needing configure password in database. Thanks! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match For database roles, you can change the password by ALTER ROLE command. For example, change the pwd for user postgres to 123 : ALTER ROLE postgres PASSWORD '123'; ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Join between tables of two or more databases
Hi people. I have 2 databases named as follows: 1) company_a 2) company_b These two databases do not have the same ER model. However, I want to access tables on company_a there are in company_b. I want to use them as local tables (as other databases like Sybase allows, since you have opened a database link). The question is: can I do this using remote database servers (different hosts)? If does not, can I do it at least on local databases on the same server? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] strange message from pg_dumpall
On 31 out, 06:56, [EMAIL PROTECTED] (Joao Miguel Ferreira) wrote: Hello all, I got surprised by this message: - The program pg_dump is needed by pg_dumpall but was not found in the same directory as /usr/bin/pg_dumpall. Check your installation. -- It's quite strange because I'm quite the pg_* binaries are well installed: $ ls -la /usr/bin/ | grep pg_ -rwxr-xr-x1 adminroot16632 Oct 16 17:39 pg_config -rwxr-xr-x1 adminroot16636 Oct 16 17:39 pg_controldata -rwxr-xr-x1 adminroot24880 Oct 16 17:39 pg_ctl -rwxr-xr-x1 adminroot 187996 Oct 16 17:39 pg_dump -rwxr-xr-x1 adminroot43444 Oct 16 17:39 pg_dumpall -rwxr-xr-x1 adminroot23020 Oct 16 17:39 pg_resetxlog -rwxr-xr-x1 adminroot84460 Oct 16 17:39 pg_restore $ This is what I do: system(su postgres -c \pg_dumpall --clean some_file\); this is run from within a Perl module called from within a Perl script. I've tried the same command directlly on the shell and it works fine. But from the perl script it complaints about pg_dump !!!??? I've even tested both commands directlly on the cmd line and they work properlly ! any ideas ? thx joao ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Did you try to create a simple batch file and call it with sh? pgdumpall.sh: su -c pg_dumpall --clean some_file postgres And inside your code: system(sh pgdumpall.sh); ... it's one idea... I had some problemas on other OS like windows, when I need to create a batch to call some executables that aren't at the same path of my application. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] getting list of tables from command line
On 31 out, 12:01, [EMAIL PROTECTED] (Craig White) wrote:
I wrote a little script to individually back up table schemas, table
data and then vacuum the table and it works nicely but I wanted a way to
query a database and get a text file with just the table names and
cannot figure out a way to do that.
my script looks like this...
(all I want is to get a list of the tables into a text file pg_tables)
#/bin/sh
#
DB_NAME=whatever
#
for i in `cat pg_tables`
do
pg_dump --username=postgres \
--schema=db
--table=$i \
--schema-only \
$DB_NAME schemas/$i.sql
pg_dump --username=postgres \
--schema=db \
--table=$i \
--data-only \
$DB_NAME data/$i.sql
vacuumdb --username=postgres \
--dbname=$DB_NAME \
--table=db.$i \
--verbose \
--full
done
Is there a way to do that?
Craig
PS there's a lack of cohesion between various commands such as vacuumdb
and pg_dump for things like '--schema'
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
First you can create a file with the SQL statement:
select tablename from pg_tables where schemaname not in
('information_schema','pg_catalog' ) order by tablename;
After, run it by psql:
psql -U [postgres_user] -d [database_name] -f [file_created_with_SQL]
[output_file]
This will dump all non-database schema tables into the output file, so
you can open it and read table names.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Replacing RDBMS
On 31 out, 11:00, [EMAIL PROTECTED] (Phoenix Kiula) wrote: The Amazon Dynamo framework is going to replace RDBMS? http://www.readwriteweb.com/archives/amazon_dynamo.php ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ This is another idea trying to put down RDBMS. I really think SQL and RDBMS still growing up and doing better more and more, specially when we talk about large database files and reports. Don't worry. PostgreSQL still have a long way forward. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Server's Locale
I'm testing PostgreSQL 8.3-beta 1, and still do not fix this message: createdb: database creation failed: ERROR: encoding LATIN1 does not match server's locale Portuguese_Brazil.1252 DETAIL: The server's LC_CTYPE setting requires encoding WIN1252. On PostgreSQL 8.2.5 I'm able to create data dir with initdb and locale Latin1, and after create individual databases with SQL_ANSI or UNICODE. Does it PostgreSQL 8.3 have a problem or its some unnusual set? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
