Re: [SQL] Table design question

On 6/2/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: On Thu, 01 Jun 2006, Chris Browne wrote: > Celko is decidedly *NOT* promoting the notion that you should use a > 100 byte long "natural key." > > Jamie's comments of "Orthodox versus Reform" seem reasonably > appropriate in outlining someth

[SQL] Alternative to serial primary key

I posted a couple of weeks back a question regarding the use of a 100 char column as a primary key and the responses uniformily advised the use of a serial column. My concern is that the key is effectively abstract and I want to use the column as a foreign key in other tables. It occurred to me th

Re: [SQL] Alternative to serial primary key

On 7/6/06, Markus Schaber <[EMAIL PROTECTED]> wrote: This is a good idea if you want to have taller indices, but you still need to re-check the "real" key due to hash collisions. I am aware there are collisions with md5 but without any actual proof I believe the risk to be very low with the dat

Re: [SQL] Alternative to serial primary key

On 7/6/06, Andrew Sullivan <[EMAIL PROTECTED]> wrote: That sort of undermines the value of the calculated primary key, though, doesn't it? He'd need the unique index for FK references, which was the point, I thought. Yes, that occurred to me as well. Frankly I believe the md5 collision genera

Re: [SQL] Alternative to serial primary key

On 7/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: i agree. all my primary keys are abstract - even though some don't have to be. iow, i'm comfortable using serials as my primary key even when i don't absolutely need to. Yes I had in fact already created my table using a serial as the pr

Re: [SQL] Alternative to serial primary key

On 7/7/06, D'Arcy J.M. Cain wrote: Are you sure? I have a hard time imagining a situation where that Absolutely. Also, you need to get into a lot more coding to handle the fact that "521 Main Avenue" is the same address as "521 Main Av." and "521 Main Ave" and even "521 Main." Actually that

Re: [SQL] Alternative to serial primary key

On 7/7/06, Scott Marlowe <[EMAIL PROTECTED]> wrote: On Thu, 2006-07-06 at 16:43, Aaron Bono wrote: I'll repeat my previous statement that this is premature optimization, and the hash is kind the wrong direction. If you store an int and the 1 to 100 characters in a varchar, you'll have about 4 to

Re: [SQL] Alternative to serial primary key

On 7/7/06, Andrew Sullivan <[EMAIL PROTECTED]> wrote: On Thu, Jul 06, 2006 at 05:18:16PM -0400, D'Arcy J.M. Cain wrote: > owns/resides there in a situation where the address can never be > changed, e.g. "521 Main" splitting into "521A Main" and "521B Main." And anyone who has looked at an even m

[SQL] Double aggregate problem

Oost-Vlaanderen If i leave out one aggregate, the result is as i expect (if I leave out 'provincie', sector gives): Gaafjes, Eeerder Only two results. I would like both array_accum returning only what they should and not doubles. Any help is appreciated. -- Regards, David W

Re: [SQL] Double aggregate problem

bericht- > Van: Peter Eisentraut [mailto:pete...@gmx.net] > Verzonden: woensdag 22 juli 2009 20:05 > Aan: pgsql-sql@postgresql.org > CC: David Weilers > Onderwerp: Re: [SQL] Double aggregate problem > > On Wednesday 22 July 2009 19:16:21 David Weilers wrote: > > I have the f

[SQL] Capacity planning.

Hi, Any way to get transaction count from the postgres daemon or any log? Also where can I find docs that can help me make a capacity plan for max 100,000 clients making around 200 transactions a day each. -- Regards. David Harel, == Home office +972 77

[SQL] Foreign key constraint referencing a parent table

stuk, amount, position) select ?, ?, ?, ?, ?, ?, count(*) from tblfactuurpost where factuur = ?; parameters: (2, Gespecialiseerde 24 uur zorg ma. t/m vr., 19, 156, 1425, 397800, 2) Regards, David -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to

[SQL] plperlu user function.

