[SQL] Perform Function When The Rows Of A View Change

[Adam]

Hello,

I have a rather complicated view that is dependent upon multiple
tables, consisting of several windowing and aggregate functions, as
well as some time intervals. I would like to be able to perform a
function, i.e. pg_notify(), whenever a row is added, changed, or
removed from the view's result set.

I think the kicker is the fact that the set of results returned by the
view is dependent on the current time.

Here's a simplified version of what's going on:

CREATE VIEW view2 AS (
 SELECT view1.id, view1.ts
   FROM view1
  WHERE view1.ts > (now() - '1 day'::interval)
);

As such, even if there are no inserts, deletes, or updates performed
on any of the tables that view1 depends on, the data contained in
view2 will change as a function of time  (i.e. rows will disappear
from the view as time elapses).  I have been unable to come up with a
trigger or rule that can detect this situation and provide the
notification I'm looking for.

I could just query the view over and over again, and look for changes
as they occur. But I'm hoping to find a more elegant (and less
resource-intensive) solution. Any ideas?

Thanks!
-Adam


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Creating sequences

[Adam Lang]

Is it possible to have a sequence (or something like it) when it increments
alpha-numeric?

Say the first value is set at A01 and it will increment to A02, A03 by
default.  Also, it would be good if it could be made the primary key.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company

Re: [SQL] Creating sequences

[Adam Lang]

This is what I wanted to do.  When I can have a primary key that is
meaningful AND won't change, I would like to try to use it, otherwise I'd
probably use an OID.  So... I'm developing a database that will have the
primary key like a "case number" (like when you call for tech support).  Say
your call center has 3 products: Word processor, Spread Sheet, Database.
When you open a case for a product, that product's code will be the
beginning of the case number.  Say, Word processor will be WP1,
Spreadsheet will be SS1, etc. (Each product would be in its own table).

Each time a case is opened, it would increment to the next number.

Granted, I could probably just code something where I set up a sequence and
when I do an insert, it concatenates the prefix to the number and then
inserts the value, but I was curious if there was a way where you can set
the initial value to alphanumeric and it will increment from there.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Gannon, Terry" <[EMAIL PROTECTED]>
To: "'Adam Lang'" <[EMAIL PROTECTED]>
Sent: Friday, August 18, 2000 4:15 PM
Subject: RE: [SQL] Creating sequences


> Adam -- suggest that a way to do this would be to use a stored procedure
> that would read the sequence number associated with the table, and build
the
> variable as you require.  You would then setup a trigger that would launch
> the stored procedure everytime you insert a new record into the table?  If
> that's a little foggy, then I would suggest that if you can part with the
> definition of the table that I could put together a little example to
> illustrate...regards...
>
> Terry Gannon
> [EMAIL PROTECTED]
>
> > -Original Message-
> > From: Adam Lang [SMTP:[EMAIL PROTECTED]]
> > Sent: Friday, August 18, 2000 12:25 PM
> > To: [EMAIL PROTECTED]
> > Subject: [SQL] Creating sequences
> >
> > Is it possible to have a sequence (or something like it) when it
> > increments
> > alpha-numeric?
> >
> > Say the first value is set at A01 and it will increment to A02, A03 by
> > default.  Also, it would be good if it could be made the primary key.
> >
> > Adam Lang
> > Systems Engineer
> > Rutgers Casualty Insurance Company

[SQL] Copy To - fixed width

[Adam Lang]

Is it possible to copy from a text file that has the fields as fixed width
with no delimiter to a table?

The other option is a CSV file... but then how do I handle if there is a
comma in one of the fields?

I'm transfering information from an AS/400 ... if it is into a text file, it
is fixed width, if I use Excel, it is Comma separated.

Re: [SQL] Copy To - fixed width

[Adam Lang]

Sort of nevermind, can I can save it tab delimited, but I am still curious
if it can be done using fixed width fields.


Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Adam Lang" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, August 23, 2000 10:38 AM
Subject: [SQL] Copy To - fixed width


> Is it possible to copy from a text file that has the fields as fixed width
> with no delimiter to a table?
>
> The other option is a CSV file... but then how do I handle if there is a
> comma in one of the fields?
>
> I'm transfering information from an AS/400 ... if it is into a text file,
it
> is fixed width, if I use Excel, it is Comma separated.

[SQL] PL/Perl documentation ...

[Adam Walczykiewicz]

Is there available some more examples of writing functions in PL/Perl ?
In standard documentation there is only just 2 examples.

Thanks in advance for any help.

Adam



-- 
 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Cannot build PL/Perl ...

[Adam Walczykiewicz]

Cannot build PL/Perl because libperl is not a shared library.
I'v got this error when I tried to compile PL/Perl.
I have SuSE Linux 7.0 , have I get a libperl.so library , and put it in 
/usr /lib ...

Thanks for any help
Adam



-- 
 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] oid and triggers

[Adam Doligalski]

Hi everybody!

Is there any possibility to access oid's in trigger routines?
I suppose that it makes no sense while 'before insert' routines
but it would be very usable while treating oid's as rows
identifiers.

Thanks,
Adam

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] retrieving all rows from a "tree" in one select - how ?

[Adam Erickson]

I'll be curious to see the responses to this.  I myself deal with this same
situation every day.  Although we're currently using MySQL but moving it to
postgres (which is why I'm on these lists..)

>  -- select a tree starting with node 1234 and all its descendants:
> SELECT * FROM t WHERE id=1234 OR ANCESTOR(t.parent) IS 1234;

I've seen some really weird solutions to this.  I'm not sure if a subselect
can do this or not.  I doubt it.  Since MySQL limits us greatly we resort to
a lookup field for each record in the node.

ie. (Forgive ASCII art please)

1 --> 2
 --> 4
 --> 5
 --> 6
--> 8
   --> 9
  --> 3
 --> 7
--> 10

The record for id=9 would have a field index='-1-2-6-8-x'

When we want all records under node id=6 we just use:
select * from t where index like "%-6-%";

We prefix with '-' for arbitrary level searches.  We suffix with -x for an
unknown (but good) reason.  My memory is leaving me.

>  -- select the path from tree node 2345 to the root
> SELECT * FROM t WHERE id=2345 OR DESCENTANT(t.parent) IS 2345;

With our lookup/index field this is trivial.  Unfortunately, it makes the
application responsible for parsing and is probably not what you're after.

Just my two cents.  It works very well for us (make the lookup field an
index btw) but their is probably a much better way in postgres.  I don't
remember if postgres allows regexes in the where clause (ie. rlike in mysql)
but with that you can "find all nodes 3 or more leaves down from node 123"
or even weirder stuff.  We have trees with 60,000 nodes 30-40 levels deep.
Queries on the tree take very little time at all.

Adam Erickson


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Rules and Triggers

[Adam Erickson]

Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
Triggers are carried out after the SQL is executed and the data is modified.
So, to answer your question, I think rules come first.  As to which order
the rules / triggers are executed probably depends on the order you put them
in when you created them but I pulled that straight out of my arse.  :)

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Paesold
> Sent: Tuesday, September 10, 2002 1:53 PM
> To: [EMAIL PROTECTED]
> Subject: [SQL] Rules and Triggers
>
>
> Hi all,
>
> can you tell me in what order rules and triggers are executed?
>
> First, what comes first, the rules, or the triggers?
> And then, in what order are all the rules / triggers executed?
>
> Regards,
> Michael Paesold
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Complex SQL query and performance strategy

