Is there a way to select records based using an if statement? My table looks like this: id | num | dis 1 | 4 | 0.5234333 2 | 4 | 8.2234 3 | 8 | 2.3325 4 | 8 | 1.4553 5 | 4 | 3.43324 And I want to select the num and dis where dis is the lowest number... So, a query that will produce the following results: id | num |...
I want to use sqlite memory database for all my testing and Postgresql for my development/production server. But the SQL syntax is not same in both dbs. for ex: SQLite has autoincrement, and Postgresql has serial Is it easy to port the SQL script from sqlite to postgresql... what are your solutions? If you want me to use standard SQL...
I'm following these instructions in order to set up Django on Windows. I have installed Python 2.6, PostgreSQL 8.4, Psycopg 2.0.14 for Python 2.6 and the latest version of Django from SVN. I'm now following these instructions to run a test project (copied from the page linked to above): C:\Documents and Settings\John>cd C:\ C:\>mkdir d...
How do you unit test your python DAL that is using postgresql. In sqlite you could create in-memory database for every test but this cannot be done for postgresql. I want a library that could be used to setup a database and clean it once the test is done. I am using Sqlalchemy as my ORM. ...
Hi everyone, as the output of this command createdb -E UTF8 --lc-collate=hr_HR.UTF8 --lc-ctype=hr_HR.UTF8 tempdb I get createdb: database creation failed: ERROR: invalid locale name hr_HR.UTF8 I'm a noob in postgresql so any help will be appreciated! As you can see from above command, I need to create database with support for Cr...
INSERT INTO MISSION_OBJECTIVE( MSN_INT_ID, MO_INT_ID, MO_MSN_CLASS_NM, MO_MSN_CLASS_CD, MO_MSN_TYPE, MO_PRIORITY, MO_COMMENT, MO_START_DT, MO_END_DT, ASP_AIRSPACE_NM, MO_OBJ_LOCATION, MO_ALO_LEG_ID, MO_ALO_ARRIVE_LOC) SELECT '1025', '1', 'AIRDROP', 'ADP', 'LAPES', NULL, COALESCE( NULL, ' '), TO_TIMESTAMP( '1002260900', 'YYMMDDHH24MI'), T...
We have a situation where walmanager is being used to ship wal files between a master and a slave Postgres database. The slave machine has failed and has had to have been rebuilt. This has caused a lot of unconsumed wal files to build up on the master. If a reboot is issued to the Postgres master, and there are 24 hours worth of unc...
It's a CentOS server (I don't know the specs) and just before anybody states the obvious, keep in mind these mitigating factors: the server does a nightly VACUUM job all the tables are indexed it's pretty much read only (meaning the DBs are not increasing in size) the number of queries being ran has been the same every month Here's a...
I have 2 tables that look like this: Table "public.phone_lists" Column | Type | Modifiers ----------+-------------------+-------------------------------------------------------------------- id | integer | not null default nextval(('"phone_list...
I am writing web application using ASP.NET MVC + NHibernate + Postres stack. I wonder if images uploaded should be stored in database as binary blobs or on filesystem (and reference only in db). One advantage of db storage I can think of is easy backup/recovery of all data without reverting to filesystem copy tools. On the other hand I ...
I'm new to postgresql, and locally, I use pgadmin3. On the remote server, however, I have no such luxury... I've already created the backup of the database and scp'd it over, but, is there a way to restore a backup from the command line? I only see things related to guis or to pg_dumps, so, if someone can tell me how to go about this,...
Hello, I am having some problems trying to work with PostgreSQL and Hibernate, more specifically, the issue mentioned in the title. I've been searching the net for a few hours now but none of the found solutions worked for me. I am using Eclipse Java EE IDE for Web Developers. Build id: 20090920-1017 with HibernateTools, Hibernate 3, ...
Hey guys, trying to optimize this query to solve a duplicate user issue: SELECT userid, 'ismaster' AS name, 'false' AS propvalue FROM user WHERE userid NOT IN (SELECT userid FROM userprop WHERE name = 'ismaster'); The problem is that the select after the NOT IN is 120.000 records and it's taking forever. Using the explain prefix as ...
i have a pgsql table with fields id, identifier, name. id serial NOT NULL, identifier character varying(16), name character varying(128) I want to fetchAll values from the table orderby identifier. but identifier is having values 12, 100, 200, 50 and after $table->fetchAll(null, 'identifier'); is giving the result 100, 12, ...
I have this problem, I store a number in a database field. After a while I check if a value exist and if it does I take the number and add 1 this works fine up until 10, when I add a number to 10 it gets reset to 2 as if the zero is not counted for. $row = pg_fetch_array($result,0); $count = (int)$row[1]['count']; $count++; Table ...
I'm trying to generate entity model from my Postgresql database. Under .net 3.5 and vs2008 it worked fine. Unfortunately under vs10 it doesn't. I get an error: "The file references an XML namespace that is inconsistend with the target framework of the project." I assume it's because EF4 is using EDMX v2 not v1, but I cannot cha...
I use Postgresql with the PostGIS extensions for ad-hoc spatial analysis. I generally construct and issue SQL queries by hand from within psql. I always wrap an analysis session within a transaction, so if I issue a destructive query I can roll it back. However, when I issue a query that contains an error, it cancels the transaction. ...
Hello, i have a problem with me trigger. Every time i insert a new line i will check if the article not sold. I can do it in the software but i think its better when the DB this does. -- Create function CREATE OR REPLACE FUNCTION checkSold() RETURNS TRIGGER AS $checkSold$ BEGIN SELECT offer_id FROM offer WHERE offer_id = NE...
So I set a variable in my main ruby file that's handling all my post and get requests and then use ERB templates to actually show the pages. I pass the database handler itself into the erb templates, and then run a query in the template to get all (for this example) grants. In my main ruby file: grants_main_order = "id_num" get '/gran...
Somehow I've managed to completely bugger the install of postgresql on Ubuntu karmic. I want to start over from scratch, but when I "purge" the package with apt-get it still leaves traces behind such that the reinstall configuration doesn't run properly. After I've done: apt-get purge postgresql apt-get install postgresql It said S...