Search Results

Search found 1922 results on 77 pages for 'postgresql contrib'.

Page 41/77 | < Previous Page | 37 38 39 40 41 42 43 44 45 46 47 48  | Next Page >

• relating data stored in NoSQL DB to data stored in SQL DB

  - by seanbrant

  Whats the best way to use a SQL DB along side a NoSQL DB? I want to keep my users and other data in postgres but have some data that would be better suited for a NoSQL DB like redis. I see a lot of talk about switching to NoSQL but little talk on integrating it with existing systems. I think it would be foolish to throw the baby out with the bath water and ditch SQL all together, unless it makes things easier to maintain and develop. I'm wondering what the best approach is for relating data stored in SQL to my data in redis. I was thinking of something along the line of this. User object stored in SQL Book object in redis, key sh1 hash of value, value is a JSON string Relations stored in redis, key User.pk:books, value redis set of sha1's Anyone have experience, tips, better ways?

  Read the article

• How to manage db connections on server?

  - by simpatico

  I have a severe problem with my database connection in my web application. Since I use a single database connection for the whole application from singleton Database class, if i try concurrent db operations (two users) the database rollsback the transactions. This is my static method used: All threads/servlets call static Database.doSomething(...) methods, which in turn call the the below method. private static /* synchronized*/ Connection getConnection(final boolean autoCommit) throws SQLException { if (con == null) { con = new MyRegistrationBean().getConnection(); } con.setAutoCommit(true); //TODO return con; } What's the recommended way to manage this db connection/s I have, so that I don't incurr in the same problem.

  Read the article

• How to get min/max of two integers in Postgres/SQL?

  - by HRJ

  How do I find the maximum (or minimum) of two integers in Postgres/SQL? One of the integers is not a column value. I will give an example scenario: I would like to subtract an integer from a column (in all rows), but the result should not be less than zero. So, to begin with, I have: UPDATE my_table SET my_column = my_column - 10; But this can make some of the values negative. What I would like (in pseudo code) is: UPDATE my_table SET my_column = MAXIMUM(my_column - 10, 0);

  Read the article

• How to test crontab entry?

  - by Mark

  I have an entry in my crontab that looks like this: 0 3 * * * pg_dump mydb | gzip > ~/backup/db/$(date +%Y-%m-%d).psql.gz That script works perfectly when I execute it from the shell, but it doesn't seem to be running every night. I'm assuming there's something wrong with the permissions, maybe crontab is running under a different user or something. How can I debug this? I'm a shared hosting environment (WebFaction).

  Read the article

• PostgeSQL: Arrays Data Type with PHP

  - by ArchJ

  I'm working on PostgeSQL with PHP and I know that PosrgeSQL allow columns of a table to be defined as arrays. So let's say I have a table like this: CREATE TABLE sal_emp ( a text ARRAY, b text ARRAY, c text ARRAY, ); These are my arrays: $a = array(aa,bb,cc); $b = array(dd,dd,aa); $c = array(bb,ff,ee); and I want to insert them into respective column each like this: a | b | c -----------+------------+------------ {aa,bb,cc} | {dd,dd,aa} | {bb,ff,ee} Can I insert it this way? $a = implode(',', $a); $b = implode(',', $b); $c = implode(',', $c); $a = array('a' => $a, 'b' => $b, 'c' => $c); pg_insert($dbconn, 'table', $a); Or is there a better way to achieve the same result?

  Read the article

• Why would a TableAdapter populate a DataSet with "1/1/2000" for an entire timestamp column?

  - by Rob

  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 performed programmatically before the issue was discovered. The DataColumn type is DateType, while the database (Postgres) column type is timestamp. Up until recently, this was all playing very nicely. I noticed the issue in a bound DataGridView control, and verified that this is not related to data binding by utilizing the 'Preview Data' option in the VS DataSet Editor. Usually when I notice unexpected values popping up in my application it's related to a mis-configured property, type conflict, or another silly mistake I've made. So after checking properties and types, and even recreating the TableAdapter from scratch, to say I'm a little baffled is an understatement. Does anyone have any ideas of what I could do to fix the issue and/or diagnose the cause?

  Read the article

• unique constraint (w/o Trigger) on "one-to-many" relation

  - by elgcom

  To illustrate the problem, I make an example: A tag_bundle consists of one or more than one tags. A unique tag combination can map to a unique tag_bundle, vice versa. tag_bundle tag tag_bundle_relation +---------------+ +--------+ +---------------+--------+ | tag_bundle_id | | tag_id | | tag_bundle_id | tag_id | +---------------+ +--------+ +---------------+--------+ | 1 | | 100 | | 1 | 100 | +---------------+ +--------+ +---------------+--------+ | 101 | | 1 | 101 | +--------+ +---------------+--------+ There can't be another tag_bundle having the combination from tag 100 and tag 101. How can I ensure such unique constraint when executing SQL "concurrently"!! that is, to prevent concurrently adding two bundles with the same tag combination Adding a simple unique constraint on any table does not work, Is there any solution other than Trigger or explicit lock. I come to only this simple way: make tag combination into string, and let it be unique. tag_bundle (unique on tags) tag tag_bundle_relation +---------------+--------+ +--------+ +---------------+--------+ | tag_bundle_id | tags | | tag_id | | tag_bundle_id | tag_id | +---------------+--------+ +--------+ +---------------+--------+ | 1 | 100,101| | 100 | | 1 | 100 | +---------------+--------+ +--------+ +---------------+--------+ | 101 | | 1 | 101 | +--------+ +---------------+--------+ but it seems not a good way :(

  Read the article

• Get the last N rows in the database in order?

  - by Kristopher

  Let's say I have the following database table: record_id | record_date | record_value -----------+-------------+-------------- 1 | 2010-05-01 | 195.00 2 | 2010-07-01 | 185.00 3 | 2010-09-01 | 175.00 4 | 2010-05-01 | 189.00 5 | 2010-06-01 | 185.00 6 | 2010-07-01 | 180.00 7 | 2010-08-01 | 175.00 8 | 2010-09-01 | 170.00 9 | 2010-10-01 | 165.00 I want to grab the last 5 rows with the data ordered by record_date ASC. This is easy to do with: SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5 OFFSET 4 Which would give me: record_id | record_date | record_value -----------+-------------+-------------- 6 | 2010-07-01 | 180.00 7 | 2010-08-01 | 175.00 3 | 2010-09-01 | 175.00 8 | 2010-09-01 | 170.00 9 | 2010-10-01 | 165.00 But how do I do this when I don't know how many records there are and can't compute the magic number of 4? I've tried this query, but if there are less than 5 records, it results in a negative OFFSET, which is invalid: SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5 OFFSET (SELECT COUNT(*) FROM mytable) - 5; So how do I accomplish this?

  Read the article

• Search a string to find which records in table are inside said string

  - by Improfane

  Hello, Say I have a string. Then I have a number of unique tokens or keywords, potentially a large number in a database. I want to search and find out which of these database strings are inside the string I provide (and get the IDs of them). Is there a way of using a query to search the provided string or must it be taken to application space? Am I right in thinking that this is not a 'full text search'? Would the best method be to insert it into the database to make it a full text search?

  Read the article

• Rails: Converting from MySQL to PostGres breaks Geokit Distance Calculations???

  - by Kevin

  I recently switched my database from MySQL to PostGres. I also use GeoKit. When I started my app up with the new database already seeded, I get the following error: PGError: ERROR: function radians(character varying) does not exist LINE 1: ...COS(0.661045389762993)*COS(-2.12957994527573)*COS(RADIANS(ti... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Anyone know why this is breaking now? I know GeoKit still works because it's still performing the geocoding in the model per ticket when the database is seeded, it just won't do the distance calculations correctly.

  Read the article

• PropelBundle database:create for postgres

  - by Karol85

  I've installed propel bundle for symfony2. my database configuration is: propel: dbal: driver: pgsql user: postgres password: postgres dsn: pgsql:host=localhost;port=5432;dbname=test_database options: {} attributes: {} When i wan to create this database from console (console propel: database:create) i have got strange error : Unable to open PDO connection [wrapped: SQLSTATE[08006] [7] FATAL: database "pgsql" does not exist. i created pgsql database on my localhost and everything was good. Database "test_database" was succesfull created. Can somebody explain me why i got this previous error? On mysql i've created database without any problems.

  Read the article

• expand a varchar column very slowly , why?

  - by francs

  Hi We need to modify a column of a big product table , usually normall ddl statments will be excutely fast ,but the above ddl statmens takes about 10 minnutes?I wonder know the reason! I just want to expand a varchar column?The following is the detailsl --table size wapreader_log= select pg_size_pretty(pg_relation_size('log_foot_mark')); pg_size_pretty ---------------- 5441 MB (1 row) --table ddl wapreader_log= \d log_foot_mark Table "wapreader_log.log_foot_mark" Column | Type | Modifiers -------------+-----------------------------+----------- id | integer | not null create_time | timestamp without time zone | sky_id | integer | url | character varying(1000) | refer_url | character varying(1000) | source | character varying(64) | users | character varying(64) | userm | character varying(64) | usert | character varying(64) | ip | character varying(32) | module | character varying(64) | resource_id | character varying(100) | user_agent | character varying(128) | Indexes: "pk_log_footmark" PRIMARY KEY, btree (id) --alter column wapreader_log= \timing Timing is on. wapreader_log= ALTER TABLE wapreader_log.log_foot_mark ALTER column user_agent TYPE character varying(256); ALTER TABLE Time: 603504.835 ms

  Read the article

• What are the rules governing how a bind variable can be used in Postgres and where is this defined?

  - by Craig Miles

  I can have a table and function defined as: CREATE TABLE mytable ( mycol integer ); INSERT INTO mytable VALUES (1); CREATE OR REPLACE FUNCTION myfunction (l_myvar integer) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol = l_myvar; RETURN l_myrow; END; $$ LANGUAGE plpgsql; In this case l_myvar acts as a bind variable for the value passed when I call: SELECT * FROM myfunction(1); and returns the row where mycol = 1 If I redefine the function as: CREATE OR REPLACE FUNCTION myfunction (l_myvar integer) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol IN (l_myvar); RETURN l_myrow; END; $$ LANGUAGE plpgsql; SELECT * FROM myfunction(1); still returns the row where mycol = 1 However, if I now change the function definition to allow me to pass an integer array and try to this array in the IN clause, I get an error: CREATE OR REPLACE FUNCTION myfunction (l_myvar integer[]) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol IN (array_to_string(l_myvar, ',')); RETURN l_myrow; END; $$ LANGUAGE plpgsql; Analysis reveals that although: SELECT array_to_string(ARRAY[1, 2], ','); returns 1,2 as expected SELECT * FROM myfunction(ARRAY[1, 2]); returns the error operator does not exist: integer = text at the line: WHERE mycol IN (array_to_string(l_myvar, ',')); If I execute: SELECT * FROM mytable WHERE mycol IN (1,2); I get the expected result. Given that array_to_string(l_myvar, ',') evaluates to 1,2 as shown, why arent these statements equivalent. From the error message it is something to do with datatypes, but doesnt the IN(variable) construct appear to be behaving differently from the = variable construct? What are the rules here? I know that I could build a statement to EXECUTE, treating everything as a string, to achieve what I want to do, so I am not looking for that as a solution. I do want to understand though what is going on in this example. Is there a modification to this approach to make it work, the particular example being to pass in an array of values to build a dynamic IN clause without resorting to EXECUTE? Thanks in advance Craig

  Read the article

• Paginating data, has to be a better way

  - by John Tyler

  I've read like 10 or so "tutorials", and they all involve the same thing: Pull a count of the data set Pull the relevant data set (LIMIT, OFFSET) IE: SELECT COUNT(*) FROM table WHERE something = ? SELECT * FROM table WHERE something =? LIMIT ? offset ?` Two very similar queries, no? There has to be a better way to do this, my dataset is 600,000+ rows and already sluggish (results are determined by over 30 where clauses, and vary from user to user, but are properly indexed of course).

  Read the article

• How to install gem pg on snow leopard

  - by dorelal

  I need to install gem pg on snow leopard because I am running rake on rails codebase. I am not using postgres. This is the error I am getting. $ sudo gem install pg Password: Sorry, try again. Password: Building native extensions. This could take a while... ERROR: Error installing pg: ERROR: Failed to build gem native extension. looked at various blogs from google search but none of them works.

  Read the article

• Tomcat Postgres Connection

  - by user191207

  Hi, I'm using a singleton class for a PostgresSQL connection inside a servelet. The problem is that once it is open it works for a while (I guess until some timeout), and then it starts throwing a I/O exception. Any idea what is happening to the singleton class inside Tomcat VM? Thanks

  Read the article

• Finding all areas that intersect with a point and vice-versa - PostGIS

  - by ForeignerBR

  I'm developing a project using PostGIS to hold spatial data where I have records that hold geometry point data and records that hold geometry area data. To solve my problem I'm looking for two queries that can take geographic shapes rather than geometric shapes as parameters. For query A I need it to return all points that intersect with a given area. For query B I need it to return all areas that intersect with a given point.

  Read the article

• How to increase query speed without using full-text search?

  - by andre matos

  This is my simple query; By searching selectnothing I'm sure I'll have no hits. SELECT nome_t FROM myTable WHERE nome_t ILIKE '%selectnothing%'; This is the EXPLAIN ANALYZE VERBOSE Seq Scan on myTable (cost=0.00..15259.04 rows=37 width=29) (actual time=2153.061..2153.061 rows=0 loops=1) Output: nome_t Filter: (nome_t ~~* '%selectnothing%'::text) Total runtime: 2153.116 ms myTable has around 350k rows and the table definition is something like: CREATE TABLE myTable ( nome_t text NOT NULL, ) I have an index on nome_t as stated below: CREATE INDEX idx_m_nome_t ON myTable USING btree (nome_t); Although this is clearly a good candidate for Fulltext search I would like to rule that option out for now. This query is meant to be run from a web application and currently it's taking around 2 seconds which is obviously too much; Is there anything I can do, like using other index methods, to improve the speed of this query?

  Read the article

• Can you send a full result set to an SQL function?

  - by PylonsN00b

  I am working in Postgres and I need to send in a full result set with many rows and column into a stored procedure or a function. Is this possible? If so, where can I see resources for syntax?

  Read the article

• Postgres vs Firebird

  - by Tedi

  I'm looking to use either Firebird or Postgres in my next development project ... largely because both are available under a BSD-like license. I found a great comparison of the two database at http://www.amsoftwaredesign.com/pg_vs_fb But this comparison is a good 2+ years old and both databases have come a long ways since. Does anyone mind updating the comparison table to be relevant for the current versions of both Firebird and Postgres ... or have a link to a site that does a good recent comparison between the two database?

  Read the article

• Postgres : Post statement (or insert) asynchronous, non-blocking processing.

  - by Hassan Syed

  I'm wondering if it is possible, that after a collection of rows is inserted, to initiate an operation that is executed asynchronously, is non-blocking, and doesn't need to inform the originator of the request - of the result. I am working with large amounts of events and I can guarantee that the post-insert logic will not fail -- I just want to have a single insert thread in my event-sources, and I want this thread to keep flying without blocking, and without being responsible for any post-delivery book-keeping. I can tell you that I would potentially have a 100 of these jobs executing concurrently and each job might operate on 5 tables with anywhere between 200-1000 inserts on each of these tables. A hint in the right direction should be enough.

  Read the article

• Escape SQL "LIKE" value for Postgres with psycopg2

  - by Evgeny

  Does psycopg2 have a function for escaping the value of a LIKE operand for Postgres? For example I may want to match strings that start with the string "20% of all", so I want to write something like this: sql = '... WHERE ... LIKE %(myvalue)s' cursor.fetchall(sql, { 'myvalue': escape_sql_like('20% of all') + '%' } Is there an existing escape_sql_like function that I could plug in here? (Similar question to How to quote a string value explicitly (Python DB API/Psycopg2), but I couldn't find an answer there.)

  Read the article

• Check value at insert

  - by ThreeFingerMark

  Hello, i have this three tables. Table: Item Columns: ItemID, Title, Content, NoChange (Date) Table: Tag Columns: TagID, Title Table: ItemTag Columns: ItemID, TagID In the Item Table is a Field with NoChange, if this field = true no Tag is allowed to insert a ItemTag value with this ItemID. How can i check this in the insert? For Updates i have this Statement: UPDATE ItemTag SET TagID = ? where ItemID = ? AND TagID = ? AND exists ( select ItemID from Item where ItemID = ? AND NoChange is null)"); Thank you.

  Read the article

• Postgesql select from 2 tables. Joins?

  - by Daniel

  I have 2 tables that look like this: Table "public.phone_lists" Column | Type | Modifiers ----------+-------------------+-------------------------------------------------------------------- id | integer | not null default nextval(('"phone_lists_id_seq"'::text)::regclass) list_id | integer | not null sequence | integer | not null phone | character varying | name | character varying | and Table "public.email_lists" Column | Type | Modifiers ---------+-------------------+-------------------------------------------------------------------- id | integer | not null default nextval(('"email_lists_id_seq"'::text)::regclass) list_id | integer | not null email | character varying | I'm trying to get the list_id, phone, and emails out of the tables in one table. I'm looking for an output like: list_id | phone | email ---------+-------------+-------------------------------- 0 | | [email protected] 0 | | [email protected] 0 | | [email protected] 0 | | [email protected] 0 | | [email protected] 1 | 15555555555 | 1 | 15555551806 | 1 | 15555555508 | 1 | 15055555506 | 1 | 15055555558 | 1 | | [email protected] 1 | | [email protected] I've come up with select pl.list_id, pl.phone, el.email from phone_lists as pl left join email_lists as el using (list_id); but thats not quite right. Any suggestions?

  Read the article

• Check if row already exists, if so tell the referenced table the id

  - by flhe

  Let's assume I have a table magazine: CREATE TABLE magazine ( magazine_id integer NOT NULL DEFAULT nextval(('public.magazine_magazine_id_seq'::text)::regclass), longname character varying(1000), shortname character varying(200), issn character varying(9), CONSTRAINT pk_magazine PRIMARY KEY (magazine_id) ); And another table issue: CREATE TABLE issue ( issue_id integer NOT NULL DEFAULT nextval(('public.issue_issue_id_seq'::text)::regclass), number integer, year integer, volume integer, fk_magazine_id integer, CONSTRAINT pk_issue PRIMARY KEY (issue_id), CONSTRAINT fk_magazine_id FOREIGN KEY (fk_magazine_id) REFERENCES magazine (magazine_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); Current INSERTS: INSERT INTO magazine (longname,shotname,issn) VALUES ('a long name','ee','1111-2222'); INSERT INTO issue (fk_magazine_id,number,year,volume) VALUES (currval('magazine_magazine_id_seq'),'8','1982','6'); Now a row should only be inserted into 'magazine', if it does not already exist. However if it exists, the table 'issue' needs to get the 'magazine_id' of the row that already exists in order to establish the reference. How can i do this? Thx in advance!

  Read the article

< Previous Page | 37 38 39 40 41 42 43 44 45 46 47 48  | Next Page >