Re: [SQL] JOIN a table twice for different values in the same query

Colin Wetherbee wrote: Greetings. I have two tables I'm having a little trouble figuring out how to JOIN. One contains a list of airports along with their IATA codes, cities, names, and so forth. This table also contains an id column, which is a serial primary key. The other table contains

Re: [SQL] Shorthand column labels (SELECT col1 foo, ...) vs (SELECT col1 AS foo, ...)

Ken Johanson wrote: Interesting thread(s)! What I didn't see discussed was the possibility of making a server and/or session option, where we could elect to turn-off the old behavior (PG specific behavior) and enable the standard/shorthand syntax. Users need a migration path. I personally c

Re: [SQL] Shorthand column labels (SELECT col1 foo, ...) vs (SELECT col1 AS foo, ...)

Ken Johanson wrote: I notice PG doesn't allow shorthand column labels -- it requires the 'AS' operand. SELECT col1 foo, ...; -> ERROR: syntax error at or near "foo" For compatibility with other databases, what objections might be argued in allowing this syntax in the future? On the 'pros' s

Re: [SQL] Query design assistance - getting daily totals

A. Kretschmer wrote: am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes: year_id integer month_id integer working_day integer Why this broken data types? We have date and timestamp[tz]. It's a financial application which needs to work using a concept of '

Re: [SQL] Function result using execute

t would be an issue. Having the test at is not null seems to be doing the job. Thanks. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds & Reynolds Company ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ign

[SQL] Function result using execute

XECUTE command and should therefore I just be using a test of IF curr_amount IS NOT NULL? Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds & Reynolds Company ---(end of broadcast)--- TIP 1: if posting/reading t

[SQL] Query design assistance - getting daily totals

anyone have any alternate suggestions that would be better still? Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Select into with dynamic criteria in a plpgsql function

Paul Lambert wrote: I've got a function defined in PL/PgSQL to update some fields in a record where the criteria for pulling out some other values from a table is dynamic. I define a string called account_criteria to which I assign a normal SQL WHERE clause based on some work done earli

[SQL] Select into with dynamic criteria in a plpgsql function

um. :( Can anyone offer some suggestion(s) on how can I make my function behave? Non-violent suggestions would be preferable. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Quick question re foreign keys.

Paul Lambert wrote: It's marked not null as a result of being part of the primary key for that table which I can't really get around. I can get away with not having the foreign key though, so I'll have to go down that path. Cheers, P. Ignore this whole thread actua

Re: [SQL] request for help with COPY syntax

quotes will be loaded with those quote characters in the string. Something similar may help with your case. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] Quick question re foreign keys.

wn that path. Cheers, P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend

[SQL] Quick question re foreign keys.

key that is conditional, i.e. only enforce the foreign key where the value in that table is not null. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Extracting hostname from URI column

Paul Lambert wrote: chester c young wrote: I'm trying to use substr() and position() functions to extract the full host name (and later a domain) from a column that holds URLs. substring( href from '.*://\([^/]*)' ); Ok, your solution looks better than mine... but I have

Re: [SQL] Extracting hostname from URI column

Paul Lambert wrote: Just use distinct... test=# select distinct count(*),substring( href from '.*://([^/]*)' ) as domain from url group by domain order by domain; OK so distinct was redundant there... it gives the same results without it. AutoDRS=# select count(*) as occurances

Re: [SQL] Extracting hostname from URI column

1 | dictionary.reference.com 2 | forums.mozillazine.org 1 | groups.google.com 4 | texturizer.net 11 | www.google.com 2 | www.mozillazine.org (7 rows) -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Extracting hostname from URI column

looking for) ... and everything AFTER a slash will be omitted. Cheers, Andrej Thanks - that makes a bit more sense. I'm in the middle of reading chapter 9.3.7 of the manual - POSIX Regular Expressions - which I'm assuming is dealing with this, so it's looking clearer. -- Paul L

Re: [SQL] Extracting hostname from URI column

t that, time to consult some manuals. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Extracting hostname from URI column

le.com | http://www.google.com/search?q=%s&btnI=I'm+Feeling+L ucky dictionary.reference.com | http://dictionary.reference.com/search?q=%s (10 rows) -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [SQL] Implementing an regex filter

Enrico Weigelt wrote: Hi folks, Any hints for futher optimization appreciated :) thx It doesn't look like you have any indexes - I'd add one to at least articles.title and blacklist.title to start with and probably also user_results.article_id and articles.inode_id. -- Pa

[SQL] Count of rows

ves a count of 1 for each table in the public schema. Can it be done or would I have to write a function? -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropr

Re: [SQL] Join question

done it for me again Phillip, thanks. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

[SQL] Join question

customeritem amount line | abc 457ABC 10.00 sundry| abc FREIGHT 5.00 line | abc FGOIL 15.00 What would be the best method of joining to create a result-set such as this? TIA for any assistance, P. -- Paul

Re: [SQL] Assistance with a trigger

EDURE fn_update_so_tran(); I'm liking PostgreSQL more and more with each new thing I try :) Thanks muchly. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by dona

