Re: [SQL] stored procedures: sybase -> postgreSQL ?
On Mon, 9 Sep 2002 18:16:07 + (UTC), [EMAIL PROTECTED] (Charles Hauser) wrote: >I am trying to port a Sybase table create script to one usable for >postgreSQL. > >(note I am not a DBA) > >In particular I am not well versed on how to use/recode the stored >procedures such as that in the example below. > >ALTER TABLE DnaFragment >ADD PRIMARY KEY (dna_fragment_id) >go > > exec sp_primarykey DnaFragment, > dna_fragment_id >go > > exec sp_bindrule DnaFragment_type_rule, 'DnaFragment.type' > exec sp_bindefault Set_To_Current_Date, >'DnaFragment.date_last_modified' > exec sp_bindefault Set_to_False, 'DnaFragment.is_obsolete' >go Postgresql and Sybase are a lot different in many ways. Lot of the Postgresql is clearly borrowed from oracle ways of thinking (mind that this is not neccessary better or worse, it is different). When using standard SQL, the differences are not really much. Postgresql uses that ANSI outer join syntax instead of the Sybase =* notation. But when you start using Sybase specific features the differences will become more clean. Rules and defaults are not defined as seperate entities, so you have to define them inside the table definitions. The alter table statement is by the way explained on http://www.postgresql.org/idocs/index.php?sql-altertable.html. If you want to convert one database definition to another, you might want to use a tool like Sybase Powerdesigner. You can read the Sybase definition, and create a Postgresql definition from there. An evaluation version (fully functional for a limited time) is downloadable from the Sybase website -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen [EMAIL PROTECTED] http://www.xs4all.nl/~reinoud ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Help
Hallo ! I have problem find out distribution of PostgreSQL for windows. Can you help me, or send me some url links, where I will find it. Thanks. Juraj S, --- Bc. Šimko Juraj Juraja Kréna 6 915 01 Nové Mesto nad Váhom Home: 032 7714271 Office: 032 7714281 kl. 246 GSM: 0903 100 173 Web: www.dzimo.sk Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] POSIX-style regular expressions
Hi
Can anybody help me with POSIX-style regular expression used to check rules
that new data must satisfy for an insert or update operetion.
Table was created as follows.
CREATE TABLE table_name1 (
id1char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
id2INT NOT NULL,
CONSTRAINT primary_key
PRIMARY KEY (id1, id2),
CONSTRAINT id2_exists
FOREIGN KEY (id2) REFERENCES table_name2
ON DELETE CASCADE
ON INSERT CASCADE
);
Any id1 that looks like
1.2.
1.12.
12.1.
12.12.
should be inserted into table, but the insert query was rejected with an
error message
ERROR:ExecAppend: rejected due to CHECK constraint table_name1_id1
I tested preceding regular expression with Perl and JavaScript and it worked
fine. Can I use regular expressions with CHECK parametar, and if so, how can
I make it work.
The platform PostgreSQL is installed is Solaris 8 - Intel.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] POSIX-style regular expressions
On Tue, 10 Sep 2002, Goran Buzic wrote:
> id1char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
> ERROR:ExecAppend: rejected due to CHECK constraint table_name1_id1
>
> I tested preceding regular expression with Perl and JavaScript and it worked
> fine. Can I use regular expressions with CHECK parametar, and if so, how can
> I make it work.
You should probably test it against PostgreSQL's regex engine. What you
may not know is that they all have different syntaxes, rules and quirks.
What works in one may or may not work in another.
So check out src/backend/regex and build retest (I think that's what it
was called). It's a command line regex tester (obviously against
PostgreSQL's implementation).
Joshua b. Jore -{ weird geeky madness }-> http://www.greentechnologist.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] stored procedures: sybase -> postgreSQL ?
On Mon, Sep 09, 2002 at 11:02:27PM +0200, Reinoud van Leeuwen wrote: > On Mon, 9 Sep 2002 18:16:07 + (UTC), [EMAIL PROTECTED] (Charles > Hauser) wrote: > > >I am trying to port a Sybase table create script to one usable for > >postgreSQL. > > > >(note I am not a DBA) > > > >In particular I am not well versed on how to use/recode the stored > >procedures such as that in the example below. > > > >ALTER TABLE DnaFragment > >ADD PRIMARY KEY (dna_fragment_id) > >go > > > > exec sp_primarykey DnaFragment, > > dna_fragment_id > >go > > > > exec sp_bindrule DnaFragment_type_rule, 'DnaFragment.type' > > exec sp_bindefault Set_To_Current_Date, > >'DnaFragment.date_last_modified' > > exec sp_bindefault Set_to_False, 'DnaFragment.is_obsolete' > >go As Reinoud hinted at, these aren't really stored procedures: they're setting up defaults and constraints, which PostgreSQL does in a more SQL standard manner. This specific example would probably translate like so - note that you don't show the table schema or rule definitions, so I have to guess at column types and there probably are other columns. CREATE TABLE DnaFragment ( dna_fragment_id INT PRIMARY KEY, type INT CHECK ([an expression equivalent to DnaFragment_type_rule]), is_obsolete BOOL DEFAULT 'f', date_last_modified DATE DEFAULT current_date) depending on what DnaFragment_type_rule does, it might just be a foreign key reference (change CHECK (expression) to REFERENCES table (column) ) You might want to upgrade the date to a timestamp field, to get finer grained information on modifications. If you're not interested in learning a fair amount of DB theory, using some sort of automated tool may in fact be the answer. On the other hand, knowing _exactly_ how the data is structured/stored can lead to a better understanding of what sort of queries are trivial, and what sort are impossible. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] POSIX-style regular expressions
On Tue, 10 Sep 2002, Goran Buzic wrote:
> Hi
>
> Can anybody help me with POSIX-style regular expression used to check rules
> that new data must satisfy for an insert or update operetion.
>
> Table was created as follows.
>
> CREATE TABLE table_name1 (
> id1char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
Bad column type choice. char(6) is space padded, so 1.2. looks like
"1.2. ". For me, 12.12. works, but any shorter one fails. Unless
you need space padding elsewhere, you may just want to use varchar(6);
---(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] POSIX-style regular expressions
On Tue, Sep 10, 2002 at 08:35:27AM -0500, Josh Jore wrote:
> On Tue, 10 Sep 2002, Goran Buzic wrote:
>
> > id1char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
>
> > ERROR:ExecAppend: rejected due to CHECK constraint table_name1_id1
> >
> > I tested preceding regular expression with Perl and JavaScript and it worked
> > fine. Can I use regular expressions with CHECK parametar, and if so, how can
> > I make it work.
>
> You should probably test it against PostgreSQL's regex engine. What you
> may not know is that they all have different syntaxes, rules and quirks.
> What works in one may or may not work in another.
>
> So check out src/backend/regex and build retest (I think that's what it
> was called). It's a command line regex tester (obviously against
> PostgreSQL's implementation).
Or, test directly in psql. I dropped your test data into a table, and
played with select:
test=# select * from testtable ;
id
1.2.
1.12.
12.1.
12.12.
(4 rows)
test=# select * from testtable ;
id
1.2.
1.12.
12.1.
12.12.
(4 rows)
test=# select * from testtable where id ~* '^([0-9]{1,2}\.){2}$';
id
12.12.
(1 row)
Hmm, that's because you said char(6), which is bank padded:
test=# select * from testtable where id ~* '^([0-9]{1,2}\.){2} *';
id
1.2.
1.12.
12.1.
12.12.
(4 rows)
Further testing with your actual table def (what version are you using?
I dont have ON INSERT CASCADE in my 7.2.1 test database) indicates you
need to double up the slashes on the '.', as so:
'^([0-9]{1,2}\\.){2}$'
One set of slashes gets stripped by the command processor.
Note that this _still_ requires a 6 char input, so 1.2. fails, but
01.02. works.
Ross
--
Ross Reedstrom, Ph.D. [EMAIL PROTECTED]
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] POSIX-style regular expressions
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Tue, 10 Sep 2002, Goran Buzic wrote:
>> id1char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
> Bad column type choice. char(6) is space padded, so 1.2. looks like
> "1.2. ". For me, 12.12. works, but any shorter one fails. Unless
> you need space padding elsewhere, you may just want to use varchar(6);
Also, the backslash has to be doubled to get through the string literal
parser, so what you really want is
id1varchar(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\\.){2}$'),
else it will accept values you don't want...
regards, tom lane
---(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
[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
Re: [SQL] Rules and Triggers
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])
Re: [SQL] Rules and Triggers
Adam Erickson wrote: > 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. :) That would be correct for triggers with AFTER INSERT/UPDATE/DELETE. But what about triggers BEFORE INSERT/UPDATE/DELETE? Regards, Michael ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Changing Column Type
Peter Atkins wrote: > All, > > Is there a way to easily change the type of column? Or do I have to drop > and create again. > > From: > assignment_notes | character varying(255) > > To: > assignment_notes | text Do that kind of change will require creating a new table, copying the data, dropping the old table, renaming the new to the old. If you were changing from varchar(20) to varchar(255), you could hack the system tables and be OK (or so I've read). Going smaller is possible too, as long as your 110% sure you don't have any data bigger then the new smaller size (or you're taking your life into your own hands so to speak. :-) HTH, Kevin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Rules and Triggers
On Tue, Sep 10, 2002 at 09:45:16PM +0200, Michael Paesold wrote: > > Adam Erickson wrote: > > > 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. :) > > That would be correct for triggers with AFTER INSERT/UPDATE/DELETE. > But what about triggers BEFORE INSERT/UPDATE/DELETE? An important thing to remember about rules vs. triggers: Rules operate on an SQL _query_ that involves the table (or view) that the rule is attached to, whereas triggers operate on the individual _tuples_ of the table they are attached to. Rules act to rewrite the incoming query, at the very earliest step: just after parsing, but before planning and optimizing. So rules _always_ act first. Triggers act when storage gets hit: when the optimizer has choosen a plan, and passes it to the executor for, well, execution. Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] R-Tree, GiST or B-Tree? I will need it?
Thanks Markus for your help (in message "How the R-Tree index works?"), but I guess, I should be more specific in my question. I have the next situation: 1- A table (A) with data, for example: ID | what | time | howmuch -- 1 | Beer | 03-01-2002 | 10 2 | Whiskey | 06-01-2002 | 3 3 | Beer | 09-01-2002 | 15 4 | Galactic Beer | 11-02-2043 | 40 2- A table (T) with time intervals, for example: Interval | Inittime | finishtime - 50 | 01-01-2002 | 04-01-2002 51 | 05-01-2002 | 10-01-2002 52 | 12-01-2002 | 12-01-2050 I want to now if is better to use R-Tree or Gist, if I want to know: "Howmuch" is in every "Interval", with something like this: SELECT Interval, sum(howmuch) from A,T where time>=Inittime and time I will have: Interval | howmuch -- 50 | 10 51 | 18 52 | 40 The problem is I have a lot of rows in A and T. Is better for the database to use R-Tree? or GiST? Should I use 2 index, one for each table (like a simple join situation)? If I use R-Tree for a B-Tree task will I loss performance? And for GiST? I have another case, more complex (not conceptually complex, complex for the database), but I will post it when this more simple question be answered. Thanks all and specially thanks Markus for his help. Andrés Sommerhoff R-Tree: "a spatial access method which splits space with hierarchically nested boxes. Objects are indexed in each box which intersects them. The tree is height-balanced." ...
