I access a a postgres table using SQLAlchemy. I want a query to have eagerloading. from sqlalchemy.orm import sessionmaker, scoped_session, eagerload from settings import DATABASE_USER, DATABASE_PASSWORD, DATABASE_HOST, DATABASE_PORT, DATABASE_NAME from sqlalchemy import create_engine from sqlalchemy import Table, Column, Integer, Strin...
I have a TableAdapter filling a DataSet, and for some reason every select query populates my timestamp column with the value 1/1/2000 for every selected row. I first verified that original values are intact on the DB side; for the most part, they are, although it seems a few rows lost their original timestamp because of update queries ...
How can you put an array to the WHERE -clause below without creating 50 pg_execute -commands in PHP / PostgreSQL? // to get the question_ids of the newest questions $result_q_id = pg_prepare($dbconn, "query6", "SELECT question_id FROM questions ORDER BY was_sent_at_time DESC LIMIT 50;" ); ...
I am running a system written in c++ that is continuously inserting large amounts of data into my database and at the same time querying the database for updated results. My problem is that the postgres threads started in this process continuously use more and more memory. I need to know how to correct this problem. The following is a mu...
How can you get many tags for a question effectively from Postgres database by PHP? I have tables tags and questions where I have the variables questions_question_id and question_id, respectively, for instance. One question may have many tags. I can select question_id, title and a tag from the tables. However, I do not know how to get...
I'm trying to vacuum my Postgres database by running the following SQL instruction within Java: vacuum verbose analyze Sometimes it just seems to "hang" is there any clean way to abort the process? I have tried SET statement_timeout TO XXXX But I get the error message "VACCUM cannot run inside the transaction block" ...
I want set some constraint to the serial type,it only produce even or odd numbers. ...
How can you reference column names with PHP's pg_fetch_row? Example about the code which we have been debugging with Cha. $dbconn = pg_connect("host=localhost port=5432 dbname=noa user=noa password=123"); $result_titles_tags = pg_prepare( $dbconn, "query777", "SELECT question_id, title FROM questions WHERE question_id IN ...
I cannot understand the syntax error in creating a composite key. It may be a logic error, because I have tested many varieties. How do you create composite keys in Postgres? CREATE TABLE tags ( (question_id, tag_id) NOT NULL, question_id INTEGER NOT NULL, tag_id SERIAL NOT NULL, ...
Hi all i install postgre on my localserver(ubuntu) with ip 192.168.1.10 and now try to access the database from my client machine(ubuntu) with ip 192.168.1.11 by pgadmin please guide me....actually i know i have to make changes in postgresql.conf to allow client ip and pg_hba.conf ...
Does anyone have any experience with creating database agnostic apps in Java, particularly with Hibernate, and simultaneously targeting Oracle and Postgres databases? In particular I am looking at Oracle Spatial and PostGIS. We want to create a Java based SOA which can be used with both Oracle Spatial and PostGIS back ends. I've used Hi...
I have a a table with a column groups INTEGER NULL. It has values groups 5 7 <NULL> If I do a select sum(groups) form table_name I would get 12. How can I get null, when the column being summed has a null. ...
I want a bijection between the pair (tag1, tag2) and tag_id. CREATE TABLE tags ( question_id INTEGER NOT NULL, tag_id SERIAL NOT NULL, tag1 VARCHAR(20), tag2 VARCHAR(20), PRIMARY KEY(question_id, tag_id), (tag1, tag2) UNIQUE references tags(tag_id) #How? ); I want no ...
I am trying to search questions which have a given tag. How can you fix the following problem? Tables questions | tags -------------------|----------------- question_id | tag title | question_id was_sent_at_time | My code SELECT question_id, title FROM questions WHERE question_...
How can you select question_id and tags for a question when one of the tags is known? I am building a tag-search which searches questions by one tag such that the result shows the question and its tags including the one which was used in the search. I use the same tables as in this question. They are Tables questions | ...
Is it normal behaviour for a DataReader to return the rows from a query out-of-order? I'm fetching some rows from a PostgreSQL 8.3.7 database and am using ORDER BY, LIMIT and OFFSET as follows: SELECT id, name FROM tbl_foo ORDER BY name LIMIT 10 OFFSET 0; If I run this query manually the results are sorted and then the first ten rows...
I have a postgresql server process each running in my desktop and the laptop. Both servers have a database called MG with exactly same scheme/layout. Now I enter the data in to similar tables but at differing times. I generally keep the primary keys seperate so that they don't clash with each other. eg: oddnumber pkey for laptop and ev...
I am curious as to whether CREATE INDEX idx ON tbl (columns); vs. CREATE UNIQUE INDEX idx ON tbl (columns); has a significant algorithmic performance benefit in PostgreSQL or MySQL implementations when scanning the indexed column(s), or whether the UNIQUE keyword simply introduces a unique constraint alongside the index. I imagin...
I maintain a software stack consisting of Perl and Cyrus IMAP among other things. Perl seems to be working fine and Cyrus cyradm (a perl script) works fine too. However, sieveshell will not execute and reason for asking for help here. When I run sieveshell, I get the follow output: Can't load '/usr/local/pozix/perl-5.10.0/lib/site...
This is somewhat related to this question: I have a table with a primary key, and I have several tables that reference that primary key (using foreign keys). I need to remove rows from that table, where the primary key isn't being referenced in any of those other tables (as well as a few other constraints). For example: Group groupid...