[SQL] Peer-review requested of soft-delete scheme

statements against the table are view, and coud reference the underlying table directly. Is this sensible? Is there another approach to soft-deletes I should be considering? Thanks! Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

[SQL] Need help revoking access WHERE state = 'deleted'

However, this isn't working, I permission denied when trying to use the view. (as the same user that has had their SELECT access removed to the underlying table.) We are not stuck on this design. What's a recommended way to solve this problem? Mark -- Sent via pgsql-sql mailing list (pgsql-sql

Re: [SQL] Need help revoking access WHERE state = 'deleted'

On 02/28/2013 01:02 PM, Ben Morrow wrote: Quoth m...@summersault.com (Mark Stosberg): We are working on a project to start storing some data as soft deleted (WHERE state = 'deleted') instead of hard-deleting it. To make sure that we never accidentally expose the deleted rows through

Re: [SQL] Need help revoking access WHERE state = 'deleted'

On 02/28/2013 02:08 PM, Tom Lane wrote: Mark Stosberg m...@summersault.com writes: # Explicitly grant access to the view. db= grant select on entities_not_deleted to myuser; GRANT # Try again to use the view. Still fails db= SELECT 1 FROM entities_not_deleted WHERE some_col = 'y'; ERROR

[SQL] Fun with Dates

() ) ) + ' days' In other words, if today is the 29th of the month, I want to select data that is within 29 days old... WHERE obstime = NOW() - INTERVAL '29 days' How do I craft a query to do use a variable day of the month? Mark attachment: mark_fenbers.vcf

Re: [SQL] Fun with Dates

simple!   thanks! Mark attachment: mark_fenbers.vcf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] complex query

d of "col3"). I could use UNION to simplify, except that UNION will return 2 rows, and the code that receives my resultset is only expecting 1 row. Is there a better way to go about this? Thanks for any help you provide. Mark attachment: mark_fenb

Re: [SQL] complex query

as appropriate. Thanks! Your idea worked like a champ! Mark attachment: mark_fenbers.vcf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Aggregates puzzle

to the GROUP BY clause, which makes the MAX(value) function report *each row* as a maximum. So, how can I revise my SQL to report the obstime that the MAX(value) occurred? Any help is sincerely appreciated. Mark attachment: Mark_Fenbers.vcf -- Sent via pgsql-sql mailing list (pgsql-sql

[SQL] recommended data model for a search?

a unique index that doesn't have a problem with null values, too. Is this a reasonable approach, or is there a better design I'm overlooking? Thanks, Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

[SQL] import ignoring duplicates

of the file. I am looking for an option/switch to tell psql (or the \copy command) to skip over any duplicate key constraint viloations and continue to load any data that doesn't violate a duplicate key constraint. Is there such an option? Mark attachment: Mark_Fenbers.vcf -- Sent via pgsql-sql

[SQL] Simple aggregate query brain fart

does not exist). When using an GROUP/ORDER BY clause, I can refer to a column number (e.g., GROUP BY 1) instead of a column name, but how can I refer to my unnamed second column in my where clause? Mark attachment: Mark_Fenbers.vcf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org

Re: [SQL] Simple aggregate query brain fart

Thanks, Joe and Tom. You cleared the webs out of my brain. I used HAVING before, but not lately and I got rusty. Mark Tom Lane wrote: Mark Fenbers mark.fenb...@noaa.gov writes: I want to do: SELECT id, count(*) FROM mytable WHERE count(*) 2 GROUP BY id

[SQL] Linked list with CTE

, C, D, E, F, G tree. If I give the query 'Y', I'd like it to return the Z, Y tree. I hope I made sense... Thanks! Mark

Re: [SQL] SQL Subqueries on each result row

index_dates; RESULT: DATE| SUM --- 2009-09-19|1 2009-09-20|3 2009-09-21|9 2009-09-24|17{inserted instead of 22 Sep} 2009-10-25|16{inserted instead of 23 Sep} Regs, Mark