[Adam Witney]

Hi,

I have a complex SQL query which requires the joining of 18 tables. There
are only primary key indices on the table and at the moment it runs a little
slow (30s or so) and so I am trying to optimise it.

The output of EXPLAIN is a little confusing and seems to vary from run to
run. Does the query optimiser have trouble with larger number of table
joins?

Also this will be running from a web front end, and I hope to have it
encapsulated all within a function. Would it be better to break it up into
multiple SQL statements/functions? Or to try to really tweak the indices?

Thanks for any advice

adam


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Creating Stored Procedures

[Adam Witney]

> I installed 7.3beta3 but how do I do it now?
> How do I make a function that returns a dataset
> 
> Greets
> Zertox


He is an email I received from Joe Conway on the subject a month or so ago

> Adam Witney wrote:
>> There have been a few emails recently concerning using functions. However I
>> am a little confused as to their use with composite types. I can see how to
>> return a whole row from a table, but is it possible to return multiple
>> fields that do not originate from the same table?
> 
> Sure. But you either need a named composite type that matches the row you want
> to return, or you can use a record datatype and specify the column definitions
> in the sql statement at run time.
> 
> A composite type exists for each table and view in your database, as well as
> any stand-alone composite types you define. So, for example:
> 
> test=# create table foo (f1 int,f2 text);
> CREATE TABLE
> test=# create table bar (f3 int,f4 text);
> CREATE TABLE
> test=# create view foobar as select f1,f2,f4 from foo, bar where f1=f3;
> CREATE VIEW
> test=# insert into foo values(1,'a');
> INSERT 1105496 1
> test=# insert into foo values(2,'b');
> INSERT 1105497 1
> test=# insert into bar values(1,'c');
> INSERT 1105498 1
> test=# insert into bar values(2,'d');
> INSERT 1105499 1
> 
> -- This uses a named composite type based on the view
> test=# create function getfoobar1() returns setof foobar as 'select f1,f2,f4
> from foo, bar where f1=f3' language sql;
> CREATE FUNCTION
> test=# select * from getfoobar1();
> f1 | f2 | f4
> ++
>  1 | a  | c
>  2 | b  | d
> (2 rows)
> 
> -- This uses an anonymous composite type specified at runtime
> test=# create function getfoobar2() returns setof record as 'select f1,f2,f4
> from foo, bar where f1=f3' language sql;
> CREATE FUNCTION
> test=# select * from getfoobar2() as (f1 int,f2 text,f4 text);
> f1 | f2 | f4
> ++
>  1 | a  | c
>  2 | b  | d
> (2 rows)
> 
> HTH,
> 
> Joe
> 
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
>   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] SQL list table names

[Adam Witney]

If you start psql like so

psql -E

Then all the SQL behind the \d type commands is displayed for you.

adam


> is it possible to execute an sql query to be able to list the tables's
> names?
> well, you can do it on psql using \dt. but im talking about the SQL
> statement, because i want to execute that query from a script.
> 
> thanks.
> 
> 
> 
> alviN
> 
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Inherancing

[Adam Witney]

On 7/1/03 11:42 am, "Nasair Junior da Silva" <[EMAIL PROTECTED]> wrote:

> Hi people,
> supposing i have the tables :
> 
> create table cities (id int, name varchar, primary key(id) );
> create table centers (state varchar(2)) inherits (cities);
> 
> ant the tuple
> insert into cities values (1, 'Lajeado');
> 
> How i do if i want to make this city as a center ?

If I understand you correctly you probably want to do this instead...

insert into centers values (1, 'Lajeado', 'AZ');

Where AZ is your state

HTH

adam


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Inherancing

[Adam Witney]

Yes, if you do 

insert into cities values (1, 'Lajeado');
insert into centers values (1, 'Lajeado', 'AZ');

Then select * from cities will give you

1 Lajedo
1 Lajedo

And select * from centers will give

1 Lajedo AZ

However, if rather than duplicating the insert and just doing

insert into centers values (1, 'Lajeado', 'AZ');

Then select * from cities will give you

1 Lajedo

And select * from centers will give

1 Lajedo AZ


Is this what you want?

adam


> In this case, when i do
> select * from cities;
> i'll have two tuples with the name 'Lajeado'.
> 
> Does it the only way i have ?
> 
> Thanks again,
> 
> Nasair Júnior da Silva
> Lajeado - RS - Brasil.
> 
> Em Tue, 07 Jan 2003 12:02:13 +, Adam Witney <[EMAIL PROTECTED]>
> escreveu:
>> On 7/1/03 11:42 am, "Nasair Junior da Silva" <[EMAIL PROTECTED]> wrote:
>> 
>>> Hi people,
>>> supposing i have the tables :
>>> 
>>> create table cities (id int, name varchar, primary key(id) );
>>> create table centers (state varchar(2)) inherits (cities);
>>> 
>>> ant the tuple
>>> insert into cities values (1, 'Lajeado');
>>> 
>>> How i do if i want to make this city as a center ?
>> 
>> If I understand you correctly you probably want to do this instead...
>> 
>> insert into centers values (1, 'Lajeado', 'AZ');
>> 
>> Where AZ is your state
>> 
>> HTH
>> 
>> adam
>> 
>> 
> 
> 
> xx===xx
> ||  °v°   Nasair Junior da Silva ||
> || /(_)\  Linux User: 246054 ||
> ||  ^ ^   [EMAIL PROTECTED]||
> ||CPD - Desenvolvimento  ||
> ||Univates - Centro Universitário||
> xx===xx
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
>   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Is it possible to connect to another database

[Adam Witney]

Take a look at dblink in the contrib directory... This may do what you need

adam


> Hi,
> 
> I try to find how is it possible to connect 2 databases, with a symbolic
> link.
> 
> I have to use tables in another database to test user or other information.
> 
> 
> Ben
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>  http://www.postgresql.org/docs/faqs/FAQ.html


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(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] weird(to me) request

[Adam Wieckowski]

Użytkownik Larry Rosenman napisał:

I have the following rows (in addition to others):

acct_num text,
master_acct_num text,
These are in the same table.

