Re: [SQL] add column if doesn't exist

[Brandon Metcalf]

p == [EMAIL PROTECTED] writes:

 p> Brandon Metcalf wrote:
 p> > Is there a way to check for the existence of a column in a table
 p> > other than, say, doing a SELECT on that column name and checking the
 p> > output?

 p> SELECT * FROM information_schema.columns;

 p> Customize to taste.


Yes, that's what I'm looking for.  Thanks.

Now, is there a way to mix PostgreSQL commands and SQL and do
something like

  ALTER TABLE foo ADD COLUMN bar WHERE EXISTS(SELECT * FROM
information_schema.columns WHERE ...)

?

-- 
Brandon

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

   http://archives.postgresql.org

Re: [SQL] how to do 'deep queries'?

[Daryl Richter]

Anthony Molinaro wrote:

that query is 100% correct.
 
it's just an equijoin (a type of inner join) between 3 tables.
 
the syntax you show is how queries should be written and is more

representative of what a joins between relations really are:
Cartesian products with filters applied
 
the ansi syntax, the explicit JOIN ... ON  stuff is (imho) unnecessary,

useful only for outer joins since all the vendors did it differently.



Whether you feel that is unnecessary or not, it *is* the ANSI Standard 
and is thus, by definition, "how queries should be written."


In addition to cleaning up the outer join issue, it was added to make 
the *intention* of the query clearer.


Because others are likely to read your query many more times than you 
write it, clarity of intent *is* important.



what you have will work for postgreSQL, I used the syntax you show in my
book
for every single join recipe except for outjoins.
 
are you seeing errors?
 
regards,

  Anthony


[original snipped]

--
Daryl

"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776


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

Re: [SQL] Updating cidr column with network operator

[Daryl Richter]

Michael Fuhr wrote:
> On Mon, Sep 26, 2005 at 12:34:59PM +0200, Axel Rau wrote:
>
>>Am 26.09.2005 um 02:05 schrieb Michael Fuhr:
>>
>>>On Fri, Sep 23, 2005 at 09:19:25PM +0200, Axel Rau wrote:
>>>
I'm sure this would be the cleanest solution but remember networks
change.
>>>
>>>Yes, which is why it's a good idea to automatically propogate those
>>>changes to tables that maintain redundant data.
>>
>>I would not call it redundant but normalized, because network has some
>>attributes, common to all addresses in the net, 1st of all the netmask.
>
>
> An attribute is redundant if it repeats a fact that can be learned
> without it.  If one table contains IP addresses and another contains
> networks, then you can associate IP addresses and networks with a
> join of the two tables; indeed, this is how the "fix the network
> column" update works.  Having a network column in the address table
> simply repeats what could be learned through the join.
>
>

I agree with Michael here.  I think the fundamental problem with your 
schema is that it is possible to have contradictory data between the 
network and address table, always a bad situation.


I would replace network.id with a serial type value and make the cidr a 
separate column, for example:


CREATE TABLE network (
  id  int not null  PRIMARY KEY,
  address cidrnot null,
  attr1   varchar(10) null
);

CREATE TABLE address (
  id inetPRIMARY KEY,
  networkint NOT NULL
  REFERENCES network
);

insert into network( id, address, attr1 ) values( 1, '10.1', 'a' );
insert into network( id, address, attr1 ) values( 2, '10.2', 'b' );
go

insert into address( id, network ) values( '10.1.0.1', 1 );
insert into address( id, network ) values( '10.1.0.2', 1 );
insert into address( id, network ) values( '10.1.0.3', 1 );
-- OOPS!
insert into address( id, network ) values( '10.2.0.4', 1 );
go

-- This will "correct" the any addresses put in the wrong network
update address
set network = ( select id from network where address >> address.id )


[additional network stuff snipped]

--
Daryl

"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776



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

  http://archives.postgresql.org

Re: [SQL] add column if doesn't exist

[Chris Browne]

[EMAIL PROTECTED] ("Brandon Metcalf") writes:

> p == [EMAIL PROTECTED] writes:
>
>  p> Brandon Metcalf wrote:
>  p> > Is there a way to check for the existence of a column in a table
>  p> > other than, say, doing a SELECT on that column name and checking the
>  p> > output?
>
>  p> SELECT * FROM information_schema.columns;
>
>  p> Customize to taste.
>
>
> Yes, that's what I'm looking for.  Thanks.
>
> Now, is there a way to mix PostgreSQL commands and SQL and do
> something like
>
>   ALTER TABLE foo ADD COLUMN bar WHERE EXISTS(SELECT * FROM
> information_schema.columns WHERE ...)
>
> ?

I set up a stored procedure to do this for Slony-I...  Replace
@NAMESPACE@ with your favorite namespace, and slon_quote_brute can
likely be treated as an identity function unless you use silly
namespace names :-).

create or replace function @[EMAIL PROTECTED] (text, text, text, text) 
returns bool as '
DECLARE
  p_namespace alias for $1;
  p_table alias for $2;
  p_field alias for $3;
  p_type  alias for $4;
  v_row   record;
  v_query text;
BEGIN
  select 1 into v_row from pg_namespace n, pg_class c, pg_attribute a
 where @[EMAIL PROTECTED](n.nspname) = p_namespace and 
 c.relnamespace = n.oid and
 @[EMAIL PROTECTED](c.relname) = p_table and
 a.attrelid = c.oid and
 @[EMAIL PROTECTED](a.attname) = p_field;
  if not found then
raise notice ''Upgrade table %.% - add field %'', p_namespace, p_table, 
p_field;
v_query := ''alter table '' || p_namespace || ''.'' || p_table || '' add 
column '';
v_query := v_query || p_field || '' '' || p_type || '';'';
execute v_query;
return ''t'';
  else
return ''f'';
  end if;
END;' language plpgsql;

comment on function @[EMAIL PROTECTED] (text, text, text, text) 
is 'Add a column of a given type to a table if it is missing';

-- 
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/sgml.html
"The  surest  sign  that  intelligent  life exists  elsewhere  in  the
universe is that it has never tried to contact us."
-- Calvin and Hobbes

---(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] how to do 'deep queries'?

[Anthony Molinaro]

Daryl,
 
> Whether you feel that is unnecessary or not, it *is* the ANSI Standard

> and is thus, by definition, "how queries should be written."  
  
I disagree 100%.  Oracle and db2 introduced window functions years
before
Ansi added them. Should we not have used them? It absurd to avoid using
a feature cuz it's not ansi.

Honestly, Don't be a slave to ansi, you miss out on all the great
vendor specific functionality *that you're already paying for*

> it was added to make the *intention* of the query clearer.

More clearer to whom? 

Certainly not developers who have been working for many years 
using the old syntax. 

The intention of the old syntax is perfect. Realize that the problem is
not the old syntax, the problem is the watered down database field
today. 
I see this more and more with each interview I conduct looking 
for dba's and developers.

You know, it used to be that database developers 
had a solid background in math and relational theory.   
Sadly, that's not the case anymore...

   select * from a,b where a.id=b.id  

Suggests a Cartesian product between two relations then a filter to keep
only matching rows. 

That's a join. And that syntax is a *perfect* representation of it.

So to whom is ansi more clear? To the person who knows nothing about
databases and decided one day to get a certification and call themselves
an expert?

Or maybe the person who decided one day on a whim to get into databases
and not realize that tons of code from the prior decade use the old
style syntax?

> Because others are likely to read your query many more times than you 
> write it, clarity of intent *is* important.

I've never worked in a place that used ANSI only syntax and I've never
had a problem with clarity nor any developers I've worked with.
So, I don't at all get what you're saying...

Old style is short and sweet and perfect. 
Ansi dumbed it down, that's the bottom line.
And for people who've been developing for sometime,
It's wholly unnecessary.

Regards,
  Anthony

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Daryl Richter
Sent: Tuesday, September 27, 2005 9:24 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] how to do 'deep queries'?

Anthony Molinaro wrote:
> that query is 100% correct.
>  
> it's just an equijoin (a type of inner join) between 3 tables.
>  
> the syntax you show is how queries should be written and is more
> representative of what a joins between relations really are:
> Cartesian products with filters applied
>  
> the ansi syntax, the explicit JOIN ... ON  stuff is (imho)
unnecessary,
> useful only for outer joins since all the vendors did it differently.
>

Whether you feel that is unnecessary or not, it *is* the ANSI Standard 
and is thus, by definition, "how queries should be written."