uot;SELECT perl_getfile('/home/harel/Misc/At_work.jpg')"); and I get an error: Warning: pg_query() [function.pg-query <http://localhost/mysqlImage1/function.pg-query>]: Query failed: ERROR: invalid input syntax for type oid: "ÿØÿà" in /home/harel/Prj/php/testImag

[SQL] user function and bind

Hi, I need an example how to write user function with columns binding and how to use it on PHP -- Thanks. David Harel, == Home office +972 77 7657645 Cellular: +972 54 4534502 Snail Mail: Amuka D.N Merom Hagalil 13802

[SQL] User function that returns a set of rows.

shell I got a nasty error message about that I am not in an environment to receive a set ??? (can't see it now. Office restrictions). Any idea? -- Regards. David Harel, == Home office +972 77 7657645 Cellular: +972 54 4534502 Snail Mail: Amuka

Re: [SQL] Domains, casts, and MS Access

ds enabling the True=-1 option in the driver, but I don't appear to be running with that, so either it didn't make a difference, or it caused me problems, I can't be remember. But with the above, I'm not having any problems with Access working against the database, including using

[SQL] backup and document views and user functions

question. I didn't find any clues on the web .(typically, I fail to phrase the right keywords) -- Thanks. David Harel, == Home office +972 77 7657645 Cellular: +972 54 4534502 Snail Mail: Amuka D.N Merom Hagalil 13802 Israel

[SQL] pgadmin debugger

hanks. David Harel, == Home office +972 77 7657645 Cellular: +972 54 4534502 eMail: harel...@gmail.com Snail Mail: Amuka D.N Merom Hagalil 13802 Israel -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To

Re: [SQL] pgadmin debugger

On Sat, 2011-08-13 at 12:56 -0700, Adrian Klaver wrote: > On Saturday, August 13, 2011 12:39:44 pm David Harel wrote: > > Greetings, > > > > I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also I > > have pgadmin version 1.10.2. I can't find debugger_

Re: [SQL] pgadmin debugger

On Sun, 2011-08-14 at 11:07 -0700, Adrian Klaver wrote: > On Sunday, August 14, 2011 10:15:43 am David Harel wrote: > > On Sat, 2011-08-13 at 12:56 -0700, Adrian Klaver wrote: > > > On Saturday, August 13, 2011 12:39:44 pm David Harel wrote: > > > > Greetings, >

Re: [SQL] Calculate the ratio

SELECT cols, SUM( CASE WHEN cf = 1 THEN 1 ELSE 0 END ) AS sum_1, SUM ( CASE WHEN cf = 0 THEN 1 ELSE 0 END ) AS sum_0 FROM table WHERE cf IN (0, 1) GROUP BY cols From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Forø Tollefsen Sent: Monday,

Re: [SQL] Calculate the ratio

Replace the “1” in the case with “area”… like the msi77 said From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of David Johnston Sent: Monday, August 15, 2011 9:08 AM To: 'Andreas Forø Tollefsen'; pgsql-sql@postgresql.org Subject: Re: [SQL] Cal

Re: [SQL] pgadmin debugger

On Mon, 2011-08-15 at 06:49 -0700, Adrian Klaver wrote: > On Sunday, August 14, 2011 11:33:13 am David Harel wrote: > > On Sun, 2011-08-14 at 11:07 -0700, Adrian Klaver wrote: > > > On Sunday, August 14, 2011 10:15:43 am David Harel wrote: > > > > On Sat, 2011-08-13

Re: [SQL] which is better: using OR clauses or UNION?

ES virtual table - which also requires Dynamic SQL. SELECT * FROM target_table NATURAL JOIN ( VALUES ('Bug1'),('Bug2'),('Bug3') ) searchtarget ( join_column_name ); David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Add one column to another

query. --- SELECT first_name, surname, ARRAY[email1, email2] AS email FROM [...] David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] function based index problem