What I want to enforce is that if the master_acct_num field is NOT 
NULL (it can be NULL, and
that's fine), that the value appears in some row as acct_num.  
acct_num has a unique index on it, so that's fine.

I'm not sure how to do this

Thanks!

LER


select
   case
   when master_acct_num is null
   then acct_num
   else master_acct_num
   end
   from table;
did you mained somethink like this ?


smime.p7s
Description: S/MIME Cryptographic Signature

[SQL] query or design question

[Adam Witney]

Hi,

I have a table like so

CREATE TABLE imagene (
id int
bioassay_idint
gene_idtext
s_row  int
s_column   int
s_meta_row int
s_meta_column  int
sig_median numeric
bkg_median numeric
);

Rows are unique on (bioassay_id, gene_id, s_row, s_column, s_meta_row,
s_meta_column)

They are grouped like this (note, the counts will not always be the same)

cabbage=# select bioassay_id, count(*) from imagene group by bioassay_id;
 bioassay_id | count
-+---
 106 | 10944
 107 | 10944


And I need to generate an output like this..

 bioassay_id=106bioassay_id=107
 --  --
gene_id, sig_median, bkg_median, sig_median, bkg_median



I can do something like this

SELECT a.gene_id,
 a.sig_median, a.bkg_median,
 b.sig_median, b.bkg_median
   FROM 
   imagene a,
   imagene b
   WHERE 
   a.s_meta_row = b.s_meta_row AND
   a.s_meta_col = b.s_meta_col AND
   a.s_row = b.s_row AND
   a.s_column = b.s_column AND
   a.bioassay_id = 106 AND
   b.bioassay_id = 107;


But this is quite slow... And not generic as I may need to generate the
output for more than two bioassay_ids. Also I may need to do an outer join
to make sure I get all rows from both data sets?

I am not sure if a crosstab function will do this?

Any ideas of pointers would be greatly appreciated

Thanks

Adam


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[SQL] Crosstab question

[Adam Witney]

Hi,

I am trying to figure out the use of crosstab(text sql, int N)

The sql I have is

cabbage=# select geneid, bioassay_id, sig_median from imagene order by 1,2;

 geneid  | bioassay_id | sig_median
-+-+
 16s rRNA (AP1A1)| 107 |65535.0
 16s rRNA (AP1A1)| 108 | 1904.0
 16s rRNA (AP1A1)| 109 |65535.0
 16s rRNA (AP2A1)| 106 |  197.0
 16s rRNA (AP2A1)| 108 |  197.0
 16s rRNA (AP2A1)| 109 |10525.0
 16s rRNA (MWG1B1)   | 106 |   49.0
 16s rRNA (MWG1B1)   | 107 |  282.0
 16s rRNA (MWG1B1)   | 108 |   49.0
 16s rRNA (MWG1B1)   | 109 |  282.0

However when I use a crosstab function like so

cabbage=# select * from crosstab('select geneid, bioassay_id, sig_median
from imagene order by 1,2;', 4) as ct(geneid text, b106 numeric, b107
numeric, b108 numeric, b109 numeric);

 geneid  |  b106   |  b107   |  b108   |  b109
-+-+-+-+-
 16s rRNA (AP1A1)| 65535.0 |  1904.0 | 65535.0 |
 16s rRNA (AP2A1)|   197.0 |   197.0 | 10525.0 |
 16s rRNA (MWG1B1)   |49.0 |   282.0 |49.0 |   282.0

However it should be:

 geneid  |  b106   |  b107   |  b108   |  b109
-+-+-+-+-
 16s rRNA (AP1A1)| | 65535.0 |  1904.0 | 65535.0
 16s rRNA (AP2A1)|   197.0 | |   197.0 | 10525.0
 16s rRNA (MWG1B1)   |49.0 |   282.0 |49.0 |   282.0

The missing values seemed to have been ignored and so the data is being
shifted to the left and so put in the wrong columns.

Am I using this function correctly? What is supposed to happen with missing
values?

Thanks for any help

Adam




-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] how to create a multi columns return function ?

[Adam Witney]

Take a look at the section on 'SQL Functions Returning Sets'


http://www.postgresql.org/docs/7.3/static/xfunc-sql.html#AEN31304



> Hi 
> 
> I'm moving databases from sybase to postgres.
> But I have difficulties in creating a postgres equivalent to the sybase stored
> procedures... 
> 
> Apparently, Postgres functions should work, but the syb stored procedures get
> only one parameter and return several colums
> 
> Here's the code I wrote in postgresql :
> 
> create function function_name( int ) returns text
> AS ' SELECT column1, column2, column3,...,column15
> FROM table_name 
> WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 =
> \'specific_value2 \' '
> LANGUAGE 'SQL'; 
> 
> and I get the message error : returns multi columns
> 
> I'm wondering too if It's possible to create a view with a parameter if
> functions don't work.
> 
> Has anybody faced the same problem ?
> 
> I need help 
> 
> thanks



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 3: 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] postgreSQL editors

[Adam Ruth]

I currently use XCode from Apple (their free development environment).  
I create sql scripts which I then either execute directly in psql or 
copy and paste into an open psql session for small changes.

On Dec 30, 2003, at 9:05 AM, beyaRecords - The home Urban music wrote:

Hi,
can anyone recommend a good editor for postgreSQl wich wil enable me 
to create functions/stored procedures?
I am currently running version 7.4.1 on OS X 10.3

regards

Uzo

---(end of 
broadcast)---
TIP 1: subscribe and unsubscribe commands go to 
[EMAIL PROTECTED]



---(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] postgreSQL editors

[Adam Ruth]

I didn't notice an OS X version on their website.  Do they have one?

On Dec 31, 2003, at 12:57 PM, Andy Lewis wrote:

I would have to agree with EMS-HiTech's product also. A feature rich,
product, very responsive tech support/bug report. PGManager beats the
competition hands down.
Andy

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Rich Hall
Sent: Wednesday, December 31, 2003 1:09 PM
To: [EMAIL PROTECTED]
Subject: Re: [SQL] postgreSQL editors


can anyone recommend a good editor for postgreSQl wich wil enable me
to create functions/stored procedures?
I am currently running version 7.4.1 on OS X 10.3

I use PG Manager from EMS Hitech. It is not free but inexpensive.

You have everything PostgreSQL-wise at your fingertips.

Ver 2.0 was just released and they are very responsive to bug reports.

Rick

---(end of 
broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

---(end of 
broadcast)---
TIP 4: Don't 'kill -9' the postmaster



---(end of broadcast)---
TIP 3: 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] sort by on two columns

[Adam Ruth]

Sounds like you may want to concatenate the columns:

... order by table1.name || table2.name

The sorting would then be performed on both of the them as though they 
were one column.

Adam Ruth

On Jan 2, 2004, at 8:04 PM, Michael Glaesemann wrote:

On Jan 2, 2004, at 8:55 PM, Andy Lewis wrote:
Yes, I understand this but, I would like to have the results of both
"table1.name, table2.name"
sorted as one column.
Is this possible?
So you want one column of name, including names from both table1 and 
table2? In that case, you need to use UNION, I believe. If it's 
something else, I'm not sure I understand what you're asking. Perhaps 
you could include a sample of what you're getting, along with what 
you'd like to see?

Michael Glaesemann
grzm myrealbox com
---(end of 
broadcast)---
TIP 7: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] perfomance question

[Adam Ruth]

On Mar 18, 2004, at 11:27 PM, sad wrote:

On Thursday 18 March 2004 21:38, you wrote:
Sad,

what are perfomance difference bitween
a)  update t1 set f1 = 'x', f2 = 'y';
b)  update t1 set f1 = 'x', f2 = f2;
c)  update t1 set f1 = 'x';
?
Not a lot.  Why don't you try it?
Really ! why ? :-)

I forgot to ask the second question:
How these updates affect a data storage ?
(does (b) cause physical update of f2 ?)
Updates always apply to full rows at a time.  So while "update t1 set 
f1 = 'x'" only changes the value of f1 to 'x', f2 will have a second 
copy with the same value in the new row that is created as part of the 
update.  It's all part of the MVCC architecture.  There may be some 
uncommon exceptions to this rule, but I'm not sure.

Adam Ruth



---(end of 
broadcast)---
TIP 7: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

[SQL] Subqueries returning more than one value?

[Adam Witney]

Hi,

I am using a function in a subquery, this works ok:

SELECT name, (SELECT p_my_func(1)) AS id
  FROM test;

