Search Results

Search found 8664 results on 347 pages for 'rails postgresql'.

Page 38/347 | < Previous Page | 34 35 36 37 38 39 40 41 42 43 44 45  | Next Page >

• Calculating correlation coefficient using PostgreSQL?

  - by Dave

  I have worked out how to calculate the correlation coefficient between two fields if both are in the same table: SELECT corr(column1, column2) FROM table WHERE <my filters>; ...but I can't work out how to do it when the columns are from different tables (I need to apply the same filters to both tables). Any hints, please?

  Read the article

• PostgreSQL: keep a certain number of records in a table

  - by Alexander Farber

  Hello, I have an SQL-table holding the last hands received by a player in card game. The hand is represented by an integer (32 bits == 32 cards): create table pref_hand ( id varchar(32) references pref_users, hand integer not NULL check (hand > 0), stamp timestamp default current_timestamp ); As the players are playing constantly and that data isn't important (just a gimmick to be displayed at player profile pages) and I don't want my database to grow too quickly, I'd like to keep only up to 10 records per player id. So I'm trying to declare this PL/PgSQL procedure: create or replace function pref_update_game(_id varchar, _hand integer) returns void as $BODY$ begin delete from pref_hand offset 10 where id=_id order by stamp; insert into pref_hand (id, hand) values (_id, _hand); end; $BODY$ language plpgsql; but unfortunately this fails with: ERROR: syntax error at or near "offset" because delete doesn't support offset. Does anybody please have a better idea here? Thank you! Alex

  Read the article

• Postgresql constraint

  - by Ryan

  I cannot seem to get this right, I am trying to modify a field to be a foreign key, with cascading delete... what am i doing wrong? ALTER TABLE my_table ADD CONSTRAINT $4 FOREIGN KEY my_field REFERENCES my_foreign_table ON DELETE CASCADE;

  Read the article

• Dynamic upsert in postgresql

  - by Daniel

  I have this upsert function that allows me to modify the fill_rate column of a row. CREATE FUNCTION upsert_fillrate_alarming(integer, boolean) RETURNS VOID AS ' DECLARE num ALIAS FOR $1; dat ALIAS FOR $2; BEGIN LOOP -- First try to update. UPDATE alarming SET fill_rate = dat WHERE equipid = num; IF FOUND THEN RETURN; END IF; -- Since its not there we try to insert the key -- Notice if we had a concurent key insertion we would error BEGIN INSERT INTO alarming (equipid, fill_rate) VALUES (num, dat); RETURN; EXCEPTION WHEN unique_violation THEN -- Loop and try the update again END; END LOOP; END; ' LANGUAGE 'plpgsql'; Is it possible to modify this function to take a column argument as well? Extra bonus points if there is a way to modify the function to take a column and a table.

  Read the article

• Why is this postgresql query so slow?

  - by user315975

  I'm no database expert, but I have enough knowledge to get myself into trouble, as is the case here. This query SELECT DISTINCT p.* FROM points p, areas a, contacts c WHERE ( p.latitude > 43.6511659465 AND p.latitude < 43.6711659465 AND p.longitude > -79.4677941889 AND p.longitude < -79.4477941889) AND p.resource_type = 'Contact' AND c.user_id = 6 is extremely slow. The points table has fewer than 2000 records, but it takes about 8 seconds to execute. There are indexes on the latitude and longitude columns. Removing the clause concering the resource_type and user_id make no difference. The latitude and longitude fields are both formatted as number(15,10) -- I need the precision for some calculations. There are many, many other queries in this project where points are compared, but no execution time problems. What's going on?

  Read the article

• postgresql duplicate table names best practice

  - by veilig

  My company has a handful of apps that we deploy in the websites we build. Recently a very old app needed to be included along side a newer app and there was a conflict w/ a duplicate table name needed to be used by both apps. We are now in the process of updating an old app and there will be some DB updates. I'm curious what people consider best practice (or how do you do it) to help ensure these name collisions don't happen. I've looked at schema's but not sure if thats the right path we want to take. As the documentation prescribes, I don't want to "wire" a particular schema name into an application and if I add schema's to the user search path how would it know which table I was referring to if two schema's have the same table name. although, maybe I'm reading to much into this. Any insights or words of wisdom would be greatly appreciated!

  Read the article

• efficiently trimming postgresql tables

  - by agilefall

  I have about 10 tables with over 2 million records and one with 30 million. I would like to efficiently remove older data from each of these tables. My general algorithm is: create a temp table for each large table and populate it with newer data truncate the original tables copy tmp data back to original tables using: "insert into originaltable (select * from tmp_table)" However, the last step of copying the data back is taking longer than I'd like. I thought about deleting the original tables and making the temp tables "permanent", but I lose constraint/foreign key info. If I delete from the tables directly, it takes much longer. Given that I need to preserve all foreign keys and constraints, are there any faster ways of removing the older data? Thanks.

  Read the article

