Hello everybody, I've been reading StackOverflow for quite a while now and I'm only now building the nerve to ask a question. I'm 20 years old and currently enrolled in college in IT here in my hometown (Cluj-Napoca, Romania). Enough for introductions :D. Basically I have my little software firm that provides Book-keeping appz. They're...
The following query returns data right away: SELECT time, value from data order by time limit 100; Without the limit clause, it takes a long time before the server starts returning rows: SELECT time, value from data order by time; I observe this both by using the query tool (psql) and when querying using an API. Questions/issues: ...
Well, I have MAMP installed on my Mac OS X, and I've got PostGres up and running, but am wondering how I can use it with PHP? I'm assuming that there's something I need to do where I compile PHP so that it can use both, but, I'm not sure how to do that if PHP has already been installed? Or, if there's something unique I need to do to g...
Hi, I have quite complicated query from which I would like to create a view. The query looks like this: select s.avg as c3, fs.bayes, fs.sure, fs.visu, fs.fstd from ( SELECT AVG(q.c3), COUNT(q.c3), q.std FROM ( SELECT std, c3, ROW_NUMBER() OVER (PARTITION BY std ORDER BY id) AS rn FROM ssims WHERE obr...
I am trying to run hibernate on a PostgreSQL 8.4.2 DB. Whenever I try to run a simple java code like: List<User> users = service.findAllUsers(); I get the following error: PSQLException: ERROR: relation "TABLE_NAME" does not exist Since I have option hibernate.show_sql option set to true, I can see that hibernate is trying to run ...
Hello, I have just setup a OpenBSD little personal development server running PHP with an almost-default(enabled debugging messages) php.ini and installed PostgreSQL with the default configuration. Well, running this very simple code $starttimer=time()+microtime(); $dbconn = pg_connect("host=localhost port=5432 dbname=earlzblog_001 us...
I simply have a table that contains a list of countries and their ISO country codes. I'm wrapping the query in a stored procedure (aka function) such as: CREATE OR REPLACE FUNCTION get_countries( ) RETURNS setof record AS $$ SELECT country_code, country_name FROM country_codes $$ LANGUAGE sql; The error I ...
In a PostgreSQL database I have a table with a primary key and another field which needs to be unique. CREATE TABLE users ( id INTEGER PRIMARY KEY DEFAULT nextval('groups_id_seq'::regclass), name VARCHAR(255) UNIQUE NOT NULL ); INSERT users (name) VALUES ('foo'); INSERT users (name) VALUES ('foo'); INSERT user...
Can any one show me working example of using cursor returned from PLSQL to c# code? I found many examples showing how to fill dataSet with returned data but I cannot find how to use that cursor with DataReader, as a result I have {unnamed portal}. NpgsqlTransaction tr = (NpgsqlTransaction) Connection.BeginTransaction(); NpgsqlCommand cu...
Hi, I just switched to mac, I was previously working on Linux. I am trying to use posgresql for my project When I start the server, it works fine. Nevertheless when I use rake:db:migrate, it fails saying: Please install the postgresql adapter: gem install activerecord-postgresql-adapter (no such file to load -- pg) I have the gem "pg...
I know I can convert SQL timestamp to unix timestamp, using the following way. SELECT extract(epoch FROM now()); Now, I have a stored procedure function, which will directly return a table row to the caller. One of the row field is "timestamp" type. In my application, I am using libpq. I wish to use libpq functions (or any c/c++ func...
Has anyone successfully configured JDO datanucleus default to google app engine to work on a local database? Why am I always getting an error in jdoconfig.xml when I have specified the property "datanucleus.storeManagerType" with value "rdbms." at the end part. I tried googling but seems no luck. Caused by: org.datanucleus.exceptions....
Hi, How would one go about cancelling execution of a query statement using pyscopg2 (the python Postgres driver)? As an example, let's say I have the following code: import psycopg2 cnx_string = "something_appropriate" conn = psycopg2.connect(cnx_string) cur = conn.cursor() cur.execute("long_running_query") Then I want to cancel th...
I was using mysql just fine until I recently switched one of my rails apps to heroku and had to change over. Almost everything works as expected except I have one query which does something totally funky. This is the postgres, but under mysql it is mostly identical except for the EXTRACT DOW and some group by additions, but that isn't ...
I'm trying to return a Custom type from a PostgreSQL function as follows: DROP TYPE IF EXISTS GaugeSummary_GetDateRangeForGauge_Type CASCADE; -- Drop our previous type CREATE TYPE GaugeSummary_GetDateRangeForGauge_Type AS -- Recreate our type ( Minimum timestamp without time zone, Maximum timestamp without time zone ); ...
I'm trying to figure out some sliding window stats on my users. I have a table with a user, and columns such as created_at and verified_at. For each month, I'd like to find out how many users registered (a simple group by date_trunc of the created_at), and then of those people, how many verified within my sliding window (call it 60 day...
Hello, I am making a simple blog for myself and while reading through the PHP manual, I found this http://us2.php.net/manual/en/function.pg-query.php It says resource pg_query ([ resource $connection ], string $query ) ... Note: Although connection can be omitted, it is not recommended, since it can be the cause of...
I'd like to get in one query a post and the first comment associated with the post. Here is how I do it in PostgreSQL: SELECT p.post_id, (select * from (select comment_body from comments where post_id = p.post_id order by created_date asc) where rownum=1 ) the_first_comment FROM posts p and it works fine. However, in Oracle I'...
Working solution: CREATE OR REPLACE FUNCTION my_search(tsq tsquery, translation_id integer, lang regconfig, count integer DEFAULT 10, skip integer DEFAULT 0) RETURNS TABLE(id int, chapter int, number int, headline text) AS $$ SELECT id, chapter, number, ts_headline($3, text, $1, 'StartSel = <em>, StopSel = </em>'::text) FROM ( ...
I have not done much RoR development. Most I've done is played around with it or went over few chapters in AWDR book. Now, though, I'm starting to do a real project in Rails and one of the client requirement is that they want the DB to be PostgreSQL. I know that rails comes with its own SqlLite. I am wondering what is the best option...