> All three of these implementation issues are, at least in theory,
> surmountable. For example, Sybase overcame problems (1) and (3) by creating
> an automated, system-controlled hash key based on the table's real key. This
> was a solution endorsed by E.F. Codd in the mid-90's when he came
On Fri, 2004-08-06 at 11:29, Gordon Ross wrote:
> Is it possible to make a column case insensitive, without having to pepper your
> SELECTs with lots of lower() function calls (and forgetting to do it at times !)
You could make yourself a set returning function to do this job -- but
that is proba
> Is this way of handling exceptions possible in postgres ?
> If so, what is the normal way to handle this exceptions, from a
> plpgsql/trigger(rule??) perspective ?
8.0 should allow you to do this. 7.4 you need to perform your own checks
and catch whether they succeed or fail.
Depending on the size of your structures, something like the below may
be significantly faster than the subselect alternative, and more
reliable than the ctid alternative.
CREATE TYPE result_info AS (a integer, b integer, c integer, d integer);
CREATE OR REPLACE FUNCTION parallelselect() RETURNS
> > faster than ones sent through the lists. It is also possible that the direct
> > replies might be handled differently by the recipient (e.g. a filter may put
> > them in different folders).
>
> This is very true. In fact, I get mildly annoyed when people *don't*
> include the direct reply to
> However, Bruce, this should be on the TODO list:
>
> * Allow foreign key to reference a superset of the columns
>covered by a unique constraint on the referenced table.
It would probably be more beneficial to be able to create a unique
constraint without requiring the fields be ind
On Wed, 2004-08-18 at 12:27, Jan Wieck wrote:
> On 8/18/2004 12:18 PM, Tom Lane wrote:
>
> > Richard Huxton <[EMAIL PROTECTED]> writes:
> >> * Allow multiple unique constraints to share an index where one is a
> >> superset of the others' columns.
> >
> >> That way you can mark it unique without
On Mon, 2004-09-20 at 12:19, T E Schmitz wrote:
> I figured it eventually. (The only thing I don't know is where to put
> the ORDER BY.)
Try this:
SELECT brand_name, model_name
FROM (SELECT ... INTERSECT SELECT ...) AS t
ORDER BY ...
---
On Wed, 2004-10-20 at 08:50, Philippe Lang wrote:
> Hello,
>
> Is it possible to reuse a previously calculated column in a following
> column, like:
SELECT col1
, col2
, col2 * 0.75 AS col3
FROM (SELECT foo.val1 AS col1
, long_calculation(foo.val1) AS col2
FROM
On Tue, 2004-11-16 at 11:29 +, Gary Stainburn wrote:
>
> How would I go about creating a view to show a) the number of photos
> in
> a gallery and b) the timestamp of the most recent addition for a
> gallery, so that it interrogates all sub-galleries?
There isn't a very simple answer to tha
On Mon, 2004-11-22 at 19:11 +0530, sreejith s wrote:
> Hello friends I have a doubt,
> Is it possible to insert images into a postgres db. What is its
> datatype. Is its possible to insert jpeg images. or v have to store
> the path into the db. Pls reply. its urgent.
Use bytea for a datatype. Post
On Mon, 2004-11-22 at 15:09 +0100, Jerome Alet wrote:
> Hi,
>
> On Mon, Nov 22, 2004 at 08:54:20AM -0500, Rod Taylor wrote:
> > On Mon, 2004-11-22 at 19:11 +0530, sreejith s wrote:
> > > Hello friends I have a doubt,
> > > Is it possible to insert image
On Sat, 2004-12-11 at 07:47 -0800, Stephan Szabo wrote:
> On Sat, 11 Dec 2004, Ian Barwick wrote:
>
> > (Oddly enough, putting the NULL in the CHECK constraint seems
> > to make the constraint worthless:
> > test=> create table consttest (field varchar(2) check (field in
> > (null, 'a','b','c')))
On Mon, 2005-03-14 at 10:02 +0100, PFC wrote:
> If you want to add a SERIAL field to an existing table, create a
> sequence
> and then create an integer field with default nextval(seq) and postgres
> will fill it automatically. The order in which it will fill it is not
> guaranteed tho
> > select * from mytable where somecondition AND uniquecol>?
> > ORDER by uniquecol limit 50 OFFSET 50;
>
> > where the ? is placeholder for last value returned by last query.
>
> Uh, you don't want the OFFSET there do you? But otherwise, yeah,
> this is a popular solutio
> To alter table column from varchar(32) to date. "Alter table" command
> does not seem to work:
>
> alter table test alter column col type date ;
> ERROR: column "col1" cannot be cast to type "date"
Alter table will not automatically throw away information. That is, in
cases where it believes
On Tue, 2005-04-12 at 23:08 -0700, Bill Lawrence wrote:
> Thanks,
>
> Unfortunately, I think that solution requires the distance calculation to be
> executed twice for each record in the table. There are ~70K records in the
> table. Is the postgres query optimizer smart enough to only perform the
On Wed, 2005-04-13 at 18:28 +0100, Matt Fulford wrote:
> I'm trying to write a query to return the number of different customers
> called on a single day. The database has a table called 'user', a table
> called 'caller_session' which references a 'user' record, and a table called
> 'call' whic
> Is there any elegent query you folks can think of that combines the
> two so I can one query that has alpha sorting on alpha categories and
> numeric sorting on numeric values that are in the same column??
select * from r order by (case when col ~ '^[0-9]+$' THEN lpad(col, 10,
'0') else col end)
On Thu, 2005-06-23 at 22:03 +0200, Markus Bertheau wrote:
> Hi,
>
> it seems to me that the following should work but it fails:
>
> CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE;
> CREATE TABLE link (ov TIMESTAMP);
> CREATE OR REPLACE VIEW co AS SELECT ov FROM link;
The da
On Thu, 2005-06-23 at 22:27 +0200, Markus Bertheau wrote:
> Dnia 23-06-2005, czw o godzinie 16:19 -0400, Rod Taylor napisał(a):
>
> > The data types are different, as one has the timestamp to (6) decimal
> > places after seconds.
>
> That's strange. I explicitly spec
On Wed, 2005-06-29 at 10:21 -0300, Martín Marqués wrote:
> El Mié 29 Jun 2005 09:40, KÖPFERL Robert escribió:
> >
> > |
> > |I personally think that the ENUM data type is for databases
> > |that are not well
> > |designed. So, if you see the need for ENUM, that means you
> > |need to re-think
> So, leave COUNT(*) alone. But it would be very handy to have a
> way to get an approximate table size that is more accurate than is
> provided by a pg_class.reltuples that is only updated on vacuums.
Create 2 sequences, one for counting tuple additions and one for
counting tuple deletions.
Whe
On Fri, 2005-07-08 at 17:34 +0200, Dawid Kuroczko wrote:
> On 7/8/05, Rod Taylor <[EMAIL PROTECTED]> wrote:
> > Create 2 sequences, one for counting tuple additions and one for
> > counting tuple deletions.
> >
> > When you INSERT a tuple, bump the
On Wed, 2005-08-24 at 15:46 +0200, Josep Sanmartí wrote:
> Hello,
> I have a 'big' problem:
> I have the following table users(name, start_time, end_time), a new row
> is set whenever a user logs into a server. I want to know how many
> users have logged in EVERYDAY between 2 different dates. Th
On Thu, 2005-11-10 at 09:03 -0600, Judith Altamirano Figueroa wrote:
> Hello everybody!!!, I'd like to know if there another way to get the
> time from a timestamp type, because in earliest versions I just get
> the time like time(fecha) and now this returns an error, thanks in
> advanced!!!
r
On Thu, 2005-11-10 at 11:21 -0700, Michael Fuhr wrote:
> On Thu, Nov 10, 2005 at 01:11:27PM -0500, Rod Taylor wrote:
> > rbt=# select cast(now() - date_trunc('day', now()) as time);
> > time
> > -
> > 13:10:42.495579
> > (1 ro
On Tue, 2005-11-22 at 16:24 +0100, Joost Kraaijeveld wrote:
> Hi,
>
> Is there a way to create a foreign key to 2 tables: e.g. a bankaccount
> table that has a column "owner", that must point to a record in either
> the customer or the supplier table?
No. What you need is an owner table that cust
On Mon, 2005-11-28 at 14:22 -0600, Announce wrote:
> Lets say I have the following tables.
>
> CREATE TABLE animals(id primary key, name varchar, type varchar);
> CREATE TABLE dogs (breed varchar)INHERITS (animals);
> CREATE TABLE birds (bool hasFeathers) INHERITS (animals);
r=# alter
> Sequential despite the indices? Or is this because the tables of my test
> DB are virtually empty?
This is it. PostgreSQL changes strategies with data load. Performance
testing must be done on an approximation of the real data (both values
and size).
--
---(end of br
> I'm experimenting with a set of triggers to automagically maintain
> ltrees-organized tables. I almost have it working, except for a pesky
> problem with re-ordering groups.
> Currently I'm doing this by only cascade-updating the row adjacent to
the
> one I'm moving. However, this is resul
On Mon, 2006-03-13 at 12:51 +0300, Eugene E. wrote:
> Hi all
> the serious problem with permissions is encountered
>
> NOTE: the following example is really useful but there is no room to
> describe it's use.
>
>
> db=# CREATE USER u;
> db=# CREATE TABLE t (i int, a text);
> db=# REVOKE all ON
On Mon, 2006-03-13 at 10:46 -0400, Alvaro Herrera wrote:
> Rod Taylor wrote:
>
> > By allowing the user a where clause you grant them select privileges.
> > You will find that delete works the same way.
> >
> > This is one of those times when per column permiss
On Mon, 2006-03-20 at 17:53 +0300, Eugene E. wrote:
> I wrote:
>
> >> the problem is: you'll get this four byte sequence '\000' _instead_
> >> of NUL-byte anyway.
>
> You wrote:
>
> > Your client library should take care of escaping and de-escaping.
>
> We both agree as you see.
>
> Then i
On Fri, 2006-03-24 at 15:19 -0500, Amos Hayes wrote:
> Hello. I've recently begun to use PostgreSQL in earnest (working with
> data as opposed to just having clever applications tuck it away in
> there) and have hit a wall with something.
>
> I'm trying to build a query that among other things
On Fri, 2006-03-24 at 16:20 -0500, Tom Lane wrote:
> Rod Taylor <[EMAIL PROTECTED]> writes:
> > The reason for the subselect is to prevent multiple calculations of
> > individual column aggregates. I believe it *may* be calculated multiple
> > times otherwise t
> So I'm designing a table and I'm looking for an appropriate key. The
> natural key is a string from a few characters up to a maximum of
> perhaps 100. Joe gets quite fierce about avoiding the use of a serial
> id column as a key. The string is unique in the table and fits the
> criteria for a key
On Thu, 2006-06-01 at 14:13 -0400, Yasir Malik wrote:
> > What I would like to do is simply get the last date_sent and it's
> > status for every broadcast. I can't do a GROUP BY because I can't put
> > an aggregate on the status column.
> >
> > SELECT MAX(date_sent), status
> > FROM broadcast_hist
On Mon, 2006-06-12 at 21:58 -0500, Aaron Bono wrote:
> I agree with Tom. Personally I cannot think of a time I would use an
> array column over a child table. Maybe someone can enlighten me on
> when an array column would be a good choice.
Arrays are a good choice when the data comes naturally s
On Tue, 2006-06-13 at 15:30 -0500, Aaron Bono wrote:
> In another post on a different topic, Rod Taylor said the following:
>
> "A\tcat in the\nhat" might be stored as ARRAY['A', 'cat', 'in', 'the',
> 'hat'].
>
On Thu, 2006-06-15 at 13:59 -0700, [EMAIL PROTECTED] wrote:
> i frequently join certain tables together in various
> tables.
> is it a good practice to leave this included in the
> queries, as is, or should i factor it out somehow? if
> i should factor it, how do i do so?
Future proofing selects
On Fri, 2006-06-16 at 08:27 -0400, John DeSoi wrote:
> On Jun 15, 2006, at 11:49 AM, chester c young wrote:
>
> > in PHP for example, where there are multiple sessions and which you
> > get is random:
> >
> > how do you know if the session you're in has prepared a particular
> > statement?
> >
On Fri, 2006-06-30 at 18:41 -0700, Richard Broersma Jr wrote:
> > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS)
> > > FROM BADGES
> > > WHERE STATUS = 'A'
> > > GROUP BY EMPNO))
> >
> > From t
On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote:
> "Markus Bertheau" <[EMAIL PROTECTED]> writes:
> > I basically want to change a boolean column to char. The boolean
> > column has a default of true. The char column should have 'f' for
> > false and 't' for true. I think that an SQL statement lik
101 - 144 of 144 matches
Mail list logo