• how to declare variable in PostgreSQL

  - by user307880

  I try to declare a variable in a code like this, but it's doesn't work. Can you tell me what's the problem? ERROR: syntax error at or near "VARCHAR" LINE 2: p_country VARCHAR; DECLARE p_country VARCHAR; p_country : = ''; SELECT p_country;

  Read the article

• PostgreSQL function to iterate through/act on many rows with state

  - by Claudiu

  I have a database with columns looking like: session | order | atype | amt --------+-------+-------+----- 1 | 0 | ADD | 10 1 | 1 | ADD | 20 1 | 2 | SET | 35 1 | 3 | ADD | 10 2 | 0 | SET | 30 2 | 1 | ADD | 20 2 | 2 | SET | 55 It represents actions happening. Each session starts at 0. ADD adds an amount, while SET sets it. I want a function to return the end value of a session, e.g. SELECT session_val(1); --returns 45 SELECT session_val(2); --returns 55 Is it possible to write such a function/query? I don't know how to do any iteration-like things with SQL, or if it's possible at all.

  Read the article

• how to restore postgresql DB without backup

  - by pukipuki

  Forgot to make a backup. Now I have harddrive with databases and new system with empty postgres. Can I somehow restore databases? by simple copy of files etc?

  Read the article

• String literals and escape characters in postgresql

  - by rjohnston

  Attempting to insert an escape character into a table results in a warning. For example: create table EscapeTest (text varchar(50)); insert into EscapeTest (text) values ('This is the first part \n And this is the second'); Produces the warning: WARNING: nonstandard use of escape in a string literal (Using PSQL 8.2) Anyone know how to get around this?

  Read the article

• PostgreSQL - how to quickly drop a user with existing privileges

  - by Sii

  I'm trying to make restricted DB users for the app I'm working on, and I want to drop the Postgres database user I'm using for experimenting. Is there any way to drop the user without having to revoke all his rights manually first, or revoke all the grants a user has?

  Read the article

• PHP syntax for postgresql Mixed-case table names

  - by yam

  I have a code below: <?php require "institution.php" /* in this portion, query for database connection is executed, and */ $institution= $_POST['institutionname']; $sCampID = 'SELECT ins_id FROM institution where ins_name= '$institution' '; $qcampID = pg_query($sCampID) or die("Error in query: $query." . pg_last_error($connection)); /* this portion outputs the ins_id */ ?> My database before has no mixed-case table names, that's why when I run this query, it shows no error at all. But because I've changed my database for some reasons, and it contains now mixed-case table names, i have to change the code above into this one: $sCampID = 'SELECT ins_id FROM "Institution" where ins_name= '$institution' '; where the Institution has to be double quoted. The query returned parse error. When i removed this portion: where ins_name= '$institution', no error occured. My question is how do I solve this problem where the table name which contains a mixed-case letter and a value stored in a variable ($institution in this case) will be combined in a single select statement? Your answers and suggestions will be very much appreciated.

  Read the article

• Strange postgresql behavior

  - by Sergey

  Hi can someone explain me why it works like this? = select client_id from clients_to_delete; ERROR: column "client_id" does not exist at character 8 but, when putting this inside an IN()... = select * from orders where client_id in(select client_id from clients_to_delete); it works! and select all rows in the orders table. Same when running delete/update. Why it doesn't produce an error like before? Thank you!

  Read the article

• Postgresql full text search part of words

  - by Grezly

  Is postresql capable of doing a full text search, based on 'half' a word? For example i'm trying to seach foor tree, but i tell postgres to search for 'tr'. I can't find such a solution that is capable of doing this. Currently i'm using this select * from test, to_tsquery('tree') as q where vectors @@ q ; But i like to do something like this: select * from test, to_tsquery('tr%') as q where vectors @@ q ;

  Read the article

• Return multiple results using dynamic sql (postgresql 8.2)

  - by precose

  I want to loop through schemas and get a result set that looks like this: Count 5 834 345 34 984 However, I can't get it to return anything using dynamic sql...I've tried everything but 8.2 is being a real pain. Here is my function: CREATE OR REPLACE FUNCTION dwh.adam_test4() RETURNS void LANGUAGE plpgsql AS $function$ DECLARE myschema text; rec RECORD; BEGIN FOR myschema IN select distinct c.table_schema, d.p_id from information_schema.tables t inner join information_schema.columns c on (t.table_schema = t.table_schema and t.table_name = c.table_name) join dwh.sgmt_clients d on c.table_schema = lower(d.userid) where c.table_name = 'fact_members' and c.column_name = 'debit_card' and t.table_schema NOT LIKE 'pg_%' and t.table_schema NOT IN ('information_schema', 'ad_delivery', 'dwh', 'users', 'wand', 'ttd') order by table_schema LOOP EXECUTE 'select count(ucic) from '|| myschema || '.' ||'fact_members where debit_card = ''yes''' into rec; RETURN rec; END LOOP; END $function$

  Read the article

