[SQL] Column limits in table/ views
I have 560 columns of NUMERIC(10,14). To not run up against max column restraints, I split the information into two tables. Does the column limit on tables imply the same limit for views or selects or could I potentially select a row across both tables and make a view that hides the split? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] EXTERN JOIN with WHEN query
Hi all; This is a query that I guess is not very dificult, but I'm a newbie; I've got a lot of tables, each of them with two columns: SELECT * FROM precal; -> (date) (real) fecha| precipitacion +--- 1996-01-01 | 0.6 1996-02-01 | 0.7 ... But in this table there are some inexistents records (some missing days) And I would like to create lists with a full list of dates and corresponding precipitation data, with gaps when the row didn't exist. So; I've created a table with a complete series of dates from 1950 up to date, and made the query: SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON (fechas.fecha = precal41.fecha); This is perfect. But to make it better, would like to include just the dates from the first one in the precal table. So, I've tried: SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON (fechas.fecha = precal41.fecha) WHEN fechas.fecha >= min(precal41.fecha); With the answer: ERROR: parser: parse error at or near "WHEN" Could you help me with this query? Thanks and regards Javier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] simulating partial fkeys.. [ATTN Developers please]
On Fri, 6 Jun 2003, Richard Huxton wrote: > On Thursday 05 Jun 2003 3:55 pm, [EMAIL PROTECTED] wrote: > > hi , > > > > Is there any way to enforce fkeys only on subset of > > the table something on the lines of unique partial indexes > > I'm afraid not (AFAIK). What might be a solution in your case is to define > another unique index. For example, you might have (row_id) as your primary > key with another unique index on (row_id,row_type). You could then have a > foreign-key that referenced those two columns. > > > or any work around ? (on update or insert trigger is the only thing i can > > think of) > > Yep, you'll need to build some triggers of your own. The techdocs guides > section is down at the moment, but see my brief example in the attachment. As a side note, I think the attached doesn't entirely work yet as something similar to a foreign key. You almost certainly need to deal with deletes as well as inserts and updates and without some kind of locking I think you're going to fall prey to concurrent transactions violating the constraint (what happens if someone say inserts a server_product at the same time someone else updates server). Neither of those should be hard to add to it. > It would be really useful to be able to have something like: > > CREATE contract ( > con_id SERIAL, > con_type varchar(4), > ... > PRIMARY KEY (con_id) > ); > > CREATE tel_con_section ( > tcs_id SERIAL, > tcs_con_ref int4, > ... > CONSTRAINT contract_fk FOREIGN KEY (tcs_con_ref,'TEL') REFERENCES contract > (con_id,con_type) > ); > > or even: > FOREIGN KEY (tcs_con_ref) REFERENCES contract (con_id) WHERE > contract.con_type='TEL' > > Is there a developer around who could comment how plausible this would be? The former syntax is probably reasonable, the latter seems more problematic. However, IMHO the right way to do this is for someone who has the time and inclination ( not me ;) ) to look at supporting subselects in CHECK constraints. This allows you to define whatever wacky constraint logic you want and it should be done properly (including the concurrency issues and such). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] "Join" on delimeter aggregate query
Hello everyone, I've set up PostgreSQL as the authentication / configuration database for my mail server (Postfix + Courier-IMAP), and though it works beautifully, I need some help on my aliases query. You see, define aliases in a database table as rows in a column in the form of "Source" and "Target". The problem is that one source address can be delivered to multiple targets (e.g. internal mailing list, or a temporary forward to another address), but postfix only processes the first record returned from an SQL query. Postfix can deliver to multiple targets, if you separate the targets with comas, like so: Source Target [EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED], What I would like to do, is something like the following (I know I'd need to group the query, but you get the idea): Select DISTINCT(Source), JOIN(Target, ',') FROM Aliases Is there any way this can be done with Postfix? -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Elegant SQL solution:
There are so many (bad) ways to skin this cat... I'm looking for a more elegant
solution.
If I
SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP
BY month;
It might only return
month | rows
---+--
1 | 234
3 | 998
4 | 403
5 | 252
10| 643
12| 933
I would like:
month | rows
---+--
1 | 234
2 | 0
3 | 998
4 | 403
5 | 252
6 | 0
7 | 0
8 | 0
9 | 0
10| 643
11| 0
12| 933
I could create a one-column table with values 1 - 12 in it, and select from
that table with a where clause matching "month". I could also create a view
"SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST be
a more elegant way to do this.
Any thoughts?
__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] (long) What's the problem?
Anybody have any ideas about a problem with this query?
urldb2=> EXPLAIN
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;
QUERY PLAN
---
Sort (cost=23.50..23.50 rows=3 width=48)
Sort Key: source
-> Seq Scan on indexscan (cost=0.00..23.47 rows=3 width=48)
Filter: (lower("substring"(urlhost(source), (rposition('www.'::text,
(urlhost(source))::character varying) + 2))) ~ '^q.*'::text)
(4 rows)
OK, cost=23.50..23.50, should be a quickie. I'll EXPLAIN ANALYZE just to be safe:
urldb2=> EXPLAIN ANALYZE
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Since EXPLAIN ANALYZE runs the query and then analyzes the results I haven't included
that in this email because the error is the same.
urlhost() is an immutable custom Pl/Perl function.
rposition() is a volatile custom C function.
indexscan has 614 tuples and only takes up 7 pages.
The load on this machine is zero when I attempt this, and no other processes are
trying to use massive amounts of resources. This is a P3 550 with 512MB of RAM.
I can provide more information if needed.
Anybody have ideas about the problem?
--
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] simulating partial fkeys..
Mallah, > Is there any way to enforce fkeys only on subset of > the table something on the lines of unique partial indexes > > or any work around ? (on update or insert trigger is the only thing i can > think of) so, why don't you ask me these questions? Nu? If you mean what I believe that you mean ... let me give you an example from my own systems: table cases field case_id field case_name field case_status table status field status field relation field description In this schema, the table "status" holds status values for all relations (tables), not just for "cases". I would like to create an FK from case_status to status.status *only for those values of status where relation = 'cases'. Can't be done. (at least, not with an FK declaration -- see below for a workaround) This is a classic example of one of the failures of the SQL Standard. The above relationship is easily definable in Relational Calculus, but SQL will not support it. And given PostgreSQL's commitment to that standard, we cannot really extend Postgres's FK implementation to cover that situation. "distributed keys" is another really good example of a useful Relational structure that SQL will not support. The only way to enforce this in the database would be to create triggers (not Rules, for performance reasons, since Rules can't use indexes) FOR INSERT, UPDATE ON cases, and FOR UPDATE, DELETE on status. The triggers on status would be annoyingly long. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] To ListAdms: Is pgsql-sql operating?
I see your post. But no others since Monday. Thanks Chad - Original Message - From: "Achilleus Mantzios" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, June 05, 2003 5:39 AM Subject: [SQL] To ListAdms: Is pgsql-sql operating? > > Is there any problem with [EMAIL PROTECTED] list? > > -- > == > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > Nikis 4, Glyfada > Athens 16610 > Greece > tel:+30-210-8981112 > fax:+30-210-8981877 > email: achill at matrix dot gatewaynet dot com > mantzios at softlab dot ece dot ntua dot gr > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] (long) What's the problem?
Anybody have any ideas about a problem with this query?
urldb2=> EXPLAIN
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;
QUERY PLAN
---
Sort (cost=23.50..23.50 rows=3 width=48)
Sort Key: source
-> Seq Scan on indexscan (cost=0.00..23.47 rows=3 width=48)
Filter: (lower("substring"(urlhost(source), (rposition('www.'::text,
(urlhost(source))::character varying) + 2))) ~ '^q.*'::text)
(4 rows)
OK, cost=23.50..23.50, should be a quickie. I'll EXPLAIN ANALYZE just to be safe:
urldb2=> EXPLAIN ANALYZE
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Since EXPLAIN ANALYZE runs the query and then analyzes the results I haven't included
that in this email because the error is the same.
urlhost() is an immutable custom Pl/Perl function.
rposition() is a volatile custom C function.
indexscan has 614 tuples and only takes up 7 pages.
The load on this machine is zero when I attempt this, and no other processes are
trying to use massive amounts of resources. This is a P3 550 with 512MB of RAM.
I can provide more information if needed.
Anybody have ideas about the problem?
--
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Domains and Joins
-- standard setup: create table t1( c1 int primary key, data text ); create domain dom_c1 int references t1 on delete cascade; create table t2( c2 int primary key, c1 dom_c1, moredata text ); -- will not work with "using" create view v1 as select t1.*, t2.moredata from t1 join t2 using( c1 ); -- will work with "on" create view v1 as select t1.*, t2.moredata from t1 join t2 on t1.c1 = t2.c1; is this right? __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