In addition to cleaning up the outer join issue, it was added to make 
the *intention* of the query clearer.

Because others are likely to read your query many more times than you 
write it, clarity of intent *is* important.

> what you have will work for postgreSQL, I used the syntax you show in
my
> book
> for every single join recipe except for outjoins.
>  
> are you seeing errors?
>  
> regards,
>   Anthony

[original snipped]

-- 
Daryl

"We want great men who, when fortune frowns, will not be discouraged."
 -- Colonel Henry Knox, 1776


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

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

   http://archives.postgresql.org

Re: [SQL] Updating cidr column with network operator

[Axel Rau]

Am 27.09.2005 um 16:02 schrieb Daryl Richter:

> An attribute is redundant if it repeats a fact that can be learned
> without it.  If one table contains IP addresses and another contains
> networks, then you can associate IP addresses and networks with a
> join of the two tables; indeed, this is how the "fix the network
> column" update works.  Having a network column in the address table
> simply repeats what could be learned through the join.
>
>

I agree with Michael here.  I think the fundamental problem with your schema is that it is possible to have contradictory data between the network and address table, always a bad situation.

I would replace network.id with a serial type value and make the cidr a separate column, for example:

CREATE TABLE network (
id  int not null  PRIMARY KEY,
address cidrnot null,
attr1   varchar(10) null
);

CREATE TABLE address (
id inetPRIMARY KEY,
networkint NOT NULL
REFERENCES network
);

I agree with Michael too, but I understand him differently: What he says is: 
"Get rid of the redundancy",
which means to me:
"remove the fk from address to network completly". 
The attribute "network" is not realy needed because we can always join
address.id << network.id
This reduces the necessary logic to keep things consistent. I still can have
my cascaded delete in network, have to do it with a trigger.
I'm currently looking at performance issues.

Introducing a synthetic pk in network does not really make things easier.
Instead I introduced an insert/update trigger which prevents from overlaps
in network  (which is not as a matter of course for cidr columns, I have learnt):

CREATE OR REPLACE FUNCTION dd.ids_network_update_insert_check() RETURNS TRIGGER AS $$
BEGIN   -- check if new net overlapps with existing one
PERFORM N.id FROM network N WHERE NEW.id << N.id OR NEW.id >> N.id;
IF FOUND THEN
RAISE EXCEPTION '?Attempt to insert overlapping network %', NEW.id;
RETURN NULL;
END IF;
RETURN NEW;  
END; 
$$ LANGUAGE 'plpgsql';

Axel

Axel Rau, Frankfurt, Germany   +49-69-951418-0

Re: [SQL] Updating cidr column with network operator

[Daryl Richter]

[EMAIL PROTECTED] wrote:


Am 27.09.2005 um 16:02 schrieb Daryl Richter: 




An attribute is redundant if it repeats a fact that can be learned 
without it.  If one table contains IP addresses and another contains 
networks, then you can associate IP addresses and networks with a 
join of the two tables; indeed, this is how the "fix the network 
column" update works.  Having a network column in the address table 
simply repeats what could be learned through the join. 





I agree with Michael here.  I think the fundamental problem with your
schema is that it is possible to have contradictory data between the
network and address table, always a bad situation. 


I would replace network.id with a serial type value and make the cidr a
separate column, for example: 

CREATE TABLE network ( 
 id  int not null  PRIMARY KEY, 
 address cidrnot null, 
 attr1   varchar(10) null 
); 

CREATE TABLE address ( 
 id inetPRIMARY KEY, 
 networkint NOT NULL 
 REFERENCES network 
); 





I agree with Michael too, but I understand him differently: What he says is:

	"Get rid of the redundancy", 
which means to me: 
	"remove the fk from address to network completly".  
The attribute "network" is not realy needed because we can always join 
	address.id << network.id 
This reduces the necessary logic to keep things consistent. I still can have


my cascaded delete in network, have to do it with a trigger. 
I'm currently looking at performance issues. 

Introducing a synthetic pk in network does not really make things easier. 
Instead I introduced an insert/update trigger which prevents from overlaps 
in network  (which is not as a matter of course for cidr columns, I have
learnt): 



Ok, I guess, but isn't tit true now that you can insert a new address 
row which doesn't belong to any valid network??



CREATE OR REPLACE FUNCTION dd.ids_network_update_insert_check() RETURNS
TRIGGER AS $$ 
BEGIN   -- check if new net overlapps with existing one 
PERFORM N.id FROM network N WHERE NEW.id << N.id OR NEW.id >> N.id; 
IF FOUND THEN 
RAISE EXCEPTION '?Attempt to insert overlapping network %',
NEW.id; 
RETURN NULL; 
END IF; 
RETURN NEW;   
END;  
$$ LANGUAGE 'plpgsql'; 

Axel 

Axel Rau, Frankfurt, Germany   +49-69-951418-0 



--
Daryl

"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776


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

  http://archives.postgresql.org

Re: [SQL] Updating cidr column with network operator

[Axel Rau]

Am 27.09.2005 um 17:02 schrieb Daryl Richter:



Ok, I guess, but isn't tit true now that you can insert a new address 
row which doesn't belong to any valid network??
Yes, I can. But in the earlier approach, the fk pointed at a special 
row in network ("UNKNOWN"), which maks no big difference.
It's the nature of my ids/abuse application, that there are always 
faked source addresses, which may not belong to any routed net.


Axel

Axel Rau, Frankfurt, Germany   +49-69-951418-0


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

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

Re: [SQL] Why doesn't the SERIAL data type automatically have a

[Scott Marlowe]

On Mon, 2005-09-26 at 20:03, Tom Lane wrote:
> Ferindo Middleton Jr <[EMAIL PROTECTED]> writes:
> > Is there some reason why the SERIAL data type doesn't automatically have 
> > a UNIQUE CONSTRAINT.
> 
> It used to, and then we decoupled it.  I don't think "I have no use for
> one without the other" translates to an argument that no one has a use
> for it ...

I have to admit, right after the change was made, I was of the opinion
that no one would ever need that.  Then, a few months later, it was
exactly what I needed for some project...  :)

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

   http://archives.postgresql.org

Re: [SQL] how to do 'deep queries'?

[Daryl Richter]

Anthony Molinaro wrote:
> Daryl,
>
>
>>Whether you feel that is unnecessary or not, it *is* the ANSI Standard
>
>
>>and is thus, by definition, "how queries should be written."
>
>
> I disagree 100%.  Oracle and db2 introduced window functions years
> before
> Ansi added them. Should we not have used them? It absurd to avoid using
> a feature cuz it's not ansi.
>

Of course it would be absurd, I have not suggested otherwise.  Joins are 
not a *new* feature.


> Honestly, Don't be a slave to ansi, you miss out on all the great
> vendor specific functionality *that you're already paying for*
>
>
>>it was added to make the *intention* of the query clearer.
>
>
> More clearer to whom?
>
> Certainly not developers who have been working for many years
> using the old syntax.
>
> The intention of the old syntax is perfect. Realize that the problem is
> not the old syntax, the problem is the watered down database field
> today.
> I see this more and more with each interview I conduct looking
> for dba's and developers.
>

I generally agree with your assessment of the state of database 
knowledge (particularly re developers).  It is, however, the reality we 
live in.


[snipped nostalgia and back-patting]

> I've never worked in a place that used ANSI only syntax and I've never
> had a problem with clarity nor any developers I've worked with.
> So, I don't at all get what you're saying...

> Old style is short and sweet and perfect.
> Ansi dumbed it down, that's the bottom line.
> And for people who've been developing for sometime,
> It's wholly unnecessary.
>

Well, perhaps you will one day and a developer will hose your server 
with a "accidental" cross join and then you will understand.


But hopefully not.  ;)

> Regards,
>   Anthony
>

[rest snipped]

--
Daryl
Director of Technology

(( Brandywine Asset Management  )
 ( "Expanding the Science of Global Investing"  )
 (  http://www.brandywine.com   ))



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

Re: [SQL] how to do 'deep queries'?

[Anthony Molinaro]

> Well, perhaps you will one day and a developer will hose your server 
> with a "accidental" cross join and then you will understand.

Hehe :)) 