However I would like to have the function return 2 values into the main
query... Something like this:

SELECT name, (SELECT p_my_func(1)) AS (id, type)
  FROM test;

Of course this gives the error:

ERROR:  subquery must return only one column

Is there any way around this?

Thanks for any help

adam


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] double left outer join on the same table

[Adam Witney]

On 2/5/04 5:23 pm, "T E Schmitz" <[EMAIL PROTECTED]> wrote:

> Hello,
> 
> I have two tables SECTION and BRAND. SECTION is related to BRAND via two
> foreign keys. I would like to select ALL SECTIONs whether the FKs are
> null or not and fetch the BRAND attributes in one SQL statement. In
> other words I need a double outer join.
> 
> Is this possible at all?
> 
> The following doesn't work for two reasons:
> a) Table name "brand" specified more than once.
> b) how would I specify the same output columns twice?
> 
> SELECT
> SECTION.SECTION_PK,
> SECTION.SECTION_NAME,
> SECTION.BRAND_1_FK,
> SECTION.BRAND_2_FK,
> BRAND.BRAND_PK,
> BRAND.BRAND_NAME
> 
> FROM SECTION
> left outer join BRAND  on BRAND_PK =BRAND_1_FK
> left outer join BRAND  on BRAND_PK =BRAND_2_FK
> 

I don't know if this will solve your specific problem, but you can use the
same table twice in the same query by using aliases, something like this
(untested of course)

SELECT
SECTION.SECTION_PK,
SECTION.SECTION_NAME,
SECTION.BRAND_1_FK,
SECTION.BRAND_2_FK,
a.BRAND_PK,
a.BRAND_NAME
b.BRAND_PK,
b.BRAND_NAME

FROM SECTION
left outer join BRAND a on BRAND_PK =BRAND_1_FK
left outer join BRAND b on BRAND_PK =BRAND_2_FK




-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Possible rounding error of large float values?

[Adam Lancaster]

Title: Possible rounding error of large float values?






When setting a float column to this value:


9223372036854775807


It gets selected out as:


9.22337203685478E18


Which appears to be rounded. When we cast it to numeric type we get:


922337203685478


Which also is rounded. It is still possible to find the row using the original value


select * from  where  = 9223372036854775807


Is this expected behavior?



Thanks,

Adam

[SQL] update from multiple rows

[adam etienne]

