Re: [SQL] Drawing a blank on some SQL

On 02/11/2011 11:46 AM, Aaron Burnett wrote: > > Hi, > > I'm just drawing a blank entirely today and would appreciate some help on > this. > > The long and short; there are 12 distinct activities that need to be queried > on a weekly basis: > > SELECT count(activity_id), activity_id > FROM fo

Re: [SQL] what's wrong in this procedure?

On 02/25/2011 10:46 AM, Camaleon wrote: > This error is returned Erro de SQL: > > ERROR: column "Aguardando Pagto" does not exist at character 352 >>> > > > create or replace function get_historico() RETURNS SETOF > twiste.type_cur__historico AS ' > >SELECT o.data_fim, sum(t.num_iten

Re: [SQL] distinguishing identical columns after joins

On 03/01/2011 12:47 PM, S G wrote: > This question is particularly geared towards self-joins, but can apply > to any join where the tables involved have any identical column names. > Aside from explicit column references, is there any way to pull all > columns (*) from each table in a join and q

Re: [SQL] distinguishing identical columns after joins

On 03/01/2011 03:13 PM, S G wrote: > On Tue, Mar 1, 2011 at 2:53 PM, Rob Sargent wrote: >> >> >> On 03/01/2011 12:47 PM, S G wrote: >>> This question is particularly geared towards self-joins, but can apply >>> to any join where the tables involved have

[SQL] xml2 support

I'm confused by the deprecation notice on xml2 seen here . What if anything was removed in the making of 8.4? Was the newer standard API implemented? Do I still need namespaces? I'm running PostgreSQL 9.0.3 built locally with --with-li

Re: [SQL] xml2 support

On 03/23/2011 11:10 AM, Serdar Gül wrote: if you want to export the data in xml type i suggest you to use the EMS SQL Manager for PostgreSQL because you can export the whole table or an executed query in many different file types including xml 2011/3/23 Rob Sargent mailto:robjsarg

[SQL] xml2 support

I'm confused by the deprecation notice on xml2 seen here . What if anything was removed in the making of 8.4? Was the newer standard API implemented? Do I still need namespaces? I'm running PostgreSQL 9.0.3 built locally with --with-li

Re: [SQL] pass in array to function for use by where clause? how optimize?

