postgresql

How can I auto-generate unique fake names for users?

We would like to give each of users an alias so that we can refer to them in discussions while protecting their identity. These aliases should be unique. The easy way would be to simply use a SERIAL column, but ints aren't memorable. We would like to use real people names so that we can remember the aliases. The other easy way would be...

Strategy for locale sensitive sort with pagination

Hi, I work on an application that is deployed on the web. Part of the app is search functions where the result is presented in a sorted list. The application targets users in several countries using different locales (= sorting rules). I need to find a solution for sorting correctly for all users. I currently sort with ORDER BY in my S...

How to set list of parameters on prepared statement?

i have a list of names e.g.: List<String> names = ... names.add('charles'); ... and a statement: PreparedStatement stmt = conn.prepareStatement('select * from person where name in ( ? )'); how to do the following: stmt.setParameterList(1,names); Is there a workaround? can someone explain why this method is missing? using: ja...

django Postgres IntegrityError

Can somebody please explain to me how to properly test Postgres DB errors, particularly IntegrityError. For example i have next test: class TestSlugs(TestCase): # This slug must be unique b = BookPublisher(slug=self.duplicate_slug) self.assertRaises(IntegrityError, b.save) #check if there's only one BookPublisher s...

Java conectivity with PostgreSQL

can some one please tell me how to connect java file to postgresql database (if posible wit code n explanation) ...

[Python] ImportError: DLL load failed : - when trying to import psycopg2 library

>>> import psycopg2 Traceback (most recent call last): File "", line 1, in File "C:\Python26\lib\site-packages\psycopg2\__init__.py", line 60, in from _psycopg import BINARY, NUMBER, STRING, DATETIME, ROWID ImportError: DLL load failed: The application has failed to start because its si de-by-side configuration is incorrect. P...

Crash and Socket Failure: 9-signalling Processes with Sudo

The aim of the test is to simulate a serious fatal error, peeking into kernel due to the socket failure. It is good practise in cases such as crash-recovery. 9-signalling all postgres processes ignite errors immediately after the command: $ psql psql: could not connect to server: Connection refused Is the server running locally...

PostgreSQL: Can one define a session variable with the language and use it in views?

Here's a simplified example of schema: Table l10n ( l10n_id SMALLINT, code VARCHAR(5) ) Table product ( product_id INT, ..language-neutral columns.. ) Table product_l10n ( product_id INT, l10n_id SMALLINT, ..language-specific columns.. ) Querying for products with localized data is done like this: SELECT * FROM product a ...

Begin...commit every 50 rows

I have a 500,000 line sql script. update users set region_id = 9814746 where id = 101 and region_id is null; update users set region_id = 9814731 where id = 102 and region_id is null; update users set region_id = 3470676 where id = 103 and region_id is null; ... .... I need to be able to generate a script with begin...commit every 50 ...

Print out the answers for a question by PHP

How can you use the following sample data either with a foreach or while -loop? I get the following sample data by running $answers = pg_fetch_all ( $result );. Sample data [0]=> array(3) { ["answer"]=> string(7) "This is the answer" ["username"]=> string(5) "roope" ["was_sent_at_time"]=> string(26) "2009-...

Simulating MySQL's ORDER BY FIELD() in Postgresql

Hello all, Just trying out Postgresql for the first time, coming from MySQL. In our Rails application we have a couple of locations with SQL like so: SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC It didn't take long to discover that this is not supported/allowed in Postg...

Postgres: Are Nested Foreign Keys allowed?

I mean things like: FK1 -> 1FK2 -> 2PK Please, note that 1FK2 is a PK to FK1, but the 1FK2 is a FK to 2PK. So the key in the nested structure is a FK and a PK. An example: --- Like an interface CREATE TABLE q_content ( user_id SERIAL PRIMARY KEY, ---); ---- tables that refer to it CREATE TABLE questions ( user_id SERIAL refer...

Split table with duplicates into 2 normalized tables?

I have a table with some duplicate rows that I want to normalize into 2 tables. user | url | keyword -----|-----|-------- fred | foo | kw1 fred | bar | kw1 sam | blah| kw2 I'd like to start by normalizing this into two tables (user, and url_keyword). Is there a query I can run to normalize this, or do I need to loop through the tabl...

Can't enter date into postgres field with datatype timestamp

I'm trying to insert a date ("This is a string") into a postgres database field. I'm getting the following error ERROR: invalid input syntax for type timestamp: "" Here is my code $date = '2002-03-11'; $query = 'INSERT INTO dates(date) VALUES('.$pdo->quote($date).')'; $pdo->query($date); I have absolutely no idea on how to do thi...

Reading boolean correctly from Postgres by PHP

The main problem of this thread is moved to here about boolean datatype in PHP / Postgres. The problem is the conversion of t and f to true and false, since Postgres stores true and false as such. How can you use the variable a_moderator in SESSION? I fetch the value of the variable a_moderator by #1 code of how I get the variable...

Preserving enters in user's input by PHP

How can you preserve "enters" given by the user in the database and show them then to other users? I store the question of the user and use the following functions to sanitize the user data, prepare it, and execute the SQL command, respectively. pg_escape_string pg_prepare pg_execute I use htmlentities with ENT_QUOTES to convert t...

To have boolean type in Postgres for PHP

I can't believe that the following statement seems to be still true So, I switched to integers and 0 or 1 works fine, but it is stupid, that the database system has boolean variables of a smaller size, but I should use integers for boolean values! How do you use boolean datatype with Postgres / PHP? In other words, Is the only way t...

Reading datypes correctly by PHP from Postgres

How do you compile integer data in Postgres by PHP? My integers are string as the following show by var_dump ( $_SESSION ) 'logged_in' => int 1 'user_id' => string '9' (length=1) // should be int 'a_moderator' => string '0' (length=1) // should be int I compile the values by the following code which ...

To understand queries in PHP PG -prepared statemens

How do you read the following code about pg_query_params and pg_prepare? $result = pg_query_params ( $dbconn, "SELECT flagged_for_moderator_removal // unsure about Repetition here FROM questions WHERE question_id = $1", array ( $_GET['question_id'] ) ); if ( pg_num_rows ( $result ) == 0 ) { $resu...

Hibernate "could not execute query" Exception!!

exception javax.servlet.ServletException: org.hibernate.exception.GenericJDBCException: could not execute query org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:286) org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913) org.apache.struts.action.ActionServlet.doGet(A...