Re: [GENERAL] Using MS Access front-end with PG
Paul Lambert wrote: I've got an MS Access front end reporting system that has previously used MS SQL server which I am moving to Postgres. Are you using "PassThrough" queries? It is not clear The front end has several hundred if not thousand inbuilt/hard-coded queries, most of which aren't working for the following reasons: 1.) Access uses double quotes ("") as text qualifiers, PG uses single quotes. ('') What Access uses should not make any difference. In pass through queries I as MS-SQL uses single quotes same as PostGres, and "attached table" queries all this gets transalated at the Access to ODBC layer. 2.) The Like function in SQL Server is case insensitive, PG it is case sensitive. The ilike function is not recognised by Access and it tries to turn that into a string, making my test (like "ilike 'blah'") The only way the "ilike" can be passed from ACCESS to Postgres is through pass through queries. This is probably not what you want though. I do not actually know how MS-Access translates the "Like" operator at the ACCESS-> ODBC layer (probably just converts the search string to use % and _ from * and ?). I do not know if it is possible to switch off case sensitivity in Postgres though Has anyone had any experience with moving an access program from SQL server to PG? Yes, but some time ago. I did not have the case sensitivity problem as I knew that was a non-standard feature and did not rely on it while developing the MS-SQL solution though (just call me smartypants :-)). I still had to change a few things though (I cannot remember what, sorry). Eddy ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Using MS Access front-end with PG]
Joshua D. Drake wrote: You could preface all your queries with something like: select * from foo where lower(bar) = lower('qualifer'); But that seems a bit silly. And also it would prevent the optimizer from using any indexes on "bar". Not a good idea. Eddy ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] problems with currval and rollback
Without seeing the SQL statements you executed in the session it is difficult to see your problem. Getting a current sequence after a rollback is no problem (in 8.0 anyway). Please note though, the sequence itself is NOT rolled back. This is correct behaviour. Currval will return the last sequence retrieved for the session (using nextval), even if other sessions have accessed the sequence in the intervening time. Eddy Assad Jarrahian wrote: Hi all, So I started to make some changes with my code here and there (nothing to do with the relevant lines) and suddenly currval and rollback don't work. What happens is that the it inserts a db, but cannot get a value back using currval (ERROR: ERROR: currval of sequence "lm_id_seq" is not yet defined in this session 55000 ) , yet the db gets the addlm inserted. Shouldn't it rollback? Furthermore, why would currval suddenly stop working? Much thanks for your feedback in advance. -assad ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] MS Access to PostgreSQL
William Shatner wrote: I have recently migrated from MS Access to PostgreSQL.Previously I had a SQL command ResultSet aGroupResultSet = aGroupPathStmt.executeQuery( "SELECT \"groupID\",\"fullpath\" FROM \"groups\" WHERE \"fullpath\" Like '" + aPath + "'"); where aPath was equal to 'folder\another folder\%'. Are you sure? In MS-Access JET engine it uses the '*' character instead of the '%' one as a "like" wildcard. Two things you can try... If you have attached the table "groups" in MS-Access and are using it through JET (as the code you provided would suggest) then try changing the "%" character to "*" - the JET engine will convert that to % for you, whereas it may escape the "%" character you have supplied to keep the behaviour the same as JET. The other possibility is to use the "dbPassThrough" parameter and execute it as a pass through query, here the SQL is sent to the PostgreSQL engine unchanged. All in all I am sure this is an MS-Access problem rather than a Postgres one. Eddy ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How best to represent relationships in a database generically?
Lincoln Yeoh wrote: Hi, importantly do searches and other processing by those relationships. So, what would be the best way to store them so that a search for the relationship like "grass is to cow", will also turn up cow is to tiger, and goat is to tiger, and fish is to penguin (and penguin is to bigger fish ;) ), and electricity is to computer. And a search for cow is to goat, could turn up tiger is to lion, and goat is to cow. Is the only way to store all the links explicitly? e.g. have a huge link table storing stuff like obj => cow, subj => grass, type => consumes, probability=90% ( => means points/links to). Or even just have one table (links are objects too). Hi This is a generic database design problem rather than a Postgres or SQL one, but here goes Excuse ASCII art.. What you really have is a multi - multi relationship, such as A <---> B Where A is a table containing grass, cow, fish and B is the table containing cow, tiger and penguin I know, A and B are the same table, so the multi - relationship is in fact A <> A As you cannot have a multi-multi relationship in a RDBMS, you need a "link" table... A ---> C C < --- A This would be represented as tables as something like create table thingy ( thingy_key varchar(12) primary key, thingy_desc varchar(30) ); or whatever and... create table munchies ( eater varchar(12) not null, dinner varchar(12) not null probablility_pc number(4,2) constraing pkey_munchies primary key(eater, dinner) ); or whatever, where "eater" and "dinner" are foreign keys for "thingy_key" The munchies table can get big, but do not worry about that. It is small and RDBMS (especially Postgres) should handle it well even on a smallish machine. Hope that makes sense Eddy ---(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] Linux distro
[EMAIL PROTECTED] wrote: Hello, I bought a Dell server and I am going to use it for installing PostgrSQL 8.2.4. I always used Windows so far and I would like now to install a Linux distribution on the new server. Any suggestion on which distribution ? Fedora, Ubuntu server, Suse or others? There is no "right" answer to this. For a Postgres server though I would probably not go for Fedora or Ubuntu as they are more desktop oriented and have frequent updates and relatively short life cycles. If I were to recommend anything I would suggest CentOS - or even RHEL if you need enterprise level support/certification. Eddy ---(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 duplicates in a table
Assuming identification_number is a unique (primary) key... select * from my_table where date_of_birth in (select date_of_birth from my_table group by date_of_birth having count(*) > 1) Or - it may be quicker to do... select * from my_table a where exists (select 'x' from my_table b where a.date_of_birth = b.date_of_birth group by b.date_of_birth having count(*) > 1) Kall, Bruce A. wrote: I've look for a solution to this, but have only been able to find solutions to delete duplicate entries in a table by deleting entries not returned by SELECT DISTINCT. What sql should I use to SELECT entries in a table that have two particular column values that match? For example, my_table has name, phone number, identification_number, zip code, date of birth, and city I want to SELECT rows from this table that have the same values in identification and date of birth (duplicates) so I can have the user look at them in order to figure out which one to delete. I tried something like: $db_sql = "SELECT * FROM my_table GROUP BY identification_number HAVING count(date_of_birth) > 1 ORDER BY name" but that doesn't seem to work. Thanks, Bruce ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] ANNOUNCE: ShellSQL 0.7
Hi Announcing ShellSQL 0.7 to an unsuspecting world... ShellSQL is a utility to allow SQL to be intergrated easily into UNIX/LINUX shell scripts. The web page is at http://www.edlsystems.com/shellsql - and at sourceforge at http://sourceforge.net/projects/shellsql . Version 0.7 includes many bug fixes and tidy ups. A new utility to import a file into a table (or use it to update tables), more input/output formats (CSV, Tab delimited etc) and a new engine (freetds for MS-SQL/Sybase engines) - (The Postgres engine was one of the originals there). Enjoy Eddy Macnaghten ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Identity Problem
It looks as though you need to create your database users. Try using the "createuser" command. For more information: http://www.postgresql.org/docs/7.4/interactive/user-manag.html Eddy Mike-Olumide Johnson wrote: Hello, Have RADIUS setup and working properly. While executing SQL got the following error: 'psql: Warning: The -u option is deprecated. Use -U. User name: Password: /root/freeradius-1.0.1/src/modules/rlm_sql/drivers/rlm_sql_postgresql/db_pos tgresql.sql: Permission denied' Below is further ouputs from CLI [EMAIL PROTECTED] data]# psql -U psql: option requires an argument -- U Try 'psql --help' for more information. [EMAIL PROTECTED] data]# psql -u psql: Warning: The -u option is deprecated. Use -U. User name: root Password: psql: FATAL: Database "root" does not exist in the system catalog. [EMAIL PROTECTED] data]# psql -u psql: Warning: The -u option is deprecated. Use -U. User name: postgres Password: psql: FATAL: IDENT authentication failed for user "postgres" Will appreciate if someone can point me to how this problem could be resolved. Thanks in anticipation, Mike ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] pgadmin3 / postgresql newbie question
pgadmin3 does not seem to want to connect to the server using UNIX sockets, it only does it through TCP. Set up the Postgres server to use TCP (as well as UNIX sockets) - i.e. - starting postmaster with the -i option, sorting it pg_hba.conf etc. Eddy Jonathan Schreiter wrote: hi all, running amd64 fedora core 3 w/ default postgresql 7.4.7-3. did asu - , su postgres and createdb mydb as explained in the postgresql tutorial. installed the latest pgadmin3 and am trying to connect to this database. as i wasn't sure what the FC3 default password was for postgres, i changed it to something i could remember. i can't seem to connect to the new database using pgadmin3. i have the correct IP address of the local computer, default port 5432, mydb as the initaldb, postgres as the username, and my new password as the password. i keep getting the error Error connecting to the server: could not connect to server: Connection refused Is the server running on host "192.168.1.24" and accepting TCP/IP connections on port 5432? i also verified the postgresql service is running, and that i've added the following to /etc/raddb/postgresql.conf: login = "postgres" password = "mynewpassword" and right underneath it: tcpip = true i've also disabled my local firewall and SELINUX just for kicks. and yes, i did a reboot. so...anyone know what else i can look at? many thanks! jonathan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Error: "catalog is missing 8 attribute(s) for relid
Alexandru Coseru wrote: Hello.. Any ideeas ? Fraid so and it is not good. I am no expert in the inner-inner workings of Postgres, but my guess is that your catalogue is, or has been, corrupt. If this is the case it is unrecoverable. It is a matter of rescuing what data you can (using pg_dump), and re-creating the database (or even the entire cluster) from scratch. Eddy Thanks Alex ---(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] Maximum size of database
roopa perumalraja wrote I would like to know that what can be the maximum size of database in postgres 8.1.4. Currently my database size is 37GB & its pretty slow. I wonder if its b'cos of huge amount of data in it. http://www.postgresql.org/docs/faqs.FAQ.html#item4.4 Slowness reason cannot be really investigated without more information of the specifics, but could well be indexing issues. Eddy ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] UK Hosting Providers
[EMAIL PROTECTED] wrote: I have a client who has had some software developed that requires a website element and the site uses postgres for the back end database (of course, its the best after all!) You might like to look at Bytemark (http://www.bytemark.co.uk), or one of the other virtual machine hosting services. Their machines are quite cheap, and you have root access to the (virtual) machine, therefore you can install PostgreSQL (and/or anything else) yourself. Eddy ---(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