Re: [GENERAL] Oracle to Postgres migration open source tool
--- On Thu, 7/7/11, Craig Ringer cr...@postnewspapers.com.au wrote: From: Craig Ringer cr...@postnewspapers.com.au Subject: Re: [GENERAL] Oracle to Postgres migration open source tool To: akp geek akpg...@gmail.com Cc: pgsql-general pgsql-general@postgresql.org Date: Thursday, July 7, 2011, 2:02 PM On 7/07/2011 9:55 PM, akp geek wrote: Hi all - Are there any open source tools available for migrating from oracle to postgres. We have 20 tables in oracles that we needed to get to postgres. Appreciate your help One avenue you may wish to investigate is ETL tools like Talend. See Google. EnterpriseDB offer an enhanced version of PostgreSQL with add-on Oracle compatibility features to ease porting. This might be worth looking into. Numerous companies offer consulting services for PostgreSQL, some of which will cover Oracle migrations/conversions. See: http://www.postgresql.org/support/professional_support As for specific oracle to PostgreSQL migration tools: Tried Google yet? -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general i have used ora2pg migrate oracle database to postgresql http://pgfoundry.org/projects/ora2pg
Re: [GENERAL] How to merge data from two separate databases into one (maybe using xlogs)?
--- On Wed, 10/27/10, Vick Khera vi...@khera.org wrote: From: Vick Khera vi...@khera.org Subject: Re: [GENERAL] How to merge data from two separate databases into one (maybe using xlogs)? To: pgsql-general pgsql-general@postgresql.org Date: Wednesday, October 27, 2010, 8:26 PM On Wed, Oct 27, 2010 at 9:58 AM, daniel.cre...@l-3com.com wrote: So, the question would be: How can I do to merge data from DB0 and DB1 and make it available in the new master, whichever is chosen? Any ideas? Perhaps investigate bucardo for replication, as it is supposed to be able to help in situations like this. I think you will have to write some policy so it knows how to resolve conflicting updates unless you don't care which one wins. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general IMHO pgpool is the solution
Re: [GENERAL] Cannot Start Postgres After System Boot
--- On Thu, 10/21/10, Reid Thompson reid.thomp...@ateb.com wrote: From: Reid Thompson reid.thomp...@ateb.com Subject: Re: [GENERAL] Cannot Start Postgres After System Boot To: Rich Shepard rshep...@appl-ecosys.com Cc: pgsql-general@postgresql.org Date: Thursday, October 21, 2010, 4:28 AM On 10/20/2010 6:53 PM, Rich Shepard wrote: For reasons I do not understand, the Slackware start-up file for postgres (/etc/rc.d/rc.postgresql) fails to work properly after I reboot the system. (Reboots normally occur only after a kernel upgrade or with a hardware failure that crashes the system.) Trying to restart the system manually (su postgres -c 'postgres -D /var/lib/pgsql/data ') regardless of the presence of /tmp/.s.PGSQL.5432 and /var/lib/pgsql/postmaster.pid. Here's what I see: [rshep...@salmo ~]$ su postgres -c 'postgres -D /var/lib/pgsql/data ' Password: [rshep...@salmo ~]$ LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets If someone would be kind enough to point out what I'm doing incorrectly (e.g., removing /tmp/.s.PGSQL.5432 and postmaster.pid when the startup process complains they're not right) I'll save this information for the next time. I can also provide the 'start' section of the Slackware init file so I could learn why it's not working properly. TIA, Rich what does $ netstat -an|grep 5432 return? what does $ ps -ef|grep post return? The above indicates that the tcp ipv4 socket is already bound by some process -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Try to delete the files like this .s.PGSQL.5432 .s.PGSQL.5432.lock 8.x-main.pid and restart postmaster
Re: [GENERAL] [9.0] On temporary tables
--- On Thu, 9/30/10, Vincenzo Romano vincenzo.rom...@notorand.it wrote: From: Vincenzo Romano vincenzo.rom...@notorand.it Subject: [GENERAL] [9.0] On temporary tables To: PostgreSQL General pgsql-general@postgresql.org Date: Thursday, September 30, 2010, 11:09 AM Hi all. This is my case: -- begin snippet -- reset search_path; drop table if exists session cascade; create table session ( name text primary key, valu text not null ); create or replace function session_init() returns void language plpgsql as $body$ declare t text; begin select valu into t from session where name='SESSION_ID'; if not found then create temporary table session ( like public.session including all ); insert into session values ( 'SESSION_ID',current_user ); end if; end; $body$; SELECT * from session; SELECT * from session_init(); SELECT * from session; SELECT * from session_init(); -- end snippet -- The output from the last four queries is: -- tmp2=# SELECT * from session; name | valu --+-- (0 rows) tmp2=# SELECT * from session_init(); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index session_pkey for table session CONTEXT: SQL statement create temporary table session ( like public.session including all ) PL/pgSQL function session_init line 6 at istruzione SQL session_init -- (1 row) tmp2=# SELECT * from session; name | valu +-- SESSION_ID | enzo (1 row) tmp2=# SELECT * from session_init(); ERROR: relation session already exists CONTEXT: SQL statement create temporary table session ( like public.session including all ) PL/pgSQL function session_init line 6 at istruzione SQL -- This means that the if not found then in the function body didn't work well. The idea is to create a temporary table to store session variables only of there's no temporary table with that name. Any hint on this? -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general check if the temp_table alredy exist select 1 from pg_class where relname = 'prueba3'
Re: [GENERAL] Ungooglable error message when running initdb: Symbol not found: _check_encoding_conversion_args
- On Wed, 9/2/09, August Lilleaas augustlille...@gmail.com wrote: From: August Lilleaas augustlille...@gmail.com Subject: [GENERAL] Ungooglable error message when running initdb: Symbol not found: _check_encoding_conversion_args To: pgsql-general@postgresql.org Date: Wednesday, September 2, 2009, 7:52 AM Hello there, I'm configuring with `./configure --prefix=/usr/local/Cellar/postgresql/8.4.0`, without sudo. I hawe chowned /usr/local so that I don't need to sudo it. I'm getting the following error when running `initdb` after successfully compiling postgresql Symbol not found: _check_encoding_conversion_args Here's the full output. augu...@honk:~$ initdb -D /usr/local/Cellar/postgresql/8.4.0/defaultdb The files belonging to this database system will be owned by user augustl. This user must also own the server process. The database cluster will be initialized with locales COLLATE: C CTYPE: UTF-8 MESSAGES: C MONETARY: C NUMERIC: C TIME: C The default database encoding has accordingly been set to UTF8. initdb: could not find suitable text search configuration for locale UTF-8 The default text search configuration will be set to simple. creating directory /usr/local/Cellar/postgresql/8.4.0/defaultdb ... ok creating subdirectories ... ok selecting default max_connections ... 20 selecting default shared_buffers ... 2400kB creating configuration files ... ok creating template1 database in /usr/local/Cellar/postgresql/8.4.0/defaultdb/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... FATAL: could not load library /usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so: dlopen(/usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so, 10): Symbol not found: _check_encoding_conversion_args Referenced from: /usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so Expected in: /usr/local/Cellar/postgresql/8.4.0/bin/postgres in /usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so STATEMENT: CREATE OR REPLACE FUNCTION ascii_to_mic (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/ascii_and_mic', 'ascii_to_mic' LANGUAGE C STRICT; child process exited with exit code 1 initdb: removing data directory /usr/local/Cellar/postgresql/8.4.0/defaultdb augu...@honk:~$ I found something on google about the file ascii_and_mic.so not existing, but that's not the case here; the file does indeed exist. Googling _check_encoding_conversion_args doesn't yield any results. My system is OS X 10.6 (Snow Leopard). I'm working with http://ftp9.us.postgresql.org/pub/mirrors/postgresql/source/v8.4.0/postgresql-8.4.0.tar.gz. i dont know the OS x 10 system but you have checked the permissions of ascii_and_mic.so, this file must have the owner and group whit the user creating the cluster
Re: [GENERAL] No buffer space available
--- On Tue, 9/1/09, Narendra Shah narendra.s...@elitecore.com wrote: From: Narendra Shah narendra.s...@elitecore.com Subject: Re: [GENERAL] No buffer space available To: pgsql-general@postgresql.org Date: Tuesday, September 1, 2009, 11:53 AM I am using machine which is exceeding more than 100 connection from my application(iview-syslog server) to postgres. I have updated configuration for postgres in postgres.conf for max connection. But then also it is giving me the error as No buffer space available. And it is happening with only iviewdb named database. Other databases are working well and good. After searching on net i found the limitation is from windows itself. and i have fixed the error with the following registry hack. Regards, Narendra Shah. No buffer space available Fix Editting the registry is not for beginners, if you don't know what you're doing I suggest you don't try this, basically it's use at your own risk. Anytime you want to edit the registry it is a good idea to back it up first. For information on how to backup and restore the registry in all versions of Windows click here. If you are using Windows 95/98/Me follow these steps: First step is to launch the registry editor. To do this go to Start, Run and type regedit. In the left pane navigate to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\VxD\MSTCP once there, look for an entry called MaxConnections. If it exists highlight it by clicking it and then right click it and select modify. Increase the value (recommended value is to double the current value). If the MaxConnections entry does not exist you must create it. To do this, right click in the right pane and choose new from the menu and select String Value. Give it the name MaxConnections. Then right click it and select modify and enter a value of 200. Restart your computer, if all goes well then you fixed the problem, if not, revert the changes by restoring the registry. (You may have to reboot to safe mode to do this). If you are running Windows NT/2000/XP follow these steps: First step is to launch the registry editor. To do this go to Start, Run and type regedit. In the left pane navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters once there, you must create the entry TcpNumConnections. To do this, right click in the right pane and choose new from the menu and select DWORD Value. Give it the name TcpNumConnections. Then right click it and select modify and enter a value of 200. Restart your computer, if all goes well then you fixed the problem, if not, revert the changes by restoring the registry. (You may have to reboot to safe mode to do this). -- View this message in context: http://www.nabble.com/No-buffer-space-available-tp9335358p25238999.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general IMHO you have to use pgpool to handle many connections Lennin Caro Pérez Usuario:GNU/LINUX PHP Developer PostgreSQL DBA Oracle DBA Linux counter id 474393
Re: [GENERAL] simulate multiple primary keys
--- On Thu, 7/2/09, Brandon Metcalf bran...@geronimoalloys.com wrote:
From: Brandon Metcalf bran...@geronimoalloys.com
Subject: [GENERAL] simulate multiple primary keys
To: pgsql-general@postgresql.org
Date: Thursday, July 2, 2009, 5:27 PM
I have the following table:
gms= \d jobclock
Table
public.jobclock
Column |
Type
|
Modifiers
-++
jobclock_id | integer
| not null default
nextval('jobclock_jobclock_id_seq'::regclass)
employee_id | integer
| not null
machine_id | character
varying(4) |
not null
workorder | character
varying(8) |
not null
operation |
integer
| not null
bartype |
character varying(10) |
not null
clockin |
timestamp(0) without time zone | not null
clockout | timestamp(0)
without time zone | default NULL::timestamp without time
zone
comments | character
varying(255) | default
NULL::character varying
Indexes:
jobclock_pkey PRIMARY KEY, btree
(jobclock_id)
...
I need to keep jobclock_id unique and not null, but I also
need to
ensure that no row is duplicated. Is my best bet to
drop the current
primary key and make a primary key out of the columns that
I want to
ensure remain unique from row to row?
Thanks.
--
Brandon
--
It depends of a lot of variables, take two stage:
Stage 1
one employee_id can use some machine_id create you can create a unique key to
employee_id
Stage 2
one employee_id can use one machine_id create you can create a unique key to
employee_id,machine_id
is a example, but i think you have to normalize the table
--
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] Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
--- On Mon, 6/29/09, Tguru g...@talend.com wrote: From: Tguru g...@talend.com Subject: Re: [GENERAL] Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function To: pgsql-general@postgresql.org Date: Monday, June 29, 2009, 1:33 PM To migrate the site, you can use an open source ETL tool. Talend Open Studio is an open source ETL tool for data integration and migration experts. It's easy to learn for a non-technical user. What distinguishes Talend, when it comes to business users, is the tMap component. It allows the user to get a graphical and functional view of integration processes. For more information: http://www.talend.com/ Justin-95 wrote: APseudoUtopia wrote: thread, then logs out (intending to read all the other forum threads at some point in the future when they log in again). If I used a VIEW, it would automatically consider all those unread forum posts to be read when the user logs out. That wouldn't work. What if a user logs in, reads only one forum You are keeping a list of all the forums a user has read, i would not worry about making sure the table tracking user activity has duplicate key values. The select can be limited to return just on row with the highest time stamp then compare this result to figure out what forms the user has not read yet. This eliminates one of problems but creates a problem where table tracking user activity is going bloat but in low traffic times delete the duplicate values. A similar topic was discussed on the performance mailing list, where updates are hung for several seconds for a similar tracking table... http://archives.postgresql.org/pgsql-performance/2009-06/msg00300.php another option is Pentaho, is good and easy too http://kettle.pentaho.org/ -- 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] connecting to a remote pq always require a password
--- On Thu, 6/18/09, David Shen davidshe...@googlemail.com wrote: From: David Shen davidshe...@googlemail.com Subject: [GENERAL] connecting to a remote pq always require a password To: pgsql-general@postgresql.org Date: Thursday, June 18, 2009, 12:11 PM Hi, I am trying to use the libpq to connect to my postgresql 8.3 server. If I use dbname = mydb, the connection made successfully because I am using a socket connection. But if I use host = 127.0.0.1 dbname = mydb, the error message is no password supplied. In the pg_hba.conf file, I even change the host access control to this: host all all 127.0.0.1/32 trust but it still does not work. What I missed? -- Best Regards, David Shen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general waht is the ip number of the host machien and the remote machine to try connect you can pass a password to the string conection host=127.0.0.1 dbname=mydb user=myuser password=mypassword maybe the problem is the net direction 127.0.0.x check this -- 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] Problem setting up PostgreSQL
--- On Wed, 2/25/09, Bill Herbert wgh8...@earthlink.net wrote: From: Bill Herbert wgh8...@earthlink.net Subject: [GENERAL] Problem setting up PostgreSQL To: pgsql-general@postgresql.org pgsql-general@postgresql.org Date: Wednesday, February 25, 2009, 4:30 AM Hello, I am attempting to install PostgreSQL. I downloaded 8.2.12-1zip from ftp9.us.postgresql.org and then followed the installation instructions outlined in http://pginstaller.projects.postgresql.org. I am installing on a Windows XP machine with an NTFS file system. I opened the zip contents from a temporary folder with all internet security functions disabled. When I reached step 11 in the instructions, I received an error message: Internal account look-up failure. No mapping between account names and security ID was done. I retried several times using a new download from a different mirror, but always received the same error message. I searched for and found a prior reference to this message (Sept 04 2008) but ws unable to retrieve the full message and response. Can you offer any suggestions for dealing with this problem? Thanks, Bill Herbert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general check if you have postgres user create in the account domain -- 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] connecting using libpq breaks printf
This is my first attempt at using libpq, and I'm running
across a strange
problem. Here is my bare-bones program:
#include stdio.h
#include libpq-fe.h
int main(int argc, char **argv) {
PGconn *conn;
fprintf(stderr, connecting\n);
conn = PQconnectdb(dbname=postgres);
PQfinish(conn);
return 0;
}
I expected this program to print connecting,
but in fact I get no output
whatsoever. If I comment out the PQconnectdb and PQfinish
lines, I see
connecting as expected. What could be going on
here?
A few notes:
- I'm running PostgreSQL 8.3.6 on Windows XP. I used
the one-click installer
to install.
- I'm compiling the program with MinGW. I get no
compiler warnings or errors.
- I can connect to the database just fine using both
pgAdmin and the
command-line client. The database is running on
localhost.
- I've tried adding code to see if PQstatus(conn) ==
CONNECTION_OK, but this
hasn't been useful. Since fprintf() isn't
working, I can't display a message
showing the result of the comparison.
- I've tried various combinations of connection
options in case there was an
issue with the hostname, database name, username, or
password. I always get
the same result: no output.
- I've tried printing to stdout and to a file, but
neither helped.
Thanks for any help you can provide.
Joey
try
fprintf(stdout,Connection \n);
printf(Connection \n);
--
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] ask: select right(column) ???
I have simple question
I tried following code
select right(column, number_of_character) from table
but it didn't work, saying that pg doesn't have the
function
is there any way to achieve such output?
honestly I have no idea that such simple feature
doesn't exist in postgresql
or am I wrong? since I look at SQL Key Words table and
it's written as
reserved
Thank you
Regards
Hendra
you are right but you can use the substring function, like this
select 'test123',substring('test123' from '...$')
this return '123'
--
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] pg_hba reload
--- On Sat, 2/14/09, Bob Pawley rjpaw...@shaw.ca wrote: From: Bob Pawley rjpaw...@shaw.ca Subject: Re: [GENERAL] pg_hba reload To: John R Pierce pie...@hogranch.com, PostgreSQL pgsql-general@postgresql.org Date: Saturday, February 14, 2009, 11:31 PM I'm running 8.3 on Windows XP. I input pg_ctl reload -D c:\program files\postgresql\8.3\data- or any combination thereof, with or without brackets and the server is running. The return is - 'pg_ctl: too many command-line arguments'. Maybe the problem is te blank space in Program Files test with pg_ctl reload -D c:\program files\postgresql\8.3\data -- 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] inconsistency in aliasing
--- On Wed, 1/14/09, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org Subject: Re: [GENERAL] inconsistency in aliasing To: pgsql-general@postgresql.org Date: Wednesday, January 14, 2009, 11:27 AM On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias -- http://www.critikart.net you can't use operator in the group by, try this select score,score+1 as score2 from ( select current_timestamp::abstime::int4 as score) order by score2 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] alocate table in memory and multui listener
I have two cuestion: 1- postgresql have the method to allocate a table in memory 2- In postgresql can have multiple port to listen thank... -- 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] ERROR
From: Gustavo Rosso [EMAIL PROTECTED] Subject: [GENERAL] ERROR To: pgsql-general@postgresql.org Date: Monday, November 17, 2008, 3:08 PM I create a db wiht user postgres, but other users can't no create tables, and I give all privileges. (banco is my db) grant all on database banco to public; grant create on database banco to public; This is the error: *ERROR: must be owner of relation (table)* Help me!!! alter table table_name owner to public the public rol must exist -- 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] INSERT with RETURNING clause inside SQL function
Hi all, I'm re-writing some functions and migrating bussines logic from a client application to PostgreSQL. I expected something like this to work, but it doesn't: -- simple table CREATE TABLE sometable ( id SERIAL PRIMARY KEY, text1 text, text2 text ); CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS INTEGER AS $$ INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 ) RETURNING id ; $$ LANGUAGE SQL ; Please note the use of RETURNING clause. If I put a SELECT 1; after the INSERT, the function works (but doesn't returns any useful value :) I need the function to return the last insert id. And yes, I'm aware that the same can be achieved by selecting the greatest id in the SERIAL secuence, but is not as readable as RETURNING syntax. And no, for me it's not important that RETURNING is not standard SQL. Does anyone knows why RETURNING doesn't works inside SQL functions? Any advise will be very appreciated. TIA. diego Hi.. what version of postgres you have? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: re[GENERAL] moving a portion of text
--- On Mon, 10/20/08, pere roca [EMAIL PROTECTED] wrote: From: pere roca [EMAIL PROTECTED] Subject: re[GENERAL] moving a portion of text To: pgsql-general@postgresql.org Date: Monday, October 20, 2008, 11:21 AM hi, I have a column with full of data like ATB-OO NCK-TT how can I easily remove the - ? it seems that the - is allways the fourth letter. thanks, Pere -- View this message in context: http://www.nabble.com/removing-a-portion-of-text-tp20067248p20067248.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. with fixed length select substring(tex1,1,3) || substring(tex1,5) from t1 with variable length select substring(tex1,1,strpos(tex1,'-'::varchar)-1) || substring(tex1,strpos(tex1,'-'::varchar)+1) from t1 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- 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] Counting unique rows as an aggregate.
--- On Tue, 9/30/08, r_musta [EMAIL PROTECTED] wrote: From: r_musta [EMAIL PROTECTED] Subject: Re: [GENERAL] Counting unique rows as an aggregate. To: pgsql-general@postgresql.org Date: Tuesday, September 30, 2008, 6:55 AM On Sep 30, 2:36 am, [EMAIL PROTECTED] (Tom Lane) wrote: SELECT count_unique(make), count_unique(color) from table WHERE criteria; I must be missing something, because I don't see why you couldn't do SELECT count(distinct make), count(distinct color) from table WHERE criteria; I didn't explain well, I want the count of each distinct value in a column, eg, if the color column has 50 rows, 20x'red', 10x'green', 20x'blue' - it will give me those results. SELECT count(distinct color) would return 3 - which is the count of distinct values, which is not what I want. SELECT count(color),color from table group by color -- 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] Error in ALTER DATABASE command
--- On Tue, 9/23/08, William Garrison [EMAIL PROTECTED] wrote: From: William Garrison [EMAIL PROTECTED] Subject: [GENERAL] Error in ALTER DATABASE command To: Postgres General List pgsql-general@postgresql.org Date: Tuesday, September 23, 2008, 3:49 PM In Postgresql 8.2.9 on Windows, you cannot rename a database if the name contains mixed case. To replicate: 1) Open the pgadmin tool. 2) Create a database named MixedCase (using the UI, not using a query window or using PSQL) 3) Open a query window, or use PSQL to issue the following command ALTER DATABASE MixedCase RENAME TO anything_else; PostgreSQL will respond with: ERROR: database mixedcase does not exist SQL state: 3D000 This does not happen if you create the database using a manual query in pgadmin, or if you use psql. Both of those tools will create the database as mixedcase instead of MixedCase I am using: PostgreSQL 8.2.9 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) I guess for now, I have to dump and reload my database. :( Postgresql seems to force many things to lower case. Is it a bug that the admin tool lets you create a database with mixed case names? Or is it a bug that you cannot rename them thereafter? error i dont think so, teh pgadmin create the object whit the double quote () implicit. Rename the database ALTER DATABASE MixedCase RENAME TO mixedcase -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.
--- On Fri, 9/19/08, Yi Zhao [EMAIL PROTECTED] wrote: From: Yi Zhao [EMAIL PROTECTED] Subject: [GENERAL] how to return the first record from the sorted records which may have duplicated value. To: pgsql-general pgsql-general@postgresql.org Date: Friday, September 19, 2008, 8:51 AM hi all: I have a table with columns(2) named query, pop, dfk. what I want is: when I do some select, if the column query in result records have duplicate value, I only want the record which have the maximum value of the pop. for example, the content of table: query pop dfk --- abc30 1 --max foo 20 lk --max def 16 kj --max foo 15 fk --discard abc 10 2 --discard bar 8are --max the result should be: query pop dfk --- abc30 1 foo 20 lk def 16 kj bar 8are now, I do it like this(plpgsql) declare hq := ''::hstore; begin for rc in execute 'select * from test order by pop desc' loop if not defined(hq, rc.query) then hq := hq || (rc.query = '1')::hstore; return next rc; end if; end loop; --- language sql/plpgsql will be ok. ps: I try to use group by or max function, because of the multi-columns(more than 2), I failed. thanks, any answer is appreciated. regards, this query work for me select distinct max(pop),query from test group by query please reply your results thanks... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 3 postgres processes
--- On Tue, 9/9/08, Markova, Nina [EMAIL PROTECTED] wrote: From: Markova, Nina [EMAIL PROTECTED] Subject: [GENERAL] 3 postgres processes To: pgsql-general@postgresql.org Date: Tuesday, September 9, 2008, 2:50 PM Hi, After issuning initdb and starting the postgres server, I checked for processes running, expecting to see just one: postgres 4926 4924 0 14:44:52 ? 0:00 /usr/postgres/8.2/bin/postgres postgres 4924 1 0 14:44:52 pts/1 0:00 /usr/postgres/8.2/bin/postgres postgres 4929 4850 0 14:44:56 pts/1 0:00 grep postgres postgres 4928 4850 0 14:44:56 pts/1 0:00 ps -ef postgres 4927 4924 0 14:44:52 ? 0:00 /usr/postgres/8.2/bin/postgres Only one of them is in postmaster.pid. What the other ones are for? more /pg_data/postmaster.pid 4924 /pg_data 543200131 Thanks, use ps auxw to see more details of the process i think the processes are one of this writer process wal writer process autovacuum launcher process stats collector process -- 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] Postgres does not start, gives no error
--- On Wed, 9/3/08, Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: From: Akhtar Yasmin-B05532 [EMAIL PROTECTED] Subject: Re: [GENERAL] Postgres does not start, gives no error To: Tom Lane [EMAIL PROTECTED] Cc: Joshua Drake [EMAIL PROTECTED], pgsql-general@postgresql.org Date: Wednesday, September 3, 2008, 11:22 PM Hi, Thanks for the prompt reply, I tried postmaster -D /home/data/www/pg7/data, but the error message still doesn't appear. Nothing really happens after this command. Is there a way I can find where the errors are logging..? Thanks n regards check the log of the OS and the permissions of the directory /home/data/www/pg7/data -Original Message- You might try invoking the postmaster manually: postmaster -D whateveritwas which should let the error message come out on your terminal. regards, tom lane PS: please don't top post. -- 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
Re: [GENERAL] Postgres does not start, gives no error
--- On Wed, 9/3/08, Akhtar Yasmin-B05532 [EMAIL PROTECTED] wrote: From: Akhtar Yasmin-B05532 [EMAIL PROTECTED] Subject: [GENERAL] Postgres does not start, gives no error To: pgsql-general@postgresql.org Date: Wednesday, September 3, 2008, 7:11 PM HI, I am facing this peciliar problem.I am using postgres 7.2.2 installed on solaris. It has been running very well since all the time, until somebody tried to stop it. Using the command Now it does not start. On giving the start command its says: /home/data/www/pg7/bin/pg_ctl: 5432: not found postmaster successfully started But when i check the status, it says postmaster is not running. Also, if I try to stop it, i get the expected error: pg_ctl: cannot find /home/data/www/pg7/data/postmaster.pid Is postmaster running? exist the file postmaster.pid in your directory? I have realised that when I give the start command, even though the msg is successfully started, the postmaster.pid file is not created.. Am i missing something.? Any help will be appreciated. -- 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] MySQL LAST_INSERT_ID() to Postgres
--- On Thu, 8/28/08, Masis, Alexander (US SSA) [EMAIL PROTECTED] wrote:
From: Masis, Alexander (US SSA) [EMAIL PROTECTED]
Subject: [GENERAL] MySQL LAST_INSERT_ID() to Postgres
To: pgsql-general@postgresql.org
Date: Thursday, August 28, 2008, 4:14 PM
I was mapping C++ application code that works with mySQL to
work with
Postgres.
There were a number of articles on line regarding the
conversion from
mySQL to Postgres like:
http://en.wikibooks.org/wiki/Programming:Converting_MySQL_to_PostgreSQL
http://groups.drupal.org/node/4680
http://jmz.iki.fi/blog/programming/converting_mysql_database_to_postgres
ql
http://www.raditha.com/blog/archives/000488.html
However, I found the most difficult issue was related to a
MySQL's
SELECT LAST_INSERT_ID() sql call.
If your code did not use LAST_INSERT_ID(), then you
don't have to read
this post.
In MySQL LAST_INSERT_ID() is a MySQL's
syntax that returns the
last auto_increment type ID of the row(record) inserted in
a table.
In other words, if your MySQL table had a auto_increment
datatype for a field, that field will automatically advance
whenever a
new record(row) is inserted into that table.
It is sometimes handy to know what is the value of that
ID, that
has just been added to the table, so that that record(row)
can be
addressed/updated later.
use insert into.returning val1,val2.
http://www.postgresql.org/docs/8.3/static/sql-insert.html
this can return the value of the sequence of the table
Well, in MySQL it's easy you just do:
SELECT LAST_INSERT_ID();
In Postgres, however it is not that simple. You have to
know the
name of so called insert sequence. Postgres has
a system function for
that( SQL line below ).
In Postgres you will have to provide the table and column
name(
auto_increment type in MySQL or serial or
bigserial in Postgres).
Here is that SQL query that returns the last inserted ID:
SELECT CURRVAL(
pg_get_serial_sequence('my_tbl_name','id_col_name'));
Alexander Masis.
--
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
Re: [GENERAL] PITR - base backup question
--- On Tue, 8/26/08, Richard Broersma [EMAIL PROTECTED] wrote: From: Richard Broersma [EMAIL PROTECTED] Subject: [GENERAL] PITR - base backup question To: pgsql-general@postgresql.org pgsql-general@postgresql.org, [EMAIL PROTECTED] Date: Tuesday, August 26, 2008, 10:53 PM From the following link: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP Step 3 says to perform the back up. Does this mean a File System Backup of the Data directory? OR Does this mean performing a pg_dumpall and backing up the dump file? is a file system backup of he data directory -- Regards, Richard Broersma Jr. -- 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] schema name in SQL statement.
--- On Tue, 8/19/08, Masis, Alexander (US SSA) [EMAIL PROTECTED] wrote: From: Masis, Alexander (US SSA) [EMAIL PROTECTED] Subject: [GENERAL] schema name in SQL statement. To: pgsql-general@postgresql.org Date: Tuesday, August 19, 2008, 10:52 PM I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: SELECT col FROM schema.table I like to be able to use generic SQL statement like: SELECT col FROM table -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general you can change the search path of the schema SET search_path TO myschema -- 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] automatic REINDEX-ing
you can use a cron job --- On Tue, 8/12/08, Joao Ferreira gmail [EMAIL PROTECTED] wrote: From: Joao Ferreira gmail [EMAIL PROTECTED] Subject: [GENERAL] automatic REINDEX-ing To: pgsql-general pgsql-general@postgresql.org Date: Tuesday, August 12, 2008, 3:13 PM Hello all [[[ while dealing with a disk size problem I realised my REINDEX cron script was not really being called every week :( so... ]]] I executed REINDEX by hand and the disk ocupation imediatelly dropped 6 Giga...!!! is there a way to configure postgres to automatically execute the needed REINDEXING (on indexes and tables) for a given database something similar to auto-vacuum... I guess thx joao -- 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
Re: [GENERAL] ALTER ROLE role-name-with-hyphen
use the double quotes () ALTER USER foo-bar WITH ENCRYPTED PASSWORD 'foo-bar' --- On Fri, 8/8/08, Tom Copeland [EMAIL PROTECTED] wrote: From: Tom Copeland [EMAIL PROTECTED] Subject: [GENERAL] ALTER ROLE role-name-with-hyphen To: pgsql-general@postgresql.org Date: Friday, August 8, 2008, 8:49 PM Hi all - This is probably a new bee question... but, how do I change the password of a role that has a hyphen in the name? == $ createuser --no-superuser --createdb --no-createrole foo-bar $ psql Welcome to psql 8.3.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# ALTER USER foo-bar WITH ENCRYPTED PASSWORD 'foo-bar'; ERROR: syntax error at or near - LINE 1: ALTER USER foo-bar WITH ENCRYPTED PASSWORD 'foo-bar'; ^ == So I can create the role but I can't figure out how to modify it. I've tried a variety of quoting and backslashing and such, but no luck yet any ideas? Thanks, Tom -- 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
Re: [GENERAL] Create Table Dinamic
try whit this http://www.postgresql.org/docs/8.3/interactive/ecpg-dynamic.html --- On Thu, 8/7/08, Anderson dos Santos Donda [EMAIL PROTECTED] wrote: From: Anderson dos Santos Donda [EMAIL PROTECTED] Subject: [GENERAL] Create Table Dinamic To: pgsql-general@postgresql.org Date: Thursday, August 7, 2008, 1:10 AM Hello All! Its my first time here in maillist and I started work with postgre on last moth. My questions is: Threre is a way to create tables dinamic? Example: To create a table we use CREATE TABLE TableName .. In my db, I have many tables with diferents names but with same colums Example: TableOne ( id int, name text ); TableTwo ( id int, name text ); TableThree ( id int, name text ); So, I created a function to create me this tables with diferents names CREATE OR REPLACE FUNCTION MakeTables ( NameTable text ) RETURNS VOID $$ BEGIN CREATE TABLE NameTable ( id int, name text ); END; $$ LANGUAGE 'plpgsql'; But, the plpgsql or postgre don't accept this.. So, How can I create a table with my function? Thanks for any helps!!! PS : If somebody want knows why I need to create this function, is because in my db have 1000 tables with the some colums, and each time I have a new client, I need to create this tables manually. -- 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] recovery via base + WAL replay failure
what error show the log file?
--- On Mon, 8/4/08, Greg Smith [EMAIL PROTECTED] wrote:
From: Greg Smith [EMAIL PROTECTED]
Subject: Re: [GENERAL] recovery via base + WAL replay failure
To: Rob Adams [EMAIL PROTECTED]
Cc: postgres general pgsql-general@postgresql.org
Date: Monday, August 4, 2008, 5:58 PM
On Sun, 3 Aug 2008, Rob Adams wrote:
I made a base backup while the postgres was running
using the following batch
file:
psql -d test_database -U user_name -c SELECT
pg_start_backup('test');
What did you have archive_command set to? That needs to
dump the WAL
files generated while the backup is going on somewhere that
gets copied
over after the main copy is done, and you need the last of
them referenced
by the backup copied over before you can use that backup.
Steps (1) and
(5) of
http://www.postgresql.org/docs/current/static/continuous-archiving.html
are the hard parts here and I don't see that you're
addressing them so
far, and that will keep the copy from starting if all the
files aren't
there.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com
Baltimore, MD
--
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
Re: [GENERAL] eliminating records not in (select id ... so SLOW?
ok try this delete from catalog_categoryitem where not exists (select id from catalog_items where catalog_items.ItemID = catalog_categoryitem.ItemID); --- On Thu, 7/31/08, Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote: From: Ivan Sergio Borgonovo [EMAIL PROTECTED] Subject: Re: [GENERAL] eliminating records not in (select id ... so SLOW? To: Cc: PostgreSQL pgsql-general@postgresql.org Date: Thursday, July 31, 2008, 11:01 PM On Thu, 31 Jul 2008 14:59:29 -0700 (PDT) Lennin Caro [EMAIL PROTECTED] wrote: The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram. Default debian etch setup. you recently run vacuum ? The tables are pretty stable. I think no more than 20 records were modified (update/insert/delete) during the whole history of the 2 tables. autovacuum is running regularly. The actual query running is: begin; create index catalog_categoryitem_ItemsID_index on catalog_categoryitem using btree (ItemID); delete from catalog_categoryitem where ItemID not in (select ItemID from catalog_items); commit; That's what came back Timing is on. BEGIN Time: 0.198 ms CREATE INDEX Time: 3987.991 ms The query is still running... As a reminder catalog_categoryitem should contain less than 1M record. catalog_items should contain a bit more than 600K record where ItemID is unique (a pk actually). PostgreSQL comes from the default install from Debian etch (8.1.X). It's configuration hasn't been modified. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] CAST(integer_field AS character) truncates trailing zeros
--- On Thu, 7/31/08, Warren Bell [EMAIL PROTECTED] wrote:
From: Warren Bell [EMAIL PROTECTED]
Subject: [GENERAL] CAST(integer_field AS character) truncates trailing zeros
To: pgsql-general@postgresql.org
Date: Thursday, July 31, 2008, 8:03 PM
I am trying to cast an int to a character. The int is the
number 1000 it
gets cast down to 1 and not 1000.
How do I cast from int to
character without loosing the trailing zeros?
--
Thanks,
Warren Bell
work to me
template1=# select cast('1000' as varchar);
varchar
-
1000
(1 fila)
template1=#
--
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
Re: [GENERAL] eliminating records not in (select id ... so SLOW?
--- On Thu, 7/31/08, Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote: From: Ivan Sergio Borgonovo [EMAIL PROTECTED] Subject: [GENERAL] eliminating records not in (select id ... so SLOW? To: PostgreSQL pgsql-general@postgresql.org Date: Thursday, July 31, 2008, 9:45 PM I'm doing something like: delete from table1 where id not in (select id from table2). both id are indexed. table1 contains ~1M record table2 contains ~ 600K record and id is unique. The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram. Default debian etch setup. It has been working for over 2h now. Is it normal? -- Ivan Sergio Borgonovo http://www.webthatworks.it you recently run vacuum ? -- 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
Re: [GENERAL] Clone a database to other machine
you can use slony-i http://slony.info/ --- On Mon, 7/28/08, Garg, Manjit [EMAIL PROTECTED] wrote: From: Garg, Manjit [EMAIL PROTECTED] Subject: [GENERAL] Clone a database to other machine To: pgsql-general@postgresql.org Date: Monday, July 28, 2008, 4:44 PM Hi All, I'm stuck to an issue while cloning the pgsql database, can you please help, or give any docs to help out. Query - Trying to have same database on two seprate linux servers. One will be used to upport Applications and other will be used for Report generation only. Want to keep both the database in Sync, hourly or nightly. Kindly help to achive the same. Thanks and regards, Manjit Garg Corbus Global Support Team INDIA -- Email: [EMAIL PROTECTED] Phone: +91-120-304-4000, Ext 252 Fax : +91-120-256-7040 Mob : 9810679256 -- CONFIDENTIALITY NOTICE: This message, including any attachments hereto, (collectively the Email Message) is intended solely for the personal and confidential use of the designated recipient(s) and may contain privileged, proprietary, or otherwise private information which may be subject to attorney-client privilege or may constitute inside information protected by law. If the reader of this message is not the intended recipient, you are hereby notified of the following: (i) Any disclosure, printing, copying, or distribution of this Email Message by you or (ii) the taking of any action by you based on the contents of this Email Message or (iii) any other use of this Email Message by you, are strictly prohibited. If you have received this message in error, please notify the sender immediately and remove all traces of the electronic mail message and its attachments from your system. -- 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] Initdb problem on debian mips cobalt: Bus error
--- On Fri, 7/18/08, Glyn Astill [EMAIL PROTECTED] wrote: From: Glyn Astill [EMAIL PROTECTED] Subject: [GENERAL] Initdb problem on debian mips cobalt: Bus error To: pgsql-general@postgresql.org Date: Friday, July 18, 2008, 10:26 AM Hi Chaps, I'm attempting to run 8.3.3 on an old cobalt qube, with debian etch. It appeared to compile ok (however I didn't stick around to watch, that'd be painfull) and said PostgreSQL compiled successfully and ready to install or whatever, but when I run make check, fails in initdb. Here is the message and the initdb log file contents showing bus error http://privatepaste.com/47jTTGw5XC the user ho execute the command initdb have owner to the directory where the cluster was create check the owner from the directory and permission I've configured it as I usually do ./configure --with-perl --with-python --with-tcl --with-openssl --with-pam --with-krb5. Of course running on a more obscure machine I don't expect too much to be honest, but does anyone have any ideas? Perhaps the kernel is missing support for something? Thanks Glyn __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- 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
Re: [GENERAL] SPACE FOR POSTGRESQL DATABASE
in this link have information about database size http://www.postgresql.org/docs/8.1/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE --- On Thu, 7/10/08, aravind chandu [EMAIL PROTECTED] wrote: From: aravind chandu [EMAIL PROTECTED] Subject: [GENERAL] SPACE FOR POSTGRESQL DATABASE To: postgresql Forums pgsql-general@postgresql.org Date: Thursday, July 10, 2008, 2:18 PM Hello, Can you please how much space does postgresql database occupies? Thank You, Aviansh -- 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] roll back to 8.1 for PyQt driver work-around
sound like you have a postmaster.pid in your PGDATA directory. Rename file postmaster.pid and launch the postgresql again --- On Sat, 7/5/08, Scott Frankel [EMAIL PROTECTED] wrote: From: Scott Frankel [EMAIL PROTECTED] Subject: [GENERAL] roll back to 8.1 for PyQt driver work-around To: PostgreSQL List pgsql-general@postgresql.org Date: Saturday, July 5, 2008, 11:40 PM Hello, I just signed on the list. Any suggestions for how best to launch one of two different versions of pg installed on the same machine? I have both 8.3 and 8.1 installed on a MacBookPro (OS X 10.5.2). I stopped the 8.3 postmaster using pg_ctl in order to roll back to 8.1. Problem is, now I can't seem to start the server using either version. When I launch 8.1 with pg_ctl, it yields a postmaster starting message; but then a status check shows that the server is not running. Issuing the same commands for 8.3, I get similar results. eg: [tiento:~] postgres% /opt/local/lib/postgresql81/bin/pg_ctl start -D / Library/PostgreSQL8/data -l /Users/Shared/pgLog/pgLog.txt postmaster starting [tiento:~] postgres% /opt/local/lib/postgresql81/bin/pg_ctl status -D / Library/PostgreSQL8/data pg_ctl: neither postmaster nor postgres running I'm trying to roll back to version 8.1 as I've run into a bug in Qt's QPSQL driver. I'm able to create tables and add rows of data to them; but my model.select() statements all fail. The This version of PostgreSQL is not supported and may not work message is ominous ;) Thanks in advance! Scott Scott Frankel President/VFX Supervisor Circle-S Studios 510-339-7477 (o) 510-332-2990 (c) -- 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
Re: [GENERAL] To store and retrive image data in postgresql
for hot_backup and restore check this http://www.postgresql.org/docs/8.3/static/continuous-archiving.html for logic backup (dump) use this http://www.postgresql.org/docs/8.3/static/app-pgdump.html --- On Mon, 7/7/08, aravind chandu [EMAIL PROTECTED] wrote: From: aravind chandu [EMAIL PROTECTED] Subject: [GENERAL] To store and retrive image data in postgresql To: pgsql-general@postgresql.org Date: Monday, July 7, 2008, 7:22 PM Hello, I need to store an image in postgresql database and after that i need to retrive the image back.Can you please help me how to do this? Thank You, Avinash. -- 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] Delete from Join
--- On Wed, 7/2/08, Gwyneth Morrison [EMAIL PROTECTED] wrote: From: Gwyneth Morrison [EMAIL PROTECTED] Subject: Re: [GENERAL] Delete from Join To: pgsql-general@postgresql.org Date: Wednesday, July 2, 2008, 7:12 PM --- On Wed, 7/2/08, Gwyneth Morrison [EMAIL PROTECTED] wrote: From: Gwyneth Morrison [EMAIL PROTECTED] Subject: [GENERAL] Delete from Join To: pgsql-general@postgresql.org Date: Wednesday, July 2, 2008, 3:15 PM Hello, Is it possible to use a join keyword in a delete? For example: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; It is not directly mentioned in the delete syntax but the delete refers to the select clause where JOIN is valid. G i have a example delete from t1 a using t2 b where a.id = b.oid A standard way to do it is delete from t1 a where id in (select a.id from t1 a inner join t2 b on (a.id = b.oid)) Thank you for your reply, You are absolutely correct, it IS the standard way. What I am actually trying to do here is write a program to convert MS SQL to Postgres. I have had quite a bit of success so far, but this is a sticking point. Apparently using the JOIN keyword directly in a delete statement is valid in MS. I am trying to determine if it is valid in postgres which I figure it is not but cannot find it exactly in the documentation. So I guess the real question is, can the JOIN keyword be used directly in a delete as above. G i have the same problem. i try use JOIN keyword in DELETE syntax but dont work. I assume cant use JOIN keywork -- 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] pg_ctl start check sum failed
use... ps auxw | grep postgres --- On Wed, 7/2/08, Fernando Dominguez [EMAIL PROTECTED] wrote: From: Fernando Dominguez [EMAIL PROTECTED] Subject: [GENERAL] pg_ctl start check sum failed To: pgsql-general@postgresql.org Date: Wednesday, July 2, 2008, 11:30 PM Hello, I try to use an old cluster into a new system. The new system comes with a newer version of postgres so I uninstalled it and I installed the same version that I had in the older system ---8.1 I got impressed when I Installed the 8.1 with dpkg -i and it started to run without starting the daemon... Is it possible to know what directory is the server using to store the data? --- main question Once I have installed the server I try to start it using pg_control start -D /oldCluster directory but I get FATAL checksum incorrect. I want to use the old data, any ideas? Many thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete from Join
--- On Wed, 7/2/08, Gwyneth Morrison [EMAIL PROTECTED] wrote: From: Gwyneth Morrison [EMAIL PROTECTED] Subject: [GENERAL] Delete from Join To: pgsql-general@postgresql.org Date: Wednesday, July 2, 2008, 3:15 PM Hello, Is it possible to use a join keyword in a delete? For example: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; It is not directly mentioned in the delete syntax but the delete refers to the select clause where JOIN is valid. G i have a example delete from t1 a using t2 b where a.id = b.oid A standard way to do it is delete from t1 a where id in (select a.id from t1 a inner join t2 b on (a.id = b.oid)) -- 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] Query
see trigger and estatus select tgname,tgenabled from pg_catalog.pg_trigger --- On Mon, 6/30/08, kartik [EMAIL PROTECTED] wrote: From: kartik [EMAIL PROTECTED] Subject: [GENERAL] Query To: pgsql-general@postgresql.org Date: Monday, June 30, 2008, 9:55 AM Hello , I am a beginner for postgresql. I want to activate a constraint for some time and after that I want to deactivate it. Or I want to know whether a particular constraint exists and whether its activated or not. Waiting for yr reply
Re: [GENERAL] ERROR: concurrent insert in progress
use REINDEX http://www.postgresql.org/docs/8.3/static/sql-reindex.html tell us the result thank --- On Fri, 6/27/08, Ganbold [EMAIL PROTECTED] wrote: From: Ganbold [EMAIL PROTECTED] Subject: [GENERAL] ERROR: concurrent insert in progress To: pgsql-general@postgresql.org Date: Friday, June 27, 2008, 3:04 AM Hi, I have problem with my DB: snort=# vacuum full; WARNING: index ip_src_idx contains 1921678 row versions, but table contains 1921693 row versions HINT: Rebuild the index with REINDEX. WARNING: index ip_dst_idx contains 1921668 row versions, but table contains 1921693 row versions HINT: Rebuild the index with REINDEX. ERROR: could not read block 988 of relation 1663/16384/16472: Input/output error snort=# reindex index ip_src_idx; ERROR: concurrent insert in progress How to solve this problem? Is there any other method fixing the index? thanks in advance, Ganbold -- 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] ERROR: concurrent insert in progress
use REINDEX REINDEX ip_dst_idx OR REINDEX table_name http://www.postgresql.org/docs/8.3/static/sql-reindex.html tell us the result thank --- On Fri, 6/27/08, Ganbold [EMAIL PROTECTED] wrote: From: Ganbold [EMAIL PROTECTED] Subject: [GENERAL] ERROR: concurrent insert in progress To: pgsql-general@postgresql.org Date: Friday, June 27, 2008, 3:04 AM Hi, I have problem with my DB: snort=# vacuum full; WARNING: index ip_src_idx contains 1921678 row versions, but table contains 1921693 row versions HINT: Rebuild the index with REINDEX. WARNING: index ip_dst_idx contains 1921668 row versions, but table contains 1921693 row versions HINT: Rebuild the index with REINDEX. ERROR: could not read block 988 of relation 1663/16384/16472: Input/output error snort=# reindex index ip_src_idx; ERROR: concurrent insert in progress How to solve this problem? Is there any other method fixing the index? thanks in advance, Ganbold -- 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] Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks
use a dynamic select in the web page $1 = 10 $2 = 5 select * from mytable limit $1 OFFSET $2 --- On Fri, 6/27/08, Bill Thoen [EMAIL PROTECTED] wrote: From: Bill Thoen [EMAIL PROTECTED] Subject: [GENERAL] Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks To: pgsql-general@postgresql.org Date: Friday, June 27, 2008, 8:14 PM What I want to do is present the results of a query in a web page, but only 10 rows at a time. My PostgreSQL table has millions of records and if I don't add a LIMIT 10 to the SQL selection, the request can take too long. The worst case scenario is when the user requests all records without adding filtering conditions (e.g. SELECT * FROM MyTable;) That can take 10-15 minutes, which won't work on a web application. What I'm wondering is how in PostgreSQL do you select only the first 10 records from a selection, then the next 10, then the next, and possibly go back to a previous 10? Or do you do the full selection into a temporary table once, adding a row number to the columns and then performing sub-selects on that temporary table using the row id? Or do you run the query with Limit 10 set and then run another copy with no limit into a temporary table while you let the user gaze thoughtfully at the first ten records? I know how to get records form the database into a web page, and I know how to sense user actions (PageDown, PageUp, etc.) so I'm basically looking for techniques to extract the data quickly. Also, if this isn't the best forum to ask this sort of question, I'd appreciate being pointed to a more appropriate one. TIA, - Bill Thoen -- 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] Partial Index Too Literal?
use this explain analyze select * from result where active = 't'; --- On Thu, 6/26/08, Phillip Mills [EMAIL PROTECTED] wrote: From: Phillip Mills [EMAIL PROTECTED] Subject: [GENERAL] Partial Index Too Literal? To: pgsql-general@postgresql.org Date: Thursday, June 26, 2008, 7:24 PM Under somewhat unusual circumstances, rows in one of our tables have an 'active' flag with a true value. We check for these relatively often since they represent cases that need special handling. We've found through testing that having a partial index on that field works well. What seems odd to me, however, is that the index gets used only if the query is a textual match for how the index was specified. That is, using an index defined as 'where active = true': dev=# explain analyze select * from result where active = true; QUERY PLAN - Bitmap Heap Scan on result (cost=5.31..472.34 rows=4206 width=1126) (actual time=7.868..7.868 rows=0 loops=1) Filter: active - Bitmap Index Scan on result_active_idx (cost=0.00..4.26 rows=2103 width=0) (actual time=4.138..4.138 rows=16625 loops=1) Index Cond: (active = true) Total runtime: 7.918 ms (5 rows) dev=# explain analyze select * from result where active is true; QUERY PLAN -- Seq Scan on result (cost=0.00..537.26 rows=4263 width=1126) (actual time=55.631..55.631 rows=0 loops=1) Filter: (active IS TRUE) Total runtime: 55.668 ms (3 rows) This is version 8.2.6. Is there something I'm missing that could make these queries ever produce different results?
Re: [GENERAL] Undocumented Postgres error: failed to fetch old tuple for AFTER trigger
the table o tables have triggers? try to use COMMIT --- On Thu, 6/26/08, Robert James [EMAIL PROTECTED] wrote: From: Robert James [EMAIL PROTECTED] Subject: [GENERAL] Undocumented Postgres error: failed to fetch old tuple for AFTER trigger To: Postgres General pgsql-general@postgresql.org Date: Thursday, June 26, 2008, 8:28 PM I'm running a very large series of commands - mainly DDL but some DML as well - in a large transaction. I get the following error, which doesn't seem to be documented: ERROR: failed to fetch old tuple for AFTER trigger : COMMIT There are no triggers that I'm aware of. I've gotten this error when running the transaction on two different instances of the database. I also did a VACUUM FULL, which didn't help. Breaking down the commands into a series of about 7 smaller transactions works fine. Each transaction runs totally fine by itself. But, when I run them all in one giant transaction, I get errors. I'm running Postgres 8.2.1 on Windows XP. If anyone can help, I'd be very greatful.
