Re: [SQL] Restrict FOREIGN KEY to a part of the referenced table

uot; CHECK( parent_discriminator = 42) or: COMMENT ON TABLE child "Your explanation goes here."; If you need to hide this column from you uses, you can use a view. -- Regards, Richard Broersma Jr.

Re: [SQL] Disabling constraint check

LE-EXCLUDE You cannot disable a check constraint. Perhaps a staging table is advisable here? -- Regards, Richard Broersma Jr.

Re: [SQL] updating a sequence

Regards, Richard Broersma Jr. -- 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 some aggregation magic

FROM Loggingtable GROUP BY user_id, project_id, date_trunc( 'day', ts ) ORDER BY date_trunc( 'day', ts ), user_id, project_id; -- Regards, Richard Broersma Jr. -- 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] selecting records X minutes apart

WHERE B.id = Accum_ts.id AND B.ts >= Accum_ts.ts - INTERVAL '5 MINUTES' GROUP BY B.id HAVING MIN( B.ts ) <= MAX( B.ts )) SELECT id, ts FROM Accum_ts ORDER BY id, ts; -- Regards, Richard Broersma Jr. -- Sent via pgsql-sql mailing list (pgsql-sql

Re: [SQL] selecting records X minutes apart

XISTS ( SELECT * FROM Yourtable AS B WHERE B.id = A.id AND B.ts > A.ts - INTERVAL '5 MINUTES' AND B.tx < A.ts ) ORDER BY id, ts; -- Regards, Richard Broersma Jr. -- Sent via pgsql-sql mailing list (pgsql-sql@p

Re: [SQL] Dates and NULL's`

will be returned too The WHERE clause will only return rows is the arguments all evaluate to TRUE. No rows will be returned for rows that cause the WHERE clause to evaluate to a NULL value. -- Regards, Richard Broersma Jr. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To m

Re: [SQL] Dates and NULL's`

4-01'::DATE AND NULL::DATE <= '2011-04-30'::DATE) IS FALSE; ?column? -- f (1 row) spi=> SELECT (NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= '2011-04-30'::DATE) IS UNKNOWN; ?column? -- t (1 row) -- Regards, Richard Br

[SQL] None numeric exclusion constraints using GIST

ostgresql.org/docs/9.1/static/datatype-geometric.html http://www.postgresql.org/docs/9.1/static/sql-createindex.html http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ Is this correct? -- Regards, Richard Broersma Jr. -- Sent via pgsql-sql mailing list (pgsql-sql@p

Re: [SQL] concatenate question

On Tue, Dec 7, 2010 at 1:47 PM, Tony Capobianco wrote: > Why did I need to cast both as text though? http://www.postgresql.org/docs/8.3/interactive/release-8-3.html#AEN87134 -- Regards, Richard Broersma Jr. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to y

Re: [SQL] force view column varchar(32) to varchar(128)

o push your data to the new schema. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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 PL/pgSQL text argument in 'IN (INT,INT,...)' clause [re-post]

should work: UPDATE foo Set x = y WHERE id = ANY( CAST( string_to_array( '1,2,3,4', ',' ) AS INTEGER[] )); -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@po

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

> native postgres driver, does any better? >From my limited experience, I believe is does do better. The following blogs as a few entries about using Base: http://www.postgresonline.com/journal/archives/167-Importing-data-into-PostgreSQL-using-Open-Office-Base-3.2.html -- Rega

Re: [SQL] Delete rules

rd to get correct. They sometimes do unexpected things. It looks like the CREATE Trigger FOR EACH STATEMENT is better suited to do what you want: "In addition, triggers may be defined to fire for a TRUNCATE, though only FOR EACH STATEMENT." -- Regards, Richard Broersma Jr. Visit the L

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

6-08 14:39:43 PDTSTATEMENT: BEGIN; UPDATE "public"."structures" SET "scoped"=E'-1' WHERE "buildingfunction" = E'CRANE OPERATOR STATION' AND "xmin" = 20497 -06-08 14:39:43 PDTLOG: duration: 0.000 ms statement: ROLLBA

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

I could find. I know that there are a couple of options that affect the representation of Booleans in the odbc driver. I'm not sure it will do what you need though. However, here is the official documentation: (hopefully it helpful) http://psqlodbc.projects.postgresql.org/ -- Regard

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

; bareword 1 and 0 instead of 't' and 'f'. For the sake of compatibility > (especially bareword integers in queries), I've defined a 'sybit' type > in postgres to be a domain. One thought would be see if ODBC configuration options will achieve this for you. H

Re: [SQL] what does this do

ta-types in the alias declaration for all set returning relations with the exception of a set returning function (i.e. store procedure). The from clause has a give-away that this is a set returning function: "jfcs_balancedue('%s')" since it has a parameter. Notice the function

Re: [SQL]

te" = '2010-05-21'::date AND "completio ndate" = '-12-31'::date /* The table was automatically re-queried to find out what the new itemnbr actually is according to its default value. And lastly the former query tha

Re: [SQL]

erial datatype. At-least in recent versions auto increment is recognized by MS-Access just fine (I'm guessing this is due to the Return clause which the ODBC driver automatically calls). -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postg

Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function

IN >    curtime := 'CURRENT_DATE'; >    LOOP I'm not "up" on my pl/pgSQL, but isn't CURRENT_DATE a literal value so it shouldn't to be enclosed in single quotes? Another idea would be to: CAST( now() AS DATE ) -- Regards, Richard Broersma Jr. V

Re: [SQL] Greetings folks, dumb question maybe?

FROM T p2082849b(> WHERE n <= 10000) p2082849b-> SELECT n,c,d FROM T; INSERT 0 10001 -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list

[SQL] Celko take on EAV

I'm rereading my Joe Celko's SQL Programming Style and I noticed an interesting comment regarding the EAV model (of course he discourages its use): "There are better tools for collecting free-from data." What tools was he referring to? -- Regards, Richard Broersma Jr. V

Re: [SQL] graphing time series data

discrepencylist ds, opendays WHERE opendays.day >= ds.discstartdt AND opendays.day <= LEAST('now'::text::date, ds.resolutiondate) GROUP BY opendays.day, ds.resolvingparty ORDER BY opendays.day, ds.resolvingparty; -- Regards, Richard Broersma Jr. Visit the Los Angele

Re: [SQL] Table Design for Hierarchical Data

raints. While 8.4 has CTE's which are good for querying adjacency list tree, we need to wait for write-able CTE's (maybe 9.1?) to preform all of the possible tree modifications. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.post

Re: [SQL] check constraint on multiple tables?

this hierarchy will have overlap, this is an indication of a violation of the second normal form (I believe). -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.or

Re: [SQL] check constraint on multiple tables?

. Each cabin is defined by a category according the set in the cabin_category table. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make chang

Re: [SQL] check constraint on multiple tables?

On Wed, Mar 3, 2010 at 7:02 AM, Louis-David Mitterrand wrote: > What is the best solution? Adding an id_ship to 'cabin'? Or check'ing > with a join down to 'ship'? (if possible). Can you post simplified table definitions for the relations involved? -- Regar

Re: [SQL] Fwd: conditional rule not applied

mpt to perform an update. The official use for update-able views is for limiting the results from a *single* base table. Having said all of this, it is possible to do what your describing. I've seen Keith Larson make update-able views from a composite of selected UNION and FULL OUT JOIN quer

[SQL] CREATE SERVER - what is this?

I noticed a few new SQL references in the manual: CREATE SERVER CREATE FOREIGN DATA WRAPPER, CREATE USER MAPPING Is this similar to DBI-Link? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing

Re: [SQL] ORDB and dot-notation query

> ERROR: schema "adress" does not exist > > Do somebody have some problem and how to fix this? Are you sure that you didn't just misspell address? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent

Re: [SQL] Learning about WITH RECURSIVE

On Wed, Nov 4, 2009 at 2:11 PM, Tom Lane wrote: > Richard Broersma writes: >> Can anyone one explain why a "WITH RECURSIVE" query has the same >> results regardless whether UNION or UNION ALL is specified? > > Well, if the rows are all different anyway, UNION

Re: [SQL] Learning about WITH RECURSIVE

On Wed, Nov 4, 2009 at 2:11 PM, Tom Lane wrote: > Richard Broersma writes: >> Can anyone one explain why a "WITH RECURSIVE" query has the same >> results regardless whether UNION or UNION ALL is specified? > > Well, if the rows are all different anyway, UNION

[SQL] Learning about WITH RECURSIVE

> SELECT sum(n) FROM t; sum -- 5050 (1 row) broersr=> WITH RECURSIVE t(n) AS ( broersr(> VALUES (1) broersr(> UNION ALL broersr(> SELECT n+1 FROM t WHERE n < 100 broersr(> ) broersr-> SELECT sum(n) FROM t; sum -- 5050 (1 row) -- Regards, Richard

Re: [SQL] left outer join on more than 2 tables? (UNCLASSIFIED)

flavors are available also). -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] left outer join on more than 2 tables?

*) more since it helps to correctly express the idea that we are counting rows per group and not cm.id(s) per group. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgre

Re: [SQL] left outer join on more than 2 tables?

have proposed; > > SELECT >    R.region_name, >    Count(*) AS RegionComplaints > FROM >    Region AS R >        LEFT JOIN City AS Ci >            LEFT JOIN Complaint AS Cm ON Ci.id = Cm.city_id >      ON R.id = C.region_id > GROUP BY >    R.region_name; Yup, it produces the s

Re: [SQL] left outer join on more than 2 tables?

on_name, Count(*) AS RegionComplaints FROM Region AS R LEFT JOIN City AS Ci ON R.id = C.region_id LEFT JOIN Complaint AS Cm ON Ci.id = Cm.city_id GROUP BY R.region_name; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgr

Re: [SQL] Type inheritance

store. Its kind-of like EAV for a column instead of a table. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] changing multiple pk's in one update

ly the most common solution is to use optimistic locking, another solution that I know of is to use serialized transaction isolation. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgs

Re: [SQL] changing multiple pk's in one update

eign key is set with ON UPDATE CASCADE then the shifts will be cascaded to all references automatically. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make

Re: [SQL] changing multiple pk's in one update

efit from this feature. The two that come to my mind are the Celko nested set tree model, and sequenced temporal tables. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresq

Re: [SQL] Problem inserting image

, in postgres 8.3 Here is an example how how ADODB.Stream class work in conjunction with ADODB.command prepared statements to push binary data into a field. The same should work with PostgreSQL. http://www.databasedevelopment.co.uk/examples/StoredBlobs.zip -- Regards, Richard Broersma Jr. V

Re: [SQL] Best way to restrict detail rows?

nto my head that may-or-may-not work would be to add a constraint trigger that checks if all of the detail records have the same xmin as the order table record. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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 how-to

( SELECT COUNT(*) FROM Yourtable AS Y2 WHERE Y2.end_date = A."date" ) AS closed FROM ( SELECT start_date AS "date" FROM Yourtable GROUP BY start_date UNION SELECT end_date AS "date" FROM Yourta

Re: [SQL] Finding sequential records

d inflexible constraints (beyond preventing duplicates) across multiple relationships, then using natural primary keys becomes very attractive. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing li

Re: [SQL] Finding sequential records

time. Well at least the ones that subscribe to www.utteraccess.com get bitten. From what I've seen not one day has gone by without someone posting a question to this site about how to both find and remove all but one of the duplicates. -- Regards, Richard Broersma Jr. Visit the Los Angeles

Re: [SQL] Finding sequential records

This works instead: SELECT D1.* FROM Dummy AS D1 INNER JOIN Dummy AS D2 ON (D1.name,D1.fkey_id)=(D2.name,D2.fkey_id) AND (D1.id = D2.id + 1 OR D1.id = D2.id - 1 ) ORDER BY D1.id; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) h

Re: [SQL] Finding sequential records

S A > INNER JOIN Dummy AS D > ON A.id - 1 = D.id > OR A.id + 1 = D.id; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To mak

Re: [SQL] Finding sequential records

BY name, fkey_id ) AS A INNER JOIN Dummy AS D ON A.id - 1 = D.id OR A.id + 1 = D.id; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.or

Re: [SQL] surrogate vs natural primary keys

last line of defense to ensure business rules and data integrity are not violated. Since I highly value the ability to enforce business rules using ordinary table DDL, I try to use natural keys as often as I can. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Grou

Re: [SQL] a simple transform

On Tue, Sep 16, 2008 at 6:37 AM, Frank Bax <[EMAIL PROTECTED]> wrote: > > That pass the SQL into crosstab(). It might be interesting to look at this blog also: http://okbob.blogspot.com/#7449458148004287481 -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users

Re: [SQL] surrogate vs natural primary keys

he time I use natural keys, however there are some occations when surrogate keys (for me) are only way to go. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make chang

Re: [SQL] Aggregates in WHERE clause?

to force it in the where, you need to put the groupby in a sub-query. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] order of rows in update

sible routes he has to find the routes with the best latency. He has configures a revolving Master-Slave replication. Perhaps you guys to share notes? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql

Re: [SQL] order of rows in update

e a database normalization problem. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] LIMIT question

