[SQL] Please help me out on this insert error
Command:
Insert into profile (userid, haveChildren)values('id98', 'No');
Error:
ERROR: Relation 'profile' does not have attribute 'havaChildren'
Table:
Table "profile"
Column| Type | Modifiers
--+---+--
userid | character varying(25) | not null
sex | character(1) | not null
dob | date | not null
pob | character varying(20) |
status | character varying(20) | not null
ethnicity| character varying(20) | not null
religion | character varying(20) | not null
bodytype | character varying(20) | not null
height | numeric(3,0) | not null
weight | numeric(2,0) | not null
education| character varying(20) |
occupation | character varying(20) | not null
incomelow| numeric(6,0) | not null
incomeup | numeric(6,0) | not null
haveChildren | character varying(20) | not null
wantChildren | character varying(20) | not null
drinking | character varying(20) |
smoking | character varying(20) | not null
narration| text |
Primary key: pro_pkey
What is wrong here?
Thanks.
---(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] Please help me out on this insert error
You are right, Steve. It needs the double quote mark. After I use the double quote
mark, an error message is:
ERROR: ExecAppend: Fail to add null value in not null attribute ...
which is right since I don't have non-null value to non-null field yet.
I, however, didn't use double quote mark when I created the table at all.
Now, I need to figure out how to add double quote mark in Java code query string.
v.
6/14/2002 6:12:18 AM, Stephan Szabo <[EMAIL PROTECTED]> wrote:
>On Thu, 13 Jun 2002, Vernon Wu wrote:
>
>>
>> Command:
>>
>> Insert into profile (userid, haveChildren)values('id98', 'No');
>
>You presumably used double quotes when creating the column, so
>you need to use them to refer to the column from that point on:
>
>insert into profile(userid, "haveChildren") ...
>
>
>
---(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] Please help me out on this insert error
6/14/2002 6:31:16 AM, Stephan Szabo <[EMAIL PROTECTED]> wrote: > >On Thu, 13 Jun 2002, Vernon Wu wrote: > >> I, however, didn't use double quote mark when I created the table at all. > >If you used an interface to generate the table def, alot of them add the >quote marks behind your back when they do the creation. That must be what really happended without my knowledge. I used the pgAdmin to create the table. So, it is safer to create tables using the command line. In general, it's >safer to just use all lowercase names. :) > > That is a separated issue, is it? Thanks. v ---(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] A problem on running a sql script in DBVisualizer
Sorry for this question is somewhat off the topic of this mailing list. I use the DBVisualizer to run a sql script. I have somethink like: drop table a; create table a( . . . ); drop table b; create table b ( . . . ) inherits a; I get an error: Relation "b" inherits from "a" It is fine when I run the sql statements line by line. How to overcome this problem? Thanks in advance. Vernon ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] i18n in pgSQL
Hi, I am working on an application intended for multilingual users. I have learnt from a source that I shall define double-byte data type such as nvarchar and ntext in DB. I don't see these data type in the pgsql online document. I have tried to create a table with the data type. The nvarchar is accepted, but not ntext. Also, I remember I can configure pgsql for unicode in Linux. I can't find what, where, and how to configure pgsql in my current setting: on cygwin. Currently, Chinese charaters can't be stored and retrieved properly. Thanks for your help. Vernon ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Fwd: Re: [SQL] Can I search for an array in csf?
Hi, Richard, Thanks for your response and see below. 10/21/2002 3:13:57 AM, Richard Huxton <[EMAIL PROTECTED]> wrote: >On Friday 18 Oct 2002 1:58 pm, Vernon Wu wrote: >> One field of a table stores an array of characters in a string fromat as >> "a,b,c,d". Is anyway to apply a select statement without using stored >> procedure? >> >> Thanks for your input. > >Not really, and I can't think any way of accessing an index on this either. >Are you sure you wanted the characters stored this way? Even if the overhead >of a separate table isn't woth it, you might want to look into arrays and the >intarray stuff in contrib/ > The reason I use this format for an array is that the array is dynamic. I have quite few cases of this type of situation. The maximize length in some cases is known, is unknown in others. I have learnt the comment separated format is one way to solve the problem. Someone also suggested to store the array as an object. I am not sure whether it works or not. The application is written in Java, by the way. I have taken a look at intarray by searching on the postgres.org web site as well as in google. (I use cypwin and unable to find the contrib directory). My impression is it isn't a standard SQL data type. And its element is integer only. It is my first time doing DB table design. Any helps will be gracfully appreciated. Thanks, Vernon >-- > 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 > End of forwarded message ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Fwd: Re: [SQL] Can I search for an array in csf?
Hi, Achilleus and Josh, I know three ways to store dynamic array in DB: object, xml or csv, and sub-table. It seems to me there are some problems of using the first method in Java. I don't know how the third method work out. That is the reason I use the second method. In my project, the simplest array type is an array of characters or user IDs. The complexest array is an array of data type of userid and another text field. All operations on the arrays are simple: either element look up, add or delete an element. Which method is the most suitable for those different operations. Thanks very much for all your helps. Vernon Thanks for your information. See below. 10/22/2002 1:03:56 AM, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: >On Mon, 21 Oct 2002, Josh Berkus wrote: > >> >> Vernon, >> >> > >> One field of a table stores an array of characters in a string fromat as >> > >> "a,b,c,d". Is anyway to apply a select statement without using stored >> > >> procedure? >> >> > The reason I use this format for an array is that the array is dynamic. I >> have quite few cases of this type of situation. The >> > maximize length in some cases is known, is unknown in others. I have learnt >> the comment separated format is one way >> > to solve the problem. Someone also suggested to store the array as an >> object. I am not sure whether it works or not. >> > The application is written in Java, by the way. >> >> You should store this data in a sub-table linked through a foriegn key. >> Period. Messing with arrays will only lead you to heartache ... > >It depends. >I can tell you of situations that doing it with child tables >will hurt performance really bad. >Its just a matter of complexity. > >One of the apps we run over here, deals with bunker >analysis of the vessels of our fleet. > >For each vessel there are 4 formulas that describe the parameters of >the consumption of fuel oil under some given conditions. > >I have implemented this using arrays. >The app is written in J2EE. > >On a dual xeon 2.2 GHz with 1 GB for postgres, >it takes about 900 miliseconds to compute >some statistics (average, std deviation,etc..) >of the consumption of all vessels (about 20 of them) >for a period of 3 years (the values are stored for each day). > >Before going with the formulas, we had a rather >primitive scheme originated from the previous >cobol application, based on subtable look ups, >(and there was no serious computations involved >just table lookups). > >I can tell you the performance boost was remarkable. > >> >> Try the book "Database Design For Mere Mortals" for a primer on SQL DB design. >> >> -- >> -Josh Berkus >> Aglio Database Solutions >> San Francisco >> >> >> ---(end of broadcast)--- >> TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/users-lounge/docs/faq.html >> > >== >Achilleus Mantzios >S/W Engineer >IT dept >Dynacom Tankers Mngmt >Nikis 4, Glyfada >Athens 16610 >Greece >tel:+30-10-8981112 >fax:+30-10-8981877 >email: [EMAIL PROTECTED] >[EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Can I search for an array in csf?
Hi, Christoph,
Thanks for reminding me regular expression.
The background of my question is about attributes of one element, say hobby for
example. Different people have
different hobbies. Inside of the application, hobbies are denoted in various
characters. The selection I mentioned in the
original mail refers to finding people who have certain hobbies in the case.
I think regular expression is the most effective way for the usage.
Thanks again.
Vernon
10/22/2002 5:50:22 AM, Christoph Haller <[EMAIL PROTECTED]> wrote:
>>
>> I thought I had made my case clear. Let me rephrase it.
>>
>> I have a character array, {'a', 'b', 'c', 'd'} for example. And this
>array is stored in a DB table field as a string
>of "a,b,c,d".
>> Now, I want to find out whether the table field, or array, contains
>any character set of {'c', 'e', 'h'}. My question
> is
>> whether SQL statement is appliable for this selection, or the data has
>been retrieved and process in the application
>> level.
>>
>> I hope I make th case clear this time.
>>
>Ok, now I see.
>Maybe the Postgres POSIX Regular Expressions are what you are looking
>for.
>Suppose your array "a,b,c,d" is stored into a table field of type
>character,
>character varying or text.
>Then, if you are searching for all entries containing a 'c', 'e', or
>'h' character
>your SELECT statement would look like
>
>SELECT col1, col2, ... FROM table1 WHERE arrayfield ~ '[ceh]' ;
>
>Still not sure if you mean a sequence like "c,e,h" on the other hand.
>Then
>SELECT col1, col2, ... FROM table1 WHERE arrayfield ~ 'c,e,h' ;
>should do the trick - as long as the order of characters is identical.
>A sequence of "e,c,h" would not show up, of course.
>
>Regular Expressions are far more powerful than these two examples can
>show.
>Refer to the related chapter in the documentation.
>I hope this helps more than the other replies you've received telling
>you
>'Learn about DB-design first'.
>
>Regards, Christoph
>
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Can I search for an array in csf?
One field of a table stores an array of characters in a string fromat as "a,b,c,d". Is anyway to apply a select statement without using stored procedure? Thanks for your input. Vernon ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How does postgres handle non literal string values
In general, it isn't a good idea to have SQL statements in JSP files. A good practise is using Mode 2. The Struts is a popular Mode 2 framework. If your application is very small and it won't grow into a big one, you can get around using Mode 1. In the situation, the SQL tags of JSTL will be a recommeded mechanism. 11/26/2002 8:05:27 AM, "Charles H. Woloszynski" <[EMAIL PROTECTED]> wrote: >Actually, we use JDBC Prepared Statements for this type of work. You >put a query with '?' in as placeholders and then add in the values and >the library takes care of the encoding issues. This avoids the double >encoding of (encode X as String, decode string and encode as SQL X on >the line). There was a good article about a framework that did this in >JavaReport about a 18 months ago. > >We have gleaned some ideas from that article to create a framework >around using PreparedStatements as the primary interface to the >database. I'd suggest looking at them. They really make your code much >more robust. > >Charlie > > >>"')..." >> >>You *will* want to escape the username and password otherwise I'll be able to >>come along and insert any values I like into your database. I can't believe >>the JDBC classes don't provide >> >>1. Some way to escape value strings >>2. Some form of placeholders to deal with this >> >> >> > >-- > > >Charles H. Woloszynski > >ClearMetrix, Inc. >115 Research Drive >Bethlehem, PA 18015 > >tel: 610-419-2210 x400 >fax: 240-371-3256 >web: www.clearmetrix.com > > > > > >---(end of broadcast)--- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How does postgres handle non literal string values
When the MVC pattern is employed and EJB is absented, the DAO pattern shall be utilized. With this system design, the DB routines are isolated from the rest of system for the purpose of "robust" as you mentioned. In the future, you only need to write another set of DAO implemention if you decide to use another DB, say SAP DB for example. A DB connection pool also is desired, which shall take care of the JDBC driver look up. 12/4/2002 5:00:18 AM, "Charles H. Woloszynski" <[EMAIL PROTECTED]> wrote: >Vernon: > >Agreed. We use Struts (as our MVC framework), and then a data access >layer (we call persistables) that uses the PreparedStatements. Our JSPs >only get data to render *after* the business logic has decided that all >logic has been performed successfully. > >The end-result is easily compartmentalized code (lots of code factoring) >that makes for very robust applications. We are working on moving this >framework to PostgreSQL (from Oracle) and we expect to have to touch the >SQL statements (which are each in their own class, again for re-use) and >perhaps two or three other classes to deal with any JDBC driver issues. > When we make the transition successfully, I hope to be able to >publicize the work and the value of PostgreSQL. > >Charlie > >Vernon Wu wrote: > >>In general, it isn't a good idea to have SQL statements in JSP files. A good >practise is using Mode 2. The Struts is a >>popular Mode 2 framework. If your application is very small and it won't grow into a >big one, you can get around using >>Mode 1. In the situation, the SQL tags of JSTL will be a recommeded mechanism. >> >>11/26/2002 8:05:27 AM, "Charles H. Woloszynski" <[EMAIL PROTECTED]> wrote: >> >> >> >>>Actually, we use JDBC Prepared Statements for this type of work. You >>>put a query with '?' in as placeholders and then add in the values and >>>the library takes care of the encoding issues. This avoids the double >>>encoding of (encode X as String, decode string and encode as SQL X on >>>the line). There was a good article about a framework that did this in >>>JavaReport about a 18 months ago. >>> >>>We have gleaned some ideas from that article to create a framework >>>around using PreparedStatements as the primary interface to the >>>database. I'd suggest looking at them. They really make your code much >>>more robust. >>> >>>Charlie >>> >>> >>> >>> >>>>"')..." >>>> >>>>You *will* want to escape the username and password otherwise I'll be able to >>>>come along and insert any values I like into your database. I can't believe >>>>the JDBC classes don't provide >>>> >>>>1. Some way to escape value strings >>>>2. Some form of placeholders to deal with this >>>> >>>> >>>> >>>> >>>> >>>-- >>> >>> >>>Charles H. Woloszynski >>> >>>ClearMetrix, Inc. >>>115 Research Drive >>>Bethlehem, PA 18015 >>> >>>tel: 610-419-2210 x400 >>>fax: 240-371-3256 >>>web: www.clearmetrix.com >>> >>> >>> >>> >>> >>>---(end of broadcast)--- >>>TIP 5: Have you checked our extensive FAQ? >>> >>>http://www.postgresql.org/users-lounge/docs/faq.html >>> >>> >>> >> >> >> >> >>---(end of broadcast)--- >>TIP 6: Have you searched our list archives? >> >>http://archives.postgresql.org >> >> > >-- > > >Charles H. Woloszynski > >ClearMetrix, Inc. >115 Research Drive >Bethlehem, PA 18015 > >tel: 610-419-2210 x400 >fax: 240-371-3256 >web: www.clearmetrix.com > > > > > >---(end of broadcast)--- >TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] query speed joining tables
Hi, Josh, I appreciate you share your experience here. I definitely don't have that many years' DBA experience behind, and are learning to get DB design right at the first place. What I stated is my observation on my project with over twenty multivalued detail tables. I have a selection query contained 200 characters, involving 10 tables, and using subquery. The performance is not bad after properly indexing, least than 3 second (what the planner says). I will have longer queries later and hope they won't have any performance problem. Thank you for recommending another DB book after the "Database Design For Mere Mortals". I will read the book. Vernon 15/01/2003 9:50:22 AM, "Josh Berkus" <[EMAIL PROTECTED]> wrote: >Vernon, > > > In regarding of recomposing multivalued field as a separated table, >I > > have observed some advantages and >> disadvantages of the approach. Good on search as you have pointed out > > and bad on updating data, two operations >> needed: deletion and insertion. A query may need to join a lot of > > table together. In Christ's personal application, for >> example, there are many mulitvalued fields such as relationship > > status other then ethnicity. There will be some very long >> and complex queries. > > Hey, it's your database. In my 8-year experience as a professional > DBA, few considerations ever outweigh normalization in a relational > database. You are merely trading the immediate inconvenience of having > to construct complex queries and data-saving functions for the >eventual > huge inconvenience (or possibly disaster) of having your data >corrupted > or at least having to modify it by hand, row-by-row. > >(Pardon me if I'm a little strident, but I've spend a good portion of > my career cleaning up other's, and sometimes my own, database design > mistakes and I had to see a disaster-in-the-making repeated) > >To put it another way: Your current strategy is saving a penny now in > order to pay a dollar tommorrow. > > For example, you currently store multiple ethnicities in a free-form > text field. What happens when: > 1) Your organization decides they need to split "Asian" into "Chinese" > and "Other Asian"? > 2) Someone types "aisan" by mistake? > 3) You stop tracking another ethnicity, and want to purge it from the > database? > 4) Your administrator decides that Ethnicity needs to be ordered as > "primary ethnicity" and "other ethnicities"? > 5) You need to do complex queries like (Asian and/or Caucasian but not > Hispanic or African)? Your current strategy would require 4 seperate > functional indexes to support that query, or do a table scan with 4 > row-by-row fuzzy text matches ... slow and memory-intensive either >way. > >As I said, it's your database, and if it's a low-budget project > destined to be thrown away in 3 months, then go for it. If, however, > you expect this database to be around for a while, you owe it to > yourself and your co-workers to design it right. > >If you want an education on database normalization, pick up Fabian > Pascal's "Practical Issues in Database Design". > >-Josh Berkus > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] query speed joining tables
16/01/2003 9:46:30 AM, "Josh Berkus" <[EMAIL PROTECTED]> wrote: >Vernon, > >> What I stated is my observation on my project with over twenty >> multivalued detail tables. I have a selection query >> contained 200 characters, involving 10 tables, and using subquery. >> The performance is not bad after properly indexing, >> least than 3 second (what the planner says). I will have longer >> queries later and hope they won't have any performance >> problem. > >Keep in mind that the complexity is all on your end, not the users'. > You can construct VIEWs, FUNCTIONs, TRIGGERs and RULEs which will make >the actual sophistication (i.e., 20 "detail tables") appear to the user >exactly as if there was only one flatfile table. > Well, my current position is a DB design as well as a DB user. I'm doing J2EE development without EJB. I currently have two ways of building a query. One is to set up a query string as a static string. This method is similar with the View in DB, but in application layer (Date Access Object). I apply this type of query strings on insertion, selection, updating, and deletion operations of a DB table. The other way to build a query string is used on selection operation for multiple table joined and/or involved. A query string is built dynmically due to whether or not any fields are examined. The characteristic of the application is that among of many fields a user may only want search on a few selected fields. I think this approach is better than to have all fields listed and fill in "%" for fields the user doesn't want to search on. (Please correct me if I'm wrong). But the building query function is as long as more than one hundred lines. >Frequently in database design, the design which is good for efficiency >and data integrity ... the "nromalized" design ... is baffling to >users. Fortunately, both SQL92-99 and PostgreSQL give us a whole >toolkit to let us "abstract" the normalized design into something the >users can handle. In fact, this is job #2 for the DBA in an >applications-development team (#1 is making sure all data is stored and >protected from corruption). > Please elaborate the above statement. I don't know any 'toolkit to let us "abstract" the normalized design into something the users can handle', other than something like View. >> Thank you for recommending another DB book after the "Database Design >> For Mere Mortals". I will read the book. > >That's a great book, too. Don't start on Pascal until *after* you >have finished "database design". I waited for the book from the local library for more than a month, but only took me less than a hour to scan over the whole book and grip the multivalued table design idea. > >-Josh Berkus > Vernon ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] performance question
20/01/2003 12:38:20 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >"Moritz Lennert" <[EMAIL PROTECTED]> writes: >> One question I asked myself is whether the use of char(2) is the best >> option. > >It may not be, particularly if you are running in a non-English locale. >Comparisons will invoke the standard library routine strcoll(), which is >depressingly slow in some locales, at least on some platforms. > In the case of selection operations involving multiple tables joined by userid that can be in various languages, is a potential performance trouble spot? Considering the number of tables anywhere from ten to twenty. >> The column (and most others in the table) contains codes that >> designate different characteristics (for ex. in a column 'sex' one would >> find '1'=male, '2'=female). > >If you are using numeric codes you are surely better off storing them >as integer or perhaps smallint (integer is less likely to create type >mismatch headaches, though). In the above example you are getting the >worst of both worlds: there's no mnemonic value in your data entries, >*and* you're paying for textual comparisons. > > regards, tom lane > >---(end of broadcast)--- >TIP 2: you can get off all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(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] null foreign key column
12/02/2003 2:24:49 PM, Dmitry Tkach <[EMAIL PROTECTED]> wrote: >You don't want it to be serial - just make it 'person_id in' Any reasons? > >I hope, it helps... > >Dima > >Arunachalam Jaisankar wrote: >> This is a multi-part message in MIME format. >> >> --=_NextPart_000_0005_01C2D1EE.61998D70 >> Content-Type: text/plain; >> charset="iso-8859-1" >> Content-Transfer-Encoding: quoted-printable >> >> Hi all, >> >> I would like to have a foreign key column in my table which allows null val= >> ue also. >> But the below create table sql command doesn't accept null value for person= >> _id. >> How to do in postgres? >> >> create table event >> ( >> event_id serialnot null, >> event_description char(255) , >> person_id serial, >> primary key (event_id), >> foreign key (person_id) >>references person (person_id) >> ); >> >> regards >> Jai >> --=_NextPart_000_0005_01C2D1EE.61998D70 >> Content-Type: text/html; >> charset="iso-8859-1" >> Content-Transfer-Encoding: quoted-printable >> >> >> >> >> >> >> >> >> Hi all, >> >> I would like to have a foreign key column = >> in my=20 >> table which allows null value also. >> But the below create table sql command doe= >> sn't=20 >> accept null value for person_id. >> How to do in postgres? >> = >> >> create table event( &nb= >> sp;=20 >> event_id &= >> nbsp; =20 >> serial &nb= >> sp; =20 >> not null, event_description&n= >> bsp;=20 >> char(255) = >> =20 >> , =20 >> person_id = >> =20 >> serial &nb= >> sp; =20 >> , primary key (event_id), forei= >> gn=20 >> key (person_id) references pe= >> rson=20 >> (person_id)); >> >> regards >> Jai >> >> --=_NextPart_000_0005_01C2D1EE.61998D70-- >> > > >---(end of broadcast)--- >TIP 2: you can get off all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Special characters in SQL queries
First a single quote in text, when a backforward slash in a file path, what other special characters need padded in two backslashes before using the data in a SQL statement? After hours' search in the PostgreSQL archive, I find a releted information on the http://www.ca.postgresql.org/users- lounge/docs/7.2/postgres/arrays.html. It's about array data though. How can I find out those characters so that I can pre-process them before a SQL query? Thanks for your information. Vernon ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
