[SQL] changing constraints

[David]

I tried adding a constraint thus:
de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
But get the msg:
ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint $2

de4=> \d genus
Table "public.genus"
 Column | Type  | Modifiers
+---+---
 genus_name | character varying(20) | not null
 gender | character varying(10) |
 cas_gen_number | integer   |
 family_name| character(7)  |
Indexes: genus_pkey primary key btree (genus_name)
Foreign Key constraints: $1 FOREIGN KEY (family_name) REFERENCES
family(family_name) ON UPDATE NO ACTION ON DELETE NO ACTION

I cant see a $2 constraint so why am i getting the error msg?

Many thanks Dave


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

Re: [SQL] changing constraints

[Achilleus Mantzios]

O kyrios David egrapse stis Mar 10, 2004 :

> I tried adding a constraint thus:
> de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
> But get the msg:
> ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint $2

Which pgsql version are you using??

In any case what you wrote means CHECK (gender='masculinefeminine')
you should write it as gender = 'masculine' OR gender = 'feminine'.

> 
> de4=> \d genus
> Table "public.genus"
>  Column | Type  | Modifiers
> +---+---
>  genus_name | character varying(20) | not null
>  gender | character varying(10) |
>  cas_gen_number | integer   |
>  family_name| character(7)  |
> Indexes: genus_pkey primary key btree (genus_name)
> Foreign Key constraints: $1 FOREIGN KEY (family_name) REFERENCES
> family(family_name) ON UPDATE NO ACTION ON DELETE NO ACTION
> 
> I cant see a $2 constraint so why am i getting the error msg?
> 
> Many thanks Dave
> 
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
> 

-- 
-Achilleus


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

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

Re: [SQL] changing constraints

[Richard Huxton]

On Wednesday 10 March 2004 12:27, David wrote:
> I tried adding a constraint thus:
> de4=> ALTER TABLE genus ADD CHECK(gender = 'masculine' || 'feminine');
> But get the msg:
> ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint $2

> I cant see a $2 constraint so why am i getting the error msg?

Firstly, $2 will be the automatically created name for this new constraint.
Secondly || doesn't mean OR - it joins strings.

Try something like (untested):
  ALTER TABLE genus ADD CONSTRAINT valid_gender CHECK (gender IN 
('masculine','feminine'));


-- 
  Richard Huxton
  Archonet Ltd

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

Re: [SQL] changing constraints

[David]

Cheers that worked fine, i guess its obvious im new to postgres (SQL in
general!), oh well you have to learn somehow

Dave

>
> Try something like (untested):
>   ALTER TABLE genus ADD CONSTRAINT valid_gender CHECK (gender IN
> ('masculine','feminine'));
>
>
> -- 
>   Richard Huxton
>   Archonet Ltd
>


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

[SQL] Break a report in Run Time

[Jander]

 

   
I have a application with a lof of reports. I need to break a report in Run 
Time.
How can I do this?
 
Thanks.
 
Jander Rebelo LuizAnalista de 
SistemastecSOFT - Tecnologia em Sistemas[EMAIL PROTECTED]Fone - (48) 
3025.2861

[SQL] Alter table

[David]

Ok another very newbie question. How can i change the data type a column can
accept? at the moment it will only take character(7) i want to change it to
varchar(30), but i cant figure how, ideas?

Many thanks Dave


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

[SQL] Changing primary keys

[David]

Is it possible to change the primary key of a relation? I want to add an
attribute, that i already have in the realtion, to the primary key (yes i
realise i designed my model pretty badly)


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

[SQL] randomized order in select?

[Enver ALTIN]

Hi,

I have got a simple table like this:

create table tips (
id integer primary key unique,
tiptext text
);

and, I've got a website where I'm willing to show these tips in a random
order. Each visitor will get a randomly selected tip. So for now, I have
to do 2 queries:

select id from tips

collect an ID list, choose one randomly and retrieve it. I wish I could
do something like:

select tiptext from tips order by random limit 1

in PostgreSQL.
-- 
 __
|  |
|  |  Enver ALTIN (a.k.a. skyblue)
|  |  Software developer, IT consultant
|FRONT |
|==|  FrontSITE Bilgi Teknolojisi A.Ş.
|_SITE_|  http://www.frontsite.com.tr/


signature.asc
Description: This is a digitally signed message part

Re: [SQL] randomized order in select?

[scott.marlowe]

On Wed, 10 Mar 2004, Enver ALTIN wrote:

> Hi,
> 
> I have got a simple table like this:
> 
> create table tips (
>   id integer primary key unique,
>   tiptext text
> );
> 
> and, I've got a website where I'm willing to show these tips in a random
> order. Each visitor will get a randomly selected tip. So for now, I have
> to do 2 queries:
> 
>   select id from tips
> 
> collect an ID list, choose one randomly and retrieve it. I wish I could
> do something like:
> 
>   select tiptext from tips order by random limit 1

You mean like:

select * from table order by random();

That Works as far back as 7.2, maybe before.


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

   http://archives.postgresql.org

Re: [SQL] randomized order in select?

[Bruno Wolff III]

On Wed, Mar 10, 2004 at 18:48:17 +0200,
  Enver ALTIN <[EMAIL PROTECTED]> wrote:
> 
> collect an ID list, choose one randomly and retrieve it. I wish I could
> do something like:
> 
>   select tiptext from tips order by random limit 1
> 
> in PostgreSQL.

You can but it won't be very efficient (for large tables) as it will generate
a random ordering for the whole table, probably do a sort and then return the
first record.  The only thing different you need to do is add () after random:
select tiptext from tips order by random() limit 1

If the number of tips isn't very large doing the above is probably best.

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

Re: [SQL] designer tool connect to PostgreSQL

[Robert Treat]

take a look at http://techdocs.postgresql.org/guides/GUITools 

Robert Treat

On Tue, 2004-03-09 at 02:53, BenLaKnet wrote:
> 
> Rekall ... 
> http://www.totalrekall.co.uk/ 
> (commercial website)
> http://www.rekallrevealed.org/ 
> (free sources)
> 
> for designing different RDBMS like mysql, postgresl, oracle and other
> ...
> free with sources ... 
> but packages for windows or linux are not free.
> 
> Ben
> 
> Yasir Malik a écrit : 
> 
> Well, there's phpPgAdmin. It's available at
> 
> http://phppgadmin.sourceforge.net/  
> 
> 
> 
> Yasir
> 
> 
> 
> On Mon, 8 Mar 2004  [EMAIL PROTECTED]   wrote:
> 
> 
> 
>   
> 
> Date: Mon, 8 Mar 2004 10:13:53 +0800
> 
> From:  [EMAIL PROTECTED]  
> 
> To:  [EMAIL PROTECTED]  
> 
> Subject: [SQL] designer tool connect to PostgreSQL
> 
> 
> 
> Hi,
> 
> 
> 
> i use postgresql as my database. does anyone know the designer tool that
> 
> can connect to postgeSQL ??? meaning to say the tools
> 
> can handle design task like create table , etc . appreciate if u can
> give
> 
> the specific URL. thanks in advance.
> 

-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


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

Re: [SQL] Alter table

[scott.marlowe]

On Wed, 10 Mar 2004, David wrote:

> Ok another very newbie question. How can i change the data type a column can
> accept? at the moment it will only take character(7) i want to change it to
> varchar(30), but i cant figure how, ideas?

While there are ways to tinker with the system catalogs to change between 
different text types / lengths, none of these are "officially supported" 
and may well screw up your database if you do something wrong.  I believe 
the archives likely have this question over and over in them.

The proper way to do this is to make a new column, put the old column in 
it, and then drop the old column:

create table test (a char(7));
insert a few thousand lines to test...;
begin;
alter table test add column b varchar(30);
update test set b=a;
alter table test drop column a;
commit; (or rollback; should things go horribly wrong...)
vacuum full test;



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

[SQL] Tsearch2 question: getting histogram of the vector elements

[Rajesh Kumar Mallah]

Greetings!

My original problem is to de duplicate a list of  around 0.3 million 
company names.

Since a company name can be potentially (mis)spelt in numerous ways 
exactmatch
obviously wont work.

To make the searches faster i am using tsearch. For each company name i 
want to
search other companies whose name is similar to the company in question.

Since inclusion of all the vector elements of a given company reduces the
chance of matching i am thinking of excluding the high frequency words
from the query.
Hence i need to find the high frequency elements like say 'consulting' , 
'limited' , 'Private'
'Industries' that occur commonly in company names.

In my table i have populated the co_name_vec feild as 
strip(to_tsvector(co_name))
can anyone help me analyzing the co_name_vec for the high frequency words?

Also i would like to know alternate / better solution to this problem.

Regds
Mallah.


SAMPLE DATA.

+-+--+
|   co_name   
|   co_name_vec|
+-+--+
| European Trade Partner & Consulting | 'trade' 
'consult' 'partner' 'european'   |
| Gulbrandsen Chemicals Pvt. Ltd. | 'ltd' 'pvt' 
'chemic' 'gulbrandsen'   |
| Govt. of Karnataka, Vision Group on Biotechnology   | 'govt' 'group' 
'vision' 'karnataka' 'biotechnolog'   |
| Digital Globalsoft Ltd. (A Hewlett Packard Company) | 'ltd' 'digit' 
'compani' 'hewlett' 'packard' 'globalsoft' |
| Shanon Construction Material Industries | 'materi' 
'shanon' 'industri' 'construct' |
| singpore india trade rsources company   | 'india' 'trade' 
'rsourc' 'compani' 'singpor' |
| RGV TELECOM CONSULTANTS PVT. LTD.   | 'ltd' 'pvt' 
'rgv' 'consult' 'telecom'|
| avid information search and documents (p) ltd.  | 'p' 'ltd' 'avid' 
'inform' 'search' 'document'|
| Tavant Technologies India (P) Ltd.  | 'p' 'ltd' 
'india' 'tavant' 'technolog'   |
| Maschinen Fabrik (India) Pvt. Ltd   | 'ltd' 'pvt' 
'india' 'fabrik' 'maschinen' |
| Manishri Refractories and Ceramics Pvt. Ltd.| 'ltd' 'pvt' 
'ceram' 'manishri' 'refractori'  |
| xavier export  import  management  institute| 'manag' 'export' 
'import' 'xavier' 'institut'|
| Best InformationTechnology ltd. | 'ltd' 'best' 
'informationtechnolog'  |
| FutureCalls Technology Private Limited  | 'limit' 'privat' 
'futurecal' 'technolog' |
| mak controls and systems pvt ltd| 'ltd' 'mak' 
'pvt' 'system' 'control' |
| NATIONAL RESEARCH CENTRE FOR CASHEW | 'centr' 'cashew' 
'nation' 'research' |
| The Madras Aluminium Company Ltd.   | 'ltd' 'madra' 
'compani' 'aluminium'  |
| Shriram Institute for Industrial Research   | 'shriram' 
'industri' 'institut' 'research'   |
| All India Carpet Trade Fair Committee   | 'fair' 'india' 
'trade' 'carpet' 'committe'   |
| Tuff Security & Allied Services | 'alli' 'tuff' 
'secur' 'servic'   |
+-+--+
(20 rows)

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

Re: [SQL] Inserting data in a table using sub-selects

[Stephan Szabo]

On Wed, 10 Mar 2004, Andreas Joseph Krogh wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hi, I'd like to fill one table with the contents of another table. Mye schema
> is like this:
>
> CREATE TABLE table1(
> id serial NOT NULL PRIMARY KEY,
> title varchar NOT NULL,
> description varchar
> );
>
> CREATE TABLE table2(
> id int NOT NULL REFERENCES(table1(id) ON DELETE CASCADE,
> content varchar NOT NULL
> );
>
> Now - is there a way I can do something like:
> INSERT INTO table2(id, content) values (select t1.id, coalesce(t1.title, '')
> || ' ' || coalesce(t1.description, '') as content from table1 t1);

I think you mostly just want to remove the values():

Insert into table2(id, content) select t1.id, coalesce(t1.title, '')
 || ' ' || coalesce(t1.description, '') as content from table t1;

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

Re: [SQL] Inserting data in a table using sub-selects]

[Marty Scholes]

INSERT INTO table2 (id, content) (
   SELECT id, coalesce(title, '') || ' ' || coalesce(description, '')
   FROM table1 t1);
If you want to keep them syncrhonized, in other words, rerun the query
over and over again without having to truncate table2 first or deleting
all of the rows, you can:
INSERT INTO table2 (id, content) (
   SELECT id, coalesce(title, '') || ' ' || coalesce(description, '')
   FROM table1 t1)
WHERE id NOT IN (
   SELECT id
   FROM table1);
With Oracle there is a slick way to do a partial outer join that allowed
you to do this without creating a complete list of table1.id in the last
subquery, but I dunno if Pg has an equivalent mechanism.