• Stored Procedure in postgresql, multiple queries w/ agreggates.

  - by fenix

  I'm trying to write a store procedure that can take some input parameters (obviously), run multiple queries against those, taking the output from those and doing calculations, and from those calculations and the original queries, outputting a formatted text string like: Number of Rows for max(Z) matching condition x and y of total rows matching x (x&y/x*100). To explain the max(Z) bit, this will be the username field, it won't matter which actual entry is picked, because the where clause will filter the results by user id, is there a saner way to do this?

  Read the article

• How to get the top most parent in PostgreSQL

  - by wereach

  I have a tree structure table with columns: id,parent,name. Given a tree A-B-C, how could i get the most top parent A's ID according to C's ID? Especially how to write SQL with "with recursive"? Thanks!

  Read the article

• IntegrityError with Booleand Fields and Postgresql

  - by xRobot

  I have this simple Blog model: class Blog(models.Model): title = models.CharField(_('title'), max_length=60, blank=True, null=True) body = models.TextField(_('body')) user = models.ForeignKey(User) is_public = models.BooleanField(_('is public'), default = True) When I insert a blog in admin interface, I get this error: IntegrityError at /admin/blogs/blog/add/ null value in column "is_public" violates not-null constraint Why ???

  Read the article

• Querying Postgresql with a very large result set

  - by sanity

  In an application I need to query a Postgres DB where I expect tens or even hundreds of millions of rows in the result set. I might do this query once a day, or even more frequently. The query itself is relatively simple, although may involve a few JOINs. My question is: How smart is Postgres with respect to avoiding having to seek around the disk for each row of the result set? Given the time required for a hard disk seek, this could be extremely expensive. If this isn't an issue, how does Postgres avoid it? How does it know how to lay out data on the disk such that it can be streamed out in an efficient manner in response to this query?

  Read the article

• Mongrel Cluster on Ubuntu Server Karmic

  - by trobrock

  I am trying to get mongrel cluster working on my Ubuntu Server Karmic box in preparation to setup Capistrano. I've been trying to get the two to work all day and finally decided to completely remove Capistrano and see if I can just get Mongrel Cluster to work. I ran this to install mongrel cluster: gem install mongrel mongrel_cluster Everything installed fine, when I change into my app's directory... # mongrel_rails -bash: mongrel_rails: command not found I can run it from its install location: # /var/lib/gems/1.8/bin/mongrel_rails Usage: mongrel_rails <command> [options] Available commands are: ... It lets me build the cluster configuration file fine, but when I run the clister:start command: # /var/lib/gems/1.8/bin/mongrel_rails cluster::start starting port 8000 /usr/lib/ruby/1.8/rubygems/custom_require.rb:31: command not found: mongrel_rails start -d -e production -p 8000 -P tmp/pids/mongrel.8000.pid -l log/mongrel.8000.log starting port 8001 /usr/lib/ruby/1.8/rubygems/custom_require.rb:31: command not found: mongrel_rails start -d -e production -p 8001 -P tmp/pids/mongrel.8001.pid -l log/mongrel.8001.log starting port 8002 /usr/lib/ruby/1.8/rubygems/custom_require.rb:31: command not found: mongrel_rails start -d -e production -p 8002 -P tmp/pids/mongrel.8002.pid -l log/mongrel.8002.log It seems it isnt calling it from the right directory after that command, what can I do to fix this? I tried setting the path previously when trying to set up Capistrano, but the path didnt stay set when Capistrano used ssh to run the commands.

  Read the article

