Hello pals, So here's the problem statement, SELECT app_label || '_' || model as name from django_content_type where id = 12; name ------------------- merc_benz DJango people might have guessed, 'merc_benz' is a table name in same db. I am writing some complex SQL migrations and I need to select result from such dyn...
I have three tables: table 'received' ------------------ partner_id int item_id int table 'delivered' ------------------ item_id int delivery_date date customer_id int table 'partners' ------------------ id int name text table 'customers' ------------------ id int name text What I'd like to query is which items have been delivered ...
I'm getting xml and rss feeds and putting the data into a database. I've run into two different date formats so far... Wed, 21 Jul 2010 00:28:50 GMT And 2010-07-20T17:33:19Z I'm sure there will be more. My postgresql database for the date is timestamp without time zone. Is there an existing function in php or is there a procedure t...
select preference.id from preference where --array(select id from date_etl where local_date >= '02/01/2011' and local_date <= '02/25/2011') && date_etl_array --option 3 gives error, do not know why? and how to fix it? --array(select id from date_etl where local_date >= '02/01/2011' and local_date <= '02/25/2011') && '{1342715,1342739}...
Update: There was a comment that the question was not clear, that I made a leap of logic claiming that I would have 118 billion rows. I have edited the text below to clarify things. See the italicized text below I have been struggling with this for a while now, have even gone down a few paths, but I turn now to the community for ideas. ...
Ok, so the title is a bit convoluted. This is basically a greatest-n-per-group type problem, but I can't for the life of me figure it out. I have a table, user_stats: ------------------+---------+--------------------------------------------------------- id | bigint | not null default nextval('user_stats_id_seq'::regcla...
Hello all, I have the following problem and I need to know if there´s a way to fix it. I have a client who was cheap enough to decline buying a backup plan for his postgreSQL databases on the main system that runs his company and as I thought it would happen some day, some OS files crashed during a blackout and the OS needs to be reins...
Short database schema: users (id) games (current_player_id) // has many cards cards (author_id, game_id, content, created_at) game_views(game_id, user_id) //shows which games user have seen I need to find game for user, which meets all whose rules: game's current_player is NULL (the game do not played by anybody right now) author o...
Hi, I need help with a recursive query. Assuming the following table: CREATE TEMPORARY TABLE tree ( id integer PRIMARY KEY, parent_id integer NOT NULL, name varchar(50) ); INSERT INTO tree (id, parent_id, name) VALUES (3, 0, 'Peter'), (2,0, 'Thomas'), (5,2, 'David'), (1, 0, 'Rob'), (8, 0, 'Brian'); I can ...
Good day all, I am fairly new with PQSQL and am slowly picking things up - I have added a new disk and would like to do two things: Restore a backup to this new disk - /hda2/pgdata/ Move a database from /hda1/pgdata to /hda2/pgdata/ Any help is most appreciated, thank you. ...
Hi, I need to get rid of trailing repeating non_alphanumeric symbols like "thanks ! !!!!!!!" to "thanks !" If these different symbols then they are ignored. I quite new to regex, so I came up with regexp_replace('Thanks . . . . ', '((\\W)\\s*)(\\2\\s*)+', '\\2') to try it out. However i realise the trailing space after 'thanks'...
Hi all, I have a User table in my Postgres database. In my application, the User can have various allowed websites. My question is: which is more disk space efficent, having a many-to-many relationship between a user and a url or storing the array in JSON in a column in the User table. Essintially, how much space does postgres use to st...
Setup: multiple webservers, running mod_wsgi, Apache, and pgbouncer which connects to the shared DB running Postgres 8.3.6. Application is running Django. What we're seeing: 'idle in transaction' queries on the DB that hang out for a long time. In order to see them, I'll run something like this: SELECT query_start, procpid, client_addr...
Hello, I've seen this problem many places, that is to say the problem with programmatically making backups from PostgreSQL. My solution is to use ExpectJ, but I'm having trouble getting my code to work. I have the following code: public class BackupUtility { public static void main(String[] args) { try { ExpectJ exp = new E...
When I try to delete a location through the django admin, there is a warning that lots of related items will be deleted. When I click to continue, I get the following error: Traceback (most recent call last): File "/mnt/hgfs/sftp/keg/dev/site/lib/django/core/servers/basehttp.py", line 651, in __call__ return self.application(envir...
Hi guys, I trying to do pg_dump for one of my database but I am having permission problems. C:\Windows\system32>pg_dump -U postgres -p 1863 -O social_sense > C:\Program Fi les\PostgreSQL\8.4\data\social_sense.sql Can anyone enlighten? ...
We are using pg_dumpall to make backups of our psql database. We have user with superuser rights that runs pg_dumpall. Everything works fine. The thing that in my opinin can be better is to limit that users rights (just in case). So, my question is - can we create some user without superuser rights but with the rigtes to use pg_dumpall ...
I am using the SQL language on Postgres to return a table using the RETURNS TABLE command: CREATE OR REPLACE FUNCTION procreadbudget() RETURNS TABLE(budgetmonth character, budgetincome numeric, budgetexpense numeric) AS $BODY$ SELECT budget_month, budget_income, budget_expense FROM budget ORDER BY unique_id; $BODY$ LANGUAGE 'sql' VO...
I'm wondering about how to scale a database. Currently it uses PostgreSQL. Would switching to Oracle be worthwhile inspite of the coding pain and expense? Or is PostgreSQL + more boxes a better/cheaper approach? ...
I want to generate this query in sqlalchemy. The table 'demande' exists in the database. There is a subquery that generates the timesteps with the generate_series function. SELECT timesteps.timestep AS timestep, d.count AS count FROM (SELECT DATE_TRUNC('hour',date_demande) AS timestep, COUNT(id) AS count FRO...