Andreas Joseph Krogh wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hi, I'd like to fill one table with the contents of another table. 
Mye schema
> is like this:
>
> CREATE TABLE table1(
> id serial NOT NULL PRIMARY KEY,
> title varchar NOT NULL,
> description varchar
> );
>
> CREATE TABLE table2(
> id int NOT NULL REFERENCES(table1(id) ON DELETE CASCADE,
> content varchar NOT NULL
> );
>
> Now - is there a way I can do something like:
> INSERT INTO table2(id, content) values (select t1.id, 
coalesce(t1.title, '')
> || ' ' || coalesce(t1.description, '') as content from table1 t1);
>
> Any hints on how to insert a lot of values at the same time like this?
>
> - --
> Andreas Joseph Krogh <[EMAIL PROTECTED]>
> Managing Director, Senior Software Developer
> OfficeNet AS
>
> I always do a CVS update before making a patch (unless I forget).
>
> gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.2.2 (GNU/Linux)
>
> iD8DBQFAT3H1UopImDh2gfQRAsa6AJ9jZjNz25w4iVnxNJYY9LJuG0HBLACfZfup
> 1TMzQSi1+YYgNjpcampX6wo=
> =fJA3
> -END PGP SIGNATURE-
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Break a Report in Run Time

[Jander Rebelo Luiz]

I have a apllication with a lot of reports, where I need to break a 
process report in run time. How Can I do this?

Thanks.

Jander.

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

Re: [SQL] Changing primary keys

[Jonathan Gardner]

On Wednesday 10 March 2004 09:17 am, David wrote:
> Is it possible to change the primary key of a relation? I want to add an
> attribute, that i already have in the realtion, to the primary key (yes i
> realise i designed my model pretty badly)
>

It sure is.

First, ensure that the values are indeed not NULL and unique.
Next, alter the table to drop the primary key. (See ALTER TABLE).
Then, alter the table to add the new primary key.

If you have other tables that have a foreign key references to this table, 
they may have to change.

I don't need to tell you to think really hard about schema changes and the 
impact it will have on the application before you do stuff. And be sure you 
are not doing this on the production database without testing it first!

My personal preference would be to add the attribute, and then make a new 
primary key column. I really don't like multi-column primary keys as they 
are a bit more difficult to use. Even if the boss wants to use the old-pk + 
attribute as the pk, you will use the new pk as the actual pk for joins and 
such. You may put a not null unique constraint on the old-pk + attribute 
combination so that you are guaranteed that the data won't violate the 
boss's idea of what the pk should be.

-- 
Jonathan Gardner
[EMAIL PROTECTED]

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

   http://archives.postgresql.org

Re: [SQL] Break a report in Run Time

[Jonathan Gardner]

On Wednesday 10 March 2004 10:23 am, Jander wrote:
>I have a application with a lof of reports. I need to
> break a report in Run Time. How can I do this?
>

Could you clarify what you mean by "break a report in run time"?

-- 
Jonathan Gardner
[EMAIL PROTECTED]

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

Re: [SQL] Inserting data in a table using sub-selects]

[Andreas Joseph Krogh]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wednesday 10 March 2004 21:56, Marty Scholes wrote:
> INSERT INTO table2 (id, content) (
> SELECT id, coalesce(title, '') || ' ' || coalesce(description, '')
> FROM table1 t1);
>
> If you want to keep them syncrhonized, in other words, rerun the query
> over and over again without having to truncate table2 first or deleting
> all of the rows, you can:
>
> INSERT INTO table2 (id, content) (
> SELECT id, coalesce(title, '') || ' ' || coalesce(description, '')
> FROM table1 t1)
> WHERE id NOT IN (
> SELECT id
> FROM table1);
>
> With Oracle there is a slick way to do a partial outer join that allowed
> you to do this without creating a complete list of table1.id in the last
> subquery, but I dunno if Pg has an equivalent mechanism.

Thanks, works perfectly.

- -- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Managing Director, Senior Software Developer
OfficeNet AS

I always do a CVS update before making a patch (unless I forget).

gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFAT5fCUopImDh2gfQRArmFAJ4qx31Wx31sehCBo7qfjPwwPWvUzACguw9t
H8mWrl+TsdYIhmQ+FDu41t8=
=qj1H
-END PGP SIGNATURE-

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

[SQL] About pg_dump

[Daniel Henrique Alves Lima]

   Hi, everybody !

   I don't know if this is the best list to ask this question but if it 
doesn't, please forgive me (should i try pgsl-general, maybe ?)...
   We have a postgresql database in production and i've 2 questions 
about pg_dumpall boring me. We "dump" both ddl and data as SQL commands, 
so :

   1. Does the created sql script respect the database encoding (will 
chars be writen as utf-8 chars or iso-8859-1 char or ...) ?
   2. We've a bytea in one table. What exactly happens with the dumped 
