Thanks to some fantastic help on a previous question I have managed to put together my query. Everything works swimmingly save one issue. careers = Career.objects.filter(job__dwarf__user = 1).annotate(dwarves_in_career = Count('job__dwarf')) In my view this does exactly what I want and when I loop it in my template like so: {% f...
I'm trying to update the database library that we use at work to use parameterized queries so that coworkers who are not very knowledgeable about SQL injection won't have to remember to escape input and just pass in an array of parameters instead (I'm using pg_query_params). However, I am running into a problem. One of the requirements ...
The geonames database has two models that refer to each other. In a postgres database, reference is provided by foreign key relationships. The problem is writing data to the database - writing to one table without having the referenced id in the other table violates a foreign key contraint. Class Geoname id = IntegerField(primary_...
I am trying to determine that standard SQL behaviour for comparing a number to a character or string version of the same number. Does SELECT 1 = '1' (or the like) always return some sort of "truthy" value (true, 1, 't', etc.)? I have confirmed as much on PostgreSQL and MySQL, but I cannot find a resource for SQL as a whole. Update: The ...
I was just curious why all databases in PostgreSQL have a public schema that is accessible to all users. I know I can revoke privileges and grant them to one user but why is that not the default? Thanks... ...
Given a Postgresql table schema: create table thing ( id serial primary key, key text, type int references thing, latest_revision int default 1, created timestamp default(current_timestamp at time zone 'utc'), last_modified timestamp default(current_timestamp at time zone 'utc') ); $for name in ['key', 'type',...
How I can round to nearest X minutes? Here's my attempt: DECLARE _stamp ALIAS FOR $1; -- timestamp _nearest ALIAS FOR $2; -- minutes (integer) _minutes decimal; _ret timestamp; BEGIN _ret := date_trunc('minute', _stamp); SELECT EXTRACT (minute FROM _ret)::integer INTO _minutes; IF (_minutes % _nearest < (_nearest / 2)) ...
I ran into a very annoying role inheritance issue with PostgreSQL. It simply doesn't behave as it should according to the documentation. I would like to have a master role, and grant its permissions to newly created users. These users should inherit the permissions without issuing SET ROLE manually. CREATE ROLE testrole NOSUPERUSER INH...
How can I change the date by the random number of days in PostgreSQL? Unfortunately http://stackoverflow.com/questions/1400505/postgresql-random-number-range-1-10 solution with trunc doesn't work: select date(now()) + (trunc(random() * 20)) results in: ERROR: operator does not exist: date + double precision LÍNEA 1: select date...
I have two postgres queries that i execute using PHP and both of them result a Resource, is it possible to combine or concatenate these two resources? ...
We are doing research right now on whether to switch our postgresql db to an embedded Derby db. Both would be using glassfish 3 for our data layer. Anybody have any opinions or knowledge that could help us decide? Thanks! edit: we are writing some performance tests ourselves right now. Looking for answers more based on experience / fir...
In PHP, I am using PDO with the pgSQL drivers. I wanted to know how to get the value of the "RETURNING" clause given in the INSERT sql query. My current code looks like this, $query = 'INSERT INTO "TEST" (firstname, lastname) VALUES ('John', 'Doe') RETURNING user_id'; $queryHandle = $connection->prepare($query); $queryHandle->execute();...
Hello, we are still pretty new to Postgres and came from Microsoft Sql Server. We are wanting to write some stored procedures now. Well, after struggling to get something more complicated than a hello world to work in pl/pgsql, we decided it's better if we are going to learn a new language we might as well learn Python because we got th...
Ideally I'd like to be able to do something like: id_of_new_row = cursor.lastrowid() in which I get the id of the newly created or modified row. But this isn't available through psycopg2. Alternatively, I've tried this: id_of_new_row = cursor.execute('INSERT INTO this_table (value1, value2, value3) VALUES (%s, %s, %s) RETURNING id'...
Hi, Consider the following query in PostgreSQL: SELECT a, b, (A VERY LONG AND COMPLICATED SUBQUERY) AS c, (ANOTHER VERY LONG AND COMPLICATED SUBQUERY) AS d FROM table I want to have c and d in the WHERE clause, like: WHERE c AND d; But, as far as I know, I can only do: WHERE A VERY LONG AND COMPLICATED SUBQUERY) AND ...
I am referring to http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/ To reproduce the problem, here is some simple steps to follow : (1) create database named "tutorial" (2) perform the following SQL query : CREATE TABLE impressions_by_day ( advertiser_id SERIAL NOT NULL, day DATE...
How do I get datatype of specific field from table in postgres ? For example I have the following table, student_details ( stu_id integer, stu_name varchar(30 ), joined_date timestamp ); In this using the field name / or any other way, I need to get the datatype of the specific field. Is there any possibili...
Hi, I am looking for tools to auto-tuning for postgresql database. Do you know any tools for postgres like DB2 Design Advisor in DB2, Database Tuning Advisor in Microsoft SQL Server or SQL Access Advisor in Oracle? Thanks for any links and ideas:). ...
Hi, I want to write a windows service in C# that will wait for a change in the particular table in PostgreSQL database and then operate based on the data from the last inserted row. As far as I know (I'm new to PostgreSQL) I can use triggers to execute a function in structural code - but how to make it pass the data to my service? Tha...
Hello, For a scheduling system, what's the best way to save the timezone of client/event in a central server database coming from multiple sources mobile,web,client app. How do you store the dates? Alarms, reminders etc... How do you handle the DST setting? How do you handle the events if client has traveled to a different locat...