ee what you get. It is likely that a simple ANALYZE on the table will solve your problem (ALWAYS RUN ANALYZE BEFORE POSTING QUESTIONS LIKE THIS); in the unlikely event it does not please post the “EXPLAIN ANALYZE” results so we can see exactly how many records each query returned. David J.

Re: [SQL] Schema partitioning

style? Or are there concrete benefits to partitioning? > > Mostly style but some ease-of-use when it comes to permissions as well. It's really no different than why you'd use sub-directories in your OS instead of putting everything in C/root. It does give you namespaces featur

Re: [SQL] Sorting of data from two tables

also help if you actually provided a detailed description of you goal instead of the generic one quoted above. Given your stated need the query does exactly what you requested. David J.

Re: [SQL] Combining several CTEs with a recursive CTE

> Thomas > > Try sticking the recursive keyword after the "with" if any of the following CTEs are recursive. WITH RECURSIVE normal1 AS () ,recursine1 AS () ,normal2 AS () ,recursine2 AS () SELECT ... David J.

Re: [SQL] ambiguous local variable name in 9.0 proc

able value into a different variable. DECLARE tbl_schema_copy VARCHAR; tbl_schema_copy := tbl_schema; . WHERE tbl_schema_copy = e.tbl_schema . David J.

Re: [SQL] using the aggregate function max()

omefield = max_date.specialdate; You can use a online query instead of the WITH if desired, same effect. You could also drop the join and use the max_date CTE in a WHERE clause: ... FROM table WHERE table.somefield = (SELECT specialdate FROM max_date) David J -- Sent via pgsql-sql mailing

Re: [SQL] Sorting of data from two tables

On Sep 25, 2011, at 12:37, "R. Smith" wrote: > On Sat, Sep 17, 2011 at 2:56 PM, David Johnston wrote: >> On Sep 17, 2011, at 9:32, "R. Smith" wrote: >> >> >> What I want to do is do a query joining table A with B and sorting >> firstly on

Re: [SQL] plpgsql function executed multiple times for each return value

result FROM ... ) SELECT (func.func_result).* FROM func David J.

Re: [SQL] advice on how to store variable attributes

lumn and multiple category columns. ( amount_value, amount_unit, amount_category, vendor_id ) If necessary each "amount_value" data type should have it's own table since the processing logic will vary (I.e., you cannot subtract text or Boolean values). You are , in effect,

Re: [SQL] advice on how to store variable attributes

On Oct 22, 2011, at 11:39, Linos wrote: > El 22/10/11 14:53, David Johnston escribió: >> On Oct 22, 2011, at 6:41, Linos wrote: >> >>> Hi all, >>> i need a little of advice on what could be the best way to store this >>> information. >>> &

Re: [SQL] Handling mutliple clients access with views

l security, for that you need functions. While a view itself will not provide the protected data a function can be used to process data (via RAISE NOTICE) that would otherwise be filtered out in the end result. This is because views are simply re-write rules. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] advice on how to store variable attributes

On Oct 22, 2011, at 10:07, Pavel Stehule wrote: > 2011/10/22 David Johnston : >> On Oct 22, 2011, at 6:41, Linos wrote: >> >>> Hi all, >>>i need a little of advice on what could be the best way to store this >>> information. >>> >&

Re: [SQL] Create Type with typmod_in

n explicit cast of a value to varchar(n) causes the value to be truncated to n characters. E.g., '1234567'::varchar(5) -> '12345' How to integrate that knowledge into a type I do not know. David J. >

Re: [SQL] the use of $$string$$

x your process. Done correctly there should be no difference in the end result regardless of how you choose to identify your strings. Don't use pgadmin3 myself so I don't know if what you are looking at would include the E. If it is outputting DDL (I.e., INSERT statements) it might but simple SELECT results should not. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Issue with a variable in a function

ON ppr_data(aarstall int, frauke int, tiluke int, prosjektkode int[] ) -- Note the change to int[] from int for prosjektkode David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Updatable view should truncate table fields

r data was provided. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] clarification about ARRAY constructor implementation

