Re: [GENERAL] serial column
On Sunday 24 September 2006 02:29 pm, Bob Pawley wrote: Choice a. I am using the numbers to identify devices. If a device is deleted or replaced with another type of device I want the numbering to still be sequential. Bob Do you have some other way of tracking a device? I am just trying to figure out how you know which device number 2 (as an example) you are looking at. I am assuming these devices exist as actual entities. So are these numbers applied to the actual device and if so are you going to be constantly renumbering them? -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Restart after poweroutage
On Sunday 24 September 2006 09:17 am, Tom Lane wrote: Jon Lapham [EMAIL PROTECTED] writes: I recently had another electrical power outage that left my machine unable to restart postgresql. I had previously reported this a while ago: http://archives.postgresql.org/pgsql-general/2005-04/msg01286.php Anyway, because I have seen this problem before, I knew exactly what the solution to the problem was (delete the postmaster.pid file), As was pointed out to you in the discussion subsequent to that message, this is not a good automatic response, and it should not be necessary at all with a post-8.0 postmaster. FATAL: pre-existing shared memory block (key 5432001, ID 65536) is still in use This is extremely odd, because a shared memory block could not possibly have survived a reboot. Too bad you have destroyed the evidence, because I would like to know what really happened there. Is it possible that you have somehow managed to try to start the postmaster twice during your system boot cycle? If you do have two postmasters running in that data directory right now, you are in deep trouble :-( Snip regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster In the its a small world department I experienced the same problem shortly after reading this message. The particulars Postgres 8.1.4, Kubuntu 6.06 on a laptop. My laptop sometimes experiences issues with ACPI and has to be powered off. After the most recent event I saw a message similar to that reported above. I checked and there were no other Postgres instances running. What information I could collect is included in the attached file. -- Adrian Klaver [EMAIL PROTECTED] Error message at Postgres start up-- pg_ctl start pg_ctl: another postmaster may be running; trying to start postmaster anyway -2006-09-24 17:16:59.986 PDT-FATAL: pre-existing shared memory block (key 5432001, ID 917506) is still in use -2006-09-24 17:16:59.987 PDT-HINT: If you're sure there are no old server processes still running, remove the shared memory block with the command ipcclean, ipcrm, or just delete the file postmaster.pid. pg_ctl: could not start postmaster Examine the log output. The postmaster.pid from the previous session(before reboot)-- postmaster.pid 6173 /usr/local/pgsql/data 5432001917506 Postgres log after I deleted above postmaster.pid and ran pg_ctl again.-- ~ -2006-09-24 17:21:40.693 PDT-LOG: database system was interrupted at 2006-09-24 14:57:34 PDT -2006-09-24 17:21:40.716 PDT-LOG: checkpoint record is at 0/59AD3D8 -2006-09-24 17:21:40.716 PDT-LOG: redo record is at 0/59AD3D8; undo record is at 0/0; shutdown FALSE -2006-09-24 17:21:40.716 PDT-LOG: next transaction ID: 32898; next OID: 456080 -2006-09-24 17:21:40.716 PDT-LOG: next MultiXactId: 1; next MultiXactOffset: 0 -2006-09-24 17:21:40.716 PDT-LOG: database system was not properly shut down; automatic recovery in progress -2006-09-24 17:21:40.728 PDT-LOG: record with zero length at 0/59AD41C -2006-09-24 17:21:40.728 PDT-LOG: redo is not required -2006-09-24 17:21:40.765 PDT-LOG: database system is ready -2006-09-24 17:21:40.811 PDT-LOG: transaction ID wrap limit is 2147484146, limited by database postgres ---(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] serial column
You might want to take a look at- http://www.varlena.com/GeneralBits/ The procedure as shown does not account for renumbering after a delete, but it might serve as a starting point. On Sunday 24 September 2006 07:03 pm, Bob Pawley wrote: The numbering system is more complex than just assigning a number. It invloves about thirty procedures which I have put together and find that it works well. I would like to keep the numbering as a database system which will be possible if I can figure out a way of generating sequential numbers without possibility of a gap. Perhaps a manually built table is the answer?? Bob - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Ragnar [EMAIL PROTECTED]; Postgresql pgsql-general@postgresql.org Sent: Sunday, September 24, 2006 4:30 PM Subject: Re: [GENERAL] serial column Bob Pawley [EMAIL PROTECTED] writes: I am using the numbers to identify devices. If a device is deleted or replaced with another type of device I want the numbering to still be sequential. It sounds to me like you oughtn't be storing these numbers in the database at all. You just want to attach them at display time --- they are certainly utterly meaningless as keys if they can change at any moment. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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 -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Restart after poweroutage
On Monday 25 September 2006 02:48 pm, Tom Lane wrote: Adrian Klaver [EMAIL PROTECTED] writes: On Sunday 24 September 2006 09:17 am, Tom Lane wrote: Jon Lapham [EMAIL PROTECTED] writes: FATAL: pre-existing shared memory block (key 5432001, ID 65536) is still in use This is extremely odd, because a shared memory block could not possibly have survived a reboot. In the its a small world department I experienced the same problem shortly after reading this message. I spent quite some time today trying to duplicate this failure (by pulling the plug on an up-to-date Fedora Core 5 machine). No luck. I suppose there is some contributing factor on your machines that we haven't identified yet ... regards, tom lane Is there something I could do to help capture useful information should I see a repeat of this behavior? -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] alter integer field to serial integer primary key
On Friday 29 September 2006 09:51 am, [EMAIL PROTECTED] wrote: I have an integer field of unique, consecutive numbers. Can I change this to be a serial, primary key field using an ALTER TABLE ALTER COLUMN command? I haven't been able to stumble over it. thanks, r ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org I think the easiest way would be to use CREATE SEQUENCE to create a sequence with a start value above that of max(integer field) and then use ALTER TABLE to set the nextval(sequence) as the DEFAULT value. -- Adrian Klaver [EMAIL PROTECTED] ---(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] basic SQL request
On Sunday 05 November 2006 11:42 am, Alain Roger wrote: Hi, i've tried to run a basic SQL request as followed : select * from articles, articletypes, department ^ s where articles.articletype_id = articletype.articletype_id AND ^ no s articles.department_id = department.department_id AND articles.validity_period_end now() and i got the following error message : ERROR: missing FROM-clause entry for table articletype i'm confused now, if i use LEFTJOIN it's the same, so where is the trouble ? thx, AL. The table name in the FROM clause has an s on the end. The name in the WHERE clause does not. -- Adrian Klaver [EMAIL PROTECTED] ---(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] R and postgres
On Monday 06 November 2006 04:58 pm, Reece Hart wrote: I'd like to get R to talk to postgresql, but my head's spinning among a web of broken links, way outdated web pages, project deprecation announcements and a terrible signal:link ratio. Rdbi and RdbiPgSQL seem to be the answer, despite both being apparently defunct projects. What is the Right Thing for a guy who wants R to talk to postgresql? Thanks, and send aspirin, Reece You might want to take a look at PL/R a procedural language for R in Postgres. http://www.joeconway.com/plr/ -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] R and postgres
On Monday 06 November 2006 07:38 pm, Reece Hart wrote: On Mon, 2006-11-06 at 19:13 -0800, Adrian Klaver wrote: You might want to take a look at PL/R a procedural language for R in Postgres. http://www.joeconway.com/plr/ PL/R is indeed neat, but I want to go the other way: within R, fetch data from postgres and manipulate it (plot, histogram, etc). I appreciate that I could expose this through postgres, but that's a little awkward for prototyping and I'm not keen to add load to my postgres box. -Reece Sorry, I did not read the message closely enough. A quick perusal of the R docs helped me understand the issue a little better. The only solution I can see at this point is to have an intermediate step. There are two ways I can see to do this. The first is to use the copy command to create a csv file. The read.table() function would then be used in R to import the data. The second is use the program pg2xbase http://www.klaban.torun.pl/prog/pg2xbase/ to create a DBF file and use read.dbf() to input the file. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] posgres headers
On Thursday 09 November 2006 06:33 am, Antonios Katsikadamos wrote: Hi all. Sorry to bother. Does anyone know where the postgres headers are stored? Which files constitute postgres headers? kind regards, Antonios - Access over 1 million songs - Yahoo! Music Unlimited. I answered this question for you on the psycopg list. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Data conversion tools?
If you don't mind an intermediate step you could use Pg2xbase http://www.klaban.torun.pl/prog/pg2xbase/ This program takes dbf files and inputs them into Postgres. It has an option for lower casing field names. You can specify the table name when you do the conversion. On Wednesday 15 November 2006 11:41 am, Dan Armbrust wrote: I'm trying to convert a database from either MS Access or MySQL into Postgres. I have found a couple of tools that will almost do what I want - but not quite. To make things match up with code that is already written - I need to have all of the table names and column names converted to lower case during the conversion process. I have found a couple of (free or demo) tools that will do it - navicat has a tools that will convert from MSAccess to Postgres - however, it doesn't offer a feature to lowercase all of the table names and column names. DB Tools Manager Professional will do it as well - but again, no way to tell it to lowercase things in the process. PGAdmin II had a tool that would do this - but alas - that part of PGAdmin was never brought into PGAdmin III. And the last version of PGAdmin II that I was able to find wouldn't run against my Postgres DB. Short of installing an older Postgres DB that PGAdmin II will work with - does anyone else know of a tool that can do what I want? Is there a script of some sort that I can run that would go through a set of tables and lowercase everything? Thanks, Dan -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] After Update Triggers
Have you explored the possibility that the trigger is doing what it is supposed to. I would investigate the procedure that updates the ip_op_equipment field. Make sure that it is not updating all the rows each time and thereby firing your trigger for each update. TG_OP is a variable available to trigger functions. It identifies what operation is being done to the row i.e. INSERT,UPDATE,DELETE. It is referenced in the pl/pgsql section of the manual. On Friday 17 November 2006 03:49 pm, Bob Pawley wrote: I am attempting to distribute the fluid from the process table to its own table (pipe or equipment) depending on whether the fluid is classified as op, ip or eq. I didn't include the after insert trigger as there can't be a trigger until the ip_op_equipment is updated. BTW what is TG_OP that you referred to? Bob - Original Message - From: Tomas Vondra [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Friday, November 17, 2006 3:34 PM Subject: Re: [GENERAL] After Update Triggers When I trigger 'after insert' the function doesn't work because the ip_op_equipment condition is an update. When I manually enter directley into the table this trigger works fine when both the fluid and ip_op_equipment are entered as one entry. When I trigger 'after update' every row in the Processes table is inserted into the other tables depending on the conditionals. I end up with multiple inserts of the same information. Is it possible to create a trigger that inserts only one row for each entry? Hello, I've read the whole message several times and I have to admit I still don't understand what are you trying to do or what is going wrong. I'm not sure what do you mean by 'when I trigger after insert' - the trigger is defined as AFTER UPDATE so naturally it does not fire in case of an INSERT. Anyway the point is you can define the trigger as AFTER INSERT OR UPDATE and use TG_OP variable, or maybe define several triggers - one for the UPDATE, one for the INSERT. Tomas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Data
On Wednesday 22 November 2006 03:16 pm, Bob Pawley wrote: I had an access violation which corrupted the PostgreSQL server to the point that it would not open. I took the opportunity to upgrade to version 8.1. Is there a way of retreiving the project in the old version and opening it in the new version without reinstalling the 8.0 version for a pgdump? Bob Pawley Off hand I would say no. The 8.0 server needs to be running in order for pg_dump to make a connection and retrieve the data.. Transferring the binary files will not work because of the version difference. Even if you could copy the files I would hesitate because of the corruption issue. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Version 8.1
On Wednesday 29 November 2006 02:42 pm, Bob Pawley wrote: I have just upgraded from v8.0 to 8.1. In the new version I keep getting errors due to the absence of 'from' -on triggers that showed no error in the old version. Was insisting on the use of from a planned part of the upgrade? Bob Pawley http://www.postgresql.org/docs/8.1/interactive/runtime-config-compatible.html See add_missing_from (boolean) -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG Admin
On Monday 04 December 2006 04:17 pm, Bob Pawley wrote: Your missing the point. I am creating a design system for industrial control. The control devices need to be numbered. The numbers need to be sequential. If the user deletes a device the numbers need to regenerate to again become sequential and gapless. Bob I am trying to figure how you keep track of the physical devices. Do they get renumbered also? -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PG Admin
- Original Message - From: Berend Tober [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: pgsql general pgsql-general@postgresql.org Sent: Monday, December 04, 2006 7:15 PM Subject: Re: [GENERAL] PG Admin Bob Pawley wrote: Your missing the point. I am creating a design system for industrial control. The control devices need to be numbered. The numbers need to be sequential. If the user deletes a device the numbers need to regenerate to again become sequential and gapless. Could you explain what it is about industrial control that requires the reassignment of numbers? Seems to me to make for confusion because over time, you then have a particular instrument referred to by different identifiers. So if you had other data, such as written logs, shop floor design diagrams, or other data not included in the data base, for example, you'ld have the problem of keeping track of which instruments were really being talked about because the names (identifying number, that is) keep changing. ---(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 On Monday 04 December 2006 07:39 pm, Bob Pawley wrote: I am talking about designing the control system. No one makes a perfect design at first go. Devices are deleted and others added. Until the end of the design stage the numbers need to be sequential with no gaps. After the design the numbers of each device are static and new devices are added to the sequence or fill in for abandoned devices - but that is another, separate problem. But that is beside the point. What I am looking for is a gapless sequence generator which has the ability to justify for deletions as well as additions. What I am looking for is a very simple adaptation of the serial function. All that I need it to do is to justify for design changes and not care that if it is reassinged to a different device. The fact that a particular device may, by happenstance, change it's assigned number - once twice or multiple times, during the design stage, is of no consequence - as long as the totallity of numbers assigned are sequential and gapless. Bob I see now. My thought would to hold the device numbers in a regular integer column. Have an AFTER INSERT/UPDATE/DELETE trigger that does a count on the table and renumbers all the rows in the id column. This is the brute force method. The alternative would be to search for the gaps and renumber from the first gap up. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] need help with plpgsql execute insert
On Wednesday 20 December 2006 7:36 pm, [EMAIL PROTECTED] wrote:
I am trying to loop through some data and then run insert some of the
resulting data into a new table. I can create the function but when I run
it i get the error:
ERROR: query SELECT 'INSERT INTO payment (
id,amount,accepted_date, company_id , date ,
patient_responsible_party_id , patient_contact_responsible_party_id ,
insurer_network_responsible_party_id, type, status)
values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
, $7 , 'Other', 'ACCEPTED' returned 11 columns
SQL state: 42601
Context: PL/pgSQL function add_missing_slrps line 20 at execute statement
I don't understand what the returned 11 columns means. I am inserting
10 and i counted and it all matches.
Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
is where the errors starts
CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
DECLARE
data RECORD;
paymentId int;
BEGIN
RAISE NOTICE 'Start loop...';
FOR data IN select slra.company_id, slra.create_date,
slra.service_line_responsibility_id,
slr.insurance_policy_responsible_party_id,
slr.patient_responsible_party_id,
slr.patient_contact_responsible_party_id,
insurer_service_center.insurer_network_id
from
.
.
.
.
.
.
.
.
.
LOOP
-- Now data has one record
EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
EXECUTE 'INSERT INTO payment (
id,amount,accepted_date, company_id , date ,
patient_responsible_party_id patient_contact_responsible_party_id ,
no comma
insurer_network_responsible_party_id, type, status)
values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
data.company_id, data.create_date , data.patient_responsible_party_id ,
data.patient_contact_responsible_party_id , data.insurer_network_id,
'Other', 'ACCEPTED';
END LOOP;
RAISE NOTICE 'Done loop .';
RETURN 1;
END;
$$ LANGUAGE plpgsql;
select add_missing_slrps() ;
I assumed using the '%' symbol will automatically use the real value.
Like if it is a date it will handle it like a java prepared statement. Am
I wrong?
I have tried all kinds of things but I truly have no idea what the problem
is.
thanks
See inline comment, but I think you are missing a comma in your columns list.
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] could not open relation:no such file or directory
On Tuesday 26 December 2006 2:43 am, karthik wrote: hello, my name is karthik . i facing a problem when trying to select values from a table in postgresql. when i execute a query like select title from itemsbytitle; i get error as Error:Could not open relation itemsbytitle. no such file or directory. can anybody help me to find an answer for this problem. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings I guess the first question is are you sure there is a table named itemsbytitle? If so you may need to quote the table name- SELECT title FROM itemsbytitle; See below for full explanation http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Generic timestamp function for updates where field
On Sunday 31 December 2006 8:48 am, novnov wrote: OK. python would be the preference, if anyone is interested in showing me how it would be done, I've never used one of the dynamic languages with postgres. Why would not be possible in plpgsql? It has loop etc, the only part I'm not sure it can do it use the variable as field name. http://archives.postgresql.org/ Here is a function I wrote in python to do something similar. My timestamp fields are of the form tc_ts_update where tc is a table code that can be found by looking up the table name in the table_code table. In pl/pythonu that ships with 8.2 it is no longer necessary to do the relid look up. There is a TD[table_name] variable that returns the table name directly. CREATE OR REPLACE FUNCTION public.ts_update() RETURNS trigger AS $Body$ table_oid=TD[relid] plan_name=plpy.prepare(SELECT relname FROM pg_class WHERE oid=$1,[oid]) plan_code=plpy.prepare(SELECT tc_table_code FROM table_code WHERE tc_table_name=$1,[text]) rs_name=plpy.execute(plan_name,[table_oid]) rs_code=plpy.execute(plan_code,[rs_name[0][relname]]) fld_name=_ts_update tbl_code=rs_code[0][tc_table_code] full_name=tbl_code+fld_name TD[new][full_name]=now() return MODIFY $Body$ LANGUAGE plpythonu SECURITY DEFINER; -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installing support for python on windows
On Sunday 31 December 2006 2:09 pm, novnov wrote: I've spent a couple of hours on this and would like to ask for an assist at this point. I need to install python support on windows. The postgresql install is the windows 8.2.0-1 binary, and pl/pgsql was included in the initial installation. I have python 2.5 installed. The workstation's path does not ref either the python or postgres directories. - I take it that plpython is what I should be installing? I first read in this list that psycopg is commonly used to work with python in postgres, and I installed the windows port of that, but 'nothing happened' ie no python lang support materialized. I'm not at all sure what the score is with psycopg and plpython, but am now aiming for plpython as that seems to be the offering that is included with the postgres installer. But I'm curious, why psycopg when plpython is there? Are they complimentary, or should it be one or the other? Psycopg is an interface from Python to Postgres. In other words it works from the outside. You use it to connect a Postgres database and manipulate data as needed. plpython is a procedural language for Postgres that allows one to use Python from within the server. For what you want to do plpython is the choice. - The postgresql docs reference plpythonu (untrusted) but what I find in \lib is plpython? Are the docs out of date or am I mixing up information? (http://www.postgresql.org/docs/8.2/interactive/plpython.html) I haven't installed Postgres on Windows so I am not familiar with the naming there. At one time there was a plpython but this has been replaced by plpythonu as Python did not have a robust mechanism for keeping the language from straying outside the server. - Per most docs the installation of a language is done with createlang. I've tried many times and cannot get the syntax right. Typically I log in to psql in the \bin directory as the main postgres user. After that, a typical attempt is looks like this: postgres=# createlang 'd:\postgresql\lib\plpython' mydb; Typical error is: ERROR: syntax error at or near createlang LINE 1: createlang d: This is a later attempt where I've added the lib path. I've tried no path, double quotes, many things. I've tried executing from the windows command line (ie not while in psql), adding the dll extension. Rather than spending the rest of the year on this (ie ten hours g) I hope someone can knock out a line or two to steer me in the right direction. Just for reference sake, did you try createlang -d mydb plpythonu See URL below for more information http://www.postgresql.org/docs/8.2/interactive/app-createlang.html -- Adrian Klaver [EMAIL PROTECTED]
Re: [GENERAL] Installing support for python on windows
On Sunday 31 December 2006 7:06 pm, novnov wrote: Thanks to both of you. I tried the following and got an error that plpython.dll couldn't be found: D:\postgresql\bincreatelang -U sauser plpythonu mydb Password: createlang: language installation failed: ERROR: could not load library D:/pos tgresql/lib/plpython.dll: The specified module could not be found. The file spec'd by the error message does indeed exist, though the slashes in windows would of course be the other way around in normal use. Then I tried leaving of the u in plpythonu D:\postgresql\bincreatelang -U sauser plpython mydb Password: createlang: language installation failed: ERROR: unsupported language plpython HINT: The supported languages are listed in the pg_pltemplate system catalog. Then I tried something like what Adrian had suggested: D:\postgresql\bincreatelang -U sauser -d mydb plpythonu Password: createlang: language installation failed: ERROR: could not load library D:/pos tgresql/lib/plpython.dll: The specified module could not be found. It's interesting that createlang knows to look in the peer lib directory for the language file but somehow does not like the plpython that it sees there. I also tried plpython (no trailing u) and had the same error as the earlier experiment. At this point I don't know what to say. Hopefully someone with with experience installing Postgres on Windows can help out. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Installing support for python on windows
On Monday 01 January 2007 11:14 am, Magnus Hagander wrote: novnov wrote: This is so much more difficult than I imagined it could be. I've added the main python dir and the lib dir to my path and nothing has changed. I may be able to figure out how to use the depends tool, so far it looks pretty obscure to a newb. It should be easy enough - just run depends plpython.dll in the directory where plpython is. Two things to verify first: 1) Verify that you added the directories to the system path, and not your personal path 2) Did you restart the server after adding it? Needs to be done for windows to pick up the change in environment. //Magnus ---(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 I broke down and installed Postgres on Windows. I had the same problem with installing plpython until I did as Magnus suggested, rebooted Windows. I then ran createlang -d template1 -U postgres plpythonu to install it into the template1 database and it succeeded. -- Adrian Klaver [EMAIL PROTECTED] ---(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] How to convert money columns to numeric?
On Monday 01 January 2007 1:45 pm, Ken Winter wrote: I want to convert a column named amount, currently of type money, to type numeric(10,2). When I try to do this using: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2); I get: PostgreSQL Error Code: (1) ERROR: column amount cannot be cast to type pg_catalog.numeric So then I figure I need to do it with SQL of the form: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING expression; But I can't find a conversion function or operator that will accept a money column as input. For example: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING to_number(amount, '.99'); Evokes this error message: PostgreSQL Error Code: (1) ERROR: function to_number(money, unknown) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. And I can't seem to cast a money column into anything else. For example: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING cast(amount as numeric); Evokes: PostgreSQL Error Code: (1) ERROR: column amount cannot be cast to type pg_catalog.numeric So I'm fresh out of ideas - other than dropping and recreating the column, which would lose a lot of data. ~ TIA ~ Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Take a look at the GeneralBits column below for a possible solution(see heading Convert money type to numeric)- http://www.varlena.com/GeneralBits/75.php -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installing support for python on windows
On Monday 01 January 2007 3:39 pm, novnov wrote: It should be easy enough - just run depends plpython.dll in the directory where plpython is. Two things to verify first: 1) Verify that you added the directories to the system path, and not your personal path 2) Did you restart the server after adding it? Needs to be done for windows to pick up the change in environment. plpython.dll is in the lib dir. But at a command prompt there, depends plpython.dll just gets me 'depends' is not recognized as an internal or external command etc. The postgres docs talk about the catalog pg_depend, and maybe 'depends' is another executable that calls that, but I've not been able to find a 'depends.exe'. depends does not seem to fly in psql. The pg_depends docs don't really tell me how to use pg_depends on it's own...I don't know anything about catalogs, or it'd probably be obvious. The server was restarted and the path modified was for the server. Thanks Depends is a windows program. To get it I had to load the Windows support tools from the Windows install disc(in my case the image on my harddrive). -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Installing support for python on windows
On Monday 01 January 2007 6:24 pm, novnov wrote: Thanks, the depends tools looks very handy, surprising I'd not heard of it before. I found that the postgresql\bin dir must be added to the path. Also, I had python 2.5 installed, and plpython apparently needs python 2.4. I've installed that and added to the path, but there is another dependency missing inside of the python stack, DWMAPI.dll. Googling DWMAPI.dll gets a mixed bag, but I think that it might be part of IE6, and not IE7 (I have IE7). Maybe the current plpython does not work unless IE6 is installed, because plpython needs python 2.4, which needs IE 6??? I installed with python 2.5 and IE7 with no problem. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Generic timestamp function for updates where field
On Wednesday 03 January 2007 12:13 am, novnov wrote: Adrian Klaver wrote: On Sunday 31 December 2006 8:48 am, novnov wrote: OK. python would be the preference, if anyone is interested in showing me how it would be done, I've never used one of the dynamic languages with postgres. Why would not be possible in plpgsql? It has loop etc, the only part I'm not sure it can do it use the variable as field name. http://archives.postgresql.org/ Here is a function I wrote in python to do something similar. My timestamp fields are of the form tc_ts_update where tc is a table code that can be found by looking up the table name in the table_code table. In pl/pythonu that ships with 8.2 it is no longer necessary to do the relid look up. There is a TD[table_name] variable that returns the table name directly. CREATE OR REPLACE FUNCTION public.ts_update() RETURNS trigger AS $Body$ table_oid=TD[relid] plan_name=plpy.prepare(SELECT relname FROM pg_class WHERE oid=$1,[oid]) plan_code=plpy.prepare(SELECT tc_table_code FROM table_code WHERE tc_table_name=$1,[text]) rs_name=plpy.execute(plan_name,[table_oid]) rs_code=plpy.execute(plan_code,[rs_name[0][relname]]) fld_name=_ts_update tbl_code=rs_code[0][tc_table_code] full_name=tbl_code+fld_name TD[new][full_name]=now() return MODIFY $Body$ LANGUAGE plpythonu SECURITY DEFINER; -- Adrian Klaver [EMAIL PROTECTED] Here is what I have tried, it fails on the TD[NEW][varFieldName]=now() line. Do I need the Return? I'm passing in the table prefix as a param. I set to VOLATILE not SECURITY DEFINER (wasn't sure what that was) CREATE OR REPLACE FUNCTION public.datem_update() RETURNS trigger AS $BODY$ varPrefix=TG_ARGV[0] varFieldName=varPrefix+_datem TD[NEW][varFieldName]=now() RETURN Modify Try return Modify. I believe the problem is actually the upper case RETURN. $BODY$ LANGUAGE 'plpythonu' VOLATILE; -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Generic timestamp function for updates where field
On Wednesday 03 January 2007 9:10 am, novnov wrote: Adrian Klaver wrote: On Wednesday 03 January 2007 12:13 am, novnov wrote: Adrian Klaver wrote: On Sunday 31 December 2006 8:48 am, novnov wrote: OK. python would be the preference, if anyone is interested in showing me how it would be done, I've never used one of the dynamic languages with postgres. Why would not be possible in plpgsql? It has loop etc, the only part I'm not sure it can do it use the variable as field name. http://archives.postgresql.org/ Here is a function I wrote in python to do something similar. My timestamp fields are of the form tc_ts_update where tc is a table code that can be found by looking up the table name in the table_code table. In pl/pythonu that ships with 8.2 it is no longer necessary to do the relid look up. There is a TD[table_name] variable that returns the table name directly. CREATE OR REPLACE FUNCTION public.ts_update() RETURNS trigger AS $Body$ table_oid=TD[relid] plan_name=plpy.prepare(SELECT relname FROM pg_class WHERE oid=$1,[oid]) plan_code=plpy.prepare(SELECT tc_table_code FROM table_code WHERE tc_table_name=$1,[text]) rs_name=plpy.execute(plan_name,[table_oid]) rs_code=plpy.execute(plan_code,[rs_name[0][relname]]) fld_name=_ts_update tbl_code=rs_code[0][tc_table_code] full_name=tbl_code+fld_name TD[new][full_name]=now() return MODIFY $Body$ LANGUAGE plpythonu SECURITY DEFINER; -- Adrian Klaver [EMAIL PROTECTED] Here is what I have tried, it fails on the TD[NEW][varFieldName]=now() line. Do I need the Return? I'm passing in the table prefix as a param. I set to VOLATILE not SECURITY DEFINER (wasn't sure what that was) CREATE OR REPLACE FUNCTION public.datem_update() RETURNS trigger AS $BODY$ varPrefix=TG_ARGV[0] varFieldName=varPrefix+_datem TD[NEW][varFieldName]=now() RETURN Modify Try return Modify. I believe the problem is actually the upper case RETURN. $BODY$ LANGUAGE 'plpythonu' VOLATILE; -- Adrian Klaver [EMAIL PROTECTED] Thanks Adrian, 'return' works better. But there may be a namespace issue with TG_ARGV. The error I get is exceptions.NameError: global name TG_ARGV is not defined. I have been unable to find anything on this by googling the web or usenet. Do the postgres names like TG_ARGV need special treatment inside a python function? tg_argv[0] (ie lowercase) did no better. As an experiment I replaced tg_argv with a hard coded the prefix value, and found that it didn't like NEW either, 'new' is better. But with that change the function works, so the TG_ARGV issue is the last one. Replace TG_ARGV[0] with TD[args][0] For complete documentation see http://www.postgresql.org/docs/8.2/interactive/plpython.html -- Adrian Klaver [EMAIL PROTECTED] ---(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] TRIGGER BEFORE INSERT
On Thursday 11 January 2007 10:26 am, Rafal Pietrak wrote: On Thu, 2007-01-11 at 15:10 +0100, Alban Hertroys wrote: Rafal Pietrak wrote: Hi! I'm re-posting this message again in hope someone would have a look at the case again. .. it's pending. You were given a solution; defer the foreign key constraint. Well. I were, but probably I'm doing something wrong with 'deferring the trigger'. When I put: What previous posters have said is that you need to defer the FK. So you need to change your table definition from: CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not null, dnia int references test_days(id)); to: CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not null, dnia int references test_days(id) INITIALLY DEFERRED); per Toms suggestion. This eliminates the need for the SET CONSTRAINTS DEFERRED statement. -- Adrian Klaver [EMAIL PROTECTED] ---(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] How can I list the function.
On Wednesday 31 January 2007 8:46 pm, Harvey, Allan AC wrote: I know the function is there. What am I doing wrong? galvdb=# galvdb=# \df+ delete_old List of functions Result data type | Schema | Name | Argument data types | Owner | Language | Source code | Description --++--+-+---+-- +-+- (0 rows) galvdb=# select delete_old(); delete_old 482 (1 row) galvdb=# select version(); version --- -- PostgreSQL 8.2.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) (1 row) galvdb=# The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(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 My guess is it is a permissions issue. The user you are logged in does not have the privileges necessary to view the function. By default functions have EXECUTE privileges granted to the ROLE PUBLIC which would explain you being able to do select delete_old(). See below for more information. http://www.postgresql.org/docs/8.2/interactive/sql-grant.html -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Count(*) throws error
-- Original message -- From: Jasbinder Singh Bali [EMAIL PROTECTED] Hi, I'm using the following statement in my plpgsql function SELECT INTO no_rows COUNT(*) FROM tbl_concurrent; I have decalred no_rows int4 and initialized it to zero Running the function throws the following error: ERROR: syntax error at or near ( at character 13 QUERY: SELECT $1 (*) FROM tbl_concurrent CONTEXT: SQL statement in PL/PgSQL function sp_insert_tbl_concurrent near line 8 Try SELECT COUNT(*) INTO no _rows FROM tbl_concurrent; See http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT for complete syntax. If i comment this count(*) line, the error goes. I don't know why isn't count(*) working Thanks Jas ---BeginMessage--- Hi,Im using the following statement in my plpgsql functionSELECT INTO no_rows COUNT(*) FROM tbl_concurrent; I have decalred no_rows int4 and initialized it to zeroRunning the function throws the following error: ERROR: syntax error at or near ( at character 13QUERY: SELECT $1 (*) FROM tbl_concurrentCONTEXT: SQL statement in PL/PgSQL function sp_insert_tbl_concurrent near line 8 If i comment this count(*) line, the error goes.I dont know why isnt count(*) workingThanksJas ---End Message--- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tables dissapearing
On Monday 27 August 2007 5:57 pm, Kamil Srot wrote: Tom Lane wrote: Kamil Srot [EMAIL PROTECTED] writes: Erik Jones wrote: Have you verified that the table's files are still on disk after it's disappeared? Do not have any idea how to do it... I wasn't able to access it using any DML/DDL commands... can try it on a binary backup of the damaged DB if you'll guide me... Make a note now of the table's relfilenode value (it'll be different in each database), and confirm that you see it in the filesystem. After the next disappearance, see if anything's still there. For background read http://www.postgresql.org/docs/8.2/static/storage.html OK, I have the filenames noted and I do confirm, they all does exist now under the base in the pgsql tree... Note that certain operations like TRUNCATE and CLUSTER change the relfilenode, so if you're using any of those then it might get harder to track where the file is. There is not any manipulation with the structure of the DB, so it'll stay the same... Thank you! I have a question. First a little history. Right now, the people who know better than I are fairly certain Postgres is not changing things on its own and the developer is certain the CMS software is not doing schema changes. As I understand it logging has been cranked up to test both those assumptions. My question is, how are legitimate schema changes done? Just wondering if there is a third party involved. -- Adrian Klaver [EMAIL PROTECTED] ---(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] Export data to MS Excel
On Saturday 01 September 2007 12:16 am, Ashish Karalkar wrote: Hello All, I want to export data from PostgreSQL tables to MS Excel. Is there any way? Thanks in advance... With Regrads Ashish... One relatively easy way to do it is to use the Base component of OpenOffice. You can dump data directly from a table to a spreadsheet. Just save as *.xls. -- Adrian Klaver [EMAIL PROTECTED] ---(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] pg_dump
On Tuesday 02 October 2007 3:37 pm, Bob Pawley wrote: Is there a better method of transfering the database and data to between DBs? Bob From the Postgres website PgFoundry project http://pgfoundry.org/projects/dbi-link/ Commercial products http://www.dbconvert.com/ More commercial projects (not all pertain to your question) http://www.postgresql.org/download/commercial -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: re[GENERAL] lations does not exist
On Tuesday 16 October 2007 12:20 pm, ctorres wrote: Hi, I doing a simple insert into a table re Perl/DBI INSERT INTO party (party_id, party_type_id, description, status_id) VALUES ($partyId, 'PERSON', 'Initial Import','PARTY_ENABLED') and I'm getting a ERROR: relations party does not exist I get the same error message in pgadmin. The table party certainly exists. I have searched for answers without any luck. Anyone know what might be going on and how to fix it? Thanks in advance, Case Two things come to mind. 1) Do you have the necessary permissions to access the schema table 'party' is in? 2) How was the name for 'party' originally entered? It could be a case sensitive problem. See http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html Section 4.1.1 for a complete explanation. Basically if the table name was entered with quotes in a form other than 'party' then selecting for 'party' will result in the error above. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Question regarding Hibernate ORDER BY issue
On Friday 19 October 2007 3:03 pm, Valerie Cole wrote: Hello I have a problem and am pretty sure Hibernate is the culprit but was wondering if anybody knew of a fix. We are working on legacy code and converted a class from Hibernate 2 xml mappings to Hibernate 3 with annotations. On one of the One To Many attributes we have used the @OrderBy(displayPosition). The SQL generated by Hibernate outputs the column name as DisplayPosition with no quoting, and Postgres kicks back an error saying the column does not exist. Our tables/columns have all been created with quotes and must be accessed with quotes (I don't know if that is the norm, I am somewhat of a Postgres newb). I have been Googling for about an hour and unable to come up with anything, so I thought I would drop a line. Thanks, V. Cole You might to look at: http://www.hibernate.org/hib_docs/reference/en/html/mapping.html See esp. section 5.4 on SQL quoted identifiers -- Adrian Klaver [EMAIL PROTECTED] ---(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] Select Command
On Sunday 21 October 2007 2:32 pm, Bob Pawley wrote: I have a column with data structured as follows. 32TT - 0002 32LT- 0004 32PT-0005 Is there a way of selecting all of the rows containing LT in that column?? I have attempted variations of ' *LT* ' with out success. Bob Pawley select col where col LIKE '%LT%'; See also: http://www.postgresql.org/docs/8.2/interactive/functions-matching.html#FUNCTIONS-LIKE -- Adrian Klaver [EMAIL PROTECTED] ---(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] Photos from the PostgreSQL Conference Fall 2007
On Monday 22 October 2007 5:06 pm, Andrej Ricnik-Bay wrote: On 10/22/07, Daniel Browning [EMAIL PROTECTED] wrote: The PostgreSQL Conference Fall 2007 was informative, fun, and well-executed. Thanks to Selena Deckelmann, Joshua Drake, and everyone else who made it happen. Here are my photos of the event: http://db.endpoint.com/pgcon07/ Now if one could put names to those faces ... :} Daniel Browning Cheers, Andrej I figured the name tags took care of that. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] select count() out of memory
On Friday 26 October 2007 8:56 am, [EMAIL PROTECTED] wrote: Serious engineering does not imply perfect engineering, I have analyzed it and made my tradeoffs. What you are forgetting here is that you clearly dont understand the enire solution, So I will try to explain it again. And if you still think its bonkers, the I urge you to come up with a solution that works with the requirements. Every predefined X seconds (e.g. 3,6,9,12 etc ) a bunch of data arrives, which must be stored by descrete time groups, e.g. second 3,6,9,12. The data that arrives is approx 4MB per second, so in this case its 12MB. This has to be processed by the server and written to the db, within 1 second. There can be up to 5 writers at the same time. Within that same second, at least 16 readers should be able to read all the data, *each*. Writers and readers are only concerned with the latest data, i.e. data from the latest time group, e.g. second 9. This has to go on every predefined seconds for the next 6-12 weeks, without stop, pause or any errors. These are the requirements. When I performed performance tests I found several unwanted effects from several test scenarios. Here are the most important ones: - single large table, with indexes created when table is created. - this leads to the performance of an insert degrading as more data is added, when I get to 1 billion rows it took 50 seconds to add the data. My lesson from this is that - single inserts can never be efficient enough - indexes cause linear performance drop as data volume increases So I tried a different approach, which would address both issues: - separate tables for each bulk of data - use of bulk insert through jdbc COPY. - add indexes to the newly create table after the copy is finished. My lesson from this is: - insert take constant time, no matter how much data is in the base - adding the indexes after insert takes constant time, i.e. some milliseconds. From this I realised that using either single tables or partitions is the way to go, since I only need to access the latest data, i.e. the newest table, in normal situations. After thinking about it and discussing with this group, I found that using partitions would be more practical for two reasons: - changes to the parent table is automatically propagated to all child tables, so the schema remains consistent and the server wont brake because of differences in the tables. - it is more maintainable to use create with inheritance sql in source code than the entire ddl of the table. So now I have tested the server 24/7 for a week and a half, with 1 writer and 16 readers writing all the mentioned data, and everything works fine. Expect for the select on the parent table, which now runs out of memory. Which in it self is not a problem since I will never use the parent table in production in any case. regards tom I might be missing the point, but couldn't you do a Copy to a single table instead of multiple inserts and avoid the index overhead. -- Adrian Klaver [EMAIL PROTECTED] ---(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] select count() out of memory
-- Original message -- From: Thomas Finneid [EMAIL PROTECTED] Adrian Klaver wrote: I might be missing the point, but couldn't you do a Copy to a single table instead of multiple inserts and avoid the index overhead. Are you saying, have one large table with indexes and do a COPY to it or are you saying a one small empty table and do a COPY to it? thomas I'm thinking do a COPY to one large table. If the cost of indexing is relatively fixed as you indicated in your previous post then you reduce the indexing overhead to each COPY operation instead of each insert. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_restore
On Sunday 28 October 2007 11:32 am, Bob Pawley wrote: Please help. I am attempting to restore a database into PostgreSQL version 8.2 running on Win XP Professional. From the 'bin' folder, I am using the command line- pg_restore psql -h localhost -d PDW -U postgres -f aurel.sql Try pg_restore psql -h localhost -d PDW -U postgres aurel.sql No -f switch I get an error - pg_restore: cannot specify both -d and -f output. If the error message is correct how does pg_restore know what to put where? The -d switch tells pg_restore to the named database. The -f switch tells it to restore to named file. It won't do both. I used the same command to successfully install the same pg_dump file into PostgreSQL 8.1 running on the same computer. Maybe 8.1 ignored the error. Any thoughts would be much appreciated. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_restore
On Sunday 28 October 2007 2:13 pm, Bob Pawley wrote: Hi Adrian With pg_restore psql -h localhost -d PDW -U postgres aurel.sql the error message is - pg_restore: could not open input file: No such file or directory exists. I get this message with aurel.sql - or aurel - or the path to aurel (..8,2\bin) or when aurel is not even mentioned. Is this really the path -(..8,2\bin)? Note the ','. This is becoming quite frustrating. The other thing to check is whether you have the necessary permissions to read the file. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_restore
On Sunday 28 October 2007 2:28 pm, Tom Lane wrote: Bob Pawley [EMAIL PROTECTED] writes: This is becoming quite frustrating. The errant psql is your problem ... although pg_restore is being quite unhelpful by not mentioning the filename that it's trying to open. regards, tom lane Well there is your problem. Might help if I was using both eyes. Thanks for the heads up Tom. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_restore
On Sunday 28 October 2007 3:01 pm, Bob Pawley wrote: The latest in the saga - By using - pg_restore -h localhost -d PDW -U postgres aurel.sql I get the message - pg_restore: input file does not appear to be a valid archive. I get this message when I used the aurel.sql file which I previously loaded successfully in 8.1 and also when I use an aurel.sql file which I just successfully dumped a few minutes ago from the 8.1 on my other computer. Could pg_restore in my 8.2 be corrupted?? Bob What does your dump command look like? My guess is your are doing a plain text dump and pg_restore only works with the custom formats. If you want to use the plain text version than you need to use psql. This maybe how you got to the point of having both pg_restore and psql on the same line. -- Adrian Klaver [EMAIL PROTECTED] ---(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] pg_restore
On Sunday 28 October 2007 3:40 pm, Bob Pawley wrote: This is the dump command pg_dump -h localhost -d Aurel -U postgres Could you suggest a dump command that will match the restore command - pg_restore -h localhost -d PDW -U postgres aurel.sql Thanks Bob It depends on what you want to do. But to use pg_restore you will need to use one of either -Fc or Ft after the pg_dump command. My concern is that you are connecting to a different database name in the dump and restore commands. This may be what you want, but then again it may not.I would suggest reading the information at the URL below before proceeding further. http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dbf to pgsql
On Thursday 04 August 2005 01:42 am, Piotr wrote: You might to take a look at the following. http://www.klaban.torun.pl/prog/pg2xbase/ Hi, Im lookig for tool to regulary transfer data from dbf files into pgsql. Would be excellent if the tool would have data tranformation possibility. regards Peter ---(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 -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] plpythonu and return void
I recently migrated a database from Postgres 7.4.1 to Postgres 8.03. The only problem I have run into is that a plpythonu function that returns void will not run under 8.03. The error message states that a plpython function cannot return void. I got around it by having it return text and not actually returning anything. I have searched for an explanation and can't seem to locate one. Obviously I have missed something I just don't know what? Any suggestions would be appreciated. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpythonu and return void
On Monday 08 August 2005 08:02 pm, Tom Lane wrote: Adrian Klaver [EMAIL PROTECTED] writes: I recently migrated a database from Postgres 7.4.1 to Postgres 8.03. The only problem I have run into is that a plpythonu function that returns void will not run under 8.03. The error message states that a plpython function cannot return void. I got around it by having it return text and not actually returning anything. I have searched for an explanation and can't seem to locate one. The explanation is doubtless here: 2004-09-19 19:38 tgl * src/pl/plpython/plpython.c: Add defenses against plpython functions being declared to take or return pseudotypes. Not sure why I neglected to add these checks at the same time I added them to the other PLs, but it seems I did. It's certainly possible to weaken this test to allow void again, but shouldn't there be corresponding changes elsewhere to ensure that the behavior is actually sensible? regards, tom lane Thanks for the explanation. One of these days I will remember that the source is my friend. -- Adrian Klaver [EMAIL PROTECTED] ---(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] Column does not exist when trying to insert data.
On Thursday 15 March 2007 9:52 pm, Parthan SR wrote:
Hello,
My table 'orderitems' looks like this
create table orderitem (
id serial not null primary key,
item integer not null,
quantity numeric(6,2) not null,
unit varchar(10),
conference integer not null,
seller integer not null,
incharge integer not null,
orderdate date not null,
duedate date not null,
returnedas varchar,
);
I get all the values from a form, validate them and try to insert into
my postgres database table.
The integers in the above table are foreign key refernces (which I
have done using constraints).
The fields 'unit' and 'returnedas' are optional. I use python-psycopg
to handle the DB part.
When I execute the following statement..
INSERT INTO orderitem
(item,quantity,unit,conference,seller,incharge,orderdate,duedate,returnedas
) VALUES (%d,%f,%s,%d,%d,%d,%s,%s,%s) %
(params['item'],params['quantity'],params['unit'],
params['conference'],params['seller'],params['incharge'],params['orderdate'
],params['duedate'],params['returnedas'])
To make it make work here I had to use pyformat formatting. To illustrate-
VALUES(%(item)s,%(quantity)s,%(unit)s,%(conference)s,%(seller)s,%(incharge)s,
%(orderdate)s,%(duedate)s,% (returnedas)),
{'item':params['item'],'quantity':params['quantity'],'unit':params['unit'],'conference':
['conference'],'seller':params['seller'],'incharge':params['incharge'],
'orderdate':params[orderdate'],'duedate':params['duedate'],'returnedas':params['returnedas']}
params['conference'],params['seller'],params['incharge'],params['orderdate'
],params['duedate'],params['returnedas']
I get the following error in the browser, whent he fields for unit and
returnedas are left blank and hence becomes None.
I also tried to have some string value such as 'nos' and 'not reqd' for
'unit' and 'requiredas' but still it get a similar error
saying column 'nos' does not exist.
*ProgrammingError*: ERROR: column none does not exist INSERT INTO
orderitem
(item,quantity,unit,conference,seller,incharge,orderdate,duedate,returnedas
) VALUES (6,10.00,None,39,1,1,2007-3-16,20-03-2007,None)
args = ('ERROR: column none does not exist\n\nINSERT INT...,
10.00,None,39,1,1,2007-3-16,20-03-2007,None)',)
Where am I getting wrong ?
This is probably a psycopg issue. If the solution I suggested above does not
work you may want to take up the problem on the psycopg list.
--
Adrian Klaver
[EMAIL PROTECTED]
---(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] plpy prepare problem
On Sunday 01 April 2007 9:09 am, jlowery wrote: I'm having a bit of a problem getting plpython's prepare to work properly: CREATE OR REPLACE FUNCTION batch_item_reversal(b batch_item) RETURNS varchar AS $BODY$ if b['reversal_flag'] == 'Y': sql = plpy.prepare( SELECT batch_item_number FROM batch_item WHERE patient_ssn=$1 AND patient_dob=$1 AND claim_number=$1 AND batch_item_number != $1, [varchar, date, varchar, varchar]) refs = plpy.execute(sql, [ b['patient_ssn'], b['patient_dob'], b['claim_number'], b['batch_item_number']]) You need to have unique numbers for the variables. patient_ssn=$1 patient_dob=$2 etc refs2 = plpy.execute( SELECT batch_item_number FROM batch_item WHERE patient_ssn='%s' AND patient_dob='%s' AND claim_number='%s' AND batch_item_number != '%s' % (b['patient_ssn'], b['patient_dob'], b['claim_number'], b['batch_item_number'])) if refs: return refs[0][batch_item_number] else: return ERROR else: return None $BODY$ LANGUAGE 'plpythonu' VOLATILE; Here, refs2 returns the proper data, but refs always returns nothing. I have a feeling it has something to do with the type list, I tried all text's but to no avail. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- Adrian Klaver [EMAIL PROTECTED] ---(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] Upgrade Process
On Thursday 26 April 2007 9:47 am, Rich Shepard wrote: Unlike my previous upgrade from the 7.4.x series to the 8.1.x series, I now have all data in /var/lib/pgsql/data. Currently running 8.1.4 on my Slackware system and want to upgrade to 8.2.4, so I'm checking my procedure before inadvertently creating major problems for myself. What I believe should work -- and I'd like conformation or corrections, please -- is the following: 1) Run 'pg_dumpall pg8.1.4.sql' as user postgres. Generally it is a better idea to dump the old version with the new versions pg_dump,pg_dumpall commands. The new versions know more about the old versions of the database than the other way around. 2) Stop the running postmaster as root. 3) Upgrade the Slackware package to 8.2.4 as root. 4) Restart the postmaster as root. 5) Run 'psql -f pg8.1.4.sql postgres' as user postgres. Have I missed a critical step? The upgrade will replace the existing files with the new ones in the same directories. TIA, Rich I generally copy the old version(while it is shutdown) to another directory and then install the new version. I modify the postgresql.conf in the directory containing the old version so that Postgres listens on a different port and start it up. I can then use the pg_dump(all) commands from the new version to pull from the old version. The catch is that you need enough room for both copies of the database. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Date Math
-- Original message -- From: Rich Shepard [EMAIL PROTECTED] On Mon, 7 May 2007, [EMAIL PROTECTED] wrote: test= select '01/01/04'::date +interval '3 year',current_date + interval '2 month'; ?column? | ?column? -+- 2007-01-01 00:00:00 | 2007-07-07 00:00:00 (1 row) Adrian, I think so, but without explicit strings. The dates and intervals are in the table, and I want the rows that meet the specified conditions. Is the following closer to correct? SELECT ... FROM Permits WHERE (date_issued::DATE + INTERVAL term) (CURRENT_DATE + INTERVAL process_time + INTERVAL '2 week') Thanks, Rich If term and process_time are stored as intervals then it will work. Also if they are stored as INTERVALS you can do CURRENT_DATE+process_time. In other words not have to declare the INTERVAL . Is date_issued stored as a date? If so it would not need to be cast. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Date Math
On Monday 07 May 2007 10:56 am, Rich Shepard wrote: On Mon, 7 May 2007, Adrian Klaver wrote: If term and process_time are stored as intervals then it will work. Also if they are stored as INTERVALS you can do CURRENT_DATE+process_time. In other words not have to declare the INTERVAL . Is date_issued stored as a date? If so it would not need to be cast. Adrian, Here are the pertinent declarations in the DDL: date_issued DATE NOT NULL CONSTRAINT invalid_date CHECK (date_applied = date_issued), term SMALLINT DEFAULT 1 NOT NULL, -- in years processing_time DEFAULT 180 NOT NULL SMALLINT, -- in days Thanks, Rich With this setup you will have to use an explicit string- date_issued + INTERVAL term|| 'years'. This will involve constructing a string and passing it to INTERVAL. The alternative is to change the column types of term and processing_time to interval and store the interval period with the interval qty i.e '1 year' for term and '400 days' for processing time for example. This way the you can use the values directly without invoking INTERVAL. -- Adrian Klaver [EMAIL PROTECTED] ---(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] Date Math
On Monday 07 May 2007 12:00 pm, Tom Lane wrote: Adrian Klaver [EMAIL PROTECTED] writes: With this setup you will have to use an explicit string- date_issued + INTERVAL term|| 'years'. This will involve constructing a string and passing it to INTERVAL. No, that's a truly awful way to do it. The correct way is to use number times interval multiplication, eg date_issued + term * '1 year'::interval; This reduces to not much more than a floating-point multiply, whereas the other way involves string-forming and string-parsing. Plus you can easily use whatever multiplier you like, eg '7 days' if weeks strike your fancy. It might be that converting those columns to interval is the best answer, depending on what other processing needs to be done with them. But if Rich wants to leave them as numbers, the above is the best way to convert them to intervals on-the-fly. regards, tom lane Yea, I realized the error of my ways after hitting send. An ounce of proof reading prevents a pound of oops. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Date Math
On Monday 07 May 2007 3:19 pm, Rich Shepard wrote: On Mon, 7 May 2007, Tom Lane wrote: It might be that converting those columns to interval is the best answer, depending on what other processing needs to be done with them. But if Rich wants to leave them as numbers, the above is the best way to convert them to intervals on-the-fly. Columns 'term' and 'process_time' converted to intervals. Just to confirm my understanding of today's lesson: SELECT permit_nbr, title, date_issued, term, process_time from Permits WHERE date_issued + term) (CURRENT_DATE + process_time + '2 week'::INTERVAL); is now correct syntax and use? Thanks all, Rich Yes. -- Adrian Klaver [EMAIL PROTECTED] ---(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] cube
On Sunday 27 May 2007 2:25 pm, ABHANG RANE wrote: Hi, Can you please let me know if anyone has used cube.c file in postgresql. I have installed the contrib modules required, but I just need to know how to implement cube operator on a table. Thanks Abhang ---(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 I found instructions in ~/contrib/cube/README.cube -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plpython and error catching
On Monday 19 November 2007 10:37 am, Sean Davis wrote:
What is the expected behavior of a construct like this:
def insert_xml(elem):
id=int(elem.findtext('PMID'))
try:
plpy.execute(plan,[unicode(ET.tostring(elem)),id])
except:
plpy.execute(plan2,[unicode(ET.tostring(elem)),id])
id is a primary key on the table into which I am inserting. plan is
the execute plan for inserting new data. plan2 is for updating data
already in the database. When I run this, I am not able to catch
errors of this type:
WARNING: plpython: in function insert_medline_file:
DETAIL: plpy.Error: Unknown error in PLy_spi_execute_plan
ERROR: duplicate key value violates unique constraint medlinexml_pkey
CONTEXT: SQL statement insert into medlinexml(content,id) values
(xmlparse(CONTENT $1),$2)
Why am I not able to catch this error and execute the update plan?
The manual (8.3beta2) implies that errors generated in functions are
genuine python errors that I should be able to catch.
Thanks,
Sean
You might want to take another look at the manual. It says an exception can be
raised from within the function and passed to the calling query by, unless
caught, by using plpy.error or plpy.fatal. In other words the opposite of
what you are counting on.
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgres schema printer
On Sunday 25 November 2007 1:32 pm, Dave Potts wrote: Is there such a thing as an opensource schema printer for postgres ? Dave. See Autodoc at: http://www.rbt.ca/autodoc/ -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] \copy ... with null as '' csv doesn't get nulls
On Thursday 29 November 2007 2:40 pm, Ivan Sergio Borgonovo wrote: On Tue, 27 Nov 2007 21:12:00 -0500 Lew [EMAIL PROTECTED] wrote: Lew wrote: Try eliminating the double quotes in the CSV file. Wannabe NULL would then be ,, (consecutive commas) in the CSV. From the docs, you don't even need the NULL AS clause in your COPY statement. Ivan Sergio Borgonovo wrote: Exactly what I did because fortunately there weren't too many chances of weird stuff in 2000 records (sed -e 's/,/,/g'). And this worked, right? right and I call it pre-processing. I thought it may be missing total support of UTF-8 or if I did something wrong or it is actually a feature. This clearly has nothing to do with UTF-8, and everything to with comma-comma representing a NULL and comma-quote-quote-comma representing an empty string. OK... misinterpreted. I thought that NULL AS '' means ,'',[1] so that empty strings could be imported as NULL if necessary and as empty string if not. So at my understanding there is no way to use \copy and insert NULL when it encounter an empty string and NULL AS '' doesn't do anything in CSV mode since when I have ,, it actually imports NULL and when I have ,'', it imports empty strings that is the same behaviour I get without NULL AS ''. Correct? If it is I found the documentation a bit misleading. I admit it could be due to not being English mother tongue. thx [1] I did try with '', '', '\\'... I tried this with 8.2 on a test case. To get it to work I needed to escape the quotes: \copy tablename from 'filename.csv' WITH NULL as E'\'\'' CSV HEADER This uses the new escape string syntax in 8.2. With versions before I believe the following would work: \copy tablename from 'filename.csv' WITH NULL as '\'\'' CSV HEADER -- Adrian Klaver [EMAIL PROTECTED] ---(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] log_line_prefix='%t %u %d %h %p %i %l %x ' causes error
On Friday 30 November 2007 2:31 am, Andrus wrote: That works fine for me... are you sure log_line_prefix is line 482 in your config file? You might have inadvertently put a superfluous % somewhere else. I use the config file below. I have added only some lines to the end of file, all other contents is from windows installer created conf file. If I remove # sign in front of last line (line 482), and reload configuration, I got syntax error in log file. Is this Postgres bug ? Andrus. # - What to Log - log_line_prefix = '%t ' # Special values: #-- - # CUSTOMIZED OPTIONS #-- - #custom_variable_classes = '' # list of custom variable class names listen_addresses = '*' log_destination = 'stderr' redirect_stderr = on stats_start_collector = on stats_row_level = on autovacuum = on shared_buffers= 15000 # kui on 1 GB opmälu log_min_duration_statement = 2 # millisekundites, -1 on keelatud, 0 logib kõik #log_line_prefix='%t %u %d %h %p %i %l %x ' You have log_line_prefix in two locations, under What to log and CUSTOMIZED OPTIONS. I would suggest keeping it under What to log. -- Adrian Klaver [EMAIL PROTECTED] ---(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] relation deleted while in use encountered with Postgresql 8.0.8
On Thursday 29 November 2007 2:44 pm, Gautam Sampathkumar wrote: Hi, I'm using a python script w/ the PyGreSQL library to insert 1 billion rows into a database table for an experiment (performing a commit every 10K rows). My script failed at about 170M rows with the following exception: File /usr/lib64/python2.3/site-packages/pgdb.py, line 163, in execute self.executemany(operation, (params,)) File /usr/lib64/python2.3/site-packages/pgdb.py, line 185, in executemany raise DatabaseError, error '%s' in '%s' % ( msg, sql ) pg.DatabaseError: error 'ERROR: relation 184577 deleted while still in use ' in 'INSERT INTO nfs_files_10 (mxid, fhInode, fhGen, fhSnapId, fhFlags, name, parentInode, parentGen, parentSnapId, parentFlags, extension, type, atime, mtime, fileSize, owner, generation) VALUES (10, 120, 927370846, 0, 0, 'gummy0.txt', 1204041, 927370729, 0, 0, 'txt', 0, 1112147234, 1112147234, 40960, NULL, 2);' After this error, my database table no longer exists and appeared to have been dropped, although my script was doing only INSERT statements. Any ideas on what might be causing this and/or if this a known issue and possible solutions would be greatly appreciated. thanks, Gautam Is it possible to show the python script? Also were you using the logging functions in Postgres? If so what does the log file show? Was another application/person accessing the database at the same time? -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Better alternative for Primary Key then serial??
On Tuesday 11 December 2007 9:42 pm, pilzner wrote:
Hi - I'm new to PostGres, but have used MSSQL for about a year. I'm going
through the documentation, but after reading about serials have a lot of
worries about keeping referential integrity in place and other things.
Specifically, here are a few scenarios:
a.)
CREATE TABLE TestTable (
TestID SERIAL NOT NULL PRIMARY KEY,
TestData varchar(20) NOT NULL
);
INSERT INTO TestTable(TestData) VALUES ('Data1');
INSERT INTO TestTable(TestData) VALUES ('Data2');
INSERT INTO TestTable(TestData) VALUES ('Data3');
UPDATE TestTable SET TestID = 10 WHERE TestID = 1;
Ok, red flag for me right here. The above works just fine. Granted, if
another table referenced the row w/ TestID = 1, it should violate foreign
key constraints and error out. However, with the use of serial, this is
going to run into another problem, down the road right?? Demonstrated here:
b.)
CREATE TABLE TestTable2 (
TestID SERIAL NOT NULL PRIMARY KEY,
TestData varchar(20) NOT NULL
);
INSERT INTO TestTable2(TestID, TestData) VALUES (1, 'DataData');
INSERT INTO TestTable(TestData) VALUES ('NextData');
--duplicate key violation occurs
INSERT INTO TestTable(TestData) VALUES ('NextData');
--Works fine
To phrase what happens, the next number from serial is '1', but that number
was already explicitly entered. The next call works, because the next
serial number is '2'. Ideally, the first insert would -never- happen and
TestID wouldn't ever be explicitly given a value, but if it were, its a
guaranteed error down the road some ways.
Does stuff like this cause any aches and pains to developers out there, or
do I just need to get in a new mindset??? Also, is there a way to be sure
the primary key is *ONLY* ever given a value by serial, and not subject to
updates???
Thanks,
Each table that has SERIAL column created gets its own sequence, so there will
be no conflict between tables. That case would only arise if you assigned the
same sequence to multiple tables using DEFAULT nextval(some_sequence) and
mixed manual updating of the sequence and auto updating. By default a
sequence will always increment forward so you will have a fresh number for
the next request. This means a sequence can have holes as it increments even
if a transaction fails. You can create a duplicate key violation within a
single table by manualling entering a SERIAL id that was already generated.
This applies to any PRIMARY KEY and is sort of the point. The best thing to
do is let the SERIAL sequence work on it own. If you want to deal with
sequences you should take a look at:
http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html
As to preventing updates. You have a couple of choices.
1)Do not let that field be changed by the user. I usually in either hid the
field or prevented data entry on that field.
2) Create an ON UPDATE TRIGGER that does what you want with the field.
--
Adrian Klaver
[EMAIL PROTECTED]
---(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] mssql migration and boolean to integer problems
On Wednesday 12 December 2007 8:09 pm, robert wrote: Hi all, I've spent the last few days hacking a mssql INSERT script to work with 8.1.9 - I could build the latest postgres source if need be. My latest problem is: ERROR: column includeScenario is of type boolean but expression is of type integer HINT: You will need to rewrite or cast the expression. So mssql uses tiny int for booleans, and I have about 50 of those ;-) . I googled alot on this, and tried 4 or 5 different ideas with Functions and alter tables - but I can't find anything that's working with 8.1.9, can someone please help me? In 8.2 there is a built in int -- bool cast. I had a similiar problem with 8.0 and I created my own int::bool cast using the following: CREATE CAST (int4 AS bool) WITH FUNCTION bool(int4) AS ASSIGNMENT; CREATE OR REPLACE FUNCTION bool(int4) RETURNS bool AS $Body$ Declare output char(1); Begin Select into output $1; Return output; End; $Body$ LANGUAGE 'plpgsql' VOLATILE; This way I did not have to include the casting in SQL statements. Be aware that if you upgrade to 8.2 the restore process will weed out the above because of the builtin cast. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Finding bad bye in invalid byte sequence error
On Thursday 13 December 2007 10:38 am, robert wrote: Hi all, I'm trying to hack my inserts script from mssql to work with postgres 8.1.9 - I can upgrade if need be. I'm getting this error: psql -h localhost atdev fuk2.sql ERROR: invalid byte sequence for encoding UTF8: 0xe1204f HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. I tried: /var/lib/pgsql recode ascii..utf8 fuk2.sql recode: fuk2.sql failed: Invalid input in step `ANSI_X3.4-1968..UTF-8' And also dos2unix , but nothing is working. 0xe1204f looks like a hex address, and I'm trying hexdump to find what its complaining about, but that's not helping either. Any ideas? Robert I had a similiar problem and found I had to set the client encoding as follows: SET client_encoding = 'windows-1252'; at the top of my import file. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] spreadsheet interface
-- Original message -- From: hjenkins [EMAIL PROTECTED] Some people in my workplace are asking if there exists a GUI that will allow cut-and-paste of multiple cells directly from (and, preferably, directly to) spreadsheets. pgAdmin III, PGAccess, and TOra don't seem to. Any suggestions? OpenOffice. Set up a datasource using the Base component. You can drag and drop to the spreadsheet component. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] spreadsheet interface
On Monday 17 December 2007 1:47 pm, Bill Moran wrote: In response to hjenkins [EMAIL PROTECTED]: Some people in my workplace are asking if there exists a GUI that will allow cut-and-paste of multiple cells directly from (and, preferably, directly to) spreadsheets. pgAdmin III, PGAccess, and TOra don't seem to. Any suggestions? To add on to Thomas' comment. You can also install OpenOffice.org with the pgsql ODBC driver and use the OOo spreadsheet to access data directly. I haven't done this, personally, so I can't vouch for how well it works. For the record I have luck using OO with either the Postgres JDBC driver or the native SDBC driver. I tried the ODBC driver with not too much success. -- Adrian Klaver [EMAIL PROTECTED] ---(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] spreadsheet interface
On Monday 17 December 2007 3:10 pm, hjenkins wrote: Hello, all, Dave Page writes: pgAdmin will copy arbitrary blocks of cells to spreadsheets, and will accept rows pasted into the edit grid. It should work out of the box with Excel and OpenOffice - if not, check the copy delimiter/quoting options under file-options. That sounds perfect, but I can't get it to work, even with OO. I can't find any copy delimiter/quoting options under file-options. When you say pgAdmin does work with a little effort, Mr. Hart, do you mean that I can set it up so that one can cut-and-paste multiple cells, as Mr. Page is describing, or that there is a work-around which requires more effort per-use than cut-and-paste? Using OpenOffice: OO driver: http://dba.openoffice.org/drivers/postgresql/index.html JDBC driver: http://jdbc.postgresql.org/ ODBC driver: http://pgfoundry.org/projects/psqlodbc/ and others Since the JDBC driver is in Gentoo-portage, I tried that, but OpenOffice can't load org.postgresql.Driver as the JDBC Driver class... If I use this, I'll let you know how it goes. You need to set up OpenOffice. Do following: 1) From Menu bar Tools -- Options 2) Click on OpenOffice.org -- Java 3) Make sure a Java Runtime Environment is set up 4) Click on the Class Path button 5) Click on Add Archive and browse for location of the Postgres JDBC jar file. 6) Back out of menus 7) Restart OpenOffice I'm too... experienced with Access (several days worth)... to want to use it, personally, but it might work for this application. I'll have a look. I've been using Python scripts to generate the COPY functions and piping them to psql -f. I doubt, though, that my co-workers will be keen on doing this each time they want to move three cells into a report, especially since they have neither Python nor a UNIX shell. Belatedly, I've found a similar if outdated post at http://lists.suse.com/archive/suse-slox-e/2003-Aug/0149.html but it's been taken down; only a cached version. Thanks for all the replies. Honestly, you have to hover over your inbox on this mailing list, it pounces so quickly. Regards, H.Jenkins -- Adrian Klaver [EMAIL PROTECTED] ---(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] After Installing a Program I get this error: psql:sql/Pg-database.sql:825: ERROR: language plpgsql does not exist
On Wednesday 09 January 2008 3:02 am, Robin-Vossen wrote: CREATE INDEX psql:sql/Pg-database.sql:825: ERROR: language plpgsql does not exist HINT: You need to use createlang to load the language into the database. snip So, I wonder what is the best and quickest way to fix this Flaw? Thanks, Robin The solution is in the HINT:. The language needs to loaded with createlang. plpgsql is shipped with Postgres it is not, however, loaded by default. -- Adrian Klaver [EMAIL PROTECTED] ---(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] temp sequence
On Saturday 02 February 2008 10:39 pm, Sim Zacks wrote: PostgreSQL 8.2.4 on i386-pc-linux-gnu, compiled by GCC i386-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1) I am creating a temporary sequence in a function and it seems like it is not going away after the function finishes. The front end is in MS Access 2000 and I have a single connection. When I call the function once it works, when I call it a second time, it gives me an error that the sequence already exists. When I restart the application, I can call the function again. I solved the problem by using: alter sequence seq_linenum restart with 1; The manual states: If specified, the sequence object is created only for this session, and is automatically dropped on session exit. Existing permanent sequences with the same name are not visible (in this session) while the temporary sequence exists, unless they are referenced with schema-qualified names. I thought that a function would be considered its own session, is that incorrect? The connection is the session. The function is the transaction. If you are going to maintain the connection you will need to drop the sequence inside the function after using it. Thank you Sim ---(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 -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How can I get the first and last date of a week, based on the week number and the year
On Tuesday 26 February 2008 5:32 pm, Bruno Baguette wrote:
Hello !
I have a week number (ISO 8601) and a year, based on theses values, I
would like to get the first and the last dates of that week.
How I can do that ?
The only solution is doing a big generate_series to build a subset that
contains the week of all the dates between the 01/01 || year and the
31/12 || year. But I find that solution quite dirty and ressources
consumming.
Is there a cleanest way to do that ?
Many thanks in advance !
Regards,
This will get you to the Monday of the week .
select to_date('9 08','IW YY');
to_date
2008-02-25
(1 row)
Where 9 is the ISO week number and 08 is the year. See below for more details:
http://www.postgresql.org/docs/8.2/interactive/functions-formatting.html
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] How can I get the first and last date of a week, based on the week number and the year
On Tuesday 26 February 2008 8:39 pm, Tom Lane wrote:
Adrian Klaver [EMAIL PROTECTED] writes:
On Tuesday 26 February 2008 5:32 pm, Bruno Baguette wrote:
I have a week number (ISO 8601) and a year, based on theses values, I
would like to get the first and the last dates of that week.
This will get you to the Monday of the week .
select to_date('9 08','IW YY');
to_date
2008-02-25
(1 row)
date_trunc('week', ...) would probably be a more convenient way of doing
that.
regards, tom lane
I am not sure I follow. The OP has a ISO week number and a year and wants
dates. I thought date_trunc('field',source) requires a timestamp or interval
as its source.
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] [SQL] Documenting a DB schema
-- Original message -- From: Shahaf Abileah [EMAIL PROTECTED] I'm looking for a systematic way to document the schema for the database behind our website (www.redfin.com http://www.redfin.com/ ), so that the developers using this database have a better idea what all the tables and columns mean and what data to expect. Any recommendations? It would be great if the documentation could be kept as close to the code as possible - that way we stand a chance of keeping it up to date. So, in the same way that Java docs go right there on top of the class or method definitions, it would be great if I could attach my comments to the table definitions. It looks like MySQL has that kind of capability: create table table_with_comments(a int comment 'this is column a...'); (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) However, Postgres doesn't support the comment keyword. Is there an alternative? Thanks, --S See: http://www.postgresql.org/docs/8.2/interactive/sql-comment.html -- Adrian Klaver [EMAIL PROTECTED] ---BeginMessage--- Im looking for a systematic way to document the schema for the database behind our website (www.redfin.com), so that the developers using this database have a better idea what all the tables and columns mean and what data to expect. Any recommendations? It would be great if the documentation could be kept as close to the code as possible that way we stand a chance of keeping it up to date. So, in the same way that Java docs go right there on top of the class or method definitions, it would be great if I could attach my comments to the table definitions. It looks like MySQL has that kind of capability: create table table_with_comments(a int comment 'this is column a...'); (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) However, Postgres doesnt support the comment keyword. Is there an alternative? Thanks, --S Shahaf Abileah|Lead Software Developer [EMAIL PROTECTED] | tel: 206.859.2869 | fax: 877.733.3469 Redfin Corporation 710 2nd Ave Suite 600 Seattle, WA 98104 ---End Message--- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
On Thursday 13 March 2008 4:49 am, David Gagnon wrote: Hi all, I think the title says everything:-) I just what a way to create a TEMP for the current transaction only. If possible I don't want to create the TEMP table first, specify all column types, etc. CREATE TEMP TABLE _T_CR1 AS SELECT CRNUM, CRYPNUM, CRMONT, CRDATE, GLNUM, CRRRNUM, CRACNUM, GLDESC FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND CR.CRYPNUM = CS.CSYPNUM INNER JOIN GL ON CS.CSGLNUM = GL.GLNUM AND 1 = GL.GLSOCTRL WHERE CRYPNUM = companyId AND DATEDIFF(DY, CRDATE, GETDATE()) = _AGELIMITE ON COMMIT DROP; Thanks for your help David I am missing something here. What is wrong with the above statement? -- Adrian Klaver [EMAIL PROTECTED] -- 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] Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
On Thursday 13 March 2008 2:46 pm, Colin Wetherbee wrote: Adrian Klaver wrote: On Thursday 13 March 2008 4:49 am, David Gagnon wrote: Hi all, I think the title says everything:-) I just what a way to create a TEMP for the current transaction only. If possible I don't want to create the TEMP table first, specify all column types, etc. CREATE TEMP TABLE _T_CR1 AS SELECT CRNUM, CRYPNUM, CRMONT, CRDATE, GLNUM, CRRRNUM, CRACNUM, GLDESC FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND CR.CRYPNUM = CS.CSYPNUM INNER JOIN GL ON CS.CSGLNUM = GL.GLNUM AND 1 = GL.GLSOCTRL WHERE CRYPNUM = companyId AND DATEDIFF(DY, CRDATE, GETDATE()) = _AGELIMITE ON COMMIT DROP; I am missing something here. What is wrong with the above statement? You're missing: cww=# BEGIN; BEGIN cww=# CREATE TEMP TABLE foo AS (SELECT 1 AS a, 2 AS b) ON COMMIT DROP; ERROR: syntax error at or near ON at character 50 LINE 1: CREATE TEMP TABLE foo AS (SELECT 1 AS a, 2 AS b) ON COMMIT D... ^ Colin Try CREATE TEMP TABLE foo ON COMMIT DROP AS (SELECT 1 AS a, 2 AS b) ; -- Adrian Klaver [EMAIL PROTECTED] -- 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] postgre vs MySQL
On Thursday 13 March 2008 5:36 am, rrahul wrote: Thanks to all you wonderful people out their. I don't know if its your love for Postgres or nepothism that makes it look far superior than mysql. But why does the client list dosen't tell that? I see Mysql bosting for Google,Yahoo, Alcatel.. What about Postgres the list is not that impressive. There is an old saying If 'everybody' else is jumping off a cliff should you too? Years ago I played around with MySQL because that was what everybody was using. The problem was it did not do what I wanted and Postgres did. Be less concerned with marketing lists and more concerned with what the software can help you do. Draw up a list of things you need in a database and then use the previous answers to decide which database better serves your needs. Are their any major implementations that moved from Mysql to Postgres? Howmany out their have done this or will advice to do that? cheers, Rahul. -- View this message in context: http://www.nabble.com/postgre-vs-MySQL-tp15976517p16024988.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Adrian Klaver [EMAIL PROTECTED] -- 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] GRANT giving error on sequence in 7.4.19
On Friday 14 March 2008 4:19 am, Kakoli Sen wrote: Hello all, I'm giving the query GRANT ALL PRIVILEGES ON SEQUENCE object_seq TO tester; ERROR: syntax error at or near object_seq at character 34. \ds is listing out the sequence. Regards, Kakoli Try: GRANT ALL PRIVILEGES ON object_seq TO tester; There is no SEQUENCE keyword in the GRANT command in 7.4 and a SEQUENCE is basically a one row table. -- Adrian Klaver [EMAIL PROTECTED] -- 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] Loging of postgres requests
On Saturday 15 March 2008 2:29 pm, Dave Potts wrote: I am using a 3rd front end to generate postgres requests , its reportng an error with the database. Is there anyway of loging which sql requests the application is actual sending to postgres. I need to known if the error is being created by the application generating invalid SQL or if there is a problem with the desgin of the database tables. Dave. See: http://www.postgresql.org/docs/8.2/interactive/runtime-config-logging.html In particular you will want to set log_statement to 'all' to see the SQL. -- Adrian Klaver [EMAIL PROTECTED] -- 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] Updating
On Sunday 16 March 2008 3:32 pm, Bob Pawley wrote: Is there a method available for triggering a function after an update on a particular column in a table? The only way that I have found is to trigger after an update on the whole table, which of course can lead to problems. Bob I trigger can be constrained to fire for each row. Inside the trigger function you can test to see if the column in question has been updated and do the appropriate thing. If the column has not been changed do nothing and RETURN NEW which makes the function non-op. -- Adrian Klaver [EMAIL PROTECTED] -- 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] Updating
On Sunday 16 March 2008 5:36 pm, Bob Pawley wrote: Would it be possible to get an example of such coding?? Bob - Original Message - From: Adrian Klaver [EMAIL PROTECTED] To: pgsql-general@postgresql.org Cc: Bob Pawley [EMAIL PROTECTED] Sent: Sunday, March 16, 2008 5:14 PM Subject: Re: [GENERAL] Updating On Sunday 16 March 2008 3:32 pm, Bob Pawley wrote: Is there a method available for triggering a function after an update on a particular column in a table? The only way that I have found is to trigger after an update on the whole table, which of course can lead to problems. Bob I trigger can be constrained to fire for each row. Inside the trigger function you can test to see if the column in question has been updated and do the appropriate thing. If the column has not been changed do nothing and RETURN NEW which makes the function non-op. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general CREATE FUNCTION foo() RETURNS trigger AS $Body$ BEGIN IF NEW.colname != OLD.colname THEN ...Do something..; RETURN whatever; ELSE RETURN NEW: END IF; END; $Body$ LANGUAGE plpgsql; CREATE TRIGGER foo_test BEFORE UPDATE ON foo_table FOR EACH ROW EXECUTE PROCEDURE foo(); -- Adrian Klaver [EMAIL PROTECTED] -- 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] Updating
On Monday 17 March 2008 4:54 am, Daniel Verite wrote: Adrian Klaver wrote: CREATE FUNCTION foo() RETURNS trigger AS $Body$ BEGIN IF NEW.colname != OLD.colname THEN ...Do something..; RETURN whatever; ELSE RETURN NEW: END IF; END; $Body$ LANGUAGE plpgsql; Beware that the Do something code path will not be taken when the column goes from NULL to non-NULL or non-NULL to NULL. In the general case where the column is nullable, better use IS DISTINCT FROM instead of inequality: IF NEW.colname IS DISTINCT FROM OLD.colname Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org Thanks for the heads up. This is a case I usually only remember when I start testing the function. -- Adrian Klaver [EMAIL PROTECTED] -- 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] Row size overhead
On Thursday 20 March 2008 7:24 am, Zubkovsky, Sergey wrote: Thanks for your reply. I had used PG 8.3.1 on 32-bit WinXP platform. PostgreSQL 8.3.1, compiled by Visual C++ build 1400 But I suppose this fact doesn't change anything essentially. Thanks, Sergey Zubkovsky What you are probably seeing is row depth not row width. Postgres uses MVCC and so there can be multiple versions of a row in existence at one time. For a better explanation see: http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html Try running Vacuum and/or Vacuum Full and see what happens to table size. -Original Message- From: Pavan Deolasee [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2008 8:23 PM To: Zubkovsky, Sergey Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Row size overhead 2008/3/19 Zubkovsky, Sergey [EMAIL PROTECTED]: Simple calculations show that each row occupies 76 bytes approximately. But anticipated row size would be 41 or near. You haven't mentioned PG version. For 8.2 onwards, the tuple header is 23 bytes. Add another 4 bytes for one line pointer for each row. If you have null values, another 5 bytes for null bitmap and alignment. Plus add few bytes for page header and any unusable space in a page (because a row can not fit in the remaining space). Also ISTM that you might be loosing some space because of alignment in the tuple itself. Try moving booleans and char(3) at the end. There is not much you can do with other overheads. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Adrian Klaver [EMAIL PROTECTED] -- 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
On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote: I have two tables - p_id.association and p_id.devices If a new row in p_id.association has a value - say 2 in column mon_function and a value 5 in column monitoring_fluid I want the new value for mon_function inserted into table p_id.devices ONLY when 2 AND 5 do not appear in the same row in p_id.devices. The following gives me a return of more than one row and I can't figure out what's wrong. First could you send the actual CREATE FUNCTION statement. I will assume you are using pl/pgsql. Second I am assuming this is a trigger function, so the CREATE TRIGGER statement would be useful. Any thoughts would be appreciated. Bob Declare xmon_function varchar; Begin Select mon_function into xmon_function From p_id.association Where mon_function = new.mon_function; If this is a trigger function, the above is redundant. Just use new.mon_function. If xmon_function = p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid = p_id.devices.fluid_id or p_id.association.monitoring_fluid = p_id.devices.pipe_id Then You can't do this. You would need to do something along lines of SELECT p_id.devices.device_number INTO dev_no FROM ... and then IF new.mon_function = dev_no THEN Return Null; Elseif xmon_function = p_id.devices.device_number or xmon_function != p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid != p_id.devices.fluid_id or p_id.association.monitoring_fluid != p_id.devices.pipe_id See above. Then Insert into p_id.devices (device_number) Values (New.mon_function) ; Return Null; END if; End; - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver [EMAIL PROTECTED] - 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
On Sunday 23 March 2008 2:40 pm, Bob Pawley wrote: CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$ Declare xmon_function varchar; Begin Select mon_function into xmon_function From p_id.association Where mon_function = new.mon_function; If xmon_function = p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid = p_id.devices.fluid_id or p_id.association.monitoring_fluid = p_id.devices.pipe_id Then Return Null; Elseif xmon_function = p_id.devices.device_number or xmon_function != p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid != p_id.devices.fluid_id or p_id.association.monitoring_fluid != p_id.devices.pipe_id Then Insert into p_id.devices (device_number) Values (New.mon_function) ; Return Null; END if; End; $$ LANGUAGE plpgsql; create trigger monitorinstall after update on p_id.association for each row execute procedure monitor_install(); Alright I am going to need to see the schema for p_id.association and p_id.devices to sort this out. The return 'more than one row' error is most likely occurring in the IF and ELSEIF clauses. There can only be one value on each side of the comparison. - Original Message - From: Adrian Klaver [EMAIL PROTECTED] To: pgsql-general@postgresql.org Cc: Bob Pawley [EMAIL PROTECTED] Sent: Sunday, March 23, 2008 2:24 PM Subject: Re: [GENERAL] Insert On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote: I have two tables - p_id.association and p_id.devices If a new row in p_id.association has a value - say 2 in column mon_function and a value 5 in column monitoring_fluid I want the new value for mon_function inserted into table p_id.devices ONLY when 2 AND 5 do not appear in the same row in p_id.devices. The following gives me a return of more than one row and I can't figure out what's wrong. First could you send the actual CREATE FUNCTION statement. I will assume you are using pl/pgsql. Second I am assuming this is a trigger function, so the CREATE TRIGGER statement would be useful. Any thoughts would be appreciated. Bob Declare xmon_function varchar; Begin Select mon_function into xmon_function From p_id.association Where mon_function = new.mon_function; If this is a trigger function, the above is redundant. Just use new.mon_function. If xmon_function = p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid = p_id.devices.fluid_id or p_id.association.monitoring_fluid = p_id.devices.pipe_id Then You can't do this. You would need to do something along lines of SELECT p_id.devices.device_number INTO dev_no FROM ... and then IF new.mon_function = dev_no THEN Return Null; Elseif xmon_function = p_id.devices.device_number or xmon_function != p_id.devices.device_number From p_id.association, p_id.devices Where p_id.association.monitoring_fluid != p_id.devices.fluid_id or p_id.association.monitoring_fluid != p_id.devices.pipe_id See above. Then Insert into p_id.devices (device_number) Values (New.mon_function) ; Return Null; END if; End; - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver [EMAIL PROTECTED] - 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 -- Adrian Klaver [EMAIL PROTECTED] - 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
On Sunday 23 March 2008 3:04 pm, Bob Pawley wrote:
Is this what you need??
Bob
CREATE TABLE p_id.association
(
monitor integer,
devices_id integer NOT NULL,
mon_function integer,
monitoring_fluid integer,
ps_open character varying(5),
ps_closed character varying(5),
CONSTRAINT association_pkey PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.association OWNER TO postgres;
CREATE TABLE p_id.devices
(
p_id_id integer,
devices_id integer NOT NULL DEFAULT
nextval('devices_devices_id_seq'::regclass),
fluid_id integer,
pipe_id integer,
tag_number character varying(100),
device_number integer,
idw_deviceid integer,
sump integer,
CONSTRAINT devices_pk PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.devices OWNER TO postgres;
Yes. Some further questions. How are association and devices related? I see
devices_id in both so is one a Foreign Key to the other? I ask because in
your function you relate them via association.mon_fluid=devices.device_number
as well as
p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
I am not quite sure I understand monitoring_fluid=pipe_id.
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, March 23, 2008 2:58 PM
Subject: Re: [GENERAL] Insert
On Sunday 23 March 2008 2:40 pm, Bob Pawley wrote:
CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid != p_id.devices.pipe_id
Then
Insert into p_id.devices (device_number) Values (New.mon_function) ;
Return Null;
END if;
End;
$$ LANGUAGE plpgsql;
create trigger monitorinstall after update on p_id.association
for each row execute procedure monitor_install();
Alright I am going to need to see the schema for p_id.association and
p_id.devices to sort this out. The return 'more than one row' error is
most
likely occurring in the IF and ELSEIF clauses. There can only be one
value on
each side of the comparison.
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, March 23, 2008 2:24 PM
Subject: Re: [GENERAL] Insert
On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote:
I have two tables - p_id.association and p_id.devices
If a new row in p_id.association has a value - say 2 in column
mon_function
and a value 5 in column monitoring_fluid I want the new value for
mon_function inserted into table p_id.devices ONLY when 2 AND 5 do
not appear in the same row in p_id.devices.
The following gives me a return of more than one row and I can't
figure out what's wrong.
First could you send the actual CREATE FUNCTION statement. I will
assume
you
are using pl/pgsql.
Second I am assuming this is a trigger function, so the CREATE TRIGGER
statement would be useful.
Any thoughts would be appreciated.
Bob
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If this is a trigger function, the above is redundant. Just use
new.mon_function.
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
You can't do this. You would need to do something along lines of
SELECT p_id.devices.device_number INTO dev_no FROM ... and
then IF new.mon_function = dev_no THEN
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid != p_id.devices.pipe_id
See above.
Then
Insert into p_id.devices (device_number) Values (New.mon_function) ;
Return Null;
END if;
End;
-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
Re: [GENERAL] Insert
On Sunday 23 March 2008 3:04 pm, Bob Pawley wrote:
Is this what you need??
Bob
CREATE TABLE p_id.association
(
monitor integer,
devices_id integer NOT NULL,
mon_function integer,
monitoring_fluid integer,
ps_open character varying(5),
ps_closed character varying(5),
CONSTRAINT association_pkey PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.association OWNER TO postgres;
CREATE TABLE p_id.devices
(
p_id_id integer,
devices_id integer NOT NULL DEFAULT
nextval('devices_devices_id_seq'::regclass),
fluid_id integer,
pipe_id integer,
tag_number character varying(100),
device_number integer,
idw_deviceid integer,
sump integer,
CONSTRAINT devices_pk PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.devices OWNER TO postgres;
Going off what I could deduce I came up with this. It needs to be proofread
and is not tested.
CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$
DECLARE
dev_ct integer;
BEGIN
SELECT count(*)INTO dev_ct FROM p_id_p_id.devices WHERE new.mon_function =
p_id.devices.device_number AND
(p_id.association.monitoring_fluid = p_id.devices.fluid_id OR
p_id.association.monitoring_fluid = p_id.devices.pipe_id)
IF dev_ct 0 THEN
RETURN NULL;
ELSE
INSERT INTO p_id.devices (device_number) VALUES (New.mon_function) ;
END IF;
END;
$$ LANGUAGE plpgsql;
create trigger monitorinstall after update on p_id.association
for each row execute procedure monitor_install();
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, March 23, 2008 2:58 PM
Subject: Re: [GENERAL] Insert
On Sunday 23 March 2008 2:40 pm, Bob Pawley wrote:
CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid != p_id.devices.pipe_id
Then
Insert into p_id.devices (device_number) Values (New.mon_function) ;
Return Null;
END if;
End;
$$ LANGUAGE plpgsql;
create trigger monitorinstall after update on p_id.association
for each row execute procedure monitor_install();
Alright I am going to need to see the schema for p_id.association and
p_id.devices to sort this out. The return 'more than one row' error is
most
likely occurring in the IF and ELSEIF clauses. There can only be one
value on
each side of the comparison.
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, March 23, 2008 2:24 PM
Subject: Re: [GENERAL] Insert
On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote:
I have two tables - p_id.association and p_id.devices
If a new row in p_id.association has a value - say 2 in column
mon_function
and a value 5 in column monitoring_fluid I want the new value for
mon_function inserted into table p_id.devices ONLY when 2 AND 5 do
not appear in the same row in p_id.devices.
The following gives me a return of more than one row and I can't
figure out what's wrong.
First could you send the actual CREATE FUNCTION statement. I will
assume
you
are using pl/pgsql.
Second I am assuming this is a trigger function, so the CREATE TRIGGER
statement would be useful.
Any thoughts would be appreciated.
Bob
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If this is a trigger function, the above is redundant. Just use
new.mon_function.
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
You can't do this. You would need to do something along lines of
SELECT p_id.devices.device_number INTO dev_no FROM ... and
then IF new.mon_function = dev_no THEN
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid
Re: [GENERAL] date and time
On Monday 24 March 2008 7:35 am, Alain Roger wrote: Hi, i have a stored procedure (a function) in which i must generate a date/time stamp. for that i use select * from now(); and store the result into a column table. is there a easier way to do that ? i tried to store directly now(); result but without success. thx. If you want the timestamp on INSERT add DEFAULT now() to column. In a pl/pgsql function I do; new.ts_update:=now() where ts_update is the column I am updating. Be aware now() records the time at the beginning of the transaction. An alternate is clock_timestamp() which records the current time. -- Adrian Klaver [EMAIL PROTECTED] - 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] pl/pgsql and controling loops
-- Original message -- From: Alain Roger [EMAIL PROTECTED] Hi, i've read several books extract about controlling loops in postgreSQL under pl/pgsql and there is something interesting. once to do a comparison they use : IF (ret == 1) THEN ... once, IF (ret = 1) THEN... Both are correct. so are they both correct ? comming from .NET/C++ world, usually we use '==' or '!=', so i expect that correct ones are '==' and ''. moreover, when i check if the returned value ret_email from SELECT email INTO ret_email FROM tmp_newsletterreg WHERE tmp_usr_id = id_session; i want to check if it is empty or not, so i do IF(ret_email '' ) THEN... but it does not work... every time it enter in the loop, even if it is empty string. where is the problem ? Are you sure ret_email is an empty string and not a NULL value. won't work on a NULL value. You will need to use IS NULL or IS NOT NULL. thx. -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008 -- Adrian Klaver [EMAIL PROTECTED] ---BeginMessage--- Hi,ive read several books extract about controlling loops in postgreSQL under pl/pgsql and there is something interesting.once to do a comparison they use : IF (ret == 1) THEN ...once,IF (ret = 1) THEN... so are they both correct ?comming from .NET/C++ world, usually we use == or !=, so i expect that correct ones are == and .moreover, when i check if the returned value ret_email from SELECT email INTO ret_email FROM tmp_newsletterreg WHERE tmp_usr_id = id_session; i want to check if it is empty or not, so i do IF(ret_email ) THEN... but it does not work... every time it enter in the loop, even if it is empty string.where is the problem ?thx. -- AlainWindows XP SP2PostgreSQL 8.2.4 / MS SQL server 2005Apache 2.2.4PHP 5.2.4C# 2005-2008 ---End Message--- -- 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] pl/pgsql and controling loops
-- Original message -- From: Tom Lane [EMAIL PROTECTED] [EMAIL PROTECTED] (Adrian Klaver) writes: From: Alain Roger [EMAIL PROTECTED] once to do a comparison they use : IF (ret == 1) THEN ... once, IF (ret = 1) THEN... Both are correct. No they're not, as a simple test would convince you ... there is no '==' operator in SQL. regards, tom lane I stand corrected. -- Adrian Klaver [EMAIL PROTECTED] -- 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] dunction issue
-- Original message --
From: Alain Roger [EMAIL PROTECTED]
Hi,
i have a problem solving my function trouble.
this function should return an email address stored in a table
(tmp_newsletterreg) based on a number (sessions ID).
if the session id is not find it should return a string corresponding to and
error.
if the email in found but already exists into another table (users), it
should also return a string value relative the this error.
here is my stored procedure.
CREATE OR REPLACE FUNCTION cust_portal.sp_u_002(id_session character
varying)
RETURNS character varying AS
$BODY$
DECLARE
ret_email CHARACTER VARYING(512) :='';
usr_exists INTEGER := 0;
usr_exists_2 INTEGER := 0;
BEGIN
set search_path = cust_portal;
SELECT count(*) INTO usr_exists FROM tmp_newsletterreg WHERE
tmp_usr_id = id_session;
IF (usr_exists = 1) THEN
SELECT email INTO ret_email FROM tmp_newsletterreg WHERE
tmp_usr_id = id_session;
IF (ret_email IS NULL || ret_email='') THEN
See if I can do better this time. I believe your problem is here:
IF (ret_email IS NULL || ret_email='') THEN
|| is the string concatenation operator. If you are trying to test both cases
then you need to do something along lines of
IF (ret_email IS NULL ) THEN
RETURN ('-3')
ELSIF (ret_email='')
RETURN ('-3')
RETURN ('-3');
ELSE
SELECT count(*) INTO usr_exists_2 FROM users WHERE users.email=
ret_email;
IF (usr_exists_2 = 0) THEN -- first try of user to get
registered
RETURN (ret_email);
ELSE -- user already exists into users tables (several
tries to register)
RETURN ('-2');
END IF;
END IF;
ELSE
RETURN('-1');
END IF;
END;
if the session id is wrong, it works correctly.
however if the session id is correct it does not return me the email address
(even if it really exist into table tmp_newsletterreg / but not in table
users.)
so i think my eyes are tired, because i do not see an error...
thanks.
--
Alain
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
--
Adrian Klaver
[EMAIL PROTECTED]
---BeginMessage---
Hi,i have a problem solving my function trouble.this function should return an email address stored in a table (tmp_newsletterreg) based on a number (sessions ID).if the session id is not find it should return a string corresponding to and error.
if the email in found but already exists into another table (users), it should also return a string value relative the this error.here is my stored procedure.
CREATE OR REPLACE FUNCTION cust_portal.sp_u_002(id_session character varying) RETURNS character varying AS$BODY$DECLARE ret_email CHARACTER VARYING(512) :=; usr_exists INTEGER := 0;
usr_exists_2 INTEGER := 0;BEGIN set search_path = cust_portal; SELECT count(*) INTO usr_exists FROM tmp_newsletterreg WHERE tmp_usr_id = id_session; IF (usr_exists = 1) THEN SELECT email INTO ret_email FROM tmp_newsletterreg WHERE tmp_usr_id = id_session;
IF (ret_email IS NULL || ret_email=) THEN RETURN (-3); ELSE SELECT count(*) INTO usr_exists_2 FROM users WHERE users.email = ret_email; IF (usr_exists_2 = 0) THEN -- first try of user to get registered
RETURN (ret_email); ELSE -- user already exists into users tables (several tries to register)RETURN (-2); END IF; END IF; ELSE
RETURN(-1); END IF;END;if the session id is wrong, it works correctly.however if the session id is correct it does not return me the email address (even if it really exist into table tmp_newsletterreg / but not in table users.)
so i think my eyes are tired, because i do not see an error...thanks.-- AlainWindows XP SP2PostgreSQL 8.2.4 / MS SQL server 2005Apache 2.2.4PHP 5.2.4
C# 2005-2008
---End Message---
--
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] dunction issue
On Thursday 27 March 2008 3:17 pm, Sam Mason wrote:
On Thu, Mar 27, 2008 at 08:43:46PM +0100, Alain Roger wrote:
i have a problem solving my function trouble.
this function should return an email address stored in a table
(tmp_newsletterreg) based on a number (sessions ID).
if the session id is not find it should return a string corresponding to
and error.
if the email in found but already exists into another table (users), it
should also return a string value relative the this error.
This is fun isn't it!
here is my stored procedure.
And here it is in a single, unreadable, SQL statement:
SELECT CASE WHEN s.email = u.email THEN 'email already exists'
ELSE COALESCE(s.email, 'no such session') END AS msg
FROM (VALUES (1)) x(one)
LEFT JOIN (
SELECT email FROM tmp_newsletterreg
WHERE sessionid = $1) s ON TRUE
LEFT JOIN (SELECT email FROM users) u ON s.email = u.email;
Why not put a foreign key on the email column to the users table---one
less error to handle that way?
Sam
Or a simpler way to do handle my previous suggestion:
IF (ret_email IS NULL ) OR (ret_email='') THEN
RETURN ('-3')
--
Adrian Klaver
[EMAIL PROTECTED]
--
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] Using tables in other PostGreSQL database
On Monday 31 March 2008 4:20 am, Pettis, Barry wrote: Wow seems like this post took on a life of it's own. All I wanted to do was to be able to use a table that someone else has all ready created. Seems like somewhere someone mentioned a DBA ( which I'm assuming to be Database Administrator ) well as far as I know we don't have one though I wish we did. The basis of my question comes from the fact that I currently use Multiple access databases. Each database contains 1 piece of information ( information that on it's own has no relationship to other data ), but information in other databases will use items from it in it. Hence in MSAccess I LINK the tables in. Which I know is nothing more than a connection. Now I am confused. If the data has no relationship to other data why do you need to link to it? Second in your original post you said you where trying to pull data from multiple Postgres databases now you say you are using multiple Access databases. I wish I could say that I knew with certainty what schemas are or 2PC is. Would be nice if I had exposure to other databases as well. I'm sure that I'd have the same questions about MSft's SQL server. But I thank the forum here for all the input. Regards, Barry Pettis CSO Atmel Corp Project Tech http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver [EMAIL PROTECTED] -- 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] Using tables in other PostGreSQL database
-- Original message -- From: Pettis, Barry [EMAIL PROTECTED] My bad. 1. I work at a Manufacturing plant. 2. We identify different process steps using numbers along with a short and long description 3. For a part we manufacture the system identifies SOME properties of said part. Things like nuper of process steps, part name, part family, and other items. 4. Using various other properties, the total of all process steps of said part being one, I can identify what kind of metals said part uses by referencing the SHORT DESC of the process steps. Now on it's own the PROCESS STEP is what I consider an atomic data meaning that the if I look at it there is not a sub piece that it could be broken down to. So when I need to store that data I place it into it's own MSAccess Database. The part about PROCESS STEP is not consistent, see below. Is the data in an actual Access database or a Postgres table linked in to Access? However, I have a database for all of the parts my site manufactures. I have a need to be able to extract a parts full process flow so using some other tables which will identify and order the process steps I can make a process flow which lists the short or long description depending on my need. I have another database which I need to look at the inventory ( not caring what particular part just qty ) at a particular process step. Now I may want to see one desc or another. However, the fact that a part is placed in a family value of say Microcontroller or ASIC or EEPROM it doesn't have any relationship to say a particular PROCESS STEP hence my comment of no relation ( I guess I should have said tenuous relation ) instead of no relation. Now as I see it logically I have 3 different databases ( 1 for holding atomic information about a process step, 1 for being able to list a products manufacturing flow, and 1 that is an inventory ). So in my logic I either have to have the PROCESS STEP data in 2 databases or I have it in one database and other databases just LINK to it. Now with the discussion in this thread I'm beginning to see that the term LINK is kind of MSAccess unique. So yes I use multiple databases. ( 4 of which are ) 1. Lists all pats I manufacture ( includes general summary information like cycle time, number of process', number of critical process', number of metals used, etc... ) 2. Lists all process steps at my site, and indicates if it is a metal and which type, if it's a critical process or not, which functional area said process resides ) 3. Lists the substeps for a particular PROCESS STEP to determine how many internal steps there are to any given process ( this can be dependent on the part ) Not consistent with assertion that PROCESS STEP is atomic and can not be broken down further as stated above. 4. Lists IF a part has an ENGINEERING restriction to a particular tool and which tool part has to run on at a given Process Step. I guess the biggest problem is trying to describe something in terms that all can understand. On a forum like this most CONTRIBUTORS those who answer questions speak a language that the typical novice poster doesn't use or understand. So when the question is posed there is a disconnect between poster and responder. So I'm hoping that I've been able to adequately clear the air. If not it's always fun to try. I am a great believer in graphite engineering i.e putting pencil to paper and sketching out my ideas before tweaking software. You might want to spend some time doodling to work out you want before getting too much into the details. As Sam said in another post a review of terms might be worthwhile, will help in making sure you are comparing apples to apples, oranges to oranges. Access is not the best way to learn about databases as it has its own terminology that does not translate well. -Original Message- From: Adrian Klaver [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 01, 2008 8:30 AM To: pgsql-general@postgresql.org Cc: Pettis, Barry Subject: Re: [GENERAL] Using tables in other PostGreSQL database On Monday 31 March 2008 4:20 am, Pettis, Barry wrote: Wow seems like this post took on a life of it's own. All I wanted to do was to be able to use a table that someone else has all ready created. Seems like somewhere someone mentioned a DBA ( which I'm assuming to be Database Administrator ) well as far as I know we don't have one though I wish we did. The basis of my question comes from the fact that I currently use Multiple access databases. Each database contains 1 piece of information ( information that on it's own has no relationship to other data ), but information in other databases will use items from it in it. Hence in MSAccess I LINK the tables in. Which I know is nothing more than a connection. Now I am confused. If the data has no relationship
Re: [GENERAL] Multiline plpython procedure
On Monday 17 January 2005 01:54 am, Hong Yuan wrote: I entered the multilineversion of this function exactly as written here and it ran properly. This was with version 8.0 of Postgres. You might want to do a /df+ circ in psql to see if your editor is putting a space at the beginning of line 2. Hi, I am biwildered at how to create a multi-line plpython function in Postgres. When I create the function in one line like this: CREATE or REPLACE FUNCTION circ (float) RETURNS float AS 'from math import pi; return 2*pi*args[0]' LANGUAGE plpythonu; and then use SELECT circ(1) to test it, it runs well. But if I try to make the code looks better by separating it into mulitple lines, like this: CREATE or REPLACE FUNCTION circ (float) RETURNS float AS ' from math import pi return 2*pi*args[0]' LANGUAGE plpythonu; I got an error message: ERROR: plpython: could not compile function circ DETAIL: exceptions.SyntaxError: invalid syntax (line 2) How to get Postgres to accept a normal looking python function? Greetings -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Multiline plpython procedure
Actually universal newline support seems to be covered by the following PEP and is present in the version of Python(2.3) I am running. http://www.python.org/peps/pep-0278.txt I would tend to agree with Hong Yuan that the problem exists in plpythonu's handling of newlines. On Tuesday 18 January 2005 05:19 am, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: http://docs.python.org/ref/physical.html A physical line ends in whatever the current platform's convention is for terminating lines. On Unix, this is the ASCII LF (linefeed) character. On Windows, it is the ASCII sequence CR LF (return followed by linefeed). On Macintosh, it is the ASCII CR (return) character. Seems like Guido has missed a bet here: namely the case of a script generated on one platform and fed to an interpreter running on another. If I were designing it, I would say that any Python interpreter should take all three variants no matter which platform the interpreter itself is sitting on. Or is cross-platform support not a Python goal? In short, any bug report on this ought to go to the Python project. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] How to query pgsql from a BASH script ?
On Sunday 03 April 2005 07:16 am, Michelle Konzack wrote: Hello *, I am puzzeling around, how to query a postgresql from a BASH script. Generaly it must do nothing else as I recently came across this program-ShellSQL. I haven't had time to try it, just read through the documentation. It may be able to do what you want. http://www.edlsystems.com/shellsql/ -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL as a filesystem
On Monday 18 April 2005 01:42 pm, Christopher Nelson wrote:
This isn't a high-priority question.
I'm developing a hobby OS and I'm looking into file systems. I've
thought about writing my own, and that appeals, but I'm also very
interested in the database-as-a-filesystem paradigm. It would be nice
to not have to write all of the stuff that goes into the DBMS (e.g.
parsers, query schedulers, etc) myself.
So I was wondering what sort of filesystem requirements Postgre has.
For example, could I write a simple interface layer that just requests
blocks from the physical device and translate those into byte sets, or
does the DB actually require multiple files mapped by a larger file
system that maintains names, etc.
I guess my real question is how much file system support is really
required by the DBMS's disk routines. Please reply to
[EMAIL PROTECTED] since I'm not subscribed to this list. Thanks in
advance!
-={C}=-
You might be interested in the following site. It is a Python DBAPI driver
that uses the file system as a database.
http://fssdb.sourceforge.net/
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Howto prevent write based on date
On Wednesday 04 May 2005 09:35 am, Fmiser wrote: I'm a newbie to database admin, but I'm not afraid to try - but this one has me stumped. I'm using SQL-Ledger as a front end to postgresql 7.4.2. on (mostly) Debian Testing, i386. My fiscal year is over and I would _like_ to prevent any changes to the data from last year. I looked/searched in the manual, but I don't even know what to call what it is that I'm trying to do! lock has another meaning for databases. :) Write seems to bring up lots of user-related stuff. So, I'm hoping one of you geniuses can tell me where to look, what to look for, or how to do it. *smile* Thanks! Philip, wanabe-admin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org If I remember correctly SQL-Ledger has just such an option in the administrator options. This would save you from having to play around with the back end. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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] Storing Snapshot Data
On Thursday 11 December 2003 12:42 am, John Gibson wrote:
Hi, all.
I have a table which is continually updated with the latest totals. I
would like to take snapshots of some of the data in that table and store
it in a second table to run statistics on it later. What might some
ways of doing this be?
Illustrative (I hope) example using fruit-qty-on-hand at a grocery store:
Fruit_table {constantly updated by other processes}
CREATE TABLE fruit_table (
fruit_namevarchar(20),
fruit_qtyint4
);
***TABLE DATA***
fruit name fruit_qty
apple 5
orange8
pear3
monitor_table {stores snapshots of fruit table from time to time}
CREATE TABLE monitor_table (
monitor_time timestamp,
mon_apples_qtyint4,
mon_oranges_qtyint4,
mon_pears_qtyint4
);
I got the following to timestamp a single row from the fruit_table and
put the results into the monitor_table:
insert into monitor_table(monitor_time, mon_apples_qty)
select now(), fruit_table.fruit_qty
where fruit_name = 'apple';
Unfortunately, I am stuck on how to get all three into the monitor table
with the same timestamp. Since the times will be relatively long
between snapshots some type of variables or functions could be used (I
guess) to store the current time ( curr_time := now(); ) and then run
the query three times with first an insert and then two updates using
the variable time stamp on the updates to locate the record to update.
That doesn't sound very elegant to me. Please help if you have any ideas.
I am definately a newbie, so forgive me if this is trivial. Also, if
another forum would be better for this, I would appreciate a nudge in
that direction. :)
...john
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
First I would create a monitor table as follows
CREATE TABLE fruit_table_moinitor (
fruit_namevarchar(20),
fruit_qtyint4,
t_stamp timestamp
);
Then use the following transaction-
BEGIN;
INSERT INTO fruit_table_monitor(fruit_name,fruit_qty,t_stamp) SELECT
fruit_name,fruit_qty,now() from fruit_table;
COMMIT;
Calling the function now() inside a transaction locks the timestamp to the
time at the beginning of the transaction.
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: 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] windows distribution
On Monday 22 December 2003 08:16 am, Keith C. Perry wrote: I'm not familiar with the cygwin environment but is there something about that is turning off users to using it to run PG? It seems to me we should be seeing *more* ports about how PG is performing on cygwin instead of questions about a native port- especially from new users. The problem is not so much Cygwin as the platform it runs on. It is Unix emulation on Windows and therefore is only as stable as the version of Windows you are using. I have run Postgres using Cygwin on various Win9x versions. The Cygwin installer makes the process easy to do. The problem is that it doesn't take much to load down the system. Also, given the frequency with which Win9X versions crash you spend alot of time waiting for the database to come up again. All in all it was more productive to move Postgres to a dedicated Linux server. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Errors after power failure
I would suspect the manner in which it was started. Does your postresql.conf file have tcpip_socket=true?. If it is set to false and the script that starts Postgres does not use the '-o -i' switch then the database will not accept tcpip connections. Have you tried psql on the database host to see if it can accept local connections via a unix socket? On Thursday 15 January 2004 08:16 am, [EMAIL PROTECTED] wrote: Heres what is get with nmap localhost [EMAIL PROTECTED] [EMAIL PROTECTED] root]# nmap localhost Starting nmap V. 3.00 ( www.insecure.org/nmap/ ) Interesting ports on database (127.0.0.1): (The 1590 ports scanned but not shown below are in state: closed) Port State Service 21/tcp openftp 22/tcp openssh 25/tcp opensmtp 80/tcp openhttp 111/tcpopensunrpc 443/tcpopenhttps 783/tcpopenhp-alarm-mgr 1024/tcp openkdm 1025/tcp openNFS-or-IIS 6000/tcp openX11 1/tcp opensnet-sensor-mgmt Nmap run completed -- 1 IP address (1 host up) scanned in 2 seconds [EMAIL PROTECTED] root]# And I doublechecked to make sure postmaster was running Here is what is in the log file LOG: database system was shut down at 2004-01-15 11:22:01 EST LOG: checkpoint record is at 0/AF6940 LOG: redo record is at 0/AF6940; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 2676; next oid: 25169 LOG: database system is ready -Dan On Wed, 14 Jan 2004 [EMAIL PROTECTED] wrote: I have deleted the pid and restarted the postgresql and it is starting fine. It just appears to not be accepting connections. I had to rebuild the server the last time this happened. Starting to be a pain. Firewalling maybe? Check which ports are open with nmap. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Best way to represent values.
The problem is that each well can have a different number of and types of layers. Trying to pre-plan all the combinations could be a big headache. My first thought is the following layout- well_number layer_number bottom_depth layer_type 1 1 10 topsoil 1 2 25gravel and so on. The bottom_depth of one layer is the top_depth of the one below. The final bottom_depth is the depth of the well. The layer_types can be pulled from another table to maintain consistency and allow for new types as needed. Come report time you order by well_no,layer_number to get the desired information. On Monday 21 November 2005 05:29 pm, Dennis Veatch wrote: On Monday 21 November 2005 20:04, Michael Glaesemann wrote: On Nov 22, 2005, at 3:19 , Dennis Veatch wrote: I had thought just adding some fields called topsoil_start/ topsoil_end, gravel_start/gravel_end, etc. But them I'm left with how to take those values and give to total depth for each layer and total depth of the well. But I'm not sure that is the best way to handle this. Does anyone have some other suggestions? This is similar in concept to temporal intervals. You might want to look at Temporal Data and the Relational Model by Date, Darwen, and Lorentzos for general theory, and Developing Time-Oriented Database Applications by Richard Snodgrass for implementations in SQL. The latter is available as a PDF download (the book itself is out of print): http://www.cs.arizona.edu/people/rts/tdbbook.pdf Hope this helps! Hee, well that's um, kinda over my head. Hee and I'm not all the way through the PostgreSQL book I just bought. There's probably a gap there. :) Though I will try to glean something out of the link. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Numbers
On Sunday 22 January 2006 02:47 pm, Bob Pawley wrote: Hi Tom I found a datatype called 'interval' which seems to separate time from its unit. Is that what you were thinking of?? Bob - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Postgresql pgsql-general@postgresql.org Sent: Sunday, January 22, 2006 11:25 AM Subject: Re: [GENERAL] Numbers Bob Pawley [EMAIL PROTECTED] writes: My initial tables have columns containing values such as 12 feet. I want to perform calculations. Is there a method in Postgresql to separate the 12 from the unit feet or am I forced to make two columns to separate the feet from the 12? Are you storing '12 feet' as a string? That seems awfully unstructured for data that you'd like to do calculations on. I seem to recall that someone had come up with a datatype that would store numbers with units attached, which seems like what you want here. Check the PG list archives, and/or poke around on pgfoundry and gborg. regards, tom lane ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings I believe this is what Tom is talking about-- http://archives.postgresql.org/pgsql-general/2005-01/msg01646.php -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