en to you: http://www.commandprompt.com/blogs/joshua_drake/2007/08/how_many_rows_do_i_have_anyway/ -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make

Re: [SQL] Join question

try FULL OUTER JOIN. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] more than 1000 connections

;t forget to reply-all so that everyone on the list can participate. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

Re: [SQL] How to change a view's owner in postgres(is it possible?)

imple use the ALTER TABLE statement to perform this operation. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://ww

Re: [SQL] more than 1000 connections

ardware. All of the postgresql.conf options are shown as well as the compiler options used to build postgresql. http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-20070703-00073.html I hope this helps. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL User

Re: [SQL] fast insert-if-key-not-already-there

T ip_addr FROM ( VALUES ( v_ip_addr )) AS A( ip_addr ) LEFT JOIN Ip_addresses AS B ON A.ip_addr = B.ip_addr WHERE B.ip_addr IS NULL; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent vi

Re: [SQL] Case Insensitive searches

;functional" index: http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html This way all expression using where lower( column ) = 'a'. will always use an index scan. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.post

Re: [SQL] Select default values

ate function overloading. Then internally you could handle the null by passing DEFAULTS to you INSERT or UPDATE statements. I don't know if this would work for you in this case. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/l

Re: [SQL] Strange query duration

38.154 ms :o) You might be encountering a bit of parallax. This shows both 38 and 36 *milliseconds*. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To ma

