> SELECT
> project_id,
> marketing_name,
> COUNT(lots.lot_id) AS def_count,
> COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_less_30,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Hi:
Has anyone encountered this before?
SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0));
returns the following error message:
Cannot cast type '"char"' to '"numeric"'
But the following sql statements returns NULL:
select NULL:
select NULL * NULL;
select cast ( NULL as NUMERIC(2,0)
Regarding to Terry's request on multiple aggregates and
Shahbaz's request for generating a cross tab ( pivot table )
in September, I've found an excellent example on a german inet page
http://www.itrain.de/
I've translated it and think it's useful for many who subscribed
(I hope so, maybe it was t
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Thu, 7 Nov 2002, Ludwig Lim wrote:
>> SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0));
>> Cannot cast type '"char"' to '"numeric"'
> It seems to me that it's trying to decide on a type
> for the expression NULL * NULL. It's a NULL, but a
> NULL of what ty
Actually, the ORDER BY *must* be replaced by GROUP BY since it is an
aggregate query.
I have implemented it, and the results are startling, I get the same value
repeated for all projects
i.e.:
AS1 AS1-AJAX/SALEM SIDE 3 0 6 7 30 0 216 240
AU3 AU3-RIVERIDGE/AURORA 3 0 6
I didn't realize there was a contrib/lo, I only created the lo type as
described in the psqlodbc faq.
I have now installed the contrib/lo and everything is working fine.
I am using PG version 7.2
thanks.
From: Tom Lane <[EMAIL PROTECTED]>
To: "Nekta Katz" <[EMAIL PROTECTED]>
CC: [EMAIL PROTECT
Hi,
I have the following table
create table scan_docs (
docid serial,
shipno numeric(10),
scanlo,
type text
);
when I try to create the following rule
create rule "delete_scan_docs_lo" as
on delete to "scan_docs"
do select lo_unlink (old.scan);
I get the error message
"psql:
On Thursday 07 Nov 2002 11:47 am, Christoph Haller wrote:
> Regarding to Terry's request on multiple aggregates and
> Shahbaz's request for generating a cross tab ( pivot table )
> in September, I've found an excellent example on a german inet page
> http://www.itrain.de/
> I've translated it and t
Hi,
Could you tell me how to get view field's source table and field name?
another word, how could I know the view field come from?
Regards,
Ho
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
No offence taken, however it is incorrect, my SQL is pretty good. I
received no other responses... And I later realized the solution to my
question:
(EXPERTS READ ON: If anyone can show me how to use a group by or otherwise
optimize I would be grateful)
This subquery:
SELECT pr
Hi,
I want to create a table which has 2 columns, and both columns have to
be primary key (or: together they are the primary key). How can I do
this using SQL? Using pgAdminII for Postgres7.2.2 on RH8.
Thanks
Huub
---(end of broadcast)---
TIP 4
A table can only have ONE primary key. It can have additional indexes with
a UNIQUE restriction, thereby forcing the second field to be as good as a
primary key.
That is probably what you are looking for, however, you can also in PG Admin
II select multiple fields as being the member of the prima
Hi,
For example you can do something like that:
CREATE TABLE "try" (
"field1"TEXT NOT NULL,
"field2"INT4 NOT NULL,
"field3"TEXT, PRIMARY KEY (field1, field2));
**
[EMAIL PROTECTED]
Laboratoire de prehistoire d
> Could you tell me how to get view field's source table and field name?
> another word, how could I know the view field come from?
Within psql, use
\d
to learn about the view's column names and types and the view
definition.
If you were thinking about querying system tables to get this
informa
On Wed, Nov 06, 2002 at 15:18:38 +0100,
Huub <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I want to create a table which has 2 columns, and both columns have to
> be primary key (or: together they are the primary key). How can I do
> this using SQL? Using pgAdminII for Postgres7.2.2 on RH8.
You can u
How do you select these sub Primary Keys?
I tried using Shift-Select, Ctrl_Selec, but doesn't work.
Thanks
Sangeetha
-Original Message-
From: [EMAIL PROTECTED]
[mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of
[EMAIL PROTECTED]
Sent: Thursday, November 07, 2002 8:54 AM
To: 'Huub'; [EMAI
On Wed, 6 Nov 2002, Huub wrote:
> Hi,
>
> I want to create a table which has 2 columns, and both columns have to
> be primary key (or: together they are the primary key). How can I do
> this using SQL? Using pgAdminII for Postgres7.2.2 on RH8.
I dont know the way in pgAdminII, but from psql simpl
[EMAIL PROTECTED] wrote:
A table can only have ONE primary key. It can have additional indexes
with
a UNIQUE restriction, thereby forcing the second field to be as good as a
primary key.
That is probably what you are looking for, however, you can also in PG
Admin
II select multiple fields as
On Thu, 7 Nov 2002, Prime Ho wrote:
> Hi,
>
> Could you tell me how to get view field's source table and field name?
> another word, how could I know the view field come from?
SELECT definition from pg_views where viewname='';
>
> Regards,
> Ho
>
>
>
> ---(end of broadcas
"Nekta Katz" <[EMAIL PROTECTED]> writes:
> I have the following table
> create table scan_docs (
> docid serial,
> shipno numeric(10),
> scanlo,
> type text
> );
> when I try to create the following rule
> create rule "delete_scan_docs_lo" as
> on delete to "scan_docs"
> do
Consider the following PLpgSQL code fragment
FOR this_record IN
EXECUTE ''SELECT ''
|| quote_ident($1)
|| ''FROM ''
|| quote_ident($2)
LOOP
list := list || '', '' || this_record.$1 ;
END LOOP;
As expected, accessing a field via this_record.$1
does not work.
Can it be d
Uz.ytkownik Huub napisa?:
> Hi,
>
> I want to create a table which has 2 columns, and both columns have to
> be primary key (or: together they are the primary key). How can I do
> this using SQL? Using pgAdminII for Postgres7.2.2 on RH8.
Dependent on what you need:
1) you can create primary key bas
On Thu, Nov 07, 2002 at 05:12:20PM +0800, Prime Ho wrote:
> Hi,
>
> Could you tell me how to get view field's source table and field name?
> another word, how could I know the view field come from?
>
\d view_name should give you the view definition in pqsl.
--
Ken Kennedy | http://www.ke
On Thursday 07 Nov 2002 2:35 pm, Christoph Haller wrote:
> Consider the following PLpgSQL code fragment
>
> FOR this_record IN
> EXECUTE ''SELECT ''
>
> || quote_ident($1)
> || ''FROM ''
> || quote_ident($2)
>
> LOOP
> list := list || '', '' || this_record.$1 ;
> END LOO
I would personnaly like this feature (assigning a composite from another
similar composite) to be added to PLPGSQL. Another nice feature would be
to able to insert a composite into a table without have to name all
atributes.
Just my $.02
"Rison, Stuart" wrote:
>
> >> 2) I am looking for an eleg
On Thu, 7 Nov 2002, Ludwig Lim wrote:
> Hi:
>
> Has anyone encountered this before?
> SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0));
>
>
> returns the following error message:
> Cannot cast type '"char"' to '"numeric"'
It seems to me that it's trying to decide on a type
for the expression NU
Josh has submitted an appropriate report to pgsql-bugs... so perhaps it will
happen!
Stuart.
> -Original Message-
> From: Jean-Luc Lachance [mailto:jllachan@;nsd.ca]
> Sent: 07 November 2002 16:29
> To: Rison, Stuart
> Cc: ''[EMAIL PROTECTED]' '; 'Josh Berkus '
> Subject: Re: [SQL] Copyin
Richard Huxton wrote:
On Thursday 07 Nov 2002 11:47 am, Christoph Haller wrote:
Regarding to Terry's request on multiple aggregates and
Shahbaz's request for generating a cross tab ( pivot table )
in September, I've found an excellent example on a german inet page
http://www.itrain.de/
I've tran
Christoph Haller <[EMAIL PROTECTED]> writes:
> Consider the following PLpgSQL code fragment
> FOR this_record IN
> EXECUTE ''SELECT ''
> || quote_ident($1)
> || ''FROM ''
> || quote_ident($2)
> LOOP
> list := list || '', '' || this_record.$1 ;
> END LOOP;
> As expected,
Here's a puzzler:
Given a query that returns rows ranked by some criteria, how can I write
another query around it that will give me the (say) first quartile (top
25%)? Another way of putting it is: if I have rows that look like this:
aaa | 1251
aba | 1197
cax | 1042
... | ...
axq | 23
(142 ro
On Thu, 7 Nov 2002, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Thu, 7 Nov 2002, Ludwig Lim wrote:
> >> SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0));
> >> Cannot cast type '"char"' to '"numeric"'
>
> > It seems to me that it's trying to decide on a type
> > for the expression
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Thu, 7 Nov 2002, Tom Lane wrote:
>> I am kind of inclined to remove the arithmetic operators on "char"
>> (+,-,*,/) in 7.4 --- they don't seem to have any real-world uses,
>> and as this example illustrates, they are perfectly positioned to
>> capture
In a PLPGPSQL script, once you know count(*) try
execute ''select * from table limit '' || int4( theCount / 4);
If you want numbering, create a sequence and add nextval() to the query.
JLL
Jeff Boes wrote:
>
> Here's a puzzler:
>
> Given a query that returns rows ranked by some criteria, h
Chris,
> FOR this_record IN
> EXECUTE ''SELECT ''
> || quote_ident($1)
> || ''FROM ''
> || quote_ident($2)
> LOOP
> list := list || '', '' || this_record.$1 ;
> END LOOP;
>
> As expected, accessing a field via this_record.$1
> does not work.
> Can it be done otherwise?
Hello,
I have several tables and one table is a reference table, all other tables
are master tablls of this reference table. One record in these master
tables have several records in the reference table.
I want to delete cascade when delete one record one reocrd in mater tables
and delete th
Dear All,
I want to dreate a delete cascade in children tables. The primary key of
parent table is oid, so when I create parent table, there is no apparetly
key word "primary key". The problem jumps out.
When I create child table using columnn constraint on delete, the SQL says:
"PRIMARY KEY
On Thu, 7 Nov 2002, Zhidian Du wrote:
> Dear All,
>
> I want to dreate a delete cascade in children tables. The primary key of
> parent table is oid, so when I create parent table, there is no apparetly
> key word "primary key". The problem jumps out.
>
> When I create child table using columnn
Hi,
I wonder if it is possible to remove a field of a table ?
I haven't found anything about this into the reference manual.
Can I do something like that ?:
ALTER TABLE table
DROP COLUMN column;
Thanks
Rachel
**
[EMAIL PROTECTED]
Laboratoire de prehistoire
Hi,
I want to change 2 columns in the same table from numeric into int. Can
I do this without deleting the old table and creating a new one? Data
stays the same..
Thanks
Huub
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
Achilleus Mantzios <[EMAIL PROTECTED]> writes:
> After recreating the missing triggers should i upgrade
> to 7.2.3??
Make that "before".
I frankly suspect pilot error here. Triggers do not simply disappear.
If you did have crash-induced corruption leading to loss of some rows
in pg_trigger, it w
On Thu, 7 Nov 2002, Tom Lane wrote:
> Achilleus Mantzios <[EMAIL PROTECTED]> writes:
> > After recreating the missing triggers should i upgrade
> > to 7.2.3??
>
> Make that "before".
>
> I frankly suspect pilot error here. Triggers do not simply disappear.
> If you did have crash-induced corrupti
42 matches
Mail list logo