hey man, that's what testing and code review is all about
(dev teams still do that don't they?)

Accidental cartesians don't get to production ;)

Regards,
  Anthony

-Original Message-
From: Daryl Richter [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 27, 2005 11:35 AM
To: Anthony Molinaro
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] how to do 'deep queries'?

Anthony Molinaro wrote:
 > Daryl,
 >
 >
 >>Whether you feel that is unnecessary or not, it *is* the ANSI
Standard
 >
 >
 >>and is thus, by definition, "how queries should be written."
 >
 >
 > I disagree 100%.  Oracle and db2 introduced window functions years
 > before
 > Ansi added them. Should we not have used them? It absurd to avoid
using
 > a feature cuz it's not ansi.
 >

Of course it would be absurd, I have not suggested otherwise.  Joins are

not a *new* feature.

 > Honestly, Don't be a slave to ansi, you miss out on all the great
 > vendor specific functionality *that you're already paying for*
 >
 >
 >>it was added to make the *intention* of the query clearer.
 >
 >
 > More clearer to whom?
 >
 > Certainly not developers who have been working for many years
 > using the old syntax.
 >
 > The intention of the old syntax is perfect. Realize that the problem
is
 > not the old syntax, the problem is the watered down database field
 > today.
 > I see this more and more with each interview I conduct looking
 > for dba's and developers.
 >

I generally agree with your assessment of the state of database 
knowledge (particularly re developers).  It is, however, the reality we 
live in.

[snipped nostalgia and back-patting]

 > I've never worked in a place that used ANSI only syntax and I've
never
 > had a problem with clarity nor any developers I've worked with.
 > So, I don't at all get what you're saying...

 > Old style is short and sweet and perfect.
 > Ansi dumbed it down, that's the bottom line.
 > And for people who've been developing for sometime,
 > It's wholly unnecessary.
 >

Well, perhaps you will one day and a developer will hose your server 
with a "accidental" cross join and then you will understand.

But hopefully not.  ;)

 > Regards,
 >   Anthony
 >

[rest snipped]

-- 
Daryl
Director of Technology

(( Brandywine Asset Management  )
  ( "Expanding the Science of Global Investing"  )
  (  http://www.brandywine.com   ))



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

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

[SQL] changing a column's position in table, how do you do that

[Ferindo Middleton Jr]

Is there a way to change the position attribute of a column in a table? 
I have data that I need to import into various tables in my db on a 
consistent basis... I usually us e the COPY ... FROM query but I can't 
control the -order- of the fields my client dumps the data so I would 
like to be able to change the position the columns in my table to be 
able to better align the data to be imported with the format of my 
table. I was thinking I could do something like "ALTER TABLE ALTER 
COLUMN ... " or something like that to change the columns position in 
the table but I can't figure out how.


Ferindo

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

Re: [SQL] changing a column's position in table, how do you do that

[Stewart Ben (RBAU/EQS4) *]

Ferindo,

> Is there a way to change the position attribute of a column 
> in a table? 

AFAIK, there's no way to change this easily. The best way to do it would
be as follows:

BEGIN WORK;
LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE;
ALTER TABLE mytable ADD COLUMN col_to_move_2 coltype;
UPDATE mytable SET col_to_move_2 = col_to_move;
ALTER TABLE mytable DROP COLUMN col_to_move;
ALTER TABLE mytable RENAME col_to_move_2 TO col_to_move;
COMMIT WORK;

Best regards,

Ben Stewart

--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:[EMAIL PROTECTED]
http://www.bosch.com.au/ 

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

Re: [SQL] changing a column's position in table, how do you do that

[Chris Browne]

[EMAIL PROTECTED] (Ferindo Middleton Jr) writes:
> Is there a way to change the position attribute of a column in a
> table? I have data that I need to import into various tables in my db
> on a consistent basis... I usually us e the COPY ... FROM query but I
> can't control the -order- of the fields my client dumps the data so I
> would like to be able to change the position the columns in my table
> to be able to better align the data to be imported with the format of
> my table. I was thinking I could do something like "ALTER TABLE ALTER
> COLUMN ... " or something like that to change the columns position in
> the table but I can't figure out how.

If you're running PostgreSQL 7.4 or later, you can specify field names
in the COPY statement.  That strikes me as a better way to control
this.
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
Rules of the Evil Overlord #65.  "If I must have computer systems with
publically available  terminals, the maps  they display of  my complex
will have  a room clearly marked  as the Main Control  Room. That room
will be  the Execution Chamber. The  actual main control  room will be
marked as Sewage Overflow Containment." 

---(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