• What does the * symbol do near a function argument and how to use that in others scenarios?

  - by user502052

  I am using Ruby on Rails 3 and I would like to know what means the presence of a *simbol near a function argument and to understand its usages in others scenarios. Example scenario (this method was from the Ruby on Rails 3 framework: def find(*args) return to_a.find { |*block_args| yield(*block_args) } if block_given? options = args.extract_options! if options.present? apply_finder_options(options).find(*args) else case args.first when :first, :last, :all send(args.first) else find_with_ids(*args) end end end

  Read the article

• I simple search controller that stores search history, should I use resource routing or non-resource?

  - by vfilby

  I am learning rails and am toying with a simple web-app that integrates with flickr to search photos based on user given criteria and store the query in a search history table. I am seeking the best or 'rails' way of handling this. Should I setup a controller and non-resource routes that handle the search and store the data in a custom table; or should I create a resource for queries with a resource route and an additional path for search?

  Read the article

• Rails Rake Error with XAMPP mysql database

  - by edu222

  I have installed XAAMP on my win7 machine and I have the apache server/mysql running on there. I set up rails to work with XAmpp as described here: XAMPP and RAILS This tutorial advises you to add this code to the XAMPP httpd.connf : Listen 3000 LoadModule rewrite_module modules/mod_rewrite.so ################################# # RUBY SETUP ################################# <virtualHost *:3000> ServerName rails DocumentRoot "c:/xampp/htdocs/FirstProject/public" <Directory "c:/xampp/htdocs/FirstProject/public/"> Options ExecCGI FollowSymLinks AllowOverride all Allow from all Order allow,deny AddHandler cgi-script .cgi AddHandler fastcgi-script .fcgi </Directory> </VirtualHost> ################################# # RUBY SETUP ################################# Xampp runs on the default localhost and mysql remains unchanged without a pw. I created a rails app with a mysql database like this: rails -d mysql C:/xampp/htdocs/FirstProject Then I started the ruby script/server from within the FirstProject location The localhost:3000/ shows the classic rails welcome I then ran a basic scaffold command: ruby script/generate scaffold FirstProject name:string email:string <br/> When I run the rake db:migrate command I get the following error: C:\xampp\htdocs\FirstProject>rake db:migrate --trace (in C:/xampp/htdocs/FirstProject) ** Invoke db:migrate (first_time) ** Invoke environment (first_time) ** Execute environment ** Execute db:migrate rake aborted! undefined method `init' for Mysql:Class C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/mysql_adapter.rb:70:in `mysql_connection' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_pool.rb:223:in `send' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_pool.rb:223:in `new_connection' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_pool.rb:245:in `checkout_new_connection' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_pool.rb:188:in `checkout' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_pool.rb:184:in `loop' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_pool.rb:184:in `checkout' C:/Ruby/lib/ruby/1.8/monitor.rb:242:in `synchronize' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_pool.rb:183:in `checkout' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_pool.rb:98:in `connection' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_pool.rb:326:in `retrieve_connection' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_specification.rb:123:in `retrieve_connection' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_a dapters/abstract/connection_specification.rb:115:in `connection' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/migration.rb :435:in `initialize' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/migration.rb :400:in `new' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/migration.rb :400:in `up' C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/migration.rb :383:in `migrate' C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.3.5/lib/tasks/databases.rake:116 C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:636:in `call' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:636:in `execute' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:631:in `each' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:631:in `execute' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:597:in `invoke_with_call_c hain' C:/Ruby/lib/ruby/1.8/monitor.rb:242:in `synchronize' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:590:in `invoke_with_call_c hain' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:583:in `invoke' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2051:in `invoke_task' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:in `top_level' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:in `each' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:in `top_level' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exceptio n_handling' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2023:in `top_level' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2001:in `run' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exceptio n_handling' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:1998:in `run' C:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/bin/rake:31 C:/Ruby/bin/rake:19:in `load' C:/Ruby/bin/rake:19 Any idea on how to fix this? Thanks in advance

  Read the article

• Is Rails Metal (& Rack) a good way to implement a high traffic web service api?

  - by Greg

  I am working on a very typical web application. The main component of the user experience is a widget that a site owner would install on their front page. Every time their front page loads, the widget talks to our server and displays some of the data that returns. So there are two components to this web application: the front end UI that the site owner uses to configure their widget the back end component that responds to the widget's web api call Previously we had all of this running in PHP. Now we are experimenting with Rails, which is fantastic for #1 (the front end UI). The question is how to do #2, the back serving of widget information, efficiently. Obviously this is much higher load than the front end, since it is called every time the front page loads on one of our clients' websites. I can see two obvious approaches: A. Parallel Stack: Set up a parallel stack that uses something other than rails (e.g. our old PHP-based approach) but accesses the same database as the front end B. Rails Metal: Use Rails Metal/Rack to bypass the Rails routing mechanism, but keep the api call responder within the Rails app My main question: Is Rails/Metal a reasonable approach for something like this? But also... Will the overhead of loading the Rails environment still be too heavy? Is there a way to get even closer to the metal with Rails, bypassing most of the environment? Will Rails/Metal performance approach the perf of a similar task on straight PHP (just looking for ballpark here)? And... Is there a 'C' option that would be much better than both A and B? That is, something before going to the lengths of C code compiled to binary and installed as an nginx or apache module? Thanks in advance for any insights.

  Read the article

< Previous Page | 34 35 36 37 38 39 40 41 42 43 44 45  | Next Page >