hi
(B  I have some trouble updating a table like this one :
(B  date | data_raw | data_sys
(B  12-01   |   5   |   4.5
(B  13-01   |   6   |   6
(B  14-01   |   7   |   8
(B
(BI would like to update the 'data_sys' row by computing values of multiple 
(B'data_raw' values. I mean for example :
(B data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] + 
(Bdata_raw['14-01'] )/3;
(B
(BI thought of a function that fetch the 3 data_raw rows for each rows 
(Bbut it was obviously too much slow...
(B
(BIs there a more efficient way to achieve this ?
(BThanks in advance.. This could help me very much..
(B
(BEtienne Adam
(B
(B_
$BL5NA%a!<%k$J$i$d$C$Q$j(B $B!V(BMSN Hotmail$B!W(B http://www.hotmail.com/ 
(B
(B
(B---(end of broadcast)---
(BTIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] update from multiple rows

[adam etienne]

Thanks for your answer
(BIn fact the computation is somewhat more complex than an average and the 
(Bdata set is quite large... I did some test with view & triggers but it's 
(Btoo slow..
(BMoreover, sometime i need to do big insertion or update and then other time 
(Bi need juste little update of this table...
(BI would like to apply a trigger only for little update but i don't know how 
(Bto proceed.. Maybe with a condition into the trigger.. But it's adding 
(Bcomputation time...
(B
(BThanks again,
(BEtienne Adam
(B
(B
(B
(B> >  I have some trouble updating a table like this one :
(B> >  date | data_raw | data_sys
(B> >  12-01   |   5   |   4.5
(B> >  13-01   |   6   |   6
(B> >  14-01   |   7   |   8
(B> >
(B> > I would like to update the 'data_sys' row by computing values of 
(Bmultiple
(B> > 'data_raw' values. I mean for example :
(B> > data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] +
(B> > data_raw['14-01'] )/3;
(B>
(B>Is there a reason to maintain data_sys in the table?  Could you use
(B>a view instead?  A view could do self joins (join the table against
(B>itself) and perform the necessary calculations on the fly when you
(B>make a query.  That could be expensive if you select large data
(B>sets, but for small slices of data it might suffice.
(B>
(B>Another possibility might be to use a trigger to recalculate data_sys
(B>when records are inserted, updated, or deleted.  You'd still have
(B>to do a potentially expensive one-time update of the entire table,
(B>but future updates would then touch only the rows that depend on
(B>the data being inserted, updated, or deleted, and the calculated
(B>values would always be current.  Using a trigger would require some
(B>care, however, to avoid cascading updates that are unnecessary or
(B>that could result in infinite recursion.
(B
(B_
$BL5NA%a!<%k$J$i$d$C$Q$j(B $B!V(BMSN Hotmail$B!W(B http://www.hotmail.com/ 
(B
(B
(B---(end of broadcast)---
(BTIP 5: Have you checked our extensive FAQ?
(B
(B   http://www.postgresql.org/docs/faq

Re: [SQL] pgdump by schema?

[Adam Witney]

From: pg_dump --help

-n, --schema=SCHEMA  dump the named schema only



> Is there a way to dump everything in a particular schema?
> 
> 
> Bradley Miller
> NUVIO CORPORATION
> Phone: 816-444-4422 ext. 6757
> Fax: 913-498-1810
> http://www.nuvio.com
> [EMAIL PROTECTED]


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] Btrieve to PostgreSQL

[Adam Alkins]

Hey,

Been looking around to do this for a while, haven't gotten concrete
information. I'm interested in taking data from Peachtree Accounting
2003 (Which is stored Btrieve DAT files) and importing them into a
Postgres SQL database. I have looked around on the net about this, but
haven't gotten any concrete methods on doing this. Figured with
Pervasive's involvement in PostgreSQL, might have some luck on this
list.

Peachtree also stores the DDL (Dictionary) files for the Btrieve
database in the directory. I'm not sure what version of Btrieve the
files are. Does anyone have any experience in doing this? I read
somewhere about Pervasive's SDK for their DB server which can convert
Btrieve files to SQL (or something like that), but looking through the
SDK and documentation, haven't found any real information. This is
just an experimental project, so any commercial solutions to do this
really doesn't apply.

Regards,
--
Adam Alkins
http://www.rasadam.com

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[SQL] Crosstab Confusion

[Adam Sherman]

I'm really trying to understand how the tablefunc crosstab function works, to 
no avail.

I have a table that looks like this:

customer_id integer
date timestamp with time zone
amount numeric(10,4)

There are rows in this table every-time a customer gets charged an amount, 
which is multiple times per day.

I would like to get a result like this:

customer_id,day1,day2,day3,(…)
1,400.00,500.01,123.00,(…)

So, one row for each customer id and a column for every day in the current 
month. Anyone used crosstab for something like this?

Thanks for your help,

A.


-- 
www.sherman.ca / +1-613-797-6819 / +1-646-233-3400

"When the burning husks of your startups warm the last of your bones, remember 
I told you so." - Zed


-- 
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] Crosstab Confusion

[Adam Sherman]

On 2010-02-01, at 11:34 , Lee Hachadoorian wrote:
> You basically have three parts:
> 
> 1) SELECT query in the form (row header, column header, cell value). In this 
> case it is an aggregate query so that  you can sum the transactions over a 
> given day.
> 
> 2) List of column headers. If you want, this can SELECT from another table, 
> so you can have a table with rows 1, 2, 3, etc and use it to select the days 
> from the month instead of listing them manually.
> 
> 3) List of output columns, which follows "pivot" in the text above. Note that 
> "pivot" is an arbitrary name. You can use foo, bar, or whatever, but that 
> will be the name of the table which must be used to reference the columns in 
> the top SELECT list.

Wow that's an incredibly complete response!

I'm not getting any data in my rows though. This query produces the data:

SELECT
cust_id as customer,
date_trunc('day', date) AS day,
SUM(billed_duration) AS minutes
FROM master_cdr
WHERE date >= '2010-01-01'
GROUP BY 1,2
ORDER BY 1,2;

Which looks like:

 customer | day | minutes 
--+-+-
1 | 2010-01-01 00:00:00 |1110
1 | 2010-01-03 00:00:00 |  60
1 | 2010-01-26 00:00:00 |   23010
1 | 2010-01-27 00:00:00 |   17910
2 | 2010-01-01 00:00:00 |  60
2 | 2010-01-02 00:00:00 |  30
2 | 2010-01-04 00:00:00 |   26310
etc, etc, etc

But this query:

-- clients by day
SELECT pivot.* FROM crosstab(
  'SELECT
cust_id as customer,
date_trunc(''day'', date) AS day,
SUM(billed_duration) AS minutes
  FROM master_cdr
  WHERE date >= ''2010-01-01''
  GROUP BY 1,2
  ORDER BY 1,2',
  'select * from day_of_month'
) pivot (
  customer integer,
  day1 numeric(10,4),
  day2 numeric(10,4),
(…)
  day31 numeric(10,4)
  )
ORDER BY customer;

Gives me a table that looks right but all values are null for the days.

Something simple maybe?

Thanks,

A.

-- 
www.sherman.ca / +1-613-797-6819 / +1-646-233-3400

"When the burning husks of your startups warm the last of your bones, remember 
I told you so." - Zed


-- 
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] Crosstab Confusion

[Adam Sherman]

On 2010-02-01, at 14:22 , Lee Hachadoorian wrote:
> The output column data type (day1, day2, etc.) is supposed to match the value 
> data type. I used numeric(10,4) because that's what your original post 
> specified, but the billed_duration column in your most recent post looks like 
> it might be integer? (Or is it defined as numeric(10,4), but you never enter 
> noninteger values?)

Actually, the query I was running is:

SELECT
cust_id as customer,
date_trunc(''day'', date) AS day,
SUM(billed_duration)/60.0::numeric(10,4) AS minutes

billed_duration is an integer. Make sense?

> What's the output of the category query by itself? I forgot to include ORDER 
> BY 1 at the end of the category query. (The order should match the order of 
> output columns, but I think without it you wouldn't get NULL values, just the 
> values would be in the wrong columns.) I assume day_of_month has only one 
> column, but I would suggest naming it explicitly instead of using *. And is 
> the day_of_month column defined in the same format as date_trunc('day', 
> date)? They must successfully pass an "equals" test to get included in the 
> right crosstab cell. If a category value in the source query doesn't match 
> any value produced by the category query, I think the crosstab function just 
> throws out that row, which could lead to a table with the correct structure 
> but all NULLs.

Right, my list of columns weren't equal to the truncated date. Using your 
suggested query to generate the columns fixed the problem!

Now, is there a way to generate the labels? Otherwise I have to adjust the 
query for th number of days returned.

Such nice output though! Awesome!

Thanks,

A.


-- 
www.sherman.ca / +1-613-797-6819 / +1-646-233-3400

"When the burning husks of your startups warm the last of your bones, remember 
I told you so." - Zed


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] which is better: using OR clauses or UNION?

[Adam Witney]

t)
   ->  Bitmap Index Scan on in_dba_data_base_identifier 
 (cost=0.00..32.64 rows=964 width=0) (actual time=0.036..0.036 rows=0 loops=1)
 Index Cond: (lower(identifier) ~=~ 
'bugs001884677'::character varying)
 ->  Bitmap Heap Scan on dba_data_base a  
(cost=32.88..3786.62 rows=964 width=62) (actual time=0.010..0.010 rows=0 
loops=1)
   Filter: (lower(identifier) ~~ 
'bugs001884678'::text)
   ->  Bitmap Index Scan on in_dba_data_base_identifier 
 (cost=0.00..32.64 rows=964 width=0) (actual time=0.008..0.008 rows=0 loops=1)
 Index Cond: (lower(identifier) ~=~ 
'bugs001884678'::character varying)
 ->  Bitmap Heap Scan on dba_data_base a  
(cost=32.88..3786.62 rows=964 width=62) (actual time=0.010..0.010 rows=0 
loops=1)
   Filter: (lower(identifier) ~~ 
'bugs001884679'::text)
   ->  Bitmap Index Scan on in_dba_data_base_identifier 
 (cost=0.00..32.64 rows=964 width=0) (actual time=0.008..0.008 rows=0 loops=1)
 Index Cond: (lower(identifier) ~=~ 
'bugs001884679'::character varying)
 ->  Bitmap Heap Scan on dba_data_base a  
(cost=32.88..3786.62 rows=964 width=62) (actual time=0.255..1.676 rows=318 
loops=1)
   Filter: (lower(identifier) ~~ 'sptigr4-2210 
(6f24)'::text)
   ->  Bitmap Index Scan on in_dba_data_base_identifier 
 (cost=0.00..32.64 rows=964 width=0) (actual time=0.178..0.178 rows=318 loops=1)
 Index Cond: (lower(identifier) ~=~ 
'sptigr4-2210 (6f24)'::character varying)
 Total runtime: 4.174 ms

Also which should scale better if I add more strings to match? would there be 
any better design patterns for this problem?

Thanks for any help

Adam

select version();
version 

 PostgreSQL 8.2.12 on i686-pc-linux-gnu, compiled by GCC 2.95.4



-- 
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] Tripping over (my own) stupidity - VARCHAR declaration minus specificity

[Adam Cornett]

On Fri, Oct 14, 2011 at 1:27 PM, Good, Thomas  wrote:

> I created a table in a hurry and forgot to specify the max capacity for a
> varchar() data type.
> Can it be said what the max would be in a case like this (just curious)?
>
> thanks much
>
> --
> Thomas A. Good, Senior Database Administrator
> Saint Vincent's Hospital Westchester
> A Division of Saint Joseph's Medical Center
> Residential Services
> 1216 Bay Street, Room L-8
> Staten Island, NY 10305
> 718.982.4752
>
> "Wir leben, wir sind Glücksmaschinen." - Peter Hein
>
> This e-mail and any files transmitted with it are confidential and are
> intended solely for the use of the individual or entity to who they are
> addressed. This communication may contain protected health information
> and/or material protected by the attorney-client privilege. If you are not
> the intended recipient or the person responsible for delivering the e-mail
> to the intended recipient, be advised that you have received this e-mail in
> error and that any use, dissemination, forwarding, printing, or copying for
> this e-mail is strictly prohibited. if you have received this e-mail in
> error, please delete the message and attachments and notify the sender
> immediately.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>


According to
http://www.postgresql.org/docs/current/static/datatype-character.html,
"If character varying is used without length specifier, the type accepts
strings of any size. The latter is a PostgreSQL extension."

-Adam Cornett

Re: [SQL] Tripping over (my own) stupidity - VARCHAR declaration minus specificity

[Adam Cornett]

On Fri, Oct 14, 2011 at 2:09 PM, k...@rice.edu  wrote:

> On Fri, Oct 14, 2011 at 01:51:04PM -0400, Adam Cornett wrote:
> > On Fri, Oct 14, 2011 at 1:27 PM, Good, Thomas  wrote:
> >
> > > I created a table in a hurry and forgot to specify the max capacity for
> a
> > > varchar() data type.
> > > Can it be said what the max would be in a case like this (just
> curious)?
> > >
> > > thanks much
> > >
> >
> > According to
> > http://www.postgresql.org/docs/current/static/datatype-character.html,
> > "If character varying is used without length specifier, the type accepts
> > strings of any size. The latter is a PostgreSQL extension."
> >
> > -Adam Cornett
>
> And according to the documentation, the maximum size of a varchar is
> 1GB.
>
> Regards,
> Ken
>

Yes, 1GB is the limit for any column size.  From what I can tell a varchar
without a size specified acts just like text.

-Adam

Re: [SQL] new user on mac

[Adam Cornett]

On Tue, Oct 18, 2011 at 5:47 PM, Scott Swank  wrote:

> I have a postgres 9.1 database up & running, no problem. Purely in
> terms of writing sql (ddl, dml & pg/plsql), what tools are
> recommended?
>
> Coming from an Oracle world, I'm thinking of toad, sql developer, etc.
>
> 1. psql & text editor of choice (if so, which one?)
> 2. navicat
> 3. textmate with pgedit
> 4. eclipse plugin
> 5. other?
>
> Thank you,
> Scott
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>


psql + editor is the basic tool set, certainly the most flexible.
Also be sure to check out pgadmin:
http://www.pgadmin.org/download/macosx.php

-Adam

[SQL] Re-Sync Stand By Server

[Adam Cornett]

I have 2 identical machines, (Ubuntu 10.04 LTS running 9.1.2 from Martin
Pitt's PPA) setup with streaming asynchronous replication.
Recently I had to take the standby down for some extended maintenance, and
when it came back up it was waiting for a WAL segment that had since been
removed from the primary.  I then shutdown the server, took a new base
backup of the primary and copied it over (as per the directions in the
streaming wiki) and cleared out the wal segments in the
pg_xlog directory of the standby and tried to start it again, but now its
not accepting connections and is looking for that wal file in the startup
process:

postgres 10090 10089  0 12:38 ?00:00:00 postgres: startup process
waiting for 0001005200E9

However, segment 5200E9 is no longer on the primary, which is about a
week ahead:

postgres@pg1:~$  psql -c "SELECT pg_current_xlog_location()"
 pg_current_xlog_location
--
 58/8E1B8D98

So, how do I get the standby to catch up to "catch up" to the primary?

-Adam Cornett

Re: [SQL] Can I simplify this somehow?

[Adam Jelinek]

Are you looking for something like this for the result for the last 45 days
or something else?

TIME  MON   TUE  WED ...
09:00 13   4
10:00 50   8


On Fri, Sep 27, 2013 at 10:22 AM, Larry Rosenman  wrote:

> I tried(!) to write this as a with (CTE), but failed.
>
> Can one of the CTE experts (or better SQL writer) help me here?
>
> -- generate a table of timestamps to match against
> select
> generate_series(date_trunc('**day',now()-'45 days'::interval),now()+'1
> hour'::inte
> rval,'1 hour')
>AS thetime  into temp table timestamps;
>
> -- get a count of logged in users for a particular time
> SELECT thetime,case extract(dow  from thetime)
>when 0 then 'Sunday'
>when 1 then 'Monday'
>when 2 then 'Tuesday'
>when 3 then 'Wednesday'
>when 4 then 'Thursday'
>when 5 then 'Friday'
>when 6 then 'Saturday' end AS "Day", count(*) AS "#LoggedIn"
> FROM  timestamps,user_session
> WHERE thetime BETWEEN login_time AND COALESCE(logout_time, now())
> GROUP BY thetime
> ORDER BY thetime;
>
> Thanks for any help at all.
>
>
> --
> Larry Rosenman http://www.lerctr.org/~ler
> Phone: +1 214-642-9640 (c) E-Mail: l...@lerctr.org
> US Mail: 108 Turvey Cove, Hutto, TX 78634-5688
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql
>

[SQL]

[Adam Ruth]

-- 
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] Comparing two tables of different database

[Adam Ruth]

The simple answer is to pg_dump both tables and compare the output  
with diff.


Other than that, I think you'll need a custom program.

On 29/04/2009, at 10:33 PM, Pawel Socha wrote:


2009/4/29 Nicholas I :

Hi,

  can anybody me suggest me, how to compare two tables of different
database.

-Nicholas I



what you mean 'different database' :)
diffrent version, 2 instance ?

Maybe this help:
http://www.postgresql.org/docs/current/static/dblink.html


--
Pawel Socha

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] textpos() in postgreSQL 7.0

[Adam Walczykiewicz]

Hi
I notice that string function textpos() doesn't exist in 
postgreSQL 7.0. I have some
plpgsql functions written for 6.5.2 that use textpos() and 
when I tried to use them 
in postgreSQL 7.0 I'v got error. Should I compile postgreSQL 
7.0 with more options
than -enable locale ?!!?
Thanks for any help.
Adam
 

[SQL] pg_recvbuf : unexpected EOF on client

[Adam Walczykiewicz]

I have an application in Java I use to insert records into 
postgreSQL base. Java shows no errors, butrecords can't write into base.In 
pgsqrever.log I found entry :pg_recvbuf : unexpected EOF on client 
connection.
 
Thanks for any help.
Adam

[SQL] SPI documantation

[Adam Walczykiewicz]

Where can I get more information about programming  in 
SPI. 
(more than in PostgreSQL documentation).
Thanks for any help
Adam

[SQL] SQL-92 SQLSTATE in PostgreSQL ?!

[Adam Walczykiewicz]

Hi,
Is there SQL-92 SQLSTATE or SQL-96 SQLCODE implemented 
in
PostgreSQL (I use version 7.0 on SuSe Linux 6.4) 
?
If so, how to take the value of it in stored 
procedures (written in PL/pgSQL or C)
In  documentation I found only a short describtion of 
sqlca
In ecpg.
 

Thanks in advance 
Adam

[SQL] copy from

[Adam Lang]

I need additional help involving the copy from command

I have a database with several fields: id, name, address, zip

id is the primary key and I use nextval to get a value.

The file I want to upload is in comma separated value, that does not have a
primry key (or an id) field.

How would I use copy from and assign the correct id number?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company

Re: [SQL] copy from

[Adam Lang]

I get it.  I upload to the temp table, then for the select/insert I just do
the nextval(seq_id) for the primary key field, correct?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Adam Lang" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, August 14, 2000 5:17 PM
Subject: Re: [SQL] copy from


>
> One way would be to copy the data into a temporary table that doesn't
> have the id field, and then
> insert into table (fields) select * from temptable;
>
> Stephan Szabo
> [EMAIL PROTECTED]
>
> On Mon, 14 Aug 2000, Adam Lang wrote:
>
> > I need additional help involving the copy from command
> >
> > I have a database with several fields: id, name, address, zip
> >
> > id is the primary key and I use nextval to get a value.
> >
> > The file I want to upload is in comma separated value, that does not
have a
> > primry key (or an id) field.
> >
> > How would I use copy from and assign the correct id number?
> >
> > Adam Lang
> > Systems Engineer
> > Rutgers Casualty Insurance Company
> >

Re: [SQL] 8K Limit, whats the best strategy?

[Adam Lang]

I believe you are correct.  Version 7.1 will supposedly eliminate the row
size.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Thomas Swan" <[EMAIL PROTECTED]>
To: "Keith Wong" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, August 15, 2000 11:37 AM
Subject: Re: [SQL] 8K Limit, whats the best strategy?


> At 06:29 AM 8/15/2000, Keith Wong wrote:
> >Hi everyone,
> >
> >Just wondering what strategies people have used to get around the 8K row
> >limit in Postgres.
> >If anyone has been troubled by this limitation before and has a nice
> >solution around it, I would love to hear it.
> >
> >My application is a web-based system in which needs to store large
amounts
> >of text.
> >The text stored needs to searchable as well.
>
> You can you the type LZTEXT which is compressed text so your mileage will
vary.
>
> The other option is to go in and recompile for a larger row size.  Most of
> the people I have talked to had no problems with this route.  I move the
> size limit on an x86 Linux box to 32K (the max) and have not had any
> problems.   In the next release, I don't believe the row size limit will
> exist (correct me if I'm wrong).
>
> Let me know the system your on, and I will try to help if I can.
>
> -
> - Thomas Swan
> - Graduate Student  - Computer Science
> - The University of Mississippi
> -
> - "People can be categorized into two fundamental
> - groups, those that divide people into two groups
> - and those that don't."

Re: [SQL] copy from

[Adam Lang]

Hmmm... well, I don't think I have an "explicit" nextval.  I created the
table and then I did a create sequence broker_id;

Are you implying that I can set the field to automatically create a nextval?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Adam Lang" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, August 15, 2000 12:34 PM
Subject: Re: [SQL] copy from


> Well, if you define your main table to have a serial, or an explicit
> default nextval(seqid), you won't even need to do that.  Just leave
> the column off the insert list and the default should fill it for you.
>
> Stephan Szabo
> [EMAIL PROTECTED]
>
> On Tue, 15 Aug 2000, Adam Lang wrote:
>
> > I get it.  I upload to the temp table, then for the select/insert I just
do
> > the nextval(seq_id) for the primary key field, correct?

Re: [SQL] copy from

[Adam Lang]

Gotcha. Now, if I do an insert and just don't specify that field at all, it
will, use the default value, correct?  As long as I don't "touch" the field
with anything it uses the default.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Adam Lang" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, August 15, 2000 2:00 PM
Subject: Re: [SQL] copy from


>
> Sort of.  You can give the field a default value of
> nextval() which means that if you do not specify
> the column in an insert, it automatically gets the default
> value which should be the next value in the sequence.
> Note, that not putting the column is different from inserting a
> NULL into the field.
>
> (Example:
> sszabo=# create sequence xyzseq;
> CREATE
> sszabo=# create table xyzseqtest ( a int default nextval('xyzseq'), b
> int);
> CREATE
> sszabo=# insert into xyzseqtest (b) values (2);
> INSERT 172188 1
> sszabo=# insert into xyzseqtest (b) values (3);
> INSERT 172189 1
> sszabo=# select * from xyzseqtest;
>  a | b
> ---+---
>  1 | 2
>  2 | 3
> (2 rows)
> )
>
> There are issues about this dealing with rules and triggers where another
> row may be inserted or the default may be evaluated a second time where
> you want to get the value you just inserted back, but in general it works.
>
> On Tue, 15 Aug 2000, Adam Lang wrote:
>
> > Hmmm... well, I don't think I have an "explicit" nextval.  I created the
> > table and then I did a create sequence broker_id;
> >
> > Are you implying that I can set the field to automatically create a
nextval?

[SQL] ORDER BY does not work as expected with multiple joins

[Adam Rosi-Kessel]

I posted this message once through the Google Groups interface to pgsql.sql,
but I've received no replies and the message doesn't appear at all in the
MHonArc archives at postgresql.org, so I'm not sure it actually was
distributed to this list. Apologies if this is a duplicate:

I have a multijoin SELECT statement where the ORDER BY clause doesn't
seem to be working. I can't figure out how pgsql is sorting the
results, but it is definitely different from the same list without the
multiple joins.

To simplify: I have two tables. The first table ("table1") has three
fields: date, id1, and id2. The data look like this:

Date ID1   ID2
1/5/06   2 1
1/1/06   1 2
1/3/06   1 3

The second table ("table2") has two fields: id and name. The data look
like this:

ID   NAME
1John
2Jane
3Sam

What I am trying to do is join the two tables and sort by the date.
ID1 and ID2 in table1 *both* reference ID in table2.  The desired
result of the SELECT statement would look like this:

Date name1  name2
1/1/06   John Jane
1/3/06   John Sam
1/5/06   Jane John

For some reason, the result is not properly sorting by date--the data
are being reordered, but not in a way I can understand.

Here is the SELECT statement:

SELECT date,x.name as name1,y.name AS name2 FROM table1 LEFT JOIN
table2 AS x ON id1=x.id RIGHT JOIN table2 AS y ON id2=y.id WHERE (date
IS NOT NULL AND (id1 = ? OR id2 = ?)) ORDER BY date;

(where ? is replaced by the desired ID -- I want the results to list
all instances in table1 where id1 or id2 is a given ID number).

Did I mess up the multiple JOINs?  It seems like the SELECT is giving
the correct results other than not ordering by DATE.



signature.asc
Description: OpenPGP digital signature

Re: [SQL] ORDER BY does not work as expected with multiple joins

[Adam Rosi-Kessel]

Tom Lane wrote:
> Adam Rosi-Kessel <[EMAIL PROTECTED]> writes:
>> I have a multijoin SELECT statement where the ORDER BY clause doesn't
>> seem to be working.
> You haven't mentioned your Postgres version, nor provided anywhere near
> enough information to let other people reproduce the problem.  You might
> find it helpful to read this:
> http://www.postgresql.org/docs/8.1/static/bug-reporting.html

Thanks. I didn't think this was a bug--I thought I had an error in my SELECT
statement somehow. I certainly would have included more information with a
bug report. (For the record, it is PostgreSQL 7.4 from Debian Sarge).

> One comment is that your joining seems wrong: I'd have thought the
> RIGHT JOIN should be LEFT JOIN for what you're trying to do.  But that
> shouldn't make any difference to whether the sort order is correct.

Actually, it looks like I might have just been confused about DATESTYLE.
Adding in some dates where the day was greater than 12 clarified how the
sorting was being done. Due to a bug in my program, the DATESTYLE was set
differently for this multiple JOIN SELECT. Sorry about the misguided request!

But I am curious why you think a LEFT JOIN should be used rather than RIGHT
JOIN? I think I believe I am getting the desired set of records. When I
substitute LEFT JOIN for RIGHT JOIN I actually get the same result. Can you
help me understand under what conditions, in this case, I would obtain a
different result?

Adam



signature.asc
Description: OpenPGP digital signature

Re: [SQL] ORDER BY does not work as expected with multiple joins

[Adam Rosi-Kessel]

Thanks for your helpful response.

Michael Glaesemann wrote:
> I don't seem to have a problem with the ordering given your data. I'm
> not sure why you're doing right and left joins, though. You might want
> to use left joins if you have NULL id1 or id2, but otherwise just plain
> JOIN should work fine. I've included both your SELECT (slightly modified
> to avoid using key words) and how I'd probably right the statement.

id1 will always have a value but id2 can be NULL. So should I do a left JOIN
on id2 but a plain JOIN on id1? Is there a disadvantage to using a left JOIN
where it is not necessary?

Adam



signature.asc
Description: OpenPGP digital signature

Re: [SQL] short-cutting if sum()>constant

[Adam Tauno Williams]

On Tue, 2009-12-22 at 13:35 +0100, Ivan Sergio Borgonovo wrote:
> Hi,
> I'd like to know if
> select sum(qty) from t where status=37;
> is > constant.
> qty is always >0.
> Is there a way to skip examining further rows and return a result
> ASAP?

SELECT SUM(object_version)
FROM date_x
WHERE owner_id = 10100
HAVING SUM(object_version) > 1000

?

-- 
OpenGroupware developer: awill...@whitemice.org

OpenGroupare & Cyrus IMAPd documenation @



-- 
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] Date Index

[Adam Tauno Williams]

On Fri, 2008-10-31 at 08:48 +0100, A. Kretschmer wrote:
> am  Thu, dem 30.10.2008, um 14:49:16 -0600 mailte Ryan Hansen folgendes:
> > Hey all,
> > I?m apparently too lazy to figure this out on my own so maybe one of you can
> > just make it easy on me. J 
> > I want to index a timestamp field but I only want the index to include the
> > -mm-dd portion of the date, not the time.  I figure this would be where 
> > the
> > ?expression? portion of the CREATE INDEX syntax would come in, but I?m not 
> > sure
> > I understand what the syntax would be for this.
> > Any suggestions?
> Sure.
> You can create an index based on a function, but only if the function is
> immutable:
> test=# create table foo (ts timestamptz);
> CREATE TABLE
> test=*# create index idx_foo on foo(extract(date from ts));
> ERROR:  functions in index expression must be marked IMMUTABLE
> To solve this problem specify the timezone:
> For the same table as above:
> test=*# create index idx_foo on foo(extract(date from ts at time zone 'cet'));
> CREATE INDEX

I'm attempting to create an index as specified in this [old] thread; but
the adapted example fails.

OGo=> create index job_date_only on job(extract(date from start_date at
time zone 'utc'));
ERROR:  timestamp units "date" not recognized

I assume this is because the data type is 'timestamp with timezone'
which differs slightly from the original example.  But -

select extract(month from start_date) from job;

- [for example] works.  Is there an equivalent syntax to 'date' for
timestamp?



-- 
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] We all are looped on Internet: request + transport = invariant

[Adam Tauno Williams]

> >> J> And there's nothing wrong with Perl, PHP, Python and the myriad
> >> J> interface languages.
> >> I said many times, what is wrong:
> >> applied users can not join sql and perl, can not use libraries,
> >> and can not adjust web-server.
> J> II have not taken any formal courses
> Joe, i speak not about you, but about statistics.
> J> there are others on this list and elsewhere that have done so
> Second question: why?
> For what people must learn php, etc,
> if they need only output data from database.

They don't;  there are a myriad options.  I've got numerous 'normal
users' who use a database to do reasonably sophisticated things.  All
you need is a good client;  we use DbVisualizer
[ http://www.minq.se/products/dbvis/ ].  There are others,  you don't
need a programming language at all if all you want is "output data from
database".  Many of these, including DbVisualizer include GUI
drag-n-drop query builders.

Yes, you have to learn some SQL,  but there is no case where you get
something for nothing;  and almost all humans are reliably
quick-studies, even innovative, when they are properly motivated.

> >others may require the assistance of a
> J> technical specialist or a full-time programming team, but what's wrong
> J> with that?
> expenses of money, time, nerve to explain task to other man

-- 
Adam Tauno Williams, Network & Systems Administrator
Consultant - http://www.whitemiceconsulting.com
Developer - http://www.opengroupware.org


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Informix Schema -> PostgreSQL ?

[Adam Tauno Williams]

On Tue, 2007-07-03 at 12:22 -0400, Mark Fenbers wrote:
> I am an ex-Informix convert.  Informix used the term "schema" to refer 
> to the SQL-format definition of how a table or view was created.  E.g., 
> CREATE TABLE john ( char(8) lid, ...);  Some views we have are quite 
> complex (and not created by me) and I want to create a similar one in 
> Pg.  If I could see the view in this SQL format, then I could use SQL to 
> create another one using this as a template.
> 
> pgadmin3 can show this definition in SQL format, but I can't use 
> pgadmin3 on a certain box.  How can I show information in Pg (psql) the 
> way that Informix would show a schema?
> 
> BTW, what does PostgreSQL call this (what Informix calls a schema)??

Just use pg_dump to dump/backup the schema.  Don't include the data;  it
is just like doing an Informix "dbschema -d {database}"

pg_dump -U OGo --schema-only  OGo

-- 
Adam Tauno Williams, Network & Systems Administrator
Consultant - http://www.whitemiceconsulting.com
Developer - http://www.opengroupware.org


---(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] Informix Schema -> PostgreSQL ?

[Adam Tauno Williams]

> pg_dump dbname -s -t tableorindexname 

[Also an Informix DBA]  Is there a way to tweak the output of pg_dump
when used in this manner to omit the verbose commentary.

$ pg_dump OGo -s -t enterprise

--
-- Name: unique_enterprise_login; Type: INDEX; Schema: public; Owner:
OGo; Tablespace: 
--

CREATE UNIQUE INDEX unique_enterprise_login ON enterprise USING btree
("login");

The "--" lines just eats up screen real estate.

-- 
Adam Tauno Williams, Network & Systems Administrator
Consultant - http://www.whitemiceconsulting.com
Developer - http://www.opengroupware.org


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Converting from MS Access field aliases

[Adam Tauno Williams]

On Thu, 2007-07-12 at 13:23 -0400, Tom Lane wrote:
> chester c young <[EMAIL PROTECTED]> writes:
> >> SELECT field1 / 2 AS foo,
> >> field2 * 2 AS bar,
> >> foo + bar AS total
> >> WHERE foo < 12;
> > First, I think it would be great if this worked - like the alias to an
> > update table added in 8.2  - saves a lot of typing and makes queries
> > much more readable.
> This is not an "extension", it is *directly* contrary to both the letter
> and spirit of the SQL standard.  I can hardly believe that M$ did that
> ... oh, actually, I can entirely believe it.  The OP has a serious
> problem of vendor lockin now, and that's exactly what M$ wants.

"AS" works in Informix, and I believe, in DB2 as well.  So it is at
least pretty common;  I'm not saying it is correct.  Since Informix
predates M$-SQL they at least didn't invent it.


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate