Continuing on my last question... Let me try to explain my schema. I have three tables we'll call users (with columns id and name), parties (with columns id, partydate, and user_id) and questions (with columns id, createdate, and user_id). My requirement is to show for every user the number of parties within the last year and questio...
I posted on here last night looking for help with some homework. I'm down to my last question. Here's the relevant piece of the schema I am working with: CREATE TABLE votesOnPoll( user_id int, poll_id int, option_id int, voteDate date, CONSTRAINT votesOnPoll_pk PRIMARY KEY (user_id, poll_id), CONSTRAINT votesOnPoll_user_f...
Is there any way to execute more sql prepared statements at once? Or at least use something to achieve this result, can it be emulated with transactions? pg_send_query can execute more statements (from php docs "The SQL statement or statements to be executed.") but pg_send_execute and pg_send_prepare can work only with one statement. ...
I want to select and then delete a list of entries in my tables that have case-insensitive duplications. In other words, there are these rows that are unique... ..but they're not unique if you ignore case factor in case. They got in while I wasn't watching. So how can I s...
Hello All! Quick question (I hope!): if I use \i to feed an input file into psql, can I get the output from the queries to be saved into a file? If so, how? Thanks!! ...
I have a users table with a primary key uid of data type bigint. I don't understand why I get the error : "Minteger out of range" when trying to add a user with uid = 100000349053153. This should work (according to the doc: http://www.postgresql.org/docs/8.3/static/datatype.html) ...
Hi guys, how do i use the models to perform a join and count query like the one below: select count(*),site_url from connection_ss join site_ss on to_id_id = site_id where site_ss.source_id = 1 group by site_url order by count desc Here are my models: class site(models.Model): site_id = models.AutoField(primary_key=...
i'm quite new to postgre. i want to create function (like stored procudure) that update multiple rows and select those affected rows out here is my statement CREATE or replace FUNCTION set_val( _val character varying(100) ) --5 RETURNS setof "table_test" AS $body$ declare results "table_test"%rowtype; beg...
Using postgreSQL 8.1.11, is there a way to select a set of columns that have name begining with same prefix. Suppose we have columns : PREFIX_col1, PREFIX_col2, ... Is it possible to do a request like : SELECT 'PREFIX_*' FROM mytable; Wich of course doesn't work. ...
I'm bulk loading data and can re-calculate all trigger modifications much more cheaply after the fact than on a row-by-row basis. How can I temporarily disable all triggers in PostgreSQL? ...
Hi, I'm trying to rename a database to a name with a hyphen (-). ALTER DATABASE one RENAME TO one-two; And psql returns an error: ERROR: syntax error at or near "-" What should I use as an escape sequence for "-" character or what's the way to do the above? Note: I've tried the '\-' and didn't work as well. Thanks. ...
I can't seem to find much documentation on this. What's the simplest way to create a database/table on postgres supporting a query like this SELECT * FROM table WHERE distance(POINT(0,0), table.location) <= 1000m; Where POINT(0,0) and table.location should be latitude/longitude pair, and 1000m is 1000 meters. And how should I go about i...
Current "Attempts" table: ID QUESTION_ID CORRECT 1 1 FALSE 2 2 TRUE 3 4 FALSE 4 3 FALSE 5 1 TRUE 6 1 TRUE 7 4 TRUE 8 3 TRUE 9 4 FALSE 10 1 TRUE 11 2 TRUE 11 1 FALSE 11...
Hello, Currently, I'm working with a website. This website is written using PHP with PostgreSQL as the back-end. For the server I use Apache (XAMPP). My website has a membership feature consist of free membership and premium membership. Premium membership valid for 1 year after the registration, after that the user membership will be re...
Goal: to get a list of N random questions that have been answered correctly the least amount of times, per user. following sql will provide me a list of having a count of correctly answered questions for user_id 4. select q.id, temp.Count from questions as q left join (select q.id, count(a.id) as count from questions as q left join att...
I'm trying to use prepared statements in PostgreSQL, but it's giving me some pretty frustrating errors. I'm trying to select a single record from the DB, by MediaID. In the DB, MediaID is a Serial Integer. In the class structure, it's an int. When I call sql.Prepare(), however, it's telling me that I have invalid input syntax for Media...
In MySQL I used use database_name; What's the PostgreSQL equivalent? ...
I've got tables like the following (Django model definition, with a postgres database underlying it): class Person(models.Model): id = models.IntegerField(primary_key=True) name = models.CharField(max_length=300) class Owner(models.Model): id = models.IntegerField() person = models.ForeignKey(Person) I use a Pyth...
This O'Reilly article gives an example of a PostgreSQL statement that parses an Apache log line: INSERT INTO http_log(log_date,ip_addr,record) SELECT CAST(substr(record,strpos(record,'[')+1,20) AS date), CAST(substr(record,0,strpos(record,' ')) AS cidr), record FROM tmp_apache; Obviously this only extrac...
Hello, I have the following table in PostgreSQL 8.4.5: snake=> create table gps ( id bytea check(length(id) = 16), stamp timestamp DEFAULT current_timestamp, pos point not null); and I'm able to INSERT record into it from psql prompt: snake=> insert into gps (id, pos) values (decode(md5('x'), 'hex'), point(0, 0)); INSERT 0 1 snake=>...