[GENERAL] Creating numeric type by hand?
Hi all: I have Postgres 6.5.3 installed on Linux... well, sort of. The problem is that the installation in question must be screwed up somewhere, because I can't find the "numeric" type in it (while I can find it in another clean 6.5.3 install), and now I need it for a project. My question is: what do I have to do to create the type in question by hand? I suppose that I have to connect to the "template1" database and edit the pg_* tables... but which ones? Of course, a possible solution would be to dump everything and reinstall Postgres from scratch, but I can't even do a pg_dumpall in it; it tells me: Can not create pgdump_oid table. Explanation from backend: 'ERROR: Relation 'pgdump_oid' already exists'. pg_dump failed on [db_name], exiting (If anybody can explain possible causes for this error, I'd be grateful too). Paulo Jan. DDnet.
Re: [GENERAL] using ID as a key
Marc Tardif wrote: > > I recommend you read Momjian's online book, very informative and provides > all the necessary information about using various kinds of id's: > http://www.postgresql.org/docs/aw_pgsql_book/index.html In glancing at Momjian's stuff on this just now, I noticed the book could leave the first-time reader with the impression that both OIDs and sequences (ie, SERIAL) are viable choices for use as primary keys (unintentionally?). Sequences and SERIALs are not mentioned until after a long example of how to do primary keys with OIDs. My recollection is that OIDs have fatal drawbacks in this context, and should NEVER be used as primary keys for serious databases. If that is true, the example of OIDs as primary keys should be removed from the book, or at the very least put after an example using sequences/serials. That section appears bound to lead many astray into the use of OIDs, even though there is a brief section following on OID "limitations"... Can anyone confirm/correct that notion? > As for referential integrity using foreign keys, you can add this > functionality to postgresql using triggers. An example of this is > available in the contrib/spi directory, look for refint.* As Jan Wieck (v7.0 foreign key author) pointed out to me earlier, concurrent transactions prevent 100% reliable referential integrity via triggers, at least with pl/pgsql, though you can get pretty close if you're guarding against the problem scenarios at the application level. I confess ignorance on refint.*... Cheers, Ed Loehr
[GENERAL] Tough question from a potential user.
We're a small startup company with several projects at the brink of deployment. When I say deployment, I'm talking potentially thousands/100 thousands of users with lots of concurrent connections to the database. Currently we use Oracle, but for the level we need we're talking *obscene* amounts of money to deploy. Management seems OK with proceeding with Oracle anyway, despite the huge price hit. A minority of the decision makers are considering MS SQL 7.0. Personally, I think Oracle is a waste of money and the SQL is distasteful, has poor cross-platform support, and has stability issues. My research indicates that PostgreSQL is the *only* viable alternative considering all other commercial DBs are also overpriced or also-rans, and no other 'non-commerial' DBs provide the Oracle-like features we require. The good news is that we have a great development team, and can hire more people (DBAs) to help out. This is good since I anticipate a lot of custom programming required to use Postgre: 1) We need a custom, *native* connection to Postgre from Delphi with or without the BDE - is this possible? 2) We need a pipe to integrate into MicroStrategies 3) We need an interface to Cold Fusion 4) We need an interface to Mapinfo Note that I mean native, not ODBC, interfaces. Less crucial, but still important: 1) We need Erwin - like tools to manipulate the DB 2) How scalable is Prostgre? 3) How about parallel processing/data streaming/clustering? 4) What are the hardware requirements? Any information related to *any* of these questions would be greatly appreciated. We're against a wall here, and I am the lone voice/proponent is this conflict. Feel free to email me directly, if you wish. Thanks in advance, --James *^~*^~*^~*^~*^~*^~*^~James MaxwellProject IntegratorT e l e g r a p h . n e twww.telegraph.net[EMAIL PROTECTED]561.832.6905*^~*^~*^~*^~*^~*^~*^~First they ignore you, then they laugh at you, then they fight you, then youwin. - Gandhi
[GENERAL] Re: [SQL] Re: [HACKERS] Proposed Changes to PostgreSQL
> Peter Eisentraut wrote: > > > This is a really stu^H^H^H bad idea. I have hierarchies 5 levels deep > with > multiple inheritance, and I > don't want to do a 10 way join just to retrieve an object. > > This is why RDBMS's performance sucks so incredibly badly on some > applications. > an ODBMS can perform 100x as fast in these cases just because of what > you > are proposing. > Hmm, and yes one may find problems where the pure relational system is 100x faster than your ODBMS. After doing a project with VERSANT and VisualWorks (election projection system for the first television sender here in Germany) I like the idea of OODBMS, but I've also noticed, that they are not the solution to all problems. Clever database desing leeds to good performance on both systems, but one should consider, that the designs of the database layout will be different. There are cases, where a pure relational system is very fast and an ODBMS never get it, but there are the examples you mentioned. Joins per se are not that bad .. it depends on when and how they are used and how good the analyzer of the database is and how good he uses the indices to get the job done. One very good point is the query language of the rdbms systems. On the odbms side no standard is really available, which can be seen as the sql of the odbms. Marten
Re: [GENERAL] using ID as a key
I recommend you read Momjian's online book, very informative and provides all the necessary information about using various kinds of id's: http://www.postgresql.org/docs/aw_pgsql_book/index.html As for referential integrity using foreign keys, you can add this functionality to postgresql using triggers. An example of this is available in the contrib/spi directory, look for refint.* Marc On Fri, 4 Feb 2000, sheila bel wrote: > Hi, > > I'm new to data base design so please bare with me if my > question is so basic.. > I'm designing a database, two of the tables are EMPLOYEES and > AGENCIES. I need an ID for each of them. I would also need to > have the agencyID in the EMPLOYEES table so that I can identify > which agency they belong to something like a foreign key. I know > postgreSQL does not support it so how do I implement this ? > What kind of data type should I use for the ID ? In general I > do not know how to implement IDs and use them as keys. > I've read the user manual and a bit of the programmer's > manual several times but haven't found anything that will > apply to this situation. > > Please Help. Thank You. > > -Sheila > > > > > > >
RE: [GENERAL] using ID as a key
Take a look at Bruce's book at http://www.postgresql.org/docs/awbook.html He explains oid's and sequences extremely well, and that might be what you are looking for. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of sheila bel Sent: Friday, February 04, 2000 11:41 AM To: [EMAIL PROTECTED] Subject: [GENERAL] using ID as a key Hi, I'm new to data base design so please bare with me if my question is so basic.. I'm designing a database, two of the tables are EMPLOYEES and AGENCIES. I need an ID for each of them. I would also need to have the agencyID in the EMPLOYEES table so that I can identify which agency they belong to something like a foreign key. I know postgreSQL does not support it so how do I implement this ? What kind of data type should I use for the ID ? In general I do not know how to implement IDs and use them as keys. I've read the user manual and a bit of the programmer's manual several times but haven't found anything that will apply to this situation. Please Help. Thank You. -Sheila
Re: [GENERAL] max query length
I think there is no maximum on the actual query length, but there is a limit on the max row size. If I remember correctly, there is an 8k limit to the contents of an entire row. If you need more than that, either split your data across multiple rows are use the blob data type. Marc On Fri, 4 Feb 2000, dean browett wrote: > Hi, > > Can anyone tell me whether there is a maximum query length using 'insert'? > > We want to insert a text statement into our db. This statement may be up to > 20k in size. > > Dean > > > > > > > >
Re: [GENERAL] using ID as a key
sheila bel wrote: > > Hi, > > I'm new to data base design so please bare with me if my > question is so basic.. > I'm designing a database, two of the tables are EMPLOYEES and > AGENCIES. I need an ID for each of them. I would also need to > have the agencyID in the EMPLOYEES table so that I can identify > which agency they belong to something like a foreign key. I know > postgreSQL does not support it so how do I implement this ? > What kind of data type should I use for the ID ? Sheila: checkout the SERIAL type. It is quite useful for this purpose, as in... CREATE TABLE foo ( key_id SERIAL, ... CREATE TABLE bar ( key_id SERIAL, foo_key_id INTEGER NOT NULL, -- foreign key to foo ... Lots of discussion in the archive on how to retrieve a new value for the purpose of a foreign key (keywords: SERIAL, nextval, sequence). http://www.postgresql.org/docs/postgres/datatype.htm#AEN842 Cheers, Ed Loehr
Re: [GENERAL] using ID as a key
search for oid and serial or sequence, referential or foreign key in the doc or archive of this list. there are a lot lot very good advices. On Fri, 4 Feb 2000, sheila bel wrote: > Hi, > > I'm new to data base design so please bare with me if my > question is so basic.. > I'm designing a database, two of the tables are EMPLOYEES and > AGENCIES. I need an ID for each of them. I would also need to > have the agencyID in the EMPLOYEES table so that I can identify > which agency they belong to something like a foreign key. I know > postgreSQL does not support it so how do I implement this ? > What kind of data type should I use for the ID ? In general I > do not know how to implement IDs and use them as keys. > I've read the user manual and a bit of the programmer's > manual several times but haven't found anything that will > apply to this situation. > > Please Help. Thank You. > > -Sheila > > > > > >
[GENERAL] using ID as a key
Hi, I'm new to data base design so please bare with me if my question is so basic.. I'm designing a database, two of the tables are EMPLOYEES and AGENCIES. I need an ID for each of them. I would also need to have the agencyID in the EMPLOYEES table so that I can identify which agency they belong to something like a foreign key. I know postgreSQL does not support it so how do I implement this ? What kind of data type should I use for the ID ? In general I do not know how to implement IDs and use them as keys. I've read the user manual and a bit of the programmer's manual several times but haven't found anything that will apply to this situation. Please Help. Thank You. -Sheila
Re: [GENERAL] Need help creating a function
create function money(float8) returns money as '
declare
f2 float8;
m money;
i2 int2;
i1 int4;
txt text;
begin
if $1 isnull then
return NULL;
end if;
--integer part...
i1:= dtrunc($1);
-- decimal part...
i2:= dround(datetime_part(''millisecond'',$1));
-- cut 3th digit...
txt:= dround(i2/10.0);
if textlen(txt) = 1 then
txt:= ''0'' || txt;
end if;
m:= i1 || (''.'' || txt);
return m;
end; ' language 'plpgsql';
Hitesh Patel wrote:
> Does anyone have a function laying around that convert a 'money' type to
> a float8 and return it?
>
>
--
Jose' Soares
Bologna, Italy [EMAIL PROTECTED]
[GENERAL] max query length
Hi, Can anyone tell me whether there is a maximum query length using 'insert'? We want to insert a text statement into our db. This statement may be up to 20k in size. Dean
[GENERAL] pl/pgsql and national characters
Hello All, I have a following problem. I have a tables and fields whith names, consisting of cyrillic characters and getting an error. When I am doing the same things but with only english characters, everything ok. All other things works properly, only pl/pgsql functions. Does anybody knows how to treat this? Here is an example: create function round_mark() returns opaque as ' begin NEW."" := 1; return NEW; end; ' LANGUAGE 'plpgsql'; CREATE CREATE TRIGGER marks_round_marks BEFORE INSERT OR UPDATE ON "rr" FOR EACH ROW EXECUTE PROCEDURE round_mark(); CREATE UPDATE "rr" SET ""=1 WHERE "" = 1; NOTICE: plpgsql: ERROR during compile of round_mark near line 1 ERROR: unterminated " Best regards, Yury ICQ 11831432 mailto:[EMAIL PROTECTED]
[GENERAL] pl/pgsql and national characters
Hello All, create function round_mark() returns opaque as ' begin NEW."" := 1; return NEW; end; ' LANGUAGE 'plpgsql'; CREATE CREATE TRIGGER marks_round_marks BEFORE INSERT OR UPDATE ON "rr" FOR EACH ROW EXECUTE PROCEDURE round_mark(); CREATE UPDATE "rr" SET "òò"=1 WHERE "òò" = 1; NOTICE: plpgsql: ERROR during compile of round_mark near line 1 ERROR: unterminated " Best regards, Yury ICQ 11831432 mailto:[EMAIL PROTECTED]