ession. Going by observation/inference here... An array can never take on the "unknown" type whereas a scalar can. The unknown type can be passed to the Insert where the target's column type can then be used for casting. The array, needing immediate casting, chooses the most

Re: [SQL] Change in 9.1?

ng all of: a test case, AND EXPLAIN ANALYZE results, AND a description of what is taken as being a problem, is necessary to provide suggestions. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] prepared statements

parameter, please regards Pavel Stehule -- Or, in lieu of readily passing arrays from your client API you can always pass a string and modify the query to parse the string into an array. ... users = ANY ( regexp_split_to_array( $1, ',' )::integer[

Re: [SQL] conditional FROM

FROM clause as the > query will return unneccessary duplicate > row...the same goes for port2node being Null or 0... > > > thanx in advance > richard > > Two options (one of which may not work for you). 1. Write two queries, one for each table, and union the results. 2

Re: [SQL] using a generated series in function

; > "select ... as week_date where week_date in ..." You are missing a FROM clause for the main select; the only FROM you have is part of the IN target subquery. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] using a generated series in function

ction and so must be sourced from somewhere. 2011-11-20 is a literal being fed into a function and so does not require a From clause in order to be evaluated. David J. On Dec 17, 2011, at 11:50, John Fabiani wrote: > As always I respect your insights - Adrian. I do understand what I did wr

Re: [SQL] JDBC Statement.setQueryTimeout : is there plan to implement this?

be done in a container-independent way (*shudder*). I'm quite sure > that using EJB timers is NOT the right way to do it - they're not > supported by web profile containers and are really intended for > "business level" timers that should be persistent across > rede

Re: [SQL] Current transaction is aborted, commands ignored until end of transaction block

that transaction until that > point. > > Any suggestions? > > Jan > > Start a "savepoint" before each sub-update and rollback to the savepoint if the update fails, and then try again with different data. If it succeeds you then release the savepoint anad mov

Re: [SQL] Nested custom types: array - unable to insert

and add additional phone numbers > -- This is where things go awry. > -- > update people > set phone = CAST(ROW(CAST(ROW(111,'home') as > phonenumber_type),ARRAY[(222,'office'), > (333,'cell'),(444,'eve')]::phonenumber_type) >

Re: [SQL] Unable To Alter Data Type

as numeric. If you deal with Canada (and maybe Mexico) at all then spaces and letters become acceptable characters within the zip_code. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Unable To Alter Data Type

On Jan 11, 2012, at 19:30, Carlos Mennens wrote: > On Wed, Jan 11, 2012 at 7:13 PM, David Johnston wrote: >> However, I will say again, you DO NOT WANT TO ACTUALLY DO THIS! >> >> The specific issue is that some US Postal Code begin with a zero ( 0 ) and >> so whenev

Re: [SQL] Unable To Modify Table

f the same length. There are many points-of-view regarding whether to use "serial" PRIMARY KEYs but regardless of whether you add one or not you should try and define a UNIQUE constraint on the table by using meaningful values. However, for things like Orders this is generally not possible

Re: [SQL] Unable To Modify Table

ore complicated to setup and execute compared to the more direct ON UPDATE CASCADE modifier to the FOREIGN KEY. But learning both methods is good. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Query Problem... Left OuterJoin / Tagging Issue

easier. Write out your desired output columns, with table prefixes, and mark whether each on is optional or mandatory. Tables with optional fields are outer joined to other tables, ideally those with only mandatory fields. The corresponding ON clauses should use simple equalities, though you may modify the the comparison values using functions. It sounds like you need to take a step back and do some serious reading on SQL basics, though I'll give you credit for at least trying and being somewhat descriptive of your goal. David J.

Re: [SQL] sql query problem

very simple queries that beginner reference materials cover adequately. Plus, you didn't display any effort in attempting to solve the question yourself; you can do this by showing and and asking what you did wrong as opposed to simply asking for an answer. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Update Mass Data in Field?

he literal value "%ghostsoftware.com" - which obviously is not an e-mail address - and the original "holyghost.org" email address would have been gone beyond easy recovery. Takeaway: test update queries on sample data (or at least within a transaction block) and confirm your res

Re: [SQL] must appear in the GROUP BY clause or be used in an aggregate function problem

nnot readily specify how. Generally you'd simply operate off of "id" and then join in the corresponding first/last names at the outer-most layer of the query. Since you are grouping on "a.id" anyway I would suggest you try it. David J. -- Sent via pgsql-sql mai

Re: [SQL] Multiple tables query on Mapserver

;name22' 'address22' 102356 2 > > geom22 >5 5 'name31' 'address31' 102356 3 > > geom31 >6 6 'name32' 'address32' 102356 3 > > geom32 >- - - - --- - > > - > > > can I achieve that results with a single query sentence? and how? > any good ideas will be appricated? > > thanks > > The general method is to use "UNION" but not sure about the changing of the gid and id values... David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Multiple tables query on Mapserver

On Feb 2, 2012, at 0:32, tiplip wrote: > I just need gid or id in increasing order start from 0 (or 1), fetching their > original gid (or id) value is not necessary:) > can I do that? > > > David Johnston wrote >> >> >> The general method is to use "

Re: [SQL] update column with multiple values

ere is not inherent linkage between the "UPDATE" table and the "FROM" table. You can make a correlated sub-query but in this case the is necessary. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] need help with import