data ? Is there any trouble with it ?

   I believe that we must to worry now (earlier). I don't want to 
expect until we must to restore database backup to descover that dumping 
data is corrupted :-)

Thanks in advance 

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

Re: [SQL] Converting query to view - duplicate fields - solved

[Richard Grosse]

At 02:17 10/03/2004, you wrote:
On Tue, 9 Mar 2004, Richard Grosse wrote:

> Trying to convert the query below to a view. The problem is
> despite it working as a query when trying to save it as a
> view  the database returns the error that the field tablealias.cmpname
> is duplicated. (Which it is and has to be)
You're going to need to use column aliases to alias (for example)
tbldeliver.cmpname, tblsender.cmpname, tblhaulier.cmpname to different
aliases in the view, perhaps
"tbldeliver.cmpname" "tblsender.cmpname" and "tblhaulier.cmpname"
(note the double quotes).
Great just what I needed to know. Works fine now :-)

Thanks

Richard



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

Re: [SQL] randomized order in select?

[Enver ALTIN]

On Wed, 2004-03-10 at 13:02 -0600, Bruno Wolff III wrote:
> You can but it won't be very efficient (for large tables) as it will generate
> a random ordering for the whole table, probably do a sort and then return the
> first record.  The only thing different you need to do is add () after random:
> select tiptext from tips order by random() limit 1

Thanks. The whole table consists of about 100 rows and I don't think it
will grow by, even twice. So this fits my needs just well.
-- 
 __
|  |
|  |  Enver ALTIN (a.k.a. skyblue)
|  |  Software developer, IT consultant
|FRONT |
|==|  FrontSITE Bilgi Teknolojisi A.Ş.
|_SITE_|  http://www.frontsite.com.tr/


signature.asc
Description: This is a digitally signed message part

Re: [SQL] designer tool connect to PostgreSQL

[azwa]

Hi,

  thanks to all for the responnd...i've look into all the tools mentioned and found many tools which has a great feautre such as designing/modelling
the object/table but it seems look like that there is no tools that can do the ETL process. thanks in advance







Rich Hall <[EMAIL PROTECTED]>

03/08/2004 10:55 AM EST


        To:        [EMAIL PROTECTED]
        cc:        
        Subject:        Re: [SQL] designer tool connect to PostgreSQL


EMS has PGManager, a great tool.

Rick

[EMAIL PROTECTED] wrote:

>
> Hi,
>
> i use postgresql as my database. does anyone know the designer tool 
> that can connect to postgeSQL ??? meaning to say the tools
> can handle design task like create table , etc . appreciate if u can 
> give the specific URL. thanks in advance. 


-- 
_
Richard Hall
Developer
MicroPatent LLC
250 Dodge Avenue
East Haven, Connecticut 06512
Tel:   203.868.3321
Fax:   203.466.5054
Eml:   [EMAIL PROTECTED]
Web:   www.micropat.com

MicroPatent is an Information Holdings Inc. company (NYSE:IHI).

Re: [SQL] randomized order in select?

[Iain]

If you have a lot of tips, you could create a unique indexed tip number
column. Select the highest tip number using:

select tip_number from tips order by tip_number desc limit 1;

Then generate a random number and select using that tip_number.

Of course, you would have to allow for the possibility of missing tip
numbers, by repeating the random number generation/read sequence until you
find something. Since the tip_number isn't the PK of the table, you can
regenerate the tip numbers to eliminate holes from deletions any time you
like. Just reset the sequence to 1 and update all rows with the
nextval(tipnumber_seq).

Sounds like a lot of work to me though...


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

   http://archives.postgresql.org

Re: [SQL] randomized order in select?

[Tom Lane]

Bruno Wolff III <[EMAIL PROTECTED]> writes:
>   Enver ALTIN <[EMAIL PROTECTED]> wrote:
>> I wish I could do something like:
>> select tiptext from tips order by random limit 1
>> in PostgreSQL.

> You can but it won't be very efficient (for large tables) as it will generate
> a random ordering for the whole table, probably do a sort and then return the
> first record.  The only thing different you need to do is add () after random:
> select tiptext from tips order by random() limit 1

If you do need to do this for a large table, I seem to recall that
we've previously worked out reasonable ways to select a random entry
efficiently using an index.  Dig around in the mail list archives for
details.

regards, tom lane

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