[SQL] convert_numeric_to_scalar: unsupported type 354210
PostgreSQL 7.3.2: Here is a piece of a table definition: CREATE TABLE data ( id SERIAL PRIMARY KEY, description TEXT, ra physreal, dec physreal, z physreal, ...); physreal is the domain of numeric(20,14). Then I created an index across ra,dec, and z (one index). I really haven't done anything else to the database. The table has ~29 rows and I indexed after inserting the data. Strangely, I can only make weird selects using these fields. Look: select dec from data limit 5; dec --- -1.2378252250 -1.2366515502 -1.2501212847 -1.0732052187 -1.1653486998 (5 rows) select id from data where dec < 191.456; id 1 2 ...this works. select id from data where dec < 2.0; ERROR: convert_numeric_to_scalar: unsupported type 354210 This doesn't. The problem persists even when I create individual indices for each column. It seems this only occurs when I use a number that is "close" to a value in the column. What's going on and what does the error mean? Thanks. ><><><><><><><><>< AgentM [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] virtual table
On Sunday 22 Jun 2003 10:23 pm, Tomasz Myrta wrote: > Hi > I have another virtual problem, currently without any examples ;-) > > Let's say we have some pl/pgsql function which puts result into table1. > This flat table must be normalized and put into table2. Sometimes 1 row > from table1 = 1 row from table2, but sometimes 1 row from table1= 3 rows > from table2. Data from table1 are transferrend into table2 using triggers. > I found, I don't to have any data in table1. > The question is: Is it possible to create virtual table in Postgresql? > Virtual - means it won't contain any columns nor data, but trigger doing > all the job. Look into views - you'll need to provide triggers to handle the update/inserts. -- Richard Huxton ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] virtual table
Dnia 2003-06-23 10:29, Użytkownik Richard Huxton napisał: Look into views - you'll need to provide triggers to handle the update/inserts. I think view won't change too much - there is not too much difference for this case between creating view and empty table with trigger returning null. I was thinking about something more comfortably - table without pre-declared columns so I don't have to change table definition when pl/pgsql function changes. Can anyone tell me how triggers work? Do they need to look into table column definition to retrieve data? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] virtual table
On Monday 23 Jun 2003 9:39 am, Tomasz Myrta wrote: > Dnia 2003-06-23 10:29, Użytkownik Richard Huxton napisał: > > Look into views - you'll need to provide triggers to handle the > > update/inserts. > > I think view won't change too much - there is not too much difference for > this case between creating view and empty table with trigger returning > null. I was thinking about something more comfortably - table without > pre-declared columns so I don't have to change table definition when > pl/pgsql function changes. > > Can anyone tell me how triggers work? Do they need to look into table > column definition to retrieve data? Hmm - I think your problem is going to come before that. Any time you do an INSERT, PostgreSQL is going to need to know the types of all the columns involved. For this sort of thing, I try to keep all the related bits (initial function, views, triggers) in the same text-file to encourage me to remember to update them all together. It sounds like this table1 is only being used during transfer. Is there any reason why you aren't just inserting the required rows into table2. -- Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] virtual table
Dnia 2003-06-23 10:54, Użytkownik Richard Huxton napisał: Hmm - I think your problem is going to come before that. Any time you do an INSERT, PostgreSQL is going to need to know the types of all the columns involved. For this sort of thing, I try to keep all the related bits (initial function, views, triggers) in the same text-file to encourage me to remember to update them all together. I also do this - it isn't really difficult with well written scripts. I was just wondering if I don't waste my time tracking all the changes. It sounds like this table1 is only being used during transfer. Is there any reason why you aren't just inserting the required rows into table2. My queries are ordinary transportation problems with 0 up to 2 changes. I found it's better (for performance reason) to find all possible relations as single rows and split them at the end into simple connections. Maybe I should dig into this problem again and rewrite my queries... Regards, Tomasz Myrta ---(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] Urgent Help : Use of return from function/procedure.
On Monday 23 Jun 2003 6:42 am, Anagha Joshi wrote: > Hi, > I'm new to postgres and using version 7.2.4 > > I've created a trigger and function which does the following: > trigger 'T' fires after insert on a spcific table takes place > and it executes function 'F' > > Function 'F' returns the new record inserted by 'return new' statement. > > Now my question is: > How can I use this 'new' value in my client program? In this prgm., I > want to know which all values are inserted into the table. Short answer - you don't. I'm not sure why you want to do this - if you just inserted the new values, surely you know what they are? There was something very similar to this last week, I'd have a look in the mailing-list archives at http://archives.postgresql.org - it was either this list or "general". -- Richard Huxton ---(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] virtual table
On Monday 23 Jun 2003 10:09 am, Tomasz Myrta wrote: > Dnia 2003-06-23 10:54, Użytkownik Richard Huxton napisał: > > Hmm - I think your problem is going to come before that. Any time you do > > an INSERT, PostgreSQL is going to need to know the types of all the > > columns involved. > > > > For this sort of thing, I try to keep all the related bits (initial > > function, views, triggers) in the same text-file to encourage me to > > remember to update them all together. > > I also do this - it isn't really difficult with well written scripts. I was > just wondering if I don't waste my time tracking all the changes. I know what you mean - except for simple cases I've never managed to automate type-checking my code without running it through PG. > > It sounds like this table1 is only being used during transfer. Is there > > any reason why you aren't just inserting the required rows into table2. > > My queries are ordinary transportation problems with 0 up to 2 changes. I > found it's better (for performance reason) to find all possible relations > as single rows and split them at the end into simple connections. Maybe I > should dig into this problem again and rewrite my queries... Not saying you've got the wrong approach. Might be worth a few minutes attention though -- Richard Huxton ---(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] TR: Like and =
Hi, I've got a table , pdi, with a field pro_id defined as char(25). One fied og this table contains the string '100058' plus spaces to fill the 25 length (ie pro_id = '100058 '). When I run: select * from pdi where pdi = '100058' the row is returned. When I run: select * from pdi where pdi like '100058' the row is NOT returned. select length(pro_id) where pdi = '100058' returns: length --- 25 2 Row(s) affected 1) In PostgreSQL documentation, it's said that without % wildcards like operates the same as = , it seems not. 2) Why does the = operator return the row ? it shouldn't because of the trailing spaces. 3) The row was inserted from the COPY command: COPY pdi FROM STDIN NULL as '' DELIMITER as '|'; VOL|100058|0|PART||PART \. Why does my field contain trailing spaces ? Regards and thanks again for your useful help. PS: create table pdi ( pmf_id char(4) not null , pro_id char(25) not null , lng_id char(3) not null , pdi_desc char(50) not null , pdi_instr text, pdi_matchdesc char(50), CONSTRAINT pk_pdi PRIMARY KEY (pro_id,pmf_id,lng_id) ); Nicolas. --- Nicolas JOUANIN - SA REGIE FRANCE Village Informatique BP 3002 17030 La Rochelle CEDEX Tel: 05 46 44 75 76 Fax: 05 46 45 34 17 email: [EMAIL PROTECTED] Web : www.regie-france.com --- ---(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] convert_numeric_to_scalar: unsupported type 354210
AgentM <[EMAIL PROTECTED]> writes: > PostgreSQL 7.3.2: > physreal is the domain of numeric(20,14). > ERROR: convert_numeric_to_scalar: unsupported type 354210 This is fixed in 7.3.3. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CREATE table1 FROM table2
Hello! many people posted their answer to this simple question. however, neither CREATE TABLE AS nor SELECT INTO do not take care about keys and triggers, etc. The commands above only copy the structure of the table, and the data. Are there any workarounds fr copying the table as a whole object? thanks, Mila > Hi, > On 17 Jun 2003, Rado Petrik wrote: >> How I create table1 from other table2 . > CREATE TABLE table1 AS SELECT * FROM table2; > will work. > Regards, ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Urgent Help : Use of return from function/procedure.
Title: Urgent Help : Use of return from function/procedure. - Original Message - From: Anagha Joshi To: [EMAIL PROTECTED] Sent: Sunday, June 22, 2003 11:42 PM Subject: [SQL] Urgent Help : Use of return from function/procedure. Hi, I'm new to postgres and using version 7.2.4 I've created a trigger and function which does the following: trigger 'T' fires after insert on a spcific table takes place and it executes function 'F' Function 'F' returns the new record inserted by 'return new' statement. Now my question is: How can I use this 'new' value in my client program? In this prgm., I want to know which all values are inserted into the table. Help is appreciated. Thx, Anagha Im not sure if this is what you are looking for. But I use postgres this way to know which record I have sent. Its a very simple function and should be self explanitory. I think the GET DIAGNOSTICS is the key for you in this case. -- Function: public.return_mortgage_id(varchar, varchar) CREATE FUNCTION public.return_mortgage_id(varchar, varchar) RETURNS int8 AS ' DECLARE oid1 INTEGER; retval integer; BEGIN insert into mortgage(contact_firstname, contact_lastname, date_submitted) values($1,$2, now()); GET DIAGNOSTICS oid1 = RESULT_OID; select id into retval from mortgage where oid = oid1; return retval; end;' LANGUAGE 'plpgsql' IMMUTABLE; Hope that helps Chad
Re: [SQL] TR: Like and =
Hi Nicholas, CHAR fields, as opposed to VARCHAR, are blank-padded to the set length. Therefore, when you inserted a < 25 character string, it got padded with spaces until the end. Likewise, when you cast '100058' to a CHAR(25) in the = below, it gets padded, so it matches. The LIKE operator takes a pattern, and since your pattern did not specify a wildcard at the end, it didn't exactly match the padded string. This behavior does seem kind of confusing; in any case, it probably argues for using varchar. Best, Randall On Monday, June 23, 2003, at 12:29 PM, Nicolas JOUANIN wrote: Hi, I've got a table , pdi, with a field pro_id defined as char(25). One fied og this table contains the string '100058' plus spaces to fill the 25 length (ie pro_id = '100058 '). When I run: select * from pdi where pdi = '100058' the row is returned. When I run: select * from pdi where pdi like '100058' the row is NOT returned. select length(pro_id) where pdi = '100058' returns: length --- 25 2 Row(s) affected 1) In PostgreSQL documentation, it's said that without % wildcards like operates the same as = , it seems not. 2) Why does the = operator return the row ? it shouldn't because of the trailing spaces. 3) The row was inserted from the COPY command: COPY pdi FROM STDIN NULL as '' DELIMITER as '|'; VOL|100058|0|PART||PART \. Why does my field contain trailing spaces ? Regards and thanks again for your useful help. PS: create table pdi ( pmf_id char(4) not null , pro_id char(25) not null , lng_id char(3) not null , pdi_desc char(50) not null , pdi_instr text, pdi_matchdesc char(50), CONSTRAINT pk_pdi PRIMARY KEY (pro_id,pmf_id,lng_id) ); Nicolas. --- Nicolas JOUANIN - SA REGIE FRANCE Village Informatique BP 3002 17030 La Rochelle CEDEX Tel: 05 46 44 75 76 Fax: 05 46 45 34 17 email: [EMAIL PROTECTED] Web : www.regie-france.com --- ---(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 ---(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] CREATE table1 FROM table2
On Mon, 23 Jun 2003, L.V.Boldareva wrote: > Hello! > > many people posted their answer to this simple question. however, > neither > CREATE TABLE AS > nor > SELECT INTO > > do not take care about keys and triggers, etc. > > The commands above only copy the structure of the table, and the data. > Are there any workarounds fr copying the table as a whole object? You probably want to use pg_dump dbname -t tablename and edit the file thus created by hand to create a new table etc... ---(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] convert_numeric_to_scalar: unsupported type 354210
> select id from data where dec < 2.0; > ERROR: convert_numeric_to_scalar: unsupported type 354210 Domains exposed (and introduced) a number of interesting issues in regards to type switching for these things. Cast the 2.0 value to the domain: CAST(2.0 AS physreal) or quote it and let the system figure it out: dec < '2.0' Simply put, without quotes the parser assumes that 2.0 is a numeric, which doesn't have a direct < operator for use with the domain. Another alternative would be to create a new < operator, but thats more work than it's worth. This has been fixed for 7.4 (the system implicitly coerces the domain to numeric for the index comparison). -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
[SQL] multi-table unique index
-- Suppose I have several types of foos create table foo_types ( foo_type_id serial primary key, foo_name text not null ); -- And the foos itself: create table foo ( foo_id serial primary key, foo_type_id int not null references foo_types, foo_general_data1 text ); -- 1st special foo: create table foo_1 ( foo_1_id serial primary key, foo_id int not null references foo, foo_1_data1 int, foo_1_data2 text ); -- 2nd special foo: create table foo_2 ( foo_2_id serial primary key, foo_id int not null references foo, foo_2_data1 numeric(8,2), foo_2_data2 timestamp ); -- And I have bars that each have zero or more (special) foos. create table bar ( bar_id serial primary key ); create table bar_foos ( bar_id int not null references bar, foo_id int not null references foo ); -- Now how do I prevent that I can insert a foo_1 and a foo_2 with -- the same foo_id? Or is the design errorneous itself? -- Markus Bertheau Berlin, Berlin, Germany ---(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] multi-table unique index
Instead of using the "serial" datatype, you can set it to "int4 PRIMARY KEY DEFAULT nextval(foo_type_id_seq)" and you can manually create the sequence "foo_type_id_seq". This way all the tables share the same sequence. On Monday 23 June 2003 06:58 am, Markus Bertheau wrote: > -- Suppose I have several types of foos > > create table foo_types ( > foo_type_id serial primary key, > foo_name text not null > ); > > -- And the foos itself: > > create table foo ( > foo_id serial primary key, > foo_type_id int not null references foo_types, > foo_general_data1 text > ); > > -- 1st special foo: > > create table foo_1 ( > foo_1_id serial primary key, > foo_id int not null references foo, > foo_1_data1 int, > foo_1_data2 text > ); > > -- 2nd special foo: > > create table foo_2 ( > foo_2_id serial primary key, > foo_id int not null references foo, > foo_2_data1 numeric(8,2), > foo_2_data2 timestamp > ); > > -- And I have bars that each have zero or more (special) foos. > > create table bar ( > bar_id serial primary key > ); > > create table bar_foos ( > bar_id int not null references bar, > foo_id int not null references foo > ); > > -- Now how do I prevent that I can insert a foo_1 and a foo_2 with > -- the same foo_id? Or is the design errorneous itself? > > -- > Markus Bertheau > Berlin, Berlin, Germany > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] multi-table unique index
В Пнд, 23.06.2003, в 19:32, Michael A Nachbaur пишет: > Instead of using the "serial" datatype, you can set it to "int4 PRIMARY KEY > DEFAULT nextval(foo_type_id_seq)" and you can manually create the sequence > "foo_type_id_seq". > > This way all the tables share the same sequence. Yeah, but I want to force this behaviour. so that it cannot happen by accident when you insert records without relying on the sequence. -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] multi-table unique index
On Monday 23 June 2003 11:16 am, Markus Bertheau wrote: > В Пнд, 23.06.2003, в 19:32, Michael A Nachbaur пишет: > > Instead of using the "serial" datatype, you can set it to "int4 PRIMARY > > KEY DEFAULT nextval(foo_type_id_seq)" and you can manually create the > > sequence "foo_type_id_seq". > > > > This way all the tables share the same sequence. > > Yeah, but I want to force this behaviour. so that it cannot happen by > accident when you insert records without relying on the sequence. I believe that's what I recommended. IIRC the "serial" datatype is simply a shortcut to what I listed above. This way, if you do not explicitly specify an id for your record, it'll pull the default; which retrieves a new values from the sequence. If you want to ensure that a value is unique when a record is inserted, even if the user explicitly specifies an ID values, you can create a unique trigger on the tables, but this depends on what you want to do. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] TR: Like and =
Randall Lucas <[EMAIL PROTECTED]> writes: > The LIKE operator takes a pattern, and since your pattern did not > specify a wildcard at the end, it didn't exactly match the padded > string. > This behavior does seem kind of confusing; Yeah. As of CVS tip, the system is actually going out of its way to cause this to happen: if we deleted the separate ~~ operator for bpchar, then the automatic rtrim() that now happens when converting bpchar to text would cause the extra spaces to go away, and the LIKE would work as Nicolas is expecting. On the other hand, this would probably create some backwards-compatibility issues, since existing uses of LIKE with bpchar operands are no doubt using patterns that expect the spaces to be there. Any opinions whether we should change it or not? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] multi-table unique index
В Пнд, 23.06.2003, в 20:34, Michael A Nachbaur пишет: > On Monday 23 June 2003 11:16 am, Markus Bertheau wrote: > > В Пнд, 23.06.2003, в 19:32, Michael A Nachbaur пишет: > > > Instead of using the "serial" datatype, you can set it to "int4 PRIMARY > > > KEY DEFAULT nextval(foo_type_id_seq)" and you can manually create the > > > sequence "foo_type_id_seq". > > > > > > This way all the tables share the same sequence. > > > > Yeah, but I want to force this behaviour. so that it cannot happen by > > accident when you insert records without relying on the sequence. > If you want to ensure that a value is unique when a record is inserted, even > if the user explicitly specifies an ID values, you can create a unique > trigger on the tables, but this depends on what you want to do. I believe what you described is what I want to do. What's a unique trigger, though? -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] multi-table unique index
On Monday 23 Jun 2003 2:58 pm, Markus Bertheau wrote: > -- Suppose I have several types of foos > > create table foo_types ( > foo_type_id serial primary key, > foo_name text not null > ); > > -- And the foos itself: > > create table foo ( > foo_id serial primary key, > foo_type_id int not null references foo_types, > foo_general_data1 text > ); > > -- 1st special foo: > > create table foo_1 ( > foo_1_id serial primary key, > foo_id int not null references foo, > foo_1_data1 int, > foo_1_data2 text > ); An alternative to Markus' ideas in the other thread - store the type in foo/foo_1, then have a foreign key over both. The irritating thing is that you're duplicating the type info unnecessarily. CREATE TABLE foo ( foo_id serial unique, foo_type_id int not null references foo_types, ... PRIMARY KEY (foo_id,foo_type_id) ); CREATE TABLE foo_1 ( extra_foo int4 not null, extra_type int4 not null extra1 text, PRIMARY KEY (extra_foo, extra_type) CONSTRAINT link_to_foo FOREIGN KEY (extra_foo,extra_type) REFERENCES foo_core (foo_id, foo_type_id) ); -- Richard Huxton ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] aggregate question
I know this one is simple enough.
I have two tables: packages and package_log.
paulsonsoft=# \d packages
Table "public.packages"
Column | Type |Modifiers
-+-+--
package_name| text| not null
package_desc| text| not null
package_ver | text| not null
package_date| text| not null
package_loc | text| not null
package_type| text| not null
package_creator | text|
package_status | boolean | default true
package_id | integer | not null default
nextval('public.packages_package_id_seq'::text)
Indexes: packages_pkey primary key btree (package_id)
Foreign Key constraints: $1 FOREIGN KEY (package_type) REFERENCES
package_types(package_type) ON UPDATE CASCADE ON DELETE RESTRICT
paulsonsoft=# \d package_log
Table "public.package_log"
Column| Type |Modifiers
-+-+-
custno | text| not null
package_id | text|
timestamp | text| not null
ip_address | text| not null
completed | boolean |
current_ver | text|
logo| text|
licenses| text|
log_id | integer | not null default
nextval('public.package_log_log_id_seq'::text)
Foreign Key constraints: $1 FOREIGN KEY (package_id) REFERENCES
packages(package_id) ON UPDATE CASCADE ON DELETE RESTRICT
I must be a total space case today because I can't hammer out the sql to
get a listing of all the packages with a count() of the package_log by
package_id.
Thanks,
Ryan
---(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] aggregate question
Dnia 2003-06-23 20:59, Użytkownik Ryan napisał: I know this one is simple enough. I have two tables: packages and package_log. I must be a total space case today because I can't hammer out the sql to get a listing of all the packages with a count() of the package_log by package_id. Thanks, Ryan select package_name, count(*) as n_packages from packages join package_log using (package_id); This query is the simplest one, but doesn't display packages without any logs. If you need this, try this one: select p.package_name, (select count(*) from package_log pl where pl.package_id=p.package_id) as n_packages from packages p; And one more: select package_name, sum(case when package_log.package_id is not null then 1 else 0 end) as n_packages from packages left join package_log using (package_id); Regards, Tomasz Myrta ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Database Design
Hi, I have a Spread Sheet which I need to make a searchable directory out of. I'm in two minds on a couple things so I thought I'd ask. I"m not sure whether to have on larger table or 2 smaller one with a join table. This is a trimmed down version of the data. The directory is of Camping grounds/parks. Here is a sample of the fields: Park Name Info Kiosk Tent Caravan Toilets Wheelchair Water Shower Telephone One large table may look like: CREATE TABLE grounds ( id serial PRIMARY KEY, parkname varchar(120), infokiosk bool NOT NULL, tent bool NOT NULL, caravan bool NOT NULL, toilets bool NOT NULL, wheelchair bool NOT NULL, water bool NOT NULL, shower bool NOT NULL, telephone bool NOT NULL ); Two smaller tables with a join table may look like: CREATE TABLE grounds ( gid serial PRIMARY KEY, parkname varchar(120) ); CREATE TABLE features ( fid serial PRIMARY KEY, feature varchar NOT NULL ); INSERT INTO features ( feature ) VALUES ( 'infokiosk' ); INSERT INTO features ( feature ) VALUES ( ' tent' ); INSERT INTO features ( feature ) VALUES ( ' caravan' ); INSERT INTO features ( feature ) VALUES ( ' toilets' ); INSERT INTO features ( feature ) VALUES ( ' wheelchair' ); INSERT INTO features ( feature ) VALUES ( ' water' ); INSERT INTO features ( feature ) VALUES ( ' shower' ); INSERT INTO features ( feature ) VALUES ( ' telephone' ); ); -- join table CREATE TABLE grounds_features ( gid int4 REFERENCES grounds ON UPDATE CASCADE ON DELETE CASCADE NOT NULL, fid int4 REFERENCES features ON UPDATE CASCADE ON DELETE CASCADE NOT NULL ); For this task, a searchable directory, which design would be better. In know the second is more flexible as new features can be added but I have very limited time and I think the one larger table design would be quicker to build. The front end will be either ColdFusion or PHP - I haven't decided yet. Thank you kindly Best regards Rudi. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Database design - AGAIN
Hi, Sorry - I make some typo's - here is the question again. I have a Spread Sheet which I need to make a searchable directory out of. I'm in two minds on a couple things so I thought I'd ask. I"m not sure whether to have on larger table or 2 smaller one with a join table. This is a trimmed down version of the data. The directory is of Camping grounds/parks. Here is a sample of the fields: Park Name Info Kiosk infokiosk tent caravan toilets wheelchair water shower telephone One large table may look like: CREATE TABLE grounds ( id serial PRIMARY KEY, parkname varchar(120), infokiosk bool NOT NULL, tent bool NOT NULL, caravan bool NOT NULL, toilets bool NOT NULL, wheelchair bool NOT NULL, water bool NOT NULL, shower bool NOT NULL, telephone bool NOT NULL ); Two smaller tables with a join table may look like: CREATE TABLE grounds ( gid serial PRIMARY KEY, parkname varchar(120) ); CREATE TABLE features ( fid serial PRIMARY KEY, feature varchar NOT NULL ); INSERT INTO features ( feature ) VALUES ( 'infokiosk' ); INSERT INTO features ( feature ) VALUES ( ' tent' ); INSERT INTO features ( feature ) VALUES ( ' caravan' ); INSERT INTO features ( feature ) VALUES ( ' toilets' ); INSERT INTO features ( feature ) VALUES ( ' wheelchair' ); INSERT INTO features ( feature ) VALUES ( ' water' ); INSERT INTO features ( feature ) VALUES ( ' shower' ); INSERT INTO features ( feature ) VALUES ( ' telephone' ); ); -- join table CREATE TABLE grounds_features ( gid int4 REFERENCES grounds ON UPDATE CASCADE ON DELETE CASCADE NOT NULL, fid int4 REFERENCES features ON UPDATE CASCADE ON DELETE CASCADE NOT NULL ); For this task, a searchable directory, which design would be better. In know the second is more flexible as new features can be added but I have very limited time and I think the one larger table design would be quicker to build. The front end will be either ColdFusion or PHP - I haven't decided yet. Thank you kindly Best regards Rudi.
Re: [SQL] TR: Like and =
Hi, Thanks for your help. In fact that means 2 solutions for this: 1) select * from pdi where rtrim(pdi) = '100058' or 2) Use VARCHAR instead of CHAR I don't which is the best , but both are working. Nicolas. > -Message d'origine- > De : Randall Lucas [mailto:[EMAIL PROTECTED] > Envoye : lundi 23 juin 2003 18:54 > A : Nicolas JOUANIN > Cc : [EMAIL PROTECTED] > Objet : Re: [SQL] TR: Like and = > > > > Hi Nicholas, > > CHAR fields, as opposed to VARCHAR, are blank-padded to the set length. > > Therefore, when you inserted a < 25 character string, it got padded > with spaces until the end. > > Likewise, when you cast '100058' to a CHAR(25) in the = below, it > gets padded, so it matches. > > The LIKE operator takes a pattern, and since your pattern did not > specify a wildcard at the end, it didn't exactly match the padded > string. > > This behavior does seem kind of confusing; in any case, it probably > argues for using varchar. > > Best, > > Randall > > > On Monday, June 23, 2003, at 12:29 PM, Nicolas JOUANIN wrote: > > > > > Hi, > > > > I've got a table , pdi, with a field pro_id defined as char(25). One > > fied > > og this table contains the string '100058' plus spaces to fill the > > 25 > > length (ie pro_id = '100058 '). > > When I run: > >select * from pdi where pdi = '100058' the row is returned. > > When I run: > >select * from pdi where pdi like '100058' the row is NOT > > returned. > > > > select length(pro_id) where pdi = '100058' returns: > > length > > --- > > 25 > > > > 2 Row(s) affected > > > > 1) In PostgreSQL documentation, it's said that without % wildcards like > > operates the same as = , it seems not. > > 2) Why does the = operator return the row ? it shouldn't because of the > > trailing spaces. > > 3) The row was inserted from the COPY command: > > COPY pdi FROM STDIN NULL as '' DELIMITER as '|'; > > VOL|100058|0|PART||PART > > \. > > Why does my field contain trailing spaces ? > > > > Regards and thanks again for your useful help. > > > > > > PS: > > create table pdi > > ( > > pmf_id char(4) not null , > > pro_id char(25) not null , > > lng_id char(3) not null , > > pdi_desc char(50) not null , > > pdi_instr text, > > pdi_matchdesc char(50), > > CONSTRAINT pk_pdi PRIMARY KEY (pro_id,pmf_id,lng_id) > > ); > > > > Nicolas. > > > > --- > > Nicolas JOUANIN - SA REGIE FRANCE > > Village Informatique BP 3002 > > 17030 La Rochelle CEDEX > > Tel: 05 46 44 75 76 > > Fax: 05 46 45 34 17 > > email: [EMAIL PROTECTED] > > Web : www.regie-france.com > > --- > > > > > > > > ---(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 > > > ---(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