On 04/13/2011 09:09 AM, Tom Lane wrote: Anish Kejariwal writes: (select store_id, avg(sales) sales from store where group_id in(select $1[i] from generate_subscripts($1, 1) g(i)) Seems like a pretty brute-force way to deal with the array. Try where group_id = any($1) Not sure if it'll be

Re: [SQL] slightly OT - Using psql from Emacs with sql.el

On 05/05/2011 04:01 PM, Seb wrote: Hi, When working with psql via sql.el, multiple prompts accumulate in a single line when sending multi-line input to the SQLi buffer. For example, sending the following: SELECT a, b, c, FROM some_table; with 'C-c C-r' results in these lines in th

Re: [SQL] slightly OT - Using psql from Emacs with sql.el

On 05/05/2011 04:55 PM, Seb wrote: On Thu, 05 May 2011 16:47:09 -0600, Rob Sargent wrote: [...] Doesn't appear to. I use sql-mode alot/daily. The multiple prompts never bothers me, though the output not starting at the left kind of does. I've adapted someone's suggesti

Re: [SQL] extracting location info from string

On 05/24/2011 10:57 AM, Lew wrote: Tarlika Elisabeth Schmitz wrote: Lew wrote: That isn't a table structure, that's a freeform text structure. You didn't state your question, Tarlika, but your database structure is terrible. For example, "region" and "country" should be different columns.

Re: [SQL] extracting location info from string

On 05/25/2011 03:13 PM, Tarlika Elisabeth Schmitz wrote: On Wed, 25 May 2011 09:25:48 -0600 Rob Sargent wrote: On 05/24/2011 10:57 AM, Lew wrote: Tarlika Elisabeth Schmitz wrote: CREATE TABLE person ( id integer NOT NULL, "name" character varying(256) NOT NULL, "loca

Re: [SQL] Function to total reset a schema

I would hope you have readily at hand the ddl for the schema in question. Then it's simply a matter of drop schema cascade and re-run you ddl scripts. Surfing wrote: Hi all, I need to write a function that totally empty a schema. So I have written a TRUNCATE statement for each table and set

Re: [SQL] Append n Rows into a Single Row

Look at the array aggregation functions here Tripura wrote: Hi, I am totally new to PostgreSQL and this is my first script. Can anyone please help me with my following requirement I have script that returns 1 column and 40 row

Re: [SQL] select xpath ...

Having a name space in the doc requires it's usage in the query. On 09/17/2011 11:48 AM, boris wrote: > hi all, > I've inserted xml file : > > > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";> > > zz > .. > > > to a table: > > CREATE TABLE "temp".tempxml

Re: [SQL] Change in 9.1?

On 11/22/2011 12:39 AM, Jasmin Dizdarevic wrote: > Hi, > > we have a reporting tool, that sometimes uses this kind of condition. > ...WHERE a.field = a.field > > To explain this: a.field can be filtered by the user. the user can > choose some values. if he does, this condition will be build: >

Re: [SQL] How to Return Table From Function

On 01/22/2012 06:09 AM, Rehan Saleem wrote: hi , i have created this function CREATE OR REPLACE FUNCTION totaloverlapcount(user_id integer , bg integer ,center_distance integer) RETURNS varchar AS $$ DECLARE percentage record; BEGIN select fname, lname, count(userid) totalcount ,100.00*co

Re: [SQL] Can I read the data without commit

If possible have the review done before starting the transaction. No sense in holding on to that stuff too long. Potential concurrency issues etc. On 03/23/2012 03:40 PM, Jonathan S. Katz wrote: On Mar 23, 2012, at 5:33 PM, John Fabiani wrote: I start a transaction. Begin Then I insert a l

Re: [SQL] Can I read the data without commit

le? Can I read the data I just inserted without a commit. Johnf On Friday, March 23, 2012 03:46:10 PM Rob Sargent wrote: If possible have the review done before starting the transaction. No sense in holding on to that stuff too long. Potential concurrency issues etc. On 03/23/2012 03:40 PM, Jonath

Re: [SQL] syntax of joins

On 04/06/2012 01:23 PM, Pavel Stehule wrote: Hello 2012/4/6 Andreas: hi, is there a disadvantage to write a join as select * froma, b where a.id = b.a_id; over select * froma join b on a.id = b.a_id; yes - newer notation has some advantages * clean specification join p

Re: [SQL] syntax of joins

On 04/06/2012 01:46 PM, Pavel Stehule wrote: 2012/4/6 Rob Sargent: On 04/06/2012 01:23 PM, Pavel Stehule wrote: Hello 2012/4/6 Andreas: hi, is there a disadvantage to write a join as select * froma, b where a.id = b.a_id; over select * froma join b on a.id = b.a_id

Re: [SQL] Uniform UPDATE queries

On 04/18/2012 04:11 AM, Dennis wrote: When a query is written to update a table, the usual process is to list all the columns that need updating. This could imply the creation of many possible queries for many columns. In an effort to keep the UPDATE queries more uniform, less number of unique qu

Re: [SQL] Uniform UPDATE queries

On 04/19/2012 04:55 AM, Dennis wrote: Hello Tom, The example you have given is EXACTLY why something like CURRENT is needed to limit the number of unique queries or prepared statements. (or to do a selection of all values before an update meaning two executed queries.) regards,. Dennis On 04/

Re: [SQL] How to compare two tables in PostgreSQL

On 11/10/2012 08:13 PM, saikiran mothe wrote: Hi, How can i compare two tables in PostgreSQL. Thanks, Sai Compare their content or their definition? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sq

Re: [SQL] \copy multiline

On 11/29/2012 02:33 AM, Guillaume Lelarge wrote: On Wed, 2012-11-28 at 21:21 -0600, Seb wrote: Hi, I use \copy to output tables into CSV files: \copy (SELECT ...) TO 'a.csv' CSV but for long and complex SELECT statements, it is cumbersome and confusing to write everything in a single line, an

Re: [SQL] Why doesn't this work

On 01/15/2013 04:50 AM, Barbara Woolums wrote: I am running a query like so SELECT id FROM image WHERE image='demo-harvey wallbanger.jpg' It returns nothing My table looks like this "demo-820.jpg";1 "demo-lemon-mousse-1.jpg";2 "demo-pumpkinchaibars.jpg";3 "demo-Lolly-Shop.jpg";4 "demo-scan000

Re: [SQL] returning the number of rows output by a copy command from a function

On 01/16/2013 09:30 AM, James Sharrett wrote: I have a function that generates a table of records and then a SQL statement that does a COPY into a text file. I want to return the number of records output into the text file from my function. The number of rows in the table is not necessarily the

Re: [SQL] Presenting data in 5 Rows & 5 Cols for 25 specific values

On 08/03/2013 07:26 AM, F Bax wrote: I have a table containing tasks completed in a game I'm playing. The game includes an extra BINGO Challenge where each cell of standard BINGO card contains a particular task to be completed. The goal is score a BINGO (row, column, diagonal) by completing f

Re: [SQL] Presenting data in 5 Rows & 5 Cols for 25 specific values

On 08/03/2013 07:26 AM, F Bax wrote: I have a table containing tasks completed in a game I'm playing. The game includes an extra BINGO Challenge where each cell of standard BINGO card contains a particular task to be completed. The goal is score a BINGO (row, column, diagonal) by completing f

[SQL] Help with optional parameters

I have the need to have optional values for a query in a stored procedure that I am building. (using postgres 8.1.4). This particular query executes against a huge table (several million rows) and has six optional parameters that can be sent to the function. If one of the parameters is null then th

Re: [SQL] Help with optional parameters

this tip.  On 8/17/06, MaXX <[EMAIL PROTECTED]> wrote: Rob Tester wrote:> I have the need to have optional values for a query in a stored procedure> that I am building. (using postgres 8.1.4). This particular query> executes> against a huge table (several million rows) and has six op

[SQL] LEFT Join Question

address email phone - Rob123 ACTIVE ROB 123 Test Drive[EMAIL PROTECTED]

Re: [SQL] LEFT Join Question

Just 1 followup to this : they MAY or MAYNOT have records in the following tables : contact_address,contact_phone There may also be multiple records in that table w/ the same account_id - but the domain_type_id will be different. so the contact_phone could have 2 records : account_id 1, domain PR

Re: [SQL] LEFT Join Question

in_type_id = DT.domain_type_id OR CN.domain_type_id IS NULL OR CP.domain_type_id IS NULL ) JOIN account_type AT ON (AT.account_type_id = A.account_type_id) HAVING AT.account_type_tag = 'ACCOUNT_VENDOR' AND DT.domain_type_tag = 'VENDOR_PRIMARY' Thanks for the help! =Rob O

Re: [SQL] Distinct oddity

Is firmen a table or a view? From: Scott Marlowe To: Maximilian Tyrtania Cc: pgsql-sql@postgresql.org Sent: Friday, May 8, 2009 5:35:21 AM Subject: Re: [SQL] Distinct oddity On Fri, May 8, 2009 at 3:28 AM, Maximilian Tyrtania wrote: > am 07.05.2009 20:54 Uhr

Re: [SQL] update from join

I wonder if this works: update stock s set s_superceded = true where s.s_updated < (select max(t.s_updated) from stock t where t.s_vin = s.s_vin) On Thu, May 14, 2009 at 7:27 AM, Gary Stainburn < gary.stainb...@ringways.co.uk> wrote: > I know I should be able to do this but my brain's mashed

[SQL] Extrapolating performance expectation

Can one extrapolate future performance expectations for ever-growing tables from a given (non-trivial) data set, and if so with what curve? Corollary: what would one expect a performance curve to approximate in terms of query execution time v. number of data rows (hardware, load staying constant).

Re: [SQL] Extrapolating performance expectation

On Mon, May 18, 2009 at 6:47 AM, Kenneth Marshall wrote: > On Sun, May 17, 2009 at 10:04:46PM -0600, Rob Sargent wrote: > > Can one extrapolate future performance expectations for ever-growing > tables > > from a given (non-trivial) data set, and if so with what curve? >

Re: [SQL] Need help combining 2 tables together

if you want topics listed which don't yet have messages try select t.id, t.topic, m.id, m.message from topics t left join messages m on m.topic = t.id; On Fri, May 22, 2009 at 8:47 AM, James Kitambara wrote: > Dear Richard Ekblom, > > I think Mr. Adrian Klaver gave you the solution. Mine is the

[SQL] page-base stats per query?

Is it possible to show information such as the ratio of cache hits or pages read etc for a single query?

Re: [SQL] Type inheritance

I'm not sure how Temperature and Humidity are related as types beyond "extends float". i.e. What tuple definition could alternatively have a Temp or a Humidity value? (I do understand their physical relationship :) ) Gianvito Pio wrote: Hi all, I just want to ask you if it's possible to def

[SQL] Default timestamp value

As a precursor to this question, I read: CREATE TABLE Date/Time Types (and corresponding input/output) Date/Time Functions ...but I still don't see how to have the default value of a timestamp to be the time at which the individual record is inserted. I just get the time

RE: [SQL] Default timestamp value

Thanks very much everyone! > BTW, this *is* covered in the FAQ, see > http://www.postgresql.org/docs/faq-english.html#4.22 Ahh, but I did look at a FAQ! I just went back and checked the date, I guess I shouldn't have just skimmed down the page: Last updated: Mon Oct 14 08:05:23 EDT 1996.

[SQL] Creating timestamps in queries?

Hi all, I would like to say, "select * from blah where stamp >= 7 days ago" ...where the "days ago" is calculated at query time; meaning that its not hardcoded into the query as a date string. Is this possible? TIA! =) - Rob Slifka

RE: [SQL] Change type of column

I think this is in the FAQ. I was reading it last nite and I believe there's something in there that might be relevant. - r > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf > Of Volker Paul > Sent: July 11, 2000 12:32 AM > To: [EMAIL PROTECTED] > Subjec

RE: [SQL] Creating timestamps in queries?

st of commonly-done queries? There are some for SELECT and whatnot, but maybe asking for that for these far-out ones is too much. Thanks again all ;) - A very grateful Rob Slifka > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf > Of Frank Bax > Sent

[SQL] Changing user passwords

; web user should be able to change the default password. I'm a pg/SQL novice, yet I'm always willing to learn new things, references to documentation will be appreciated as much as solutions. Apologies in advance for my ignorance. -- Rob van der Leek E-mail: [EMAIL PROTECTED] ---

Re: [SQL] Changing user passwords

d would do). DROP USER nobody; CREATE USER nobody; doesn't do the job either. And why does the ALTER USER command operates on pg_shadow instead of pg_passwd? (or is this configurable?) rob. - "Nagy Laszlo Zsolt , KLTE TTK pm1" wrote: > > > Is it possible to change a u

<    1   2