Search Results

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

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

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

  • jdbc query - date ranges as parameters

    - by pstanton
    Hi all, I'd like to write a single JDBC statement that can handle the equivalent of any number of NOT BETWEEN date1 AND date2 where clauses. By single query, i mean that the same SQL string will be used to create the JDBC statements and then have different parameters supplied. This is so that the underlying frameworks can efficiently cache the query (i've been stung by that before). Essentially, I'd like to find a query that is the equivalent of SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? and at the same time could be used with fewer parameters: SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ? or more parameters SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? I will consider using a temporary table if that will be simpler and more efficient. thanks for the help!

    Read the article

  • Porting join from Oracle to Postgres

    - by Grasper
    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'), TO_TIMESTAMP( '1002260915', 'YYMMDDHH24MI'), 'TRANSIT ALPHA', 'TRANSIT ALPHA', '1', 'TRANSIT ALPHA' FROM AIRSPACE ASP, apsmain .MISSION_CLASS MC WHERE ASP.ASP_AIRSPACE_NM(+)= 'TRANSIT ALPHA' AND MC.MCS_MISSION_CLASS_NAME= 'AIRDROP' AND 'TRANSIT ALPHA' IS NOT NULL Is that exactly the same as: 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'), TO_TIMESTAMP( '1002260915', 'YYMMDDHH24MI'), 'TRANSIT ALPHA', 'TRANSIT ALPHA', '1', 'TRANSIT ALPHA' FROM AIRSPACE ASP, apsmain .MISSION_CLASS MC WHERE ASP.ASP_AIRSPACE_NM = 'TRANSIT ALPHA' AND MC.MCS_MISSION_CLASS_NAME= 'AIRDROP' AND 'TRANSIT ALPHA' IS NOT NULL I just deleted the (+). The part that is confusing me is that ASP.ASP_AIRSPACE_NM is being right joined to a constant.

    Read the article

  • PL/PGSQL function, having trouble accessing a returned result set from psycopg2...

    - by Paul
    I have this pl/pgsql function: CREATE OR REPLACE FUNCTION get_result(id integer) RETURNS SETOF my_table AS $ DECLARE result_set my_table%ROWTYPE; BEGIN IF id=0 THEN SELECT INTO result_set my_table_id, my_table_value FROM my_table; ELSE SELECT INTO result_set my_table_id, my_table_value FROM my_table WHERE my_table_id=id; END IF; RETURN; END; $ LANGUAGE plpgsql; I am trying to use this with Python's psycopg2 library. Here is the python code: import psycopg2 as pg conn = pg.connect(host='myhost', database='mydatabase', user='user', password='passwd') cur = conn.cursor() return cur.execute("SELECT * FROM get_result(0);") # returns NoneType However, if i just do the regular query, I get the correct set of rows back: ... return cur.execute("SELECT my_table_id, my_table_value FROM mytable;") # returns iterable result set Theres obviously something wrong with my pl/pgsql function, but I can't seem to get it right. I also tried using RETURN result_set; instead of just RETURN in the 10th line of my plpgsql function, but got an error from postgres.

    Read the article

  • I don't find the sql request

    - by user301089
    Hi everybody, Here it's my problem I've a list of the following measure : src1 dst2 24th december 2009 src1 dst3 22th december 2009 src1 dst2 18th december 2009 I would like to have just the latest measures with a sql request - 2 first lines in my case because the pairs(src and dst) aren't the same. I try to use DISTINCT but I have just the 2 first columns and I will all columns. I try too GROUP BY but I hadn't success. Anyone can help me ? Thx Narglix

    Read the article

  • Is there anything as good as TOAD for Postgres (Windows)?

    - by misc090912
    Hi guys, I'm just looking for a management tool like TOAD for Postgres. Anyone used a good one? Edit - I work mostly within the data itself and the database already has a mature model/design. I use the edit windows the most (well, in TOAD for Oracle anyway.) As far as I know, Toad only exists naturally for: Oracle, MS SQL, DB2 and MySQL... --JS

    Read the article

  • Error in Postgres execute

    - by RAJA
    I'm using this function... -- Function: dbo.sp_acc_createaccount(character varying, integer, integer, character varying, character varying, character varying, character varying) -- DROP FUNCTION dbo.sp_acc_createaccount(character varying, integer, integer, character varying, character varying, character varying, character varying); CREATE OR REPLACE FUNCTION dbo.sp_acc_createaccount(IN in_orgmgrtype character varying, INOUT in_parentid integer, IN in_levelid integer, IN in_name character varying, IN in_phone character varying, IN in_webpage character varying, IN in_owner character varying, OUT out_accountid integer) RETURNS record AS $BODY$ DECLARE l_CoID int; l_CurrID int; l_OrgMgrId int; errmsg varchar(250); BEGIN IF in_ParentID = -1 THEN errmsg := 'execute sp_Acc_GetCompanyIDForUser failed'; l_CoID := dbo.sp_Acc_GetCompanyIDForUser(in_user); IF l_CoID = -2 THEN RAISE EXCEPTION 'execute sp_Acc_GetCompanyIDForUser failed'; END IF; errmsg := 'execute sp_Acc_GetOrgMgrIDForCompany failed'; l_OrgMgrID := dbo.sp_Acc_GetOrgMgrIDForCompany(in_OrgMgrType, l_CoID); IF l_OrgMgrID = -2 THEN RAISE EXCEPTION 'execute sp_Acc_GetOrgMgrIDForCompany failed'; END IF; in_ParentID := l_OrgMgrID; ELSE errmsg := 'Select orgmgrid failed'; SELECT OrgMgrID INTO l_CurrID FROM dbo.OrgMgr WHERE Name = in_Name AND ParentID = in_ParentID; END IF; -- if not, add it IF l_CurrID IS NULL THEN errmsg := 'Insert into orgmgr(account creation) failed'; INSERT INTO dbo.OrgMgr (ParentID, LevelID, Name, PrimaryPhone, WebPage, Owner) VALUES (in_ParentID, in_LevelID, in_Name, in_Phone, in_WebPage, in_Owner); out_AccountID := currval('dbo.OrgMgr_accountid_seq'); ELSE out_AccountID := -1; END IF; COMMIT; EXCEPTION WHEN RAISE_EXCEPTION THEN out_AccountID := 99; RAISE NOTICE 'ERROR : %',errmsg; WHEN OTHERS THEN out_AccountID := 99; RAISE EXCEPTION 'ERROR : %',errmsg; END $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION dbo.sp_acc_createaccount(character varying, integer, integer, character varying, character varying, character varying, character varying) OWNER TO postgres; But.. it's showing error in execute time .. ERROR: SPI_execute_plan failed executing query "ROLLBACK": SPI_ERROR_TRANSACTION

    Read the article

  • Escaping colons in hibernate createSQLQuery

    - by Stratosgear
    I am confused on how I can create an SQL statement containing colons. I am trying to create a view and I am using (notice the double colons): create view MyView as ( SELECT tableA.colA as colA, tableB.colB as colB, round(tableB.colD / 1024)::numeric, 2) as calcValue, FROM tableA, tableB WHERE tableA.colC = 'someValue' ); This is a postgres query and I am forced to use the double colons (::) in order to correctly run the statement. I then pass the above statement through: s.createSQLQuery(myQuery).executeUpdate(); and I get a: Exception in thread "main" org.hibernate.exception.DataException: \ could not execute native bulk manipulation query at org.hibernate.exception.SQLStateConverter.convert(\ SQLStateConverter.java:102) ... more stacktrace... with an output of my above statement changed as (notice the question mark): create view MyView as ( SELECT tableA.colA as colA, tableB.colB as colB, round(tableB.colD / 1024)?, 2) as calcValue, FROM tableA, tableB WHERE tableA.colC = 'someValue' ); Obviously, hibernate confuses my colons with named parameters. Is there a way to escape the colons (a google suggestion that mentions that a single colon is escaped as a double colon does NOT work) or another way of running this statement? Thanks.

    Read the article

  • Table with a lot of attributes

    - by Robert
    Hi, I'm planing to build some database project. One of the tables have a lot of attributes. My question is: What is better, to divide the the class into 2 separate tables or put all of them into one table. below is an example create table User { id, name, surname,... show_name, show_photos, ...) or create table User { id, name, surname,... ) create table UserPrivacy {usr_id, show_name, show_photos, ...) The performance i suppose is similar due to i can use index.

    Read the article

  • postgres counting one record twice if it meets certain criteria

    - by Dashiell0415
    I thought that the query below would naturally do what I explain, but apparently not... My table looks like this: id | name | g | partner | g2 1 | John | M | Sam | M 2 | Devon | M | Mike | M 3 | Kurt | M | Susan | F 4 | Stacy | F | Bob | M 5 | Rosa | F | Rita | F I'm trying to get the id where either the g or g2 value equals 'M'... But, a record where both the g and g2 values are 'M' should return two lines, not 1. So, in the above sample data, I'm trying to return: $q = pg_query("SELECT id FROM mytable WHERE ( g = 'M' OR g2 = 'M' )"); 1 1 2 2 3 4 But, it always returns: 1 2 3 4

    Read the article

  • Possible to rank partial matches in Postgres full text search?

    - by Joe
    I'm trying to calculate a ts_rank for a full-text match where some of the terms in the query may not be in the ts_vector against which it is being matched. I would like the rank to be higher in a match where more words match. Seems pretty simple? Because not all of the terms have to match, I have to | the operands, to give a query such as to_tsquery('one|two|three') (if it was &, all would have to match). The problem is, the rank value seems to be the same no matter how many words match. In other words, it's maxing rather than multiplying the clauses. select ts_rank('one two three'::tsvector, to_tsquery('one')); gives 0.0607927. select ts_rank('one two three'::tsvector, to_tsquery('one|two|three|four')); gives the expected lower value of 0.0455945 because 'four' is not the vector. But select ts_rank('one two three'::tsvector, to_tsquery('one|two')); gives 0.0607927 and likewise select ts_rank('one two three'::tsvector, to_tsquery('one|two|three')); gives 0.0607927 I would like the result of ts_rank to be higher if more terms match. Possible? To counter one possible response: I cannot calculate all possible subsequences of the search query as intersections and then union them all in a query because I am going to be working with large queries. I'm sure there are plenty of arguments against this anyway! Edit: I'm aware of ts_rank_cd but it does not solve the above problem.

    Read the article

  • Restart of Master Postgres DB with unconsumed Wal files

    - by Douglas Sellers
    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 unconsumed wal files hanging around, will the master be effected at all or will it start clean?

    Read the article

  • Fast find near users using PostGIS

    - by opedge
    I have 5 tables: - users - information about user with current location_id (fk to geo_location_data) - geo_location_data - information about location, with PostGIS geography(POINT, 4326) column - user_friends - relationships between users. I want to find near friends for current user, but it takes a lot of time of executing select query to know if user is a friend and after that execute select using ST_DWithin.. May be something wrong in domain model or in queries?

    Read the article

  • Ruby on rails active-record generated SQL on Postgres

    - by jpartogi
    Dear all, Why does Ruby on rails generated more queries in the background on Postgres than MySQL? I haven't tried deploying Rails on production with Postgres yet, but I am just afraid this generated queries would affect the performance. Do you find Rails with Postgres is slower than MySQL, knowing that it produce more query on the background? Or it is relatively the same?

    Read the article

  • Postgres: clear entire database before re-creating / re-populating from bash script

    - by Hoff
    hi folks, I'm writing a shell script (will become a cronjob) that will: 1: dump my production database 2: import the dump into my development database Between step 1 and 2, I need to clear the development database (drop all tables?). How is this best accomplished from a shell script? So far, it looks like this: #!/bin/bash time=`date '+%Y'-'%m'-'%d'` # 1. export(dump) the current production database pg_dump -U production_db_name > /backup/dir/backup-${time}.sql # missing step: drop all tables from development database so it can be re-populated # 2. load the backup into the development database psql -U development_db_name < backup/dir/backup-${time}.sql Many thanks in advance! Martin

    Read the article

  • INSERT and transaction serialization in PostreSQL

    - by Alexander
    I have a question. Transaction isolation level is set to serializable. When the one user opens a transaction and INSERTs or UPDATEs data in "table1" and then another user opens a transaction and tries to INSERT data to the same table, does the second user need to wait 'til the first user commits the transaction?

    Read the article

  • SQL statement to split a table based on a join

    - by williamjones
    I have a primary table for Articles that is linked by a join table Info to a table Tags that has only a small number of entries. I want to split the Articles table, by either deleting rows or creating a new table with only the entries I want, based on the absence of a link to a certain tag. There are a few million articles. How can I do this? Not all of the articles have any tag at all, and some have many tags. Example: table Articles primary_key id table Info foreign_key article_id foreign_key tag_id table Tags primary_key id It was easy for me to segregate the articles that do have the match right off the bat, so I thought maybe I could do that and then use a NOT IN statement but that is so slow running it's unclear if it's ever going to finish. I did that with these commands: INSERT INTO matched_articles SELECT * FROM articles a LEFT JOIN info i ON a.id = i.article_id WHERE i.tag_id = 5; INSERT INTO unmatched_articles SELECT * FROM articles a WHERE a.id NOT IN (SELECT m.id FROM matched_articles m); If it makes a difference, I'm on Postgres.

    Read the article

  • Database query optimization

    - by hdx
    Ok my Giant friends once again I seek a little space in your shoulders :P Here is the issue, I have a python script that is fixing some database issues but it is taking way too long, the main update statement is this: cursor.execute("UPDATE jiveuser SET username = '%s' WHERE userid = %d" % (newName,userId)) That is getting called about 9500 times with different newName and userid pairs... Any suggestions on how to speed up the process? Maybe somehow a way where I can do all updates with just one query? Any help will be much appreciated! PS: Postgres is the db being used.

    Read the article

  • named_scope + average is causing the table to be specified more then once in the sql query run on po

    - by hadees
    I have a named scopes like so... named_scope :gender, lambda { |gender| { :joins => {:survey_session => :profile }, :conditions => { :survey_sessions => { :profiles => { :gender => gender } } } } } and when I call it everything works fine. I also have this average method I call... Answer.average(:rating, :include => {:survey_session => :profile}, :group => "profiles.career") which also works fine if I call it like that. However if I were to call it like so... Answer.gender('m').average(:rating, :include => {:survey_session => :profile}, :group => "profiles.career") I get... ActiveRecord::StatementInvalid: PGError: ERROR: table name "profiles" specified more than once : SELECT avg("answers".rating) AS avg_rating, profiles.career AS profiles_career FROM "answers" LEFT OUTER JOIN "survey_sessions" survey_sessions_answers ON "survey_sessions_answers".id = "answers".survey_session_id LEFT OUTER JOIN "profiles" ON "profiles".id = "survey_sessions_answers".profile_id INNER JOIN "survey_sessions" ON "survey_sessions".id = "answers".survey_session_id INNER JOIN "profiles" ON "profiles".id = "survey_sessions".profile_id WHERE ("profiles"."gender" = E'm') GROUP BY profiles.career Which is a little hard to read but says I'm including the table profiles twice. If I were to just remove the include from average it works but it isn't really practical because average is actually being called inside a method which gets passed the scoped. So there is some times gender or average might get called with out each other and if either was missing the profile include it wouldn't work. So either I need to know how to fix this apparent bug in Rails or figure out a way to know what scopes were applied to a ActiveRecord::NamedScope::Scope object so that I could check to see if they have been applied and if not add the include for average.

    Read the article

  • Optimum size of transaction in Postgres?

    - by Joe
    I'm running a process that does a lot of updates ( 100,000) to a table. I have the choice between putting all the updates in a single transaction or committing transactions every 1000 or so. Ignore for the moment the case where a transaction fails and is aborted. I'm interested in the best size of transaction for memory and speed efficiency.

    Read the article

  • Lazarus Pascal - DB Connection - clarification

    - by itsols
    The following code is from the docs here: Program ConnectDB var AConnection : TSQLConnection; Procedure CreateConnection; begin AConnection := TIBConnection.Create(nil); AConnection.Hostname := 'localhost'; AConnection.DatabaseName := '/opt/firebird/examples/employee.fdb'; AConnection.UserName := 'sysdba'; AConnection.Password := 'masterkey'; end; begin CreateConnection; AConnection.Open; if Aconnection.Connected then writeln('Succesful connect!') else writeln('This is not possible, because if the connection failed, ' + 'an exception should be raised, so this code would not ' + 'be executed'); AConnection.Close; AConnection.Free; end. The main body of the code makes sense to me BUT I don't get where TSQLConnection came from. I cannot use CTRL + Space to autocomplete it either, which means my program has no reference to it. I'm trying to connect to Postgres by the way. Can someone please state what TSQLConnection is? Thanks!

    Read the article

  • Foreign/accented characters in sql query

    - by FromCanada
    I'm using Java and Spring's JdbcTemplate class to build an SQL query in Java that queries a Postgres database. However, I'm having trouble executing queries that contain foreign/accented characters. For example the (trimmed) code: JdbcTemplate select = new JdbcTemplate( postgresDatabase ); String query = "SELECT id FROM province WHERE name = 'Ontario';"; Integer id = select.queryForObject( query, Integer.class ); will retrieve the province id, but if instead I did name = 'Québec' then the query fails to return any results (this value is in the database so the problem isn't that it's missing). I believe the source of the problem is that the database I am required to use has the default client encoding set to SQL_ASCII, which according to this prevents automatic character set conversions. (The Java environments encoding is set to 'UTF-8' while I'm told the database uses 'LATIN1' / 'ISO-8859-1') I was able to manually indicate the encoding when the resultSets contained values with foreign characters as a solution to a previous problem with a similar nature. Ex: String provinceName = new String ( resultSet.getBytes( "name" ), "ISO-8859-1" ); But now that the foreign characters are part of the query itself this approach hasn't been successful. (I suppose since the query has to be saved in a String before being executed anyway, breaking it down into bytes and then changing the encoding only muddles the characters further.) Is there a way around this without having to change the properties of the database or reconstruct it? PostScript: I found this function on StackOverflow when making up a title, it didn't seem to work (I might not have used it correctly, but even if it did work it doesn't seem like it could be the best solution.):

    Read the article

  • postgres stored procedure problem

    - by easyrider
    Hi all, Ich have a problem in postgres function: CREATE OR REPLACE FUNCTION getVar(id bigint) RETURNS TABLE (repoid bigint, suf VARCHAR, nam VARCHAR) AS $$ declare rec record; BEGIN FOR rec IN (WITH RECURSIVE children(repoobjectid,variant_of_object_fk, suffix, variantname) AS ( SELECT repoobjectid, variant_of_object_fk, '' as suffix,variantname FROM b2m.repoobject_tab WHERE repoobjectid = id UNION ALL SELECT repo.repoobjectid, repo.variant_of_object_fk, suffix || '..' , repo.variantname FROM b2m.repoobject_tab repo, children WHERE children.repoobjectid = repo.variant_of_object_fk) SELECT repoobjectid,suffix,variantname FROM children) LOOP RETURN next; END LOOP; RETURN; END; It can be compiled, but if y try to call it select * from getVar(18) I got 8 empty rows with 3 columns. If i execute the following part of procedure with hard-coded id parameter: WITH RECURSIVE children(repoobjectid,variant_of_object_fk, suffix, variantname) AS ( SELECT repoobjectid, variant_of_object_fk, '' as suffix,variantname FROM b2m.repoobject_tab WHERE repoobjectid = 18 UNION ALL SELECT repo.repoobjectid, repo.variant_of_object_fk, suffix || '..' , repo.variantname FROM b2m.repoobject_tab repo, children WHERE children.repoobjectid = repo.variant_of_object_fk) SELECT repoobjectid,suffix,variantname FROM children I got exactly, what i need 8 rows with data: repoobjectid suffix variantname 18 19 .. for IPhone 22 .. for Nokia 23 .... OS 1.0 and so on. What is going wrong ? Please help. Thanx in advance

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

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