Re: [SQL] Rewrite without correlated subqueries

.  Your new query would not include the ... IN ( list ) syntax... Mark bricklen wrote: Hi All, I'm having some trouble wrapping my head around the syntax to rewrite a query using correlated subqueries, to using outer joins etc. The query: SELECT  ps.userid,    SUM( ps.hits

[SQL] drop PW

: ALTER USER george PASSWORD ''; and that looked like it succeeded, but running psql again prompted me and when I just hit Enter, it complained that no password was supplied. So how do I turn off being prompted for a password for george. (I am aware of the security risks...) Mark -- Sent via

[SQL] Best practices for geo-spatial city name searches?

, the coordinates for a city could be found. Is there any other way I should be considering data modelling to support searches on zipcodes and cities? Thanks! Mark -- . . . . . . . . . . . . . . . . . . . . . . . . . . . Mark StosbergPrincipal Developer m...@summersault.com

Re: [SQL] Left Join Question

with it. The planner will likely pare down tasks to its result set before joining to the other joins (which are all keyed on various fields from task). I know that we have lots of joins like this scattered all over our code, but we usually use inner joins unless there's a specific reason not to. -Mark

Re: [SQL] For each key, find row with highest value of other field

| value -++--- A | 2008-05-01 | foo B | 2008-03-01 | baz C | 2008-06-03 | foo (3 rows) Time: 0.524 ms -Mark On Sat, 2008-10-04 at 00:25 +0530, Raj Mathur wrote: I have some data of the form: Key | Date | Value A | 2008-05-01 | foo* A | 2008-04

Re: [SQL] exclusion query

on it. Sorry if this isn't quite what you're asking for. -Mark -- 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] RE: [SQL] Why *no* ambig.uous complain in select part?

, col2) where T2.col1 is not null -Mark -- 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] Question on partitioning

that fine grained partitioning coupled with check constraints can even eliminate the need for certain indexes. I do know that check constraints on the date will help you tremendously if you decide to partition. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your

[SQL] vacuum in single-user mode

. Can anyone lend some advice, please? Mark -- 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] DELETE with JOIN

DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?) This is akin to: delete from a where (a.key1, a.key2, a.key3) in (select key1, key2, key3 from b) I use this every day for millions of rows per delete and it works just fine and in a very reasonable time period. -Mark

Re: [SQL] more than 1000 connections

right now. OLTP and/or large connection counts isn't really what I spend my days thinking about. ;-) -Mark -- 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] pg_advisory_lock(bigint) vs. LOCK TABLE

things that don't bother checking advisory locks (such as users, manual scripts, buggy applications, etc). Don't forget that you can use select for update another locking mechanism as well. Well, that's my take on it. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make

Re: [SQL] How to GROUP results BY month

On Wed, 2008-07-16 at 07:39 +0200, A. Kretschmer wrote: am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts folgendes: On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote: am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes: Howdy, all

Re: [SQL] How to GROUP results BY month

recommend group by date_trunc('month', timestamp field) -Mark -- 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] Rollback in Postgres

://www.postgresql.org/docs/8.3/interactive/sql-rollback.html Best of luck in your endeavor :) -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Re: Best practice for long-lived journal tables: bigint or recycling IDs?

that it wraps around at the point it reaches INT_MAX. So inserting this number would never actually happen. Ah, that does look like the best solution. I'll confirm that will work for our cases. Thanks! Mark alvherre=# create table t (a serial); NOTICE: CREATE TABLE créera des séquences

[SQL] Best practice for long-lived journal tables: bigint or recycling IDs?

for me. What have others done? Mark -- . . . . . . . . . . . . . . . . . . . . . . . . . . . Mark StosbergPrincipal Developer [EMAIL PROTECTED] Summersault, LLC 765-939-9301 ext 202 database driven websites . . . . . http://www.summersault.com