-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Sent: Wednesday, February 15, 2012 8:03 PM To: pgsql-sql@postgresql.org Subject: [SQL] need help with import Hi I get CSV files to import. Th structure is like this. main p

Re: [SQL] need help with import

On Feb 15, 2012, at 21:05, Andreas wrote: > Am 16.02.2012 02:13, schrieb David Johnston: >> -Original Message- >> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] >> On Behalf Of Andreas >> Sent: Wednesday, February 15, 20

Re: [SQL] Window function frame clause

er doesn't make sense with a frame clause...frame is more useful for stuff like calculating rolling sums/averages and the like - where you evaluate fields in the surrounding frame as part of the aggregate. Window functions do not affect the number of rows returned. David J. -- Sent via pgs

Re: [SQL] Invalid syntax for integer

uot;" SQL state: 22P02 Context: PL/pgSQL function "getsitesbytfid" line 6 at IF The empty-string is not a valid syntax for an integer. You probably want: “IF (distance IS NULL) …”. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] help on a function with exception

> LANGUAGE plpgsql VOLATILE > > CREATE TRIGGER trg_check_PO_extra_date > BEFORE INSERT OR UPDATE > ON extra_values > FOR EACH ROW > EXECUTE PROCEDURE fnc_check_PO_extra_date(); > > You are suppressing the original exception so figuring out what is wrong is very d

Re: [SQL] Postgresql function which compares values from both tables

You would need to install the "HSTORE" extension to convert the record into a key->value pair then perform the comparison on that. Dave From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Rehan Saleem Sent: Monday, March 19, 2012 3:40 PM To: pgsql-sq

Re: [SQL] Postgresql function which compares values from both tables

> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Richard Huxton > Sent: Monday, March 19, 2012 4:59 PM > To: David Johnston > Cc: 'Rehan Saleem'; pgsql-sql@postgresql.org > Subject: Re:

Re: [SQL] Wrong output from union

Documented behavior. Please read the section on UNION for the why and the proper alternative syntax: http://www.postgresql.org/docs/9.0/interactive/sql-select.html On Mar 28, 2012, at 7:01, Gary Stainburn wrote: > Hi folks. > > I have two selects which in themselves report what they should.

Re: [SQL] generic crosstab ?

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Sent: Tuesday, April 24, 2012 5:35 PM To: Samuel Gendler Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] generic crosstab ? Am 24.04.2012 22:08, schrieb Samuel Gendler: On Tue, Apr 24, 2012 at

Re: [SQL] Finding Max Value in a Row

0/interactive/functions-aggregate.html Assuming that the users_id field is an integer: SELECT MAX(users_id) FROM users; --NO GROUP BY needed since no other fields are being output... That said, you really should create and attach a sequence so that you can avoid race/concurrency issues. Dav

Re: [SQL] Lowest 2 items per

Mike > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql I would recommend using the "RANK" window function with an appropriate partition clause in a sub-query then i

Re: [SQL] defaults in a function

one is > used as the default. > > Is this possible with plpgsql??? > > Johnf > http://www.postgresql.org/docs/9.0/interactive/sql-createfunction.html ... param_n type DEFAULT value OR ... param_n type = value It is SQL DDL syntax and not specific to the language the function is

Re: [SQL] using ordinal_position

should not meaningfully impact query plan generation and thus it should be no slower than a more direct query. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Need help building this query

ess) row while unmatched records will have one of the two resultant columns NULLed SELECT tableA.subid_a, tableB.subid_b FROM tableA FULL OUTER JOIN tableB ON (tableA.subid_a = tableB.subid_b) Requires at least version 8.4 David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How to solve the old bool attributes vs pivoting issue?

How would I build a view for this that shows all flags for any company? > When I create this view I'would not know how many flags exist at > execution time. > > > This must be a common issue. > > Is there a common solution, too? > > You should look

Re: [SQL] Prevent double entries ... no simple unique index

te the partial index on that. You could also consider creating an updatable view and avoid directly interacting with the three individual tables. You could also just turn event states into a history table and leave the current state on the event table. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] query two tables using same lookup table

.id_project, n1.name_auth, n2.name_auth From pj Left join t3 as n1 on (id_auth1 = id_auth) Left join t3 as n2 on (id_auth2 = id_auth) ; Full join the two project tables and give aliases to the duplicate id_auth field. Then left join against t3 twice (once for eachid_auth) using yet a another set of

Re: [SQL] join against a function-result fails

er. With a recursive CTE you can start at the bottom of the trees and only evaluate the needed branches. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] [GENERAL] Indexing question

t;1}. Zero you ignore, 1 you generally put on the same table - though not always, and more-than-one you create a separate table and store multiple values as separate rows instead of as columns. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Error: Template Id should be teh identifier of a template - help

tions that the triggers sets forth so the trigger performs a "RAISE ERROR 'Template Id should be the identifier of a template'". You will need to speak with someone responsible for maintaining the database. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org

Re: [SQL] prepared statement in crosstab query

NY ( split_to_array($$'1,3,5,7,11'$$, ',')::int[] ) In this case you pass a single delimited string (replacing the $-quoted literal shown) with whatever values you want as a single parameter/input. Convert that string to an array and then use the =ANY array operator to match th

Re: [SQL] Need to Iterate the record in plpgsql

he column name and lookup the corresponding column on "test3_table" to determine the data type associated with the value. I do not know the specific syntax to do this but the information is available in the database. It helps to provide the why behind what you are trying to accomplish and just ask whether some behavior can be accomplished or emulated. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] returning values to variables from dynamic SQL

ments.html#PLPGS QL-STATEMENTS-EXECUTING-DYN Basically when you use "EXECUTE" you do not embed the "INTO" as part of the SQL query but rather INTO becomes a modifier of the EXECUTE itself: EXECUTE 'some query' INTO {variables} David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Query with LIMIT clause

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of JORGE MALDONADO Sent: Sunday, September 09, 2012 1:26 PM To: pgsql-sql@postgresql.org Subject: [SQL] Query with LIMIT clause I have the following records that I get from a query, the fields are date type in d

Re: [SQL] Query with LIMIT clause

well though if you are going to filter/sort on initial date frequently (by itself) then it likely wants its own index anyway and having two separate indexes would be better. David J. On Sep 9, 2012, at 20:56, JORGE MALDONADO wrote: > Firstly, who should I reply to, you or the list? > Yo

Re: [SQL] ERROR: missing FROM-clause entry for table "new"

d have the behavior, if different section numbers. In short the whole "NEW.name" is a variable and you need to build the statement the same way you would with any user-defined variable. David J.

Re: [SQL]

solve > this issue without affecting the ORDER BY it already has ? > > Regards, > Jorge Maldonado Since you are forced to include the ON field(s) first in the ORDER BY if you want a different final sort order you will have to use either a sub-select or a CTE/WITH to execute the above query then in the outer/main query you can perform a second sort. David J.

Re: [SQL] Reuse temporary calculation results in an SQL update query

are allowed to use a FROM clause with UPDATE so if you can figure out how to write a SELECT query, including a CTE if needed, you can use that as your cache. An immutable function should also be optimized in theory though I've never tried it. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Need help with a special JOIN

, syntax not tested) Select objectid, name, coalesce(actuals.value, defaults.value) From objects cross join (select ... From attributes ...) as defaults Left join attributes as actuals on ... Build up a master relation with all defaults then left join that against the attributes taking the matche

Re: [SQL] Reuse temporary calculation results in an SQL update query [SOLVDED]

.. FROM ( WITH final_result AS ( SELECT pkid, FROM tbl WHERE ... ) -- /WITH SELECT pkid, FROM final_result ) src -- /FROM WHERE src.pkid = tbl.pkid ; If you provide an actual query better help may be provided. David J. -- Sent via pgsql-sql mailing list (pg

Re: [SQL] [noob] How to optimize this double pivot query?

. As a second (not necessarily mutually exclusive) alternative: install and use the hstore extension. David J. Thanks in advance, Bob select t.id_name, max(t.begin_time) as begin_time, max(t.end_time) as end_time, max(case when (m.id_name = 'package-version&#

Re: [SQL] [noob] How to optimize this double pivot query?

one time per id and create a materialized view - basically insert the results of the query into a physical table and for live usage query that table. This is a cache and comes with all the benefits and downsides thereof. David J. On Oct 1, 2012, at 21:13, Robert Buck wrote: > So as you

Re: [SQL] Help in accessing array

there. Each, "unnest(array)" call explodes one dimension. Consider wraping things in a function to make the code cleaner. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Calling the CTE for multiple inputs

L JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS long_high) long_high_rel You may (probably will) need to move the generate_series into a FROM clause in the sub-query but the concept holds. Then in the main query you'd simply... AND lat BETWEEN lat_low AND lat_hi

Re: [SQL] String Search

ions") as well as PostgreSQL's full text search capability since both are "string" related. http://www.postgresql.org/docs/9.2/interactive/functions.html http://www.postgresql.org/docs/9.2/interactive/textsearch.html My best guess is you want to learn about substring functions and/

Re: [SQL] How to make this CTE also print rows with 0 as count?

zcount, 0) AS zcount FROM (cal CROSS JOIN id_master) master LEFT JOIN datavalues USING (id, stamp) Also, the mixing of multiple FROM relations and JOINs is confusing. In particular is the fact the JOIN takes precedence over the "," in FROM "A JOIN clause combines two FROM items.

Re: [SQL] Trigger triggered from a foreign key

o determine how a DELETE was issued. The trigger itself would seem to be possibly exhibit concurrency issues, meaning that in certain circumstances the last record could be deleted. You may want to add explicit locking to avoid that possibility. That or figure out a better way to accompl

Re: [SQL] Insert strings that contain colons into a table

;colon" has nothing to do with it. It appears that "wwn_end" is defined as an array and not a simple text value. You need to provide the table definition for "wwn". This is a section in the documentation regarding arrays but I am guessing the use of an array in this situatio

Re: [SQL] pull in most recent record in a view

rt/update/delete triggers. A boolean flag along with a partial index can work instead of an actual table in some cases. If using a table only the pkid needs to be stored, along with any desired metadata. It probably isn't worth the effort until you actually do encounter performance proble

<    1   2   3   4   5   >