How can I convert a Google search query to something I can feed PostgreSQL's to_tsquery() ? If there's no existing library out there, how should I go about parsing a Google search query in a language like PHP? For example, I'd like to take the following Google-ish search query: ("used cars" OR "new cars") -ford -mistubishi And turn ...
I have a large query in a PostgreSQL database. The Query is something like this: SELECT * FROM table1, table2, ... WHERE table1.id = table2.id... When I run this query as a sql query, the it returns the wanted row. But when I tries to use the same query to create a view, it returns an error: "error: column "id" specified more than o...
Hi, In our current project, a system that will run on a local network with no more than 50 clients that connects to the same local server, we are creating a DB user for each client, to take advantage of the postgresql privilege system. I have some questions about this situation: 1) Analyzing the "performance", its OK to have ~ 50 DB u...
I'm having to start building the architecture for a database project but i really don't know the differences between the engines. Anyone can explain whats the pros and bads of each of these three engines? We'll have to choose one of them and the only thing I actualy know about them is this: Mysql & Postgres: Are free but not so good ...
Can anyone provide a clear explanation / example of what these functions do, and when it's appropriate to use them? ...
I've got two PostgreSQL databases that have been created using the same sql file. One of them is 2GB larger. Can someone help me figure out why? I'm sure the databases have the same row counts, tables, indexes, etc.. The databases are on different servers, there are small differences in the postgresql.conf and the PostgreSQL version. Wou...
I have SQL query, which is working nice on Oracle and MSSQL. Now I'm trying this on PostgreSQL and it gives a strange exception: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "main" Here is the query: SELECT * FROM "main" main INNER JOIN "something_link" something_link ON main."id" = something_link...
Hi community, Perhaps this question is not very clear but I didn't find better words for the heading, which describes the problem I like to deal with shortly. I want to restrict access from a java desktop application to postgres. The background: Suppose you have 2 apps running and the first Application has to do some complex calcula...
I did this tests and the results seems the count function scale linearly. I have another function relying strongly in the efficiency to know if there are any data, so I would like to know how to replace this select count(*) with another more efficient (maybe constant?) query or data structure. psql -d testdb -U postgres -f truncate_a...
When I connect to a MySQL database using PDO, the way I need to connect is: $pdoConnection = new PDO("mysql:host=hostname;dbname=databasename",user,password); But, for PostgreSQL, the DSN is more standard (IMO): $pdoConnection = new PDO("pgsql:host=hostname;dbname=databasename;user=username;password=thepassword"); Is there any reason w...
When there are one of more columns that reference another, I'm struggling for the best way to update that column while maintaining referential integrity. For example, if I have a table of labels and descriptions and two entries: Label | Description ------------------------------------ read | This item has been read READ | You read thi...
I have just installed PostgreSQL 8.3.4 on Mac OS X 10.5 (using ports), but I cannot figure out how to enable PL/Python. When I run the CREATE LANGUAGE plpythonu I get the following errors: ERROR: could not access file "$libdir/plpython": No such file or directory STATEMENT: CREATE LANGUAGE plpythonu; psql:<stdin>:18: ERROR: could not...
How would you model booked hotel room to guests relationship (in PostgreSQL, if it matters)? A room can have several guests, but at least one. Sure, one can relate guests to bookings with a foreign key booking_id. But how do you enforce on the DBMS level that a room must have at least one guest? May be it's just impossible? ...
I have a table in my pgsql database with columns named "type", "desc", and "start". When I call $dbh->column_info, only "desc" comes back quoted, whereas in pgAdmin3 all three of them are quoted. Is one of them wrong? ...
Is there some way to get a value from the last inserted row? I am inserting a row where the PK will automatically increase, and I would like to get this PK. Only the PK is guaranteed to be unique in the table. I am using Java with a JDBC and PostgreSQL. ...
I have to connect to a legacy postgre database wich has ENCODING = 'SQL_ASCII';. How do I set this encoding in my rails app? ...
The database type is PostGres 8.3. If I wrote: SELECT field1, field2, field3, count(*) FROM table1 GROUP BY field1, field2, field3 having count(*) > 1; I have some rows that have a count over 1. How can I take out the duplicate (I do still want 1 row for each of them instead of +1 row... I do not want to delete them all.) Example:...
I ran into the problem that my primary key sequence is not in sync with my table rows. That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists. It seems to be caused by import/restores not maintaining the sequence properly. ...
Besides the syntactic sugar and expressiveness power what are the differences in runtime efficiency. I mean, plpgsql can be faster than, lets say plpythonu or pljava? Or are they all approximately equals? We are using stored procedures for the task of detecting nearly-duplicates records of people in a moderately sized database (around 1...
I have to connect my rails app in a legacy Postgre database. It uses schemas so in a SQL its is common to use something like SELECT * FROM "Financial".budget I want to write a Budget model but I don't know how to set the table name in this case. I've tried the following: set_table_name 'budget' set_table_name '"Financial".budget' ...