[SQL] Assistance with a trigger

d give me a starting point that would be highly appreciated. Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [SQL] Using escape strings in an insert statement.

Michael Glaesemann wrote: On Jul 2, 2007, at 17:45 , Paul Lambert wrote: tester=# insert into testing (test_text) values ('abcE'\\'123'); This should be INSERT INTO testing (test_text) values (E'abc\123'); The help itself (ch 4.1.2.1) tells me to use double

[SQL] Using escape strings in an insert statement.

er, write two backslashes (\\). " Which one is the correct syntax and how can I make it not return anything other than a successful insert? Noob question, I know... But TIA. (Version is 8.2.3 on Weenblowz if that is of any relevance) -- Paul Lambert Database Administrator AutoLedgers ---

Re: [SQL] ignoring primary key violations in COPY command

elds in the destination table. If your record length in the load file is going to vary you may need to consider writing a program to read the data from the file and load it in. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)

Re: [SQL] pg_dump?

SERT INTO statements whereas copy will give you a flat delimited file. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command t

[SQL] Passing input to a view?

27;t find anything in the documentation that answers this, but maybe I'm not looking hard enough. Thanks in advance. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please se

Re: [SQL] Query RE using COPY

onto my side in the near future, just looking for a workaround until then. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [SQL] Query RE using COPY

icularly if it is only one or two columns in each table that that the client doesn't need. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend

[SQL] Query RE using COPY

bad design for a database, but we'll skip that point for now) thus I am not concerned if the load procedure doesn't supply it. BTW, this is done on Weendoze. Thanks, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)-

Re: [SQL] Windows postgres

Senthil wrote: Respected sir/madam, Please can you send me link of windows postgres sql download page. thanks senthil ---(end of broadcast)--- TIP 6: explain analyze is your friend http://www.postgresql.org/ftp/binary/v8.2.4/win32/ -- Paul

Re: [SQL] Moving a simple function to pl/pgsql (Novice question)

Richard Broersma Jr wrote: --- Paul Lambert <[EMAIL PROTECTED]> wrote: The purpose being when a row in a table in one database is updated, it will copy (or replicate I guess) the record into a different table into another database in the same server. (deleting said record first if it a

[SQL] Moving a simple function to pl/pgsql (Novice question)

ns or rules yet. :-) (BTW: No I am not trying to get someone to do my work for me :-P - I have about 30ish triggers of various nature to convert, I just need somewhere to start so I can figure out how it's done then I can do the rest myself) TIA, P. -- Paul Lambert D

Re: [SQL] Issue with copying data from a text file.

racter to form-feed which is not going to appear in the file and that appears to do the trick without any changes to the source file. Thanks, P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support

Re: [SQL] Issue with copying data from a text file.

Andrej Ricnik-Bay wrote: On 3/20/07, Paul Lambert <[EMAIL PROTECTED]> wrote: The source file comes from extracts on our main application which sits inside an in-house pretending-to-be-a-dbms file system. The content of these extracts would be difficult to change - the extract program

Re: [SQL] Issue with copying data from a text file.

tten would require a few dozen lines of code to each extract, and theres about 40ish extracts. Plus I don't maintain that side of our code, and those that do can be a bit lazy and I'd likely be waiting months to get it done - if they even decide to do it. -- Paul Lambert Database Admin

[SQL] Issue with copying data from a text file.

e any way to get a copy to have no quote character? I.e. read the file and put whatever is between the caret characters straight into the appropriate field exactly as is. TIA, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)---

Re: [SQL] COPY FROM query.

re the O/S gets to deal with it :) -p OK, that makes sense, cheers for the help. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] COPY FROM query.

Joe wrote: Hi Paul, On Mon, 2007-02-12 at 08:19 +0900, Paul Lambert wrote: I'm attempting to copy from a table into a file using a select query inside the copy. The following is my command: COPY (SELECT DISTINCT ON (dealer_id,appraisal_id) * FROM appraisals_temp)

Re: [SQL] COPY FROM query.

An INSERT INTO will fix my problem with needing the extra copy from and copy to. I'm still curious as to why i was being told I couldn't specify a relative path though. P. -- Paul Lambert Database Administrator AutoLedgers ---(end of

[SQL] COPY FROM query.

CSV HEADER; COPY appraisals FROM 'c:\autodrs_appraisal_new.txt' WITH DELIMITER AS '^' CSV HEADER; I'd be greatful if anyone could explain why my copy to does not work, also greatful if anyone can offer any suggestions on a better way to do what I am doing (if such a w

Re: [SQL] Removing duplicate rows

Andrew Sullivan wrote: On Wed, Feb 07, 2007 at 07:15:02AM +0900, Paul Lambert wrote: I'm assuming the best way to get around this is to load the data into a temporary table with "copy from" and then do a "select distinct into" my real table. You might find that

[SQL] Removing duplicate rows

definition of a table. Apologies if this is a stupid question, I'm still fairly new to Postgres so I'm not sure what system tables are available for pulling out this kind of information. Thanks in advance for any assistance you can offer. Regards, Paul. -- Paul Lambert Databa