Re: [SQL] Rollback in Postgres

architecture and would work automatically with transaction ids and tuple ids. On the other hand, temporal tables/schemes are implemented by the data modeller. Also the associated temporal operations on the data would be handled by client DML designed to simulate temporal data operations. --

Re: [SQL] Rollback in Postgres

temporal db project on PGfoundry. This project is just getting started and could benefit from a lot of development help. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@pos

Re: [SQL] column default dependant on another columns value

T *, seconds / 60 AS minutes FROM yourtable; -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] ANSI Standard

ansi features, certain ddl will probably not commit or at least will be ignored on most databases. We database brand has incorporated a different set of features. > How do I identify them? This should get you started : http://www.postgresql.org/docs/8.3/interactive/features.html -- Regards,

Re: [SQL] Extremely Low performance with ODBC

ap of the problem you are having and, the log snippet for each query( one from ODBC and one from PGadmin ) (I would attach these as a txt file) the EXPLAIN ANALYZE of your query ( on run from the ODBC client and on from PGadmin) (I would attach these as a txt file) -- Regards, Richard Broersma

Re: [SQL] Extremely Low performance with ODBC

, and PGADMIN. If we compare them It should at least tell us what the server is doing differently that is causing the query mis-behavior. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailin

Re: [SQL] Extremely Low performance with ODBC

