Re: [GENERAL] Re: MySQLs Describe emulator!
On Tuesday 06 March 2001 10:19 am, Boulat Khakimov wrote: > Karel Zak wrote: > > > On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote: > > > > > > Tom Lane wrote: > > > > Boulat Khakimov <[EMAIL PROTECTED]> writes: > > > > > Here is a nifty query I came up with > > > > > that provides a detailed information on any row of any table. > > > > > Something that is build into mySQL (DESC tablename fieldname) > > > > > but not into PG. > > > > > > > > Er, what's wrong with psql's "\d table" ? > > > > > > 2) as a programmer I need to be able to find out as much info as > > > possible about any given field > > >which is what "describe" for in mySQL. > > > > As a programmer you can see psql source and directly found how SQL > > query execute this tool. The PostgreSQL needn't non-standard statements > > like MySQL's SHOW, DESC -- the postgreSQL has system catalogs. > > > > Karel > > Agreed! Why make someones life easier?? > Let's complicate things as much as possible that way it's more > fun,right? ;o) > > Dont understand how this works? No problem -- just read the source > code. > Dont understand how to get that to work? Not a problem -- read the > source code! > > The only problem tho, the source codes tend to be thousands of lines > when it comes > to DBs and time is ... And, further, some of us are web programmers, and the source code doesn't help one whit in getting DATA that one needs to do stuff on the front end, like data entry validation. Thanks much Boulat - you made the coding for my robust validator a lot easier to write and read... and it works well, too! Michelle Michelle Murrain, Ph.D. President Norwottuck Technology Resources [EMAIL PROTECTED] http://www.norwottuck.com ---(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] text vs varchar
On Tue, 2002-06-18 at 18:07, Wei Weng wrote: > Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR > datatype with a maximum length, especially when I do searches on them? There is one big disadvantage that I've found, but might be utterly unimportant to you. Some applications (particularly Crystal Reports) do not like unbounded text fields, and make it quite difficult to do any manipulations of them. This might be an odbc issue, rather than an application issue, but it can be a big deal sometimes. -- .Michelle -- Michelle Murrain, Technology Consulting [EMAIL PROTECTED] http://www.murrain.net 413-253-2874 413-222-6350 cell 413-825-0288 fax ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Please Help me
Title: Re: [SQL] Please Help me At 2:49 PM +0800 8/1/02, Christopher Kings-Lynne wrote: I have no experience with ColdFusion, but if you ask a question like whether MySQL or Postgres is better on a Postgres mailing list - we're going to say Postgres. Yeah, except I do have experience with ColdFusion, and ColdFusion runs into some very problematic issues with Postgres, sadly. Although I use Postgres almost exclusively, I had to switch to MySQL for use with ColdFusion. -- .Michelle -- Michelle Murrain, Technology Consulting [EMAIL PROTECTED] http://www.murrain.net 413-253-2874 ph 413-222-6350 cell 413-825-0288 fax AIM:pearlbear0 Y!:pearlbear9 ICQ:129250575
Re: [SQL] Please Help me
Title: Re: [SQL] Please Help me At 8:32 AM -0600 8/1/02, Chad Thompson wrote: I am running RedHat, with Apache and Cold Fusion. I chose PostgreSQL for all of the aforementioned reasons. It works very well with Cold Fusion. I have done some optimizing and am able to run rather complex queries much faster than I ever was able to on any Windows platform database. I had to bail on MySQL because it wouldnt run the sub-queries that i needed. How did you solve the problem of large text fields? We ran into this problem, and was unable to solve it. We'd get an error if we wanted to add more than, I think 200 or so characters. It had to do with the connection between postgres and Cold Fusion. -- .Michelle -- Michelle Murrain, Technology Consulting [EMAIL PROTECTED] http://www.murrain.net 413-253-2874 ph 413-222-6350 cell 413-825-0288 fax AIM:pearlbear0 Y!:pearlbear9 ICQ:129250575
[SQL] Transaction Newbie
Hi,
I've been using Postgres for a while, almost exclusively through the
perl DBI (although I do plenty of work on the command line).
I have realized, belatedly, that I need transactions for this thing I
want to accomplish, but I've not done transactions before, so I need
a bit of help. And, I'm not sure whether it's a transaction I need,
or a lock.
I have (many) tables with automatically entering serial value as
primary key, set by a sequence. I need to insert a row, and then get
the value of that row I just entered. I thought first of doing two
sql statements in a row:
if the primary key is table_id, with default value
"nextval('table_seq') - then these two statements:
insert into table (field1,field2,field3) values (value1,value2,value3)
select currval('table_seq')
work to get me the value I need. Except, of course if someone else
has inserted a row inbetween these two statements.
I tried a transaction test, and this is what I got:
pew=# begin work;
BEGIN
pew=# insert into categories values
('23423423','test','testing','3','today','today','mpm','test
category');
INSERT 83910 1
pew=# select currval('category_id');
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
pew=# commit work
pew-# ;
COMMIT
pew=# select * from categories;
And the insert didn't happen.
Am I thinking about this right? Is there a better way to get the
value of a newly inserted record?
Thanks!
PS: I'm subscribed to sql, odbc and general, and have not been
getting general mail for quite some time. I've send emails to the
address that's supposed to be read by humans, but gotten no response.
If anyone is in a position to help me out - much appreciated!
--
.Michelle
--
Michelle Murrain, Technology Consulting
[EMAIL PROTECTED] http://www.murrain.net
413-253-2874 ph
413-222-6350 cell
413-825-0288 fax
AIM:pearlbear0 Y!:pearlbear9 ICQ:129250575
"A vocation is where the world's hunger & your great gladness meet."
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] simple addition of constraints problem
Hi All,
I'm trying to do something which seems really simple to me. (Postgres 7.3.4)
I've got this table:
charter_dev2=# \d rcourseinfo
Table "public.rcourseinfo"
Column| Type | Modifiers
-+---+---
courseid| integer | not null default
nextval('rcourseinfo_seq'::text)
coursename | character varying(50) |
dept| character varying(30) |
number | character varying(4) |
section | character varying(2) |
trimester | character varying(5) |
schoolyear | character varying(8) |
facultyid | integer |
description | text |
credits | real |
Indexes: rcourseinfo_pkey primary key btree (courseid),
rcourseinfo_number_index btree (number)
With a primary key called 'courseid'.
I've got a second table:
charter_dev2=# \d coursesevaluations
Table "public.coursesevaluations"
Column | Type |Modifiers
---+-+--
courseid | integer |
evalid| integer |
coursesevaluations_id | integer | not null default
nextval('coursesevaluations_seq'::text)
Indexes: coursesevaluations_pkey primary key btree (coursesevaluations_id),
coursesevaluations_evalid_index btree (evalid)
I'd like to make 'courseid' in this second table a foreign key,
referencing 'courseid' in the first table. So I did this command:
charter_dev2=# ALTER TABLE coursesevaluations ADD CONSTRAINT rc_ce
FOREIGN KEY (courseid) REFERENCES rcourseinfo (courseid);
And I get this result:
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: rc_ce referential integrity violation - key referenced from
coursesevaluations not found in rcourseinfo
This doesn't make any sense to me - the fields are named the same,
and are the same data type. I've pored over the docs, to no avail.
I'm sure I'm missing something really elementary, but it's escaping
me.
I did try the following (because the error said "key referenced"):
ALTER TABLE coursesevaluations ADD CONSTRAINT rc_ce FOREIGN KEY
(courseid) REFERENCES rcourseinfo (rcourseinfo_pkey);
I got the error :
ERROR: ALTER TABLE: column "rcourseinfo_pkey" referenced in foreign
key constraint does not exist
!!
Thanks for any advice.
--
.Michelle
--
Michelle Murrain
mmurrain at dbdes dot com
413-222-6350 ph
617-889-0929 ph
952-674-7253 fax <--- new
Page: [EMAIL PROTECTED]
AIM:pearlbear0 ICQ:129250575
Y!: michelle_murrain Jabber: [EMAIL PROTECTED]
"Work like you don't need the money. Love like you've never been
hurt. Dance like nobody's watching." - Satchel Paige
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] help on a query
Hi all, This is one of those things I know I should know, but it's not coming to me. It's probably really simple. I have two related tables, registrations and receipts, related by the field registration_id. So registrations looks kinda like: registration_id bigint (primary key) foo varchar(10) bar varchar(20) and receipts looks like: receipt_id bigint (primary key) registration_id bigint (foreign key) amount float baz varchar(10) If someone has paid, there is a row in the receipts table for that registration ID#. I need to find a list of the registration IDs that *don't* have an entry in the receipts table. Thanks in advance!!! -- .Michelle -- Michelle Murrain mmurrain at dbdes dot com 413-222-6350 ph 617-889-0929 ph 952-674-7253 fax <--- new Page: [EMAIL PROTECTED] AIM:pearlbear0 ICQ:129250575 Skype: pearlbear Jabber: [EMAIL PROTECTED] "I see all the different religious traditions as paths for the development of inner peace, which is the true foundation of world peace. These ancient traditions come to us as a gift from our common past. Will we continue to cherish it as a gift and hand it over to the future generations as a legacy of our shared desire for peace?" - His Holiness the Dalai Lama ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] help on a query
Thomas F.O'Connell wrote: I think the OUTER JOIN version is probably more efficient, but EXPLAIN would tell you. Well, this all makes me feel better. For everyone's edification: select registration_id FROM registrations where registration_id not in (select registration_id from receipts); Generates 0 rows The OUTER JOIN version is quite a bit more efficient (by an order of magnitude) than the option with WHERE NOT EXISTS subquery. Thanks all for the help. Saved my butt again!! -- .Michelle -- Michelle Murrain mmurrain at dbdes dot com 413-222-6350 ph 617-889-0929 ph 952-674-7253 fax <--- new Page: [EMAIL PROTECTED] AIM:pearlbear0 ICQ:129250575 Skype: pearlbear Jabber: [EMAIL PROTECTED] "I see all the different religious traditions as paths for the development of inner peace, which is the true foundation of world peace. These ancient traditions come to us as a gift from our common past. Will we continue to cherish it as a gift and hand it over to the future generations as a legacy of our shared desire for peace?" - His Holiness the Dalai Lama ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