[SQL] Re: Best practice for long-lived journal tables: bigint or recycling IDs?

On Tue, 8 Jul 2008 17:20:13 -0400 Alvaro Herrera [EMAIL PROTECTED] wrote: Mark Stosberg wrote: Hello, I have some tables that continually collect statistics, and then over time are pruned as the stats are aggregated into more useful formats. For some of these tables

[SQL] Egroupware infolog query slow (includes query plan)

to finish! I'm not skilled enough at reading the Explain Analzyze output to understand what the primary problem is. Thanks! Mark ### SELECT DISTINCT main.* ,( SELECT count(*) FROM egw_infolog sub WHERE sub.info_id_parent=main.info_id AND (info_owner=6

Re: [SQL] Egroupware infolog query slow (includes query plan)

I should have mentioned in the last post that PostgreSQL 8.2.9 is in use. I could upgrade to 8.3.x if that is expected to help performance in this case. Mark On Sun, 2008-07-06 at 16:23 -0400, Mark Stosberg wrote: Hello, I could use some help figuring out how to speed up a query. Below

[SQL] GROUP BY on a column which might exist in one of two tables

to try next. Thanks! Mark -- 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] GROUP BY on a column which might exist in one of two tables

select coalesce(h.partner_id, v.partner_id) as partner_id, coalesce(v.count, 0) as total_views, coalesce(h.count, 0) as total_hits from (select partner_id, count(*) from hits group by partner_id) as h full outer join (select partner_id, count(*) from views group by

Re: [SQL] GROUP BY on a column which might exist in one of two tables

this?: SELECT partner_id, COUNT(view_id) AS total_views, COUNT(hit_id) AS total_hits FROM ( SELECT partner_id, hit_id, NULL AS view_id FROM hits UNION ALL SELECT partner_id, NULL, view_id FROM views ) AS foo GROUP BY 1; That was very helpful, Greg. My new design looks much like this. Mark

Re: [SQL] Different type of query

: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 order by nutrient_value desc limit 8 -Mark -- 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 create function which takes no arguments

IIRC, current_timestamp doesn't require parens. You could try something like this: select extract(epoch from current_timestamp)::int4 as result; -Mark On Mon, 2008-06-09 at 12:05 -0400, Michael Eshom wrote: I am a project manager for a popular forum system. We are adding support

[SQL] Informix Schema - PostgreSQL ?

does PostgreSQL call this (what Informix calls a schema)?? Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

[SQL] Requirement for PostgreSQL Database Developer

Hi , Location: San Diego, CA [You can also TeleCommute but you have to be on the client side for 2 Weeks in a month] Duration: 6+ months. This is Mark with ProV International, This email is in regards to the requirement we have with one of our direct client in San Diego, CA

[SQL] Requirement for PostgreSQL Database Developer

Hi , Location: San Diego, CA [You can also TeleCommute but you have to be on the client side for 2 Weeks in a month] Duration: 6+ months. This is Mark with ProV International, This email is in regards to the requirement we have with one of our direct client in San Diego, CA

[SQL] Requirement for PostgreSQL Database Developer

. thanks , Mark,ProV InternationalTampa, FL 33607Tel 408 - 241 - 7795 Xtn - 27[EMAIL PROTECTED]www.provintl.com

[SQL] Create View

whatever. I thought it should be possible to save the star (*) as part of the view. Otherwise, I run into the problem of not getting all of the fields if I add columns to the table (whatever). Is this an SQL thing or a PostGreSQL thing? Thanks, Mark -- Mark Simon Manngo Net Pty Ltd Phone/Fax: 1300

[SQL] Create Function (SQL)

the table (whatever) as out parameters, or as a return type, or can I have a generic table returned from this? Thanks, Mark -- Mark Simon Manngo Net Pty Ltd Phone/Fax: 1300 726 000 mobile: 0411 246 672 email: [EMAIL PROTECTED] web: http://www.manngo.net Resume: http://mark.manngo.net

Re: [SQL] Temp tables and functions

Thanks, Jim. I'll give it a try. On Tue, 2006-10-10 at 21:11 -0500, Jim C. Nasby wrote: On Tue, Oct 10, 2006 at 03:21:36PM -0400, [EMAIL PROTECTED] wrote: Everyone, I've written a function (language = sql) which uses a temporary table to simplify the process; however, when I go to

[SQL] INSERT ... SELECT FROM .... FOR UPDATE?

, I wanted to get the opinions of others here if this would be a good idea. Thanks! Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

[SQL] Avoiding RI failure with INSERT ... SELECT FROM

design that avoids this possible error? Thanks! Mark ---(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] Good examples of calling slony stored procedures

Hi Can somebody direct me to the mailing list for slony. I couldn't find it anywhere on the postgres.org website (which is where I found this list to begin with). Thanks Mark -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 20:27 To: Mark Adan

Re: [SQL] Good examples of calling slony stored procedures

Hi Jim I looked there already and didn't find what I needed. I saw this web page from cbbrowne and he briefly talked about using bare metal slony functions, but doesn't have any examples. Mark -Original Message- From: Jim Buttafuoco [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 14

Re: [SQL] Good examples of calling slony stored procedures

Excellent. I will subscribe to that one. Thanks Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan Sent: Wednesday, June 14, 2006 09:14 To: pgsql-sql@postgresql.org Subject: Re: [SQL] Good examples of calling slony stored procedures

[SQL] Requirement for PostgreSQL Database Developer

tight time constraints Strong analytical skills Capable of working independently with minimal supervision. Location: San Diego, CA Duration: 6+ months. If you find yourself comfortable with this job profile find it interesting please send me your resume in MS Word Format. Kindest Regards,Mark

[SQL] Good examples of calling slony stored procedures

Hi I was wondering if where can I find some examples of calling the slony stored procedures instead of using slonik? I want to be able to for example add a table into slony. Thanks Mark Adan ---(end of broadcast)--- TIP 1: if posting/reading

[SQL] Does PG have a database

for each host, so specifying this in the software is not practical. Bottom line: What trick can I use to determine whether a box has a living, breathing Pg database if I don't know the DB owner? Mark ---(end of broadcast)--- TIP 2: Don't 'kill

Re: [SQL] Looking for some help with cascading updates...

: Mark True [EMAIL PROTECTED] schrieb: Here is the question: I have a situation where I need to create triggers to cascade an insert operation to many tables to maintain foreign key constraints. So at a high level INSERT INTO myTable (Name, Address, Zip) VALUES ('Mark', '3 Dingle St.', '01832') myTable

Re: [SQL] Looking for some help with cascading updates...

Next silly question, how do you get your database to like plpgsql...I do CREATE LANGUAGE 'plpgsql' and it says it is not defined?--MarkOn 4/18/06, Andreas Kretschmer [EMAIL PROTECTED] wrote: Mark True [EMAIL PROTECTED] schrieb: I guess I should have clarified.I am having trouble figuring out how

[SQL] Change date format through an environmental variable?

no effect if trying to set it as an envvar. Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Change date format through an environmental variable?

I found PGDATESTYLE that solves my problem, but ever since, I've been looking for a comprehensive list of environmental variables that Pg recognizes, but haven't been able to find such a list in any of the books I looked in or the man pages. Anyone know where I can find such a list? Mark

Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.

Pedro, Would something such as this suffice? Mark create function get_date_range(date, date) returns setof date as ' DECLARE     cur date; BEGIN     cur := $1;     while cur = $2 LOOP          return next cur;          cur := cur + interval ''1 day'';     end LOOP;     return; END;' language

Re: [SQL] group by complications

Wow! I didn't know you could have a (select ...) as a replacement for a 'from' table/query. Your SQL worked as-is, except I had to add a 'limit 1' to the first subquery. Thanks! I would have never figured that out on my own! Mark chester c young wrote: --- Mark Fenbers [EMAIL PROTECTED

[SQL] group by complications

to do something like this: select l.lid,l.fs,most_recent(h.obsvalue) from location as l inner join height as h on h.lid = l.lid where l.fs 0.0 group by l.lid,l.fs; Can someone offer hints, please? Mark ---(end of broadcast)--- TIP 9: In versions

[SQL] pgadmin

and PostGIS installed without any problems, but this single utility (pgadmin) is really throwing me for a loop. Does anyone have any ideas I could try? Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

[SQL] Just 1 in a series...

this issue? Mark ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Re: [SQL] Just 1 in a series...

You might find the DISTINCT ON syntax does just what you want --- see the weather report example on the SELECT reference page. It's not standard SQL though. This works! Thanks! What would have to be done if I needed a standard SQL solution? Mark ---(end

[SQL] APPEND INTO?

I want to SELECT INTO mytable WHERE (criteria are met), except that I want to APPEND into an existing table the rows that are selected, instead of creating a new table (which SELECT INTO will do). How can this be done? (Is this what the FOR UPDATE OF tablename clause is for?) Mark

Re: [SQL] PGSQL encryption functions

is appropriate or that I'm being a bit paranoid? Thanks On Tuesday 01 November 2005 05:13 pm, Tom Lane wrote: Mark R. Dingee [EMAIL PROTECTED] writes: md5 works, but I've been able to brute-force crack it very quickly, Really? Where's your publication of this remarkable breakthrough

Re: [SQL] PGSQL encryption functions

into sensitive data. Thanks Mark On Wednesday 02 November 2005 01:59 pm, Bruno Wolff III wrote: On Tue, Nov 01, 2005 at 17:00:50 -0500, Mark R. Dingee [EMAIL PROTECTED] wrote: Bruno, I use an authenticate() function as a part of state maintenance in a PHP web app. In the function

[SQL] PGSQL encryption functions

Everyone, I'm in need of a one-way pgsql script that will take a plain-text string and return an ecrypted string (preferably 32 character) . I've been using md5('string'), but I'm concerned it's too weak for my needs. Does anyone have any recommendations? Thanks, Mark

Re: [SQL] PGSQL encryption functions

I'll check it out. Thanks, Josh On Tuesday 01 November 2005 02:49 pm, Josh Berkus wrote: Mark, I'm in need of a one-way pgsql script that will take a plain-text string and return an ecrypted string (preferably 32 character) . I've been using md5('string'), but I'm concerned it's too

Re: [SQL] PGSQL encryption functions

for an alternative. Any thoughts would be greatly appreciated. Thanks, Mark On Tuesday 01 November 2005 04:28 pm, Bruno Wolff III wrote: On Tue, Nov 01, 2005 at 14:38:05 -0500, Mark R. Dingee [EMAIL PROTECTED] wrote: Everyone, I'm in need of a one-way pgsql script that will take a plain-text

[SQL] Age in days

? Many thanks Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Can EXCEPT Be Used for To Solve This Problem?

you can also do select ... order by update_time desc offset 1 limit 1 On Thursday 25 August 2005 10:47 am, Vivek Khera wrote: On Aug 24, 2005, at 4:47 PM, Lane Van Ingen wrote: I want to select 2nd oldest transaction from foo (transaction 3). The solution below works, but I think there

Re: [SQL] Problem with self-made plpgsql-function / casting

fc_editlanguage(123, 'some name', 'some text value', 456) What you need to do is be more specific with the types being passed. Try this instead: select fc_editlanguage(123, 'some name'::varchar, 'some text value'::varchar, 456) Best of luck, Mark On Sat, 2005-08-20 at 16:54 +0200, Moritz Bayer

[SQL] Multi-column returns from pgsql

FROM mytable LOOP RETURN NEXT rec.txt1; RETURN NEXT rec.txt2; END LOOP; RETURN; END;' language 'plpgsql'; which leaves me parsing multiple records to achieve the desired end result. Anyone have any thoughts? Thanks, Mark ---(end of broadcast

[SQL] Dumping table definitions

to check if Pg already had tools to export the table structure (without the data). Does it? Also, Is there a way to export the structure of all tables at once instead of one table at a time? Mark begin:vcard fn:Mark Fenbers n:Fenbers;Mark org:DoC/NOAA/NWS/OHRFC adr:;;1901 South SR 134;Wilmington

[SQL] Error on dynamic code.

codeEND; (full code can be found at http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT ) Which is basically identical (except for the WHERE clause) to the query returned in the my error message!! Can anyone help please? Regs, Mark J

[SQL] [Fr*e-Content] Embrace the Four Fundamental Laws of Communication

permission from the author to publish this article in print or in a paid for arena. • Contact info: Mark Susnow, Life Coach mailto:[EMAIL PROTECTED] Summary: 732 Word Count: In this article, Mark Susnow, teaches us four fundamental truths of communication when embraced will transform

Re: [SQL] Sum() rows

[EMAIL PROTECTED] wrote: Hi. How can I sum a row and show the sum for each row??? For example, in a finances table that have the total movimentation(debit/credit) in the bank. i.e: CREATE TABLE TB1 (id integer primary key, value numeric); insert into tb1 values (1,20); insert into tb1 values

Re: [SQL] Sum() rows

Mark Dilger wrote: [EMAIL PROTECTED] wrote: Hi. How can I sum a row and show the sum for each row??? For example, in a finances table that have the total movimentation(debit/credit) in the bank. i.e: CREATE TABLE TB1 (id integer primary key, value numeric); insert into tb1 values (1,20

Re: [SQL] Aggregate Functions Template

Yes, your varlena links are what I was looking for as a source of help... Thanks! Mark Michael Fuhr wrote: On Thu, May 19, 2005 at 03:17:07PM -0400, Mark Fenbers wrote: I need to create an aggregate function to do some math not currently provided by the available tools. Can someone point

[SQL] Aggregate Functions Template

on how to do this properly from scratch. Thanks for the help! Mark begin:vcard fn:Mark Fenbers n:Fenbers;Mark org:DoC/NOAA/NWS/OHRFC adr:;;1901 South SR 134;Wilmington;OH;45177-9708;USA email;internet:[EMAIL PROTECTED] title:Sr. HAS Meteorologist tel;work:937-383-0430 x246 x-mozilla-html:TRUE url:http

[SQL] sub-selects

3 times in the query. I tried the following: SELECT a, (select b from c where d = e limit 1) AS g, npoints( g ) FROM f WHERE isValid( g ); But this gave an error regarding "column 'g' does not exist". How can I avoid making the same sub-select 3 times? Mark begin:vcard fn:Ma

[SQL] Replacing a table with constraints

ELETE FROM Counties;", nor will it let me "DROP TABLE Counties;" I'm perplexed. Can someone suggest how I can best get data from Counties to look just like newCounties? Mark begin:vcard fn:Mark Fenbers n:Fenbers;Mark org:DoC/NOAA/NWS/OHRFC adr:;;1901 South SR 134;Wilmingto

Re: [SQL] Replacing a table with constraints

True, but Counties has about 8 or 9 rules, view, or pk constraints attached to it. I don't want to break all these unless I knew of a way to save off the SQL for them beforehand so I can easily rebuild them... Mark Ing. Jhon Carrillo wrote: Use Drop table YOUR_TABLE cascade

[SQL] default value for select?

mn ..." returns zero rows? Mark begin:vcard fn:Mark Fenbers n:Fenbers;Mark org:DoC/NOAA/NWS/OHRFC adr:;;1901 South SR 134;Wilmington;OH;45177-9708;USA email;internet:[EMAIL PROTECTED] title:Sr. HAS Meteorologist tel;work:937-383-0430 x246 x-mozilla-html:TRUE url:http://weather.gov/ohrfc v

[SQL] Postgres 8 - Database access, new install.

rpms) postgresql-server-8.0.1-1PGDGpostgresql-8.0.1-1PGDG Does anyone have any idea ofhow to resolve this problem, I can connect using PGAdminIII but I really would like to be able to login in properly from Command-line also. Many Thx, Mark. ___ Disclaimer

[SQL] Updating column to link one table to another

yet. I need to know how I can UPDATE the FrequentTable rows, where their infrequentId is zero, to point at the last InfrequentTable entry whose timestamp ts is before its own timestamp ts. Can anyone help me with this? Thank you. - Mark Collette ---(end of broadcast

[SQL] Explicitly rolling back transaction from within a C-Language function

on this subject is also welcome. Thank you for any help, mark __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com ---(end of broadcast)--- TIP 9: the planner

Re: [SQL] Aggregate Function with Argument

(string_to_array('one,two,three',',')); -- Mark Gibson gibsonm |AT| cromwell |DOT| co |DOT| uk Web Developer Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] create function atof?

Hello, Is it possible to create a database function that mimics the C function atof? I'm guessing it should look something like this: create function atof(varchar) returns float as '??' language returns null on null input; Thanks, Mark

Re: [SQL] Elegant way to monitor for changes in a trigger and migrate

. It needs some cleaning up first, when I have time. -- Mark Gibson gibsonm |AT| cromwell |DOT| co |DOT| uk Web Developer Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] Date format problems

to my previous mail. Many Thanks in advance, Kind Regards, Mark. ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure

[SQL] Tip: a function for creating a remote view using dblink

* FROM local_view; The schema MUST be specified for the remote table name. Suggestions for improvement welcome. Any ideas? Is there any existing site (a wiki for example) for posting PostgreSQL specific tips? (Wasn't sure if pgsql-sql is the right place for this kind of thing) -- Mark Gibson

Re: [SQL] Date format problems

, Mark. Tom Lane [EMAIL PROTECTED] 02/16/04 03:34pm Mark Roberts [EMAIL PROTECTED] writes: Hi im using the function below to insert data into my db; im using now() to get the timestamptz, however when inserted in the db the format seems to vary, the majority of the time its in the required

[SQL] Implementation of a bag pattern using rules

, and results in much head scratching, but I still can't work out how to fix it. Any suggestions on improving the rules? Other than the problem mentioned, can anyone see a flaw in this method? Cheers -- Mark Gibson gibsonm |AT| cromwell |DOT| co |DOT| uk Web Developer Database Admin Cromwell Tools

Re: [SQL] Implementation of a bag pattern using rules

Robert Creager wrote: When grilled further on (Mon, 09 Feb 2004 12:42:10 +), Mark Gibson [EMAIL PROTECTED] confessed: CREATE RULE bag_abs AS ON INSERT TO bag_test WHERE EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) DO INSTEAD UPDATE bag_test SET qty = NEW.qty WHERE

[SQL] timestamptz - problems

'; * Kind Regards, Mark. ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure communication

Re: [SQL] Implementation of a bag pattern using rules

Richard Sydney-Smith wrote: Mark, love the idea, guess I should have read it somewhere but haven't. Obvious and beautiful. Please let me know if you or someone else solves the initial double value. I used to use functions for this kind of thing, and was thinking that what SQL really needed

Re: [SQL] Implementation of a bag pattern using rules

Tom Lane wrote: Mark Gibson [EMAIL PROTECTED] writes: CREATE RULE bag_rel AS ON INSERT TO bag_test WHERE EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) DO INSTEAD UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item; This can't work because an ON INSERT

  1   2   >