[GENERAL] .mdb to Postgres ?
Hi ! One of our customers has an MS Access DB (I think..) He sent me a .mdb file. Does anyone knows a way to convert this to postgreSQL db ? thank you ! EuroVox 4, place Flix Eboue 75583 Paris Cedex 12 Tel : 01 44 67 05 05 Fax : 01 44 67 05 19 Web : http://www.eurovox.fr ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Making references
On Mon, 26 Mar 2001, bswiatek wrote: Assuming you're doing these in this order, this can't succeed. LECZENIE referenes WIZYTY which isn't created yet as do some other tables, which causes those tables not to exist later when you reference them. If you want to make circular trees of references you need to use ALTER TABLE ADD CONSTRAINT to add the foreign key constraints after the create tables. Hi Can you tell me what I've done wrong writting that base. Queries doesn't work. That's bash script code which makes tah base ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] .mdb to Postgres ?
You can use some conversion tools that can be found on www.greatbridge.org I've used PgAdmin (windows only) to convert MS Access files to PostgreSQL. Poul L. Christiansen On Tue, 27 Mar 2001, Jean-Arthur Silve wrote: Hi ! One of our customers has an MS Access DB (I think..) He sent me a .mdb file. Does anyone knows a way to convert this to postgreSQL db ? thank you ! EuroVox 4, place Flix Eboue 75583 Paris Cedex 12 Tel : 01 44 67 05 05 Fax : 01 44 67 05 19 Web : http://www.eurovox.fr ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Supertypes?
On Tue, Mar 27, 2001 at 03:58:39PM +0200, Christian Marschalek wrote: Pg DOES support it! Oh... My fault :o) However, it doesn't really have anything to do with data redundancy. Data redundancy means storing the same DATA in more than one table meaning that if it changes in one table, you have to update all of the other tables. That's not the same as inheritance which means you can use a common base_table with additions to child tables as necessary. Well we've learned that when you have lets say five tables which all contain name, adresse, city and so on you also have a form of redundancy... Can be wrong, though ;o) there's also this construct -- i don't know the name -- exemplified below by using the table ADDR (very u.s.centric, i know) as a DATATYPE in table PERSON: create table addr( street varchar(30), city varchar(30), state char(2), zip char(5) ); create table person( loc addr, -- how about them apples? phone char(10), name varchar(30) ); \d person Table "person" Attribute |Type | Modifier ---+-+-- loc | addr| phone | char(10)| name | varchar(30) | the trick, is, how do you insert data into person.addr? -- It is always hazardous to ask "Why?" in science, but it is often interesting to do so just the same. -- Isaac Asimov, 'The Genetic Code' [EMAIL PROTECTED] http://newbieDoc.sourceforge.net/ -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(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] Re: Patch (tiny): \cd (change dir) for psql.
On Tue, Mar 27, 2001 at 09:52:05AM -0500, Bruce Momjian wrote: On Mon, 26 Mar 2001 15:07:37 + (UTC), Mark Hamby [EMAIL PROTECTED] wrote: Here's a patch for a \cd (change dir) command in psql program. It's very small and only adds a few lines. Personally, I am surprised psql does not have this command. It is EXTREMELY useful when \include'ing SQL scripts to set up a database schema. It's a good idea. This is an itch I've needed scratched many times. Will be in 7.2. and with it i'd imagine we'll also need # return to previous directory, for the lazy \cd - # show current working directory \cwd \pwd of course, we could continue working the way we are now: somePsqlDb= \q % cd /some/where/over/the/rainbow % psql mydata mydata= but why? :) -- It is always hazardous to ask "Why?" in science, but it is often interesting to do so just the same. -- Isaac Asimov, 'The Genetic Code' [EMAIL PROTECTED] http://newbieDoc.sourceforge.net/ -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(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] Re: Patch (tiny): \cd (change dir) for psql.
will trillich writes: and with it i'd imagine we'll also need # return to previous directory, for the lazy \cd - # show current working directory \cwd \pwd You can do this with \!pwd (Of course \!cd won't do what you want. This situation is similar to an ftp client.) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] download PostgreSQL
On Tue, 27 Mar 2001 [EMAIL PROTECTED] wrote: Hi Could someone tell me where I can download PostgreSQL for Windows and MacOS from HTTP site other than ftp site, since the proxy server won't allow me go to the ftp site? Looking forward to your reply! Yeah, I put a link up for this. http://www.postgresql.org/ftpsite Looks like I forgot to put up a link to it on a webpage. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] RE: Supertypes?
Oh yeah we've learned about the normal forms too :) It's all flying back into my mind right now ;o) Thx! -Original Message- From: Gregory Wood [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 27, 2001 5:04 PM To: Christian Marschalek Subject: Re: Supertypes? Well we've learned that when you have lets say five tables which all contain name, adresse, city and so on you also have a form of redundancy... Can be wrong, though ;o) When dealing with relational databases, that redundancy (or the process of eliminating it) is called normalization. This usually involves breaking tables apart into smaller tables. There are several 'normal forms' which database designers strive for... each normal form is closer to the 'ideal' database, although most designers don't bother with much beyond 3rd normal form. Of course, it can be wise to break the rules for performance reasons... Anyway, I highly recommend reading up on the topic... I didn't when I first started playing with SQL and look back on those days as my little database 'Dark Ages'. Greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Two tables for the price of one?
I am seeing something kinda weird when I am creating tables (or viewing existing ones): BEGIN SESSION bash$ createdb test CREATE DATABASE bash$ psql test Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit test=# \dt No relations found. test=# CREATE TABLE mytable ( test(# id INT, test(# stuff VARCHAR(10) test(# ); CREATE test=# \dt List of relations Name | Type | Owner -+---+-- mytable | table | gmguest mytable | table | postgres (2 rows) test=# /END SESSION As you can see when I create a table it appears it is either creating it twice (not likely) or thinks that there are two instances of it. The gmguest user is a guest user for another database however any database I look at has another table for each table with this gmguest user as the owner. I found this after seeing duplicate table names while trying to access a database via odbc and MSAccess. Anyone seen this before? I imagine somewhere I screwed up a system table or something (don't remember doing it). I may just recompile and dump and resoter the tables from scratch but I'd like to avoid it if possible. Thanks. -- Patrick Aland [EMAIL PROTECTED] Network Administrator Voice: 904.822.7217 Stetson University Fax: 904.822.7367 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] C functions, arguments, and ssh oh my!
Hello All, I am writing my first C function for postgres and failing miserably. my C function needs to get passed a username (char) , uid(int), and gid(int) and what it does with those is builds a command string for an external app that is called with system() and exits 0. I know this is strange and ugly but I need to trigger this external app when an insert is made into a user table. I am looking into the version 1 calling convention to get these arguments into my C function but I can't seem to match the types correctly. I am looking at SPI with triggers now but I don't know if that will work either. Am I barking up the wrong tree here? any comments are appreciated and I appreciate the time you have taken to read my post. Below is to cludgy poor code I have so far (it doesnt work of course) and I am halfway between converting to version 1 calling but I wanted to give you an idea of what I am trying to do. Thanks again. - Joel #include stdlib.h #include "postgres.h" #include "fmgr.h" /*int *ssh_exec(char *uname[FILENAME_MAX], char *uid[FILENAME_MAX], char *gid[FILENAME_MAX])*/ PG_FUNCTION_INFO_V1(ssh_exec); Datum ssh_exec(PG_FUNCTION_ARGS) { char sshcmd[255]; strncpy(sshcmd, "/usr/local/bin/plsshexec ", 255); strncat(sshcmd, *uname, 255); strncat(sshcmd, " ", 255); strncat(sshcmd, *uid, 255); strncat(sshcmd, " ", 255); strncat(sshcmd, *gid, 255); system(sshcmd); return 0; } ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Two tables for the price of one?
Patrick Aland writes: test=# \dt No relations found. test=# CREATE TABLE mytable ( test(# id INT, test(# stuff VARCHAR(10) test(# ); CREATE test=# \dt List of relations Name | Type | Owner -+---+-- mytable | table | gmguest mytable | table | postgres (2 rows) Both of these users probably have the same user id. See pg_shadow.usesysid column. If this installation was ever subject of a backup/restore, then this might explain things. If there are indeed duplicate user ids, dropping and recreating gmguest should work. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] C functions, arguments, and ssh oh my!
* Joel Dudley [EMAIL PROTECTED] [010327 11:29] wrote: Hello All, I am writing my first C function for postgres and failing miserably. my C function needs to get passed a username (char) , uid(int), and gid(int) and right, wrong and wrong. char *, uid_t, gid_t. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] C functions, arguments, and ssh oh my!
Joel Dudley writes: PG_FUNCTION_INFO_V1(ssh_exec); Datum ssh_exec(PG_FUNCTION_ARGS) { char sshcmd[255]; strncpy(sshcmd, "/usr/local/bin/plsshexec ", 255); strncat(sshcmd, *uname, 255); strncat(sshcmd, " ", 255); strncat(sshcmd, *uid, 255); strncat(sshcmd, " ", 255); strncat(sshcmd, *gid, 255); system(sshcmd); return 0; } You missed the part about fetching the arguments using PG_GETARG_xxx. Also you should use PG_RETURN_xxx and you need to null-terminate your string. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] C functions, arguments, and ssh oh my!
Joel Dudley [EMAIL PROTECTED] writes: what it does with those is builds a command string for an external app that is called with system() and exits 0. I know this is strange and ugly but I need to trigger this external app when an insert is made into a user table. This seems an extremely dubious practice. If the transaction doing the insert is later rolled back, the insert effectively never happened --- but the effects of your external app will still be there. I'd suggest thinking twice about your whole system design, if it requires this. You can mitigate the problem a little bit by making the trigger an AFTER trigger, so that it's only fired when we are about to commit the transaction. But there's still a possibility of trouble if a later AFTER trigger decides to abort. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] how to load a sql-file????
hi, maybe this is somewhere in the docs but I couldn't find it. I am a beginner to postgresql and do not know much till now. I have bought a book on SQL and now I want to import the sample databases into postgresql they are provided as *.sql. in MySQL I can type something like in Bash on my linux box: $ mysql -u root -p file.sql then the file file.sql is read. can you tell me how to do this with postgresql??? thanks a lot. markus -- Markus Jais http://www.mjais.de [EMAIL PROTECTED] The road goes ever on and on - Bilbo Baggins ---(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] how to load a sql-file????
On Tue, Mar 27, 2001 at 09:42:39PM +, markus jais wrote: hi, maybe this is somewhere in the docs but I couldn't find it. I am a beginner to postgresql and do not know much till now. I have bought a book on SQL and now I want to import the sample databases into postgresql they are provided as *.sql. You can do "psql -f yourfile.sql" from the shell, or, from within psql, type "\i yourfile.sql". Zach -- [EMAIL PROTECTED] Zachary Beane http://www.xach.com/ ---(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] Supertypes?
I believe that what you are looking for is inheritance. http://postgresql.readysetnet.com/devel-corner/docs/user/inherit.html I hope this is helpful, Jason Earl --- Christian Marschalek [EMAIL PROTECTED] wrote: In school we've learned about supertypes. I don't know if the raw translation is correct so here is an example: Supertype human with the attributes name,age,size. Now I can derrive types from it... For example - employe with even more attributes like personal id. Can I realise this in PostgreSQL? And if yes, would anybody please explain how, or point me to the right documentation? Thanks and regards, Christian Marschalek ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Logging Queries
Is there any way to have the server log the queries that are coming in so I can see what's actually getting processed? Start the postmaster with "-d 2" on the command line. -- Alex Howansky Wankwood Associates http://www.wankwood.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Alternate data locations
Back in the old days of 6.x, the use of alternate database locations (for example, by defining $PGDATA2 wherever I wanted it to be located) didn't work. This was scheduled to be fixed in 7.x. Has it? I want to use postgres as the attribute-storing dbms for my GRASS (GIS) projects. What I'd like to do is store attribute data in each project directory, along with the spatial object files. I do want postgres to find the proper data files, so I'll need to specify the proper path. But, the question is whether or not postgres will now respond properly. TIA, Rich Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) 2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A. + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] fmgr, C , and character arguments
Hello all, I am having a bit of trouble getting my arguments formatted into a command string for system(). Here is what I have so far for code. #include string.h #include stdlib.h #include "postgres.h" #include "fmgr.h" PG_FUNCTION_INFO_V1(ssh_exec); Datum ssh_exec(PG_FUNCTION_ARGS) { char*uname = PG_GETARG_CHAR(0); char*uid = PG_GETARG_CHAR(1); char*gid = PG_GETARG_CHAR(2); strncpy(sshcmd, "/usr/local/bin/plsshexec ", 255); strncat(sshcmd, *uname, 255); strncat(sshcmd, " ", 255); strncat(sshcmd, *uid, 255); strncat(sshcmd, " ", 255); strncat(sshcmd, *gid, 255); system(sshcmd); return 0; } I am thinking that strncpy is not a bad idea after moving to the V-1 function manager interface. As I am sure you can see I am a C hack at best. what is the best way to format the string and make this code work? memcpy? Thanks for reading my post. - Joel ---(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] fmgr, C , and character arguments
On Tue, Mar 27, 2001 at 03:45:17PM -0700, Joel Dudley wrote: Hello all, I am having a bit of trouble getting my arguments formatted into a command string for system(). Here is what I have so far for code. #include string.h #include stdlib.h #include "postgres.h" #include "fmgr.h" PG_FUNCTION_INFO_V1(ssh_exec); Try this: Datum ssh_exec(PG_FUNCTION_ARGS) { char*uname = PG_GETARG_CHAR(0); char*uid = PG_GETARG_CHAR(1); char*gid = PG_GETARG_CHAR(2); strcpy(sshcmd, "/usr/local/bin/plsshexec "); strncat(sshcmd, VARDATA(uname), VARSIZE(uname)); strcat(sshcmd, " "); strncat(sshcmd, VARDATA(uid), VARSIZE(uid)); strcat(sshcmd, " "); strncat(sshcmd, VARDATA(gid), VARSIZE(gid)); system(sshcmd); return 0; } This may work, but is more to give you idea what PostgreSQL vars are about. I would not use such code in my projects anyway :) I suggest you study the code in src/backend/utils/adt/varchar.c to see examples of char/text manipulation. -- marko ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Out of date security docs
This seems pretty out-of-date, since we seem to have had a 'CREATE GROUP' command for as long as I can remember -- are there any more accurate docs out there? How would I go about updating them? http://www.postgresql.org/users-lounge/docs/7.0/postgres/security17760.htm /***/ Currently, there is no easy interface to set up user groups. You have to explicitly insert/update the pg_group table. For example: jolly= insert into pg_group (groname, grosysid, grolist) jolly= values ('posthackers', '1234', '{5443, 8261}'); INSERT 548224 jolly= grant insert on foo to group posthackers; CHANGE jolly= /***/ -- Adam Haberlach| [EMAIL PROTECTED] | http://youlook.org http://www.newsnipple.com | '88 EX500'00 ^ | ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Out of date security docs
Adam Haberlach [EMAIL PROTECTED] writes: This seems pretty out-of-date, since we seem to have had a 'CREATE GROUP' command for as long as I can remember -- are there any more accurate docs out there? How would I go about updating them? http://www.postgresql.org/users-lounge/docs/7.0/postgres/security17760.htm The 7.0 docs are out of date by definition ;-). Take a look at the development docs; if you don't like 'em, feel free to submit a patch against the SGML sources. Right offhand, the specific item you complain of seems to be fixed, but that doesn't mean there's not plenty more work to do ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Out of date security docs
On Tue, 27 Mar 2001, Adam Haberlach wrote: This seems pretty out-of-date, since we seem to have had a 'CREATE GROUP' command for as long as I can remember -- are there any more accurate docs out there? How would I go about updating them? http://www.postgresql.org/users-lounge/docs/7.0/postgres/security17760.htm /***/ Currently, there is no easy interface to set up user groups. You have to explicitly insert/update the pg_group table. For example: jolly= insert into pg_group (groname, grosysid, grolist) jolly= values ('posthackers', '1234', '{5443, 8261}'); INSERT 548224 jolly= grant insert on foo to group posthackers; CHANGE jolly= /***/ The easiest thing to do is to write a small utility to do this. Here is a litle perlscript (as attachement) that does the inserting feel free to use it. You need DBI and DBD::Pg to use it. - Einar Karttunen #!/usr/bin/perl # Einar Karttunen 28.3.2001 [EMAIL PROTECTED] # this file must be distributed under GNU GPL use DBI; use DBD::Pg; print 'group name: '; $gn = ; chomp $gn; print 'group id (random if none entered): '; $gid= ; chomp $gid; unless($gid =~ /^\d+$/) { $gid = int(rand(50)); } print 'space separated list of users to add: '; $u = ; chomp $u; @users = split(' ',$u); print ("groupname=$gn, groupid=$gid, users="); foreach (@users) { print "$_\/"; } print "\ncontinue (y/n) "; exit unless( =~ /y/o); $dbh = DBI-connect("dbi:Pg:dbname=ressu", einar, poiu,{RaiseError = 1, AutoCommit = 1}); foreach $n (@users) { $val = $dbh-selectrow_array(q|SELECT usesysid FROM pg_user WHERE usename='|.$n.q|'|); push @a,$val; } $dbh-do(qq|INSERT INTO pg_group (groname, grosysid, grolist) VALUES ('$gn', '$gid', '{|.join(',',@a).q|}')|); $dbh-disconnect(); print "success\n"; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])