Hello,
I'm working on designing a soft-delete scheme for our key entity-- there
are 17 other tables that reference our key table via RI. Let's call the
table "foo".
I understand there are a couple common design patterns for soft-deletes:
1. Use a trigger to move the rows to a "tombstone table".
On 02/28/2013 02:08 PM, Tom Lane wrote:
> Mark Stosberg 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_
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 su
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
the application, I had the idea to use a view and permissions for this
purpose.
I thought I coul
We have an application that manages RSS feeds for searches. Our first
attempt at modeling this was to create a table for the searches with the
standard integer primary key generated from a sequence, and then have
one column per search key. Each row should represent a unique search.
When a new se
primary key, and a zipcode as an additional column. Then, by joining on the
zipcodes table, 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
--
. . . . . . . . . . . .
> > > 3. Deal with wraparound by ensuring that the applications behave sanely
> >
> > Wrap-around?
> >
> > Exceeding the max size of "int" looks more like a brick wall than
> > wrap-around to me:
> >
> > insert into t values (2147483648);
> > ERROR: integer out of range
>
> Hmm, you can a
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 in
manent storage.
Both options have some appeal for me. What have others done?
Mark
--
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Mark StosbergPrincipal Developer
[EMAIL PROTECTED] Summersault, LLC
765-939-9301 ext 202 database driven websit
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 u
Hello,
I could use some help figuring out how to speed up a query. Below is the
SQL and query plan for a common SELECT done by the open source
eGroupware project.
Now that there are about 16,000 rows in egw_infolog and 32,000 in
egw_infolog_extra, the below query takes about 6 minutes to finish
On Wed, 2008-06-25 at 14:20 +, Greg Sabino Mullane wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
>
>
> > Where the totals are "counts" of the the rows in the hits and views
> > tables. There should be no rows for partners without hits or views.
>
> How about something like th
> 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 g
Hello,
I could use some help figuring out a complex "GROUP BY".
As a setup, let's say I have two tables which stores a single "hit" or
"view" in a row
hits
hit_id
partner_id
views
view_id
partner_id
There is of course a "partners" table with a "partner_id" column.
My target result is
Hello,
This is a re-phrasing of question I asked recently, inquiring about a
an approach I'm considering.
My query is:
> INSERT into item_hit_log (item_id, hit_date, hit_count)
>SELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0
> FROM items where item_state = 'available';
Hello! I got an unexpected SQL failure last night, and want to see how
to prevent it in the future. This is with 8.1.3.
I have a couple of insert statements that run in a transaction block,
initializing rows that will be used the next day:
INSERT into item_hit_log
SELECT item_id, CURRENT_DAT
On 2003-10-22, Timo <[EMAIL PROTECTED]> wrote:
>
> You can't have any recursion in an pure sql-query, can you?
It depends on how you think of recursion, I'd say. You join on the same
table a number of times, by giving it a different alias each time. You
have to manually specify (or generate with
In article <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> wrote:
>
> can anyone recommend a good reference source for doing recursive sql on
> postgresql? i want to do something similar to a BOM expansion. (i.e. i need
> to traverse a self-referencing table that stores a tree structure and answer
> a qu
On Mon, 19 Aug 2002, Stephan Szabo wrote:
> On Mon, 19 Aug 2002, Jiaqing wrote:
>
> > Hello,
> > I'm still new here and new to PostgreSQL, I'd like to know that after I
> > have created two databases on my site, such as one is called backend, and
> > another one is called admin, how do I refer(qu
On Sun, 25 Aug 2002, Andreas Tille wrote:
> On Sat, 24 Aug 2002, Mark Stosberg wrote:
>
> > On Thu, 22 Aug 2002, Andreas Tille wrote:
> > > Hello,
> > >
> > > I want to solve the following problem:
> > >
> > > CREATE TABLE
On 21 Aug 2002, Robert Treat wrote:
> On Tue, 2002-08-20 at 22:15, Mark Stosberg wrote:
> >
> > Hello Darrin,
> >
>
> >
> > I've been happy with this solution. I think the Perl turned out to be
> > fairly easy to understand and maintain, the SQL
Hello Darrin,
I recently implemented what I would consider the "hard part" of a
solution to this using Perl and Postgres. My solution handles multi-day
events and recurring events, including events that are both multi-day
and recurring. Here's an overview of how I did it:
A table called "calend
On Mon, 19 Aug 2002, Nick Fankhauser wrote:
>
> This may not be the best way, but I couldn't resist taking a shot at it...
Thanks for the response Nick. If only I knew I was going to get a
response from a block away, I would have just come down to say hi. :)
I had an "a ha" moment about this ov
Hello,
I'm using Postgres 7.1 and have been enjoying the upgraded "sub-select"
support in this release versus older versions.
At the moment, I'm stuck with a SQL issue that I haven't run into
before.
I need to select the data for all the "parks" that match some search
criteria. The parks are s
--CELKO-- wrote:
>
> >> The table causing my headache:
>
> CREATE TABLE app_components
> (idNUMERIC(7) NOT NULL PRIMARY KEY,
> name VARCHAR(100) NOT NULL,
> description VARCHAR(500) NULL,
> parent_id NUMERIC(7) NULL
>REFERENCES app_components(id)
>
David BOURIAUD wrote:
>
> Hi the list !
> As far as I know, column names for a table can't contain any space,
> tabs, and other sort of "exotic" characters.
In fact, I know you can have at least spaces in your column names, like this:
mark=> create table t ("column one" text);
CREATE
Just put
Hello,
I'm a long time Postgres user who uses MySQL when I have to. I recently
ran into an issue with MySQL where this construct didn't do what I expect:
WHERE date_column = NULL
I expected it to work like "date_column IS NULL" like it does it
Postgres 7.0.2, but instead it returned an empty r
Hello,
I'd like to create some tables that would visible across databases,
much like the postgres system tables. These would be for "static" data,
such as state and country codes, and geo-spatial data. I couldn't find
this mentioned in the docs, but unless this feature of the system tables
is
Lonnie Cumberland wrote:
>
> Hello All,
>
> We are developing an application that will allow our websites to talk to our
> database.
>
> In the interest of security, I am wondering if it is possible to turn off some
> of the functions in the SQL command list such that a user can only communicat
Now that I've figured out that numeric is good for storing money, and
that I can format with like this:
to_char(price, '9,999,999.99') as price
Then I discovered that sometimes this returns leading spaces I don't
want. I can get rid of them like this:
trim(to_char(price, '9,999,999.99')) as pr
Peter Eisentraut wrote:
>
> Mark Stosberg writes:
>
> > I'm curious to know if there is a function available in Postgres 7.0.3
> > (or 7.1) that will format a float style number as "money". I understand
> > that the money type is going away in the
Hello,
I'm curious to know if there is a function available in Postgres 7.0.3
(or 7.1) that will format a float style number as "money". I understand
that the money type is going away in the future, so using a float type
that is sometimes formatted like money seems like a good alternative. So
Here's a situation I've run into more than once with SQL:
I want to select all the rows in a table that match a criteria, where one
of the criteria is possibly having a related entry in a second table. For
my example, lets say I have table named 'messages' and another named
'message_attachment
33 matches
Mail list logo
