The following line works fine in MySQL, but in Postgresql I get a Rails error stating, "can't convert Fixnum to String". viewable.update_attribute(:total_views, viewable.total_views.to_i + 1) total_views is an integer field Shouldn't Rails know to treat total_views as an integer even if I am using Postgresql? ...
I have a Postgresql function which returns a composite type defined as (location TEXT, id INT). When I run "SELECT myfunc()", My output is a single column of type text, formatted as: ("locationdata", myid) This is pretty awful. Is there a way to select my composite so that I get 2 columns back - a TEXT column, and an INT column? ...
I'm experimenting with using the pg_bulkload project to import millions of rows of data into a database. However, none of the new rows have a primary key and only two of several columns are avalable in my input file. How do I tell pg_bulkload which columns I'm importing and how do I generate the primary key field? Do I need to edit my...
As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a "purchases" table that looks like this: > SELECT * FROM purchases: id | customer | total 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1 I'd like to query for "the id of the largest...
Is CREATE ASSERTION supported in postgresql? ...
Hi all, I know in sql we can compress the text field like the following, CREATE TABLE TableName (FieldName CHARACTER(255) WITH COMPRESSION) ; I want to know how to achieve the text compression in psql. ...
I am not able to drop old partition tables in a postgresql 8.4.4 live db. My drop statement hangs with a access share lock on those partition tables. Is there any way I can drop those tables? ...
I have a fixture (json) which loads in development environment but fails to do so in server environment. The error says: "DatabaseError: value too long for type character varying(50)" My development environment is Windows & Postgres 8.4. The server runs Debian and Postgres 8.3. Database encoding is UTF8 in both systems. It is as if uni...
Django saves the data but eventually raises an IntegrityError. If there is an error, why it is saving that data? And I'm sure that the uniqueness property is not violated on that data. What is going on? Why is that error occurs? and how can I solve that? ...
Is email address a bad candidate for primary when compared to auto incrementing numbers. Our web application needs the email address to be unique in the system. So, I thought of using email address as primary key. But, my colleague suggests that string comparison will be slower to integer comparison. Is it a valid reason to not use ema...
How can I customize the prompt in the PostgreSQL command line tool psql (ideally in a per-user start-up script)? In particular, I'd like to be able to change it while still including the character that indicates whether the command is multi-line (eg. =, -, ', etc.). I'm running Ubuntu 10.04 (Lucid), PostgreSQL 8.4.4. ...
Looking for a good host for a project on Win2008 and PostgreSQL. I found these 2 sites but they support older versions of PostgreSQL. http://www.jodohost.com/ http://www.gbehost.com/ Does anyone have any good suggestions? ...
If I have a SQL table with columns: NR_A, NR_B, NR_C, NR_D, R_A, R_B, R_C and on runtime, I add columns following the column's sequence such that the next column above would be R_D followed by R_E. My problem is I need to reset the values of columns that starts with R_ (labeled that way to indicate that it is resettable) back to 0 ea...
I am using PostgreSQL. I have the data in table like: Parent_id Count Read --------- ------ ------ 52405 2 False 52405 1 True Now i want to summarize the data like : Parent_id Count Read --------- ------ ------ 52405 3 False Count would be the sum o...
I'm experimenting with postgres and sql server the same query in sql server gives me: CPU time = 31 ms, elapsed time = 800 ms. and in postgres: 38 ms but when I do the same query via .net using SqlConnection,SqlCommand and NpgsqlConnection,NpgsqlCommand the sqlserver is 30% faster can anyone explain this ? ...
In our application, we've implemented an automatic DB migration triggered from within our code. Now we want to backup the existing DB before doing any migration. Can anyone explain how to do a full backup of a Postgresql-DB via JDBC from within Java code? Update: it doesn't work via JDBC. Here some working code to the response of Fra...
Hi all, I am having some trouble invalidating user passwords every 60 days on my postgres database: CREATE RULE user_expiration AS ON UPDATE TO users DO INSTEAD UPDATE user SET user_expires = user_expires + '60' This will work every time the user changes their password, however it also works every time any...
I am using ActiveRecord and want to average a generic table column that's typed as a string (char), but holds numbers: | key | value | | "square_footage" | "142555" | How do I write this so Postgres doesn't give me this error: PGError: ERROR: function avg(character varying) does not exist LINE 1: SELECT avg("settings".value) AS ...
OK we have a custom ticketing system developed in PHP/PostgreSQL. How it works now is that information is entered, click submit, ticket number is shown. What They would like, Ticket number shown, enter information, click submit. Now I know I can just create a blank record and use that record id then do an update when the user submits. ...
I'm curious if it is possible to do the following select id from foo where foo.bar = (select SUM(bar) from foo ) without the use of a subquery. Edit: To clarify, I'm trying to do this with postgresql, which does not appear to support the two solutions posted thus far. ...