s you've hand coded in pg-admin 4) also check the query duration when issued by ODBC in the postgresql log to see if it is really taking 2 minutes. It would be nice to see the snippet of the PG log showing this ODBC query with associate duration times. -- Regards, Richard Broersma Jr. Visit

Re: [SQL] Extremely Low performance with ODBC

get a client interface to prefix its select statements with explain analyze. And this, I don't thing can be done. That's why you have to manually view the server logs to see what query are being passed so that identify the problem queries your self. -- Regards, Richard Broersma Jr.

Re: [SQL] Extremely Low performance with ODBC

l command line to help identify where the performance problem is. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] Extremely Low performance with ODBC

passing the actual SQL statement back to the server. If it is trying to perform the join on the client, it could be a big performance killer. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (p

Re: [SQL] Support for SQL TOP clause?

me < I2.name WHERE --any where criteria you might have GROUP BY I1.name, ... HAVING COUNT(*) < 3 ORDER BY I1.name; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by dona

Re: [SQL] Get the max(value1, value2, value3) from a table

--- On Mon, 1/7/08, Scott Marlowe <[EMAIL PROTECTED]> wrote: > select max(col1) from table > union all > select max(col2) from table > union all > select max(col3) from table Would the following work also? SELECT MAX( GREATEST( col1, col2, col3 ) ) FROM TABLE; Regards

Re: [SQL] Limit # of recs on inner join?

SELECT DISTINCT ON ( B.book_id, B.title ) B.book_id, B.title, A.Author FROM Books AS B INNER JOIN Authors AS A ON A.book_id = B.book_id; --- On Mon, 12/31/07, Josh <[EMAIL PROTECTED]> wrote: > From: Josh <[EMAIL PROTECTED]> > Subject: [SQL] Limit # of recs on inner join? > To:

Re: [SQL] Advice for generalizing trigger functions

etermine the Primary Key components of the table that will also be needed in the SQL string that will be executed? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] how to use pgsql like mssql

from the SQL prompt? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] how to use pgsql like mssql

guage.html You should probably use PLpgSQL since it is available across OS all platforms and is easy to learn and use. http://www.postgresql.org/docs/8.2/interactive/plpgsql.html There are some nice examples in this documentation. Your PGadmin will also have features to help the cre

Re: [SQL] need help

postgresql references." Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] need help

not, but you can find alot of PostgreSQL people on IRC: http://www.postgresql.org/community/irc Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Advice for generalizing trigger functions

;s state that existed in "times passed". If I didn't need to regularly provide this functionality, I would certainly use an EAV history table. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/read

[SQL] Advice for generalizing trigger functions

ze these myriad of functions into one? Below is a sample of a typical logging trigger function. Regards, Richard Broersma Jr. CREATE OR REPLACE FUNCTION "project"."log_managers_ops"() RETURNS trigger AS $BODY$ BEGIN IF TG_OP IN ( 'UPDATE', 'DELETE&#x

Re: [SQL] Describe Table

construct my own sql queries that provide the same useful information as 'psql \d'. So instead, I cheat: start psql with the '-E' option to echo all sql strings psql sends to the back-end server. Simply copy and tailor each statement to suit your needs. Regards, Richard

Re: [SQL] Insert Rule, Multiple Insert, Update Problem

--- On Mon, 12/10/07, Scott <[EMAIL PROTECTED]> wrote: > I am having trouble with an insert rule that also does an > update. It works > fine for a single insert but multiple inserts in a single > statement don't > behave as I expected. Yup, that is the limitation of rules. They are only useful

[SQL] Bad Schema Design or Useful Trick?

ts( part_nbr, part_type) ON DELETE CASCADE ON UPDATE CASCADE); Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] update on join ?

you are probably after: UPDATE Inventory SET number = 0 FROM Things WHERE Inventory.thing_fk = Things.thing_id AND Things.color = 'red'; IIRC, Joe Celko referrers to this syntax as "T-SQL". Regards, Richard Broersma Jr. ---(end of broadcast)-

Re: [SQL] postgres bogged down beyond tolerance

to : [EMAIL PROTECTED] . This mailing list ( pgsql-sql@postgresql.org ) is really intended for discussions of how to construct SQL queries to achieve desired results in a PostgreSQL database. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2:

Re: [SQL] Having difficulty writing a "best-fit" query..

YMENT AGENCY PUZZLE http://www.elsevier.com/wps/find/bookdescription.cws_home/710075/description#description the only difference was that he was modeling employees and skillsets. IIRC, the terminology for the improved model was the "full disjuctive" model. Regards, Richard Broersma Jr. -

Re: [SQL] Need help with CASE statement in Function

one without? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Many databases

API on top of it that manages the data. Not a big deal but it > complicates things :-) The veil project already does this. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Simple Query?

1 > 3 3 > > > Now what I want to have is the items that match with id_search 1 and > 2 and 3. Therefore I use the following SQL query. SELECT id_search FROM Search_item GROUP BY id_search HAVING Count( * ) = 3

Re: [SQL] Cast on character columns in views

u open psql -U your_db_user -d your_database_name. and type: \d schema_name.view_name was does it show as the definition of the column. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by

Re: [SQL] Cast on character columns in views

ostgresql.org/docs/8.2/interactive/sql-createview.html I've created large SQL scripts that employ: DROP VIEW IF EXITS viewname; CREATE VIEW ... Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Cast on character columns in views

le1.id > UNION ALL > SELECT table1.id, >table1.col1, >CAST( NULL AS CHARACTER( 3 )), >table3.type2 > FROM table1 > JOIN table3 ON table3.fk_table1 = table1.id; Would the above changes work? Regards, Richard Broersma Jr. ---(e

Re: [SQL] Constraints for grouping

OOPS! --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > CREATE UNIQUE INDEX Only_one_row_true > ON Your_table ( featured ) >WHERE featured = true; > > Or if you want to only allow 1 featured article per catagory then: > > CREATE UNIQUE INDEX Onl

Re: [SQL] Constraints for grouping

E featured = true; This will ensure that the sum( featured = true ) <= sum( unique( catagories )). Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-noma

Re: [SQL] SELECT syntax synopsis: column_definition?

--- Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Any function declared as returning SETOF RECORD needs it, when you don't > use OUT params. Before OUT params existed, it was the only way to use > those functions. Thanks everyone for the exposition! It makes sense. Regards,

  1   2   3   >