[SQL] where'd the spaces come from
Hi all, Can someone please explain how to remove the spaces from the results of the query below. The current output is also included. What I want out of the query is something like 'NE/027-05'. psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' || to_char(m.mnumber,'00') as unitno from teams t, members m where m.mteam = t.tid;" unitno - SW/ 041- 03 SW/ 041- 05 NE/ 011- 06 NE/ 011- 01 NE/ 011- 03 NE/ 011- 02 NE/ 011- 10 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] where'd the spaces come from
Hi all, forget it, I've solved it. I converted the calls to: to_char(t.tnumber,'FM000') and it worked. Gary On Monday 23 July 2001 10:18 am, Gary Stainburn wrote: > Hi all, > > Can someone please explain how to remove the spaces from the results of the > query below. The current output is also included. What I want out of the > query is something like 'NE/027-05'. > > psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' || > to_char(m.mnumber,'00') as unitno from teams t, members m > where m.mteam = t.tid;" >unitno > - > SW/ 041- 03 > SW/ 041- 05 > NE/ 011- 06 > NE/ 011- 01 > NE/ 011- 03 > NE/ 011- 02 > NE/ 011- 10 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] where'd the spaces come from
From: "Gary Stainburn" <[EMAIL PROTECTED]> > psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' || > to_char(m.mnumber,'00') as unitno from teams t, members m > where m.mteam = t.tid;" >unitno > - > SW/ 041- 03 > SW/ 041- 05 Looks like a buglet in to_char()s handling of numbers (unless I misunderstand the way the formatting is supposed to work). select '[' || to_char(12,'x000') || ']'; ?column? -- [x 012] If you're running the current version, might be worth posting a bug report. You can work around it with something like: ... substr(to_char(t.tnumber,'000'),2,3) ... HTH - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Possible problems with cyclic references
Hi all, me again. I've been looking at the doc's again (must stop doing that!) I've been looking at the 'references' clause to implement referential integrity. My problem is that I'm wanting to create a cyclic reference, and was wondering what problems this may cause, e.g. when restoring from a pg_dump. I have a region table (rregion character(2), rname varchar(40), rliasson int4). I have a teams table (ttid int4, tregion character(2) references region(rregion),...) I have a members table (mid int4, mteam references teams(tid),.) Pretty straight forward so far, a member must be a part of a team and a team must be in a region. My problem is that I want to set rliasson as a reference to members (mid) as the Regional Liasson Officer for each region is a member. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Procedures in views
Does anyone know if the following is possible: Table A has a list of names with a column entry containing the name of an assoc pl/pgsql function Table B has a list of values Table C is a junction table btw A and B I would like to create a view that returns info from both tables and does a calc on some of the values in B based on the function name stored in A. Can this be done? If so, can someone point me in the right direction for documnetation. Thanks, Morgan ---(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] unique index on more than one field using functions
I'm trying create a unique index using more than one field and applying a function in one field to achieve case insensitive uniqueness but postgresql doesn't accept. create table a( id int primary key, id2 int not null, name varchar(50), unique(id2, lower(name)) ); Anyone have an idea ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Possible problems with cyclic references
Gary Stainburn wrote: > Hi all, me again. > > I've been looking at the doc's again (must stop doing that!) > > I've been looking at the 'references' clause to implement referential > integrity. My problem is that I'm wanting to create a cyclic reference, and > was wondering what problems this may cause, e.g. when restoring from a > pg_dump. > > I have a region table (rregion character(2), rname varchar(40), rliasson > int4). > I have a teams table (ttid int4, tregion character(2) references > region(rregion),...) > I have a members table (mid int4, mteam references teams(tid),.) > > Pretty straight forward so far, a member must be a part of a team and a team > must be in a region. My problem is that I want to set rliasson as a > reference to members (mid) as the Regional Liasson Officer for each region is > a member. No problem. pg_dump outputs commands to disable referential integrity checks during the restore. And you could even make rliasson NOT NULL. All you have to do then is to have the constraints INITIALLY DEFERRED and insert all the cyclic rows in one transaction. Add the constraint to the region table with ALTER TABLE after creating the members table. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: Get the tables names?
Hi Everybody!! Well I tried this (below) and it workt perfectly. Thanks a lot for ALL answers to my question! >select tablename from pg_tables where tablename not like 'pg_%'; Kind regards, Magnus Landahl "Joel Burton" <[EMAIL PROTECTED]> skrev i meddelandet [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > On Fri, 20 Jul 2001, Dado Feigenblatt wrote: > > > Magnus Landahl wrote: > > > > >Hi everybody! > > > > > >Is it possible to get the names of all tables in the database with a sql > > >query?? > > > > > >Best regards, > > > > > >Magnus > > > > > > > > > > > >---(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 > > > > > > > > > > > Not sure if this is the best way, but it works. > > SELECT relname , relowner from pg_class where relkind = 'r'; > > > > The only thing is that this includes system tables. > > So if you want to strip those you need to > > SELECT relname , relowner FROM pg_class WHERE relkind = 'r' and > > relowner != 26; > > > > Is user postgres always 26? Maybe you have to find that out first. > > system tables all ~ '^pg', which is probably a better check than > user=postgresql. > > > hth, > -- > Joel Burton <[EMAIL PROTECTED]> > Director of Information Systems, Support Center of Washington > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: Records exactly the same.
> Well, there isn't an easy answer for you ... because you've designed > your database wrong. Records should *never* be the same. That is, ni > fact, one of the cardinal rules of Relational Database Design. Well, I started with "I'm a beginner". But I'm sure there's no reason NOT to accept two records that are exactly the same. In the example I gave, it is clear that the information I want to store can contain two records that are exactly the same; doing the same thing, on the same day, for the same amount of time. In this case it is the technical structure that doesn't want it like that. So I have to change it to make it work. Fons. [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
[SQL] Searching multiple fields from multiple tables
Hi, I should make search engine for psql environment. I works so that user sets search criterias (multiple words) and gets answers (like google does). Let say that I have one common key (primary) which is used as foreign key in rest of the tables. So I have many one_to_many relations. In this case what is the best way to implement SQL-query. Performance of the query is the most important thing. Should I use temporary table and collect the keys into it one by one whit INSERT INTO statements. Single SQL query will be quite complex and slow. After all, what kind of SQL-query philosophy is suitable? ---(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] how can we get total records in pg server?
Hi all, how can we get the COUNT of total records in the db server? hope this could be simple for pg experts. thankx in advance! Regards, Bhuvaneswar. Eighty percent of married men cheat in America. The rest cheat in Europe. -- Jackie Mason ---(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] Re: Records exactly the same.
"Fons Rave" <[EMAIL PROTECTED]> wrote in message 9jbrpj$r67$[EMAIL PROTECTED]">news:9jbrpj$r67$[EMAIL PROTECTED]... > > Well, there isn't an easy answer for you ... because you've designed > > your database wrong. Records should *never* be the same. That is, ni > > fact, one of the cardinal rules of Relational Database Design. > > Well, I started with "I'm a beginner". But I'm sure there's no reason NOT to > accept two records that are exactly the same. In the example I gave, it is clear > that the information I want to store can contain two records that are exactly > the same; doing the same thing, on the same day, for the same amount of time. In > this case it is the technical structure that doesn't want it like that. So I > have to change it to make it work. OK - so you enter two records, one hour in the morning and one hour in the afternoon. You then realise you *didn't* work in the afternoon and you want to delete one of the records. You can't because you can't build a query that specifies the one without the other. You can't delete only one, you can't update only one and you can't select only one. This is because the only way to pick out a piece of data in SQL is by it's values - there is no "file position" or "record number" (well there's OID but you're best off leaving that). If you want to store two separate pieces of information, make sure they differ in some way (timestamp, serial-number, whatever). If you can't think of anything useful to distinguish between records add a SERIAL column called "id". If you don't *want* two records, but just want the total hours worked that day, then restrict your system to one record per person (or job or whatever it was) per day. You can either do this in your application, or (better but harder) do it with triggers in PostgreSQL itself. If you can't quite see where the problem is at the present time, just add an "id" or "seq" field of type SERIAL and get on with your project. The reason people on the list are shouting is because we got burnt at some time with exactly this thing and we're trying to stop that happening to you. Oh - check the glossary at techdocs.postgresql.org and look at "Normal Forms" (numbered 1st NF, 2nd NF etc). Check out a book on RDBMS theory too if you get a chance. HTH - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Possible problems with cyclic references
Hi Jan, Thanks for the additional info. I did, having already posted the msg, tru to create the references, but found that it would not let me do that as I was trying to create a reference to a table that didn't exist yet. I ended up setting up a one-way reference, running pg_dump to see how to set up the reference after creating the tables (it uses create triggers), and then changing/adding these lines to my create script. Your way seems much nicer. Gary On Monday 23 July 2001 3:18 pm, Jan Wieck wrote: > Gary Stainburn wrote: > > Hi all, me again. > > > > I've been looking at the doc's again (must stop doing that!) > > > > I've been looking at the 'references' clause to implement referential > > integrity. My problem is that I'm wanting to create a cyclic reference, > > and was wondering what problems this may cause, e.g. when restoring from > > a pg_dump. > > > > I have a region table (rregion character(2), rname varchar(40), rliasson > > int4). > > I have a teams table (ttid int4, tregion character(2) references > > region(rregion),...) > > I have a members table (mid int4, mteam references teams(tid),.) > > > > Pretty straight forward so far, a member must be a part of a team and a > > team must be in a region. My problem is that I want to set rliasson as a > > reference to members (mid) as the Regional Liasson Officer for each > > region is a member. > > No problem. pg_dump outputs commands to disable referential > integrity checks during the restore. > > And you could even make rliasson NOT NULL. All you have to do > then is to have the constraints INITIALLY DEFERRED and insert > all the cyclic rows in one transaction. > > Add the constraint to the region table with ALTER TABLE after > creating the members table. > > > Jan > > -- > > #==# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #== [EMAIL PROTECTED] # > > > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Re: PLpgSQL
Josh Berkus wrote: > Dado, > > > But still, the $1 is still plaguing me. > > > > Here's your problem: > > > >>CREATE FUNCTION new_proj_pts_seq(int4) > > >>RETURNS text > > >>AS 'DECLARE > > >>proj_ID alias for $1; > > >>seq_name TEXT; > > >>BEGIN > > >>seq_name := ''proj_pts_'' || proj_ID; > > >>create sequence seq_name; > > >>END; > > >>RETURNS seq_name;' > > This should read: RETURN seq_name > > No "S". And should be placed before the END; > > > >>LANGUAGE 'plpgsql'; > > -Josh Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.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
[SQL] RE: Records exactly the same.
The content of your record is not limited to user data. Including a field that provides a unique key is simple: look at the documentation for data type SERIAL for an easy way to do this. You can also include information about when the record was inserted, and by whom, just by including fields in your table definition like: when_inserted timestamp default now(), who_insertedtext default current_user, which will be populated automatically every time a record is inserted. Also, see documentation on triggers for more sophisticated ways of doing this kind of thing. > -Original Message- > From: Fons Rave [SMTP:[EMAIL PROTECTED]] > Sent: Saturday, July 21, 2001 7:15 AM > To: [EMAIL PROTECTED] > Subject: Re: Records exactly the same. > > > Well, there isn't an easy answer for you ... because you've designed > > your database wrong. Records should *never* be the same. That is, ni > > fact, one of the cardinal rules of Relational Database Design. > > Well, I started with "I'm a beginner". But I'm sure there's no reason NOT > to > accept two records that are exactly the same. In the example I gave, it is > clear > that the information I want to store can contain two records that are > exactly > the same; doing the same thing, on the same day, for the same amount of > time. In > this case it is the technical structure that doesn't want it like that. So > I > have to change it to make it work. > > Fons. > [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 ---(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] position(text,text) function
I'm having trouble using the position function, it's a simple built-in function.
It returns the index of a substring within a main string.
But, I just can't get it to work, it always gives this silly parser error.
I've tried it in a variety of ways with variables instead of constants casting
to text and using it in other contexts, (ie. in functions, not just in a
select). What am I doing wrong? Is this not the correct syntax for a two
argument function?
test=# select position('hello','el');
ERROR: parser: parse error at or near ","
Karl Orbell.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Full-text Indexing and Primary Keys
Folks,
1. Can anyone explain to me what "full-text indexing" is, and why we do
or don't need it for Postgres? The marketing types keep asking me about
it ("buzzword o' the day") and I don't have an answer for them.
2. I propose that future versions of PostgreSQL require a primary key at
table creation. Frankly, I'm a little mystified as to why this was not
done already, but it's not too late to correct ...
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: Records exactly the same.
Fons, > Well, I started with "I'm a beginner". But I'm sure there's no reason > NOT to > accept two records that are exactly the same. In the example I gave, > it is clear > that the information I want to store can contain two records that are > exactly > the same; doing the same thing, on the same day, for the same amount > of time. In > this case it is the technical structure that doesn't want it like > that. So I > have to change it to make it work. Yes, there is a reason not to accept them. And the requirement is conceptual, not technical -- that is, the way relational databases work -- *all* relational databases -- is founded on 12 concepts, one of which is the uniqueness of relations (records). Or, to phrase it another way, if your design allows identical records (person, task, time period) then how are *you* going to seperate illegitimate duplicate data-entry from legitimate identical records? You can't, and the questionable accuracy of your tables will haunt you for years. I'm trying to save you months of pain & suffering here by conveying a very important concept in database design, one I learned the hard way. For a more exhaustive explanation of the necessity of uniqueness and primary keys, please pick up a copy of Fabian Pascal's "Practical Issues in Database Design." -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(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] Full-text Indexing and Primary Keys
Josh Berkus wrote:
> 1. Can anyone explain to me what "full-text indexing" is, and why we do
> or don't need it for Postgres? The marketing types keep asking me about
> it ("buzzword o' the day") and I don't have an answer for them.
Full text indexing: something like google does for HTML pages, or
fts.postgresql.org does for the PostgresSQL mailing list archives.
Good full text indexing programs understand something about the
language they're indexing and automatically deal with plurals,
suffixes, etc.
Full text indexing is useful for many applications that include text
data along with other more structured information. It is painful to
have to use SQL plus some other facility for searching; just for
starters it usually means keeping a second copy of the data.
I suspect the push for full text indexing in PostgreSQL is stronger
now that the 8KB row size limit that "ancient" releases had is gone.
> 2. I propose that future versions of PostgreSQL require a primary key at
> table creation. Frankly, I'm a little mystified as to why this was not
> done already, but it's not too late to correct ...
SQL uses a "bag" model, not a "set" model. Duplicates are allowed. I
doubt that PostgreSQL wants to step away from both the standards and
current practice in this area.
Regards,
Giles
---(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] RE: position(text,text) function
You are confusing the syntax of two similar functions:
position ('substring' in 'string')
strpos ('string', 'substring').
I have a feeling that "position" actually calls "strpos", but I am guessing
on that.
> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, July 23, 2001 9:27 AM
> To: [EMAIL PROTECTED]
> Subject: position(text,text) function
>
> I'm having trouble using the position function, it's a simple built-in
> function.
>
> It returns the index of a substring within a main string.
>
> But, I just can't get it to work, it always gives this silly parser error.
> I've tried it in a variety of ways with variables instead of constants
> casting
> to text and using it in other contexts, (ie. in functions, not just in a
> select). What am I doing wrong? Is this not the correct syntax for a two
>
> argument function?
>
> test=# select position('hello','el');
> ERROR: parser: parse error at or near ","
>
>
> Karl Orbell.
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Full-text Indexing and Primary Keys
Josh,
Full Text Indexing
It will allow you to store text into a database and find portions of this
text again, based on a few words of the text in the record.
Say, for example, you want to store the bible in a PostGres database. You
will store these fields:
1. Book
2. Chapter
3. Verse
4. Verse text
If you know your bible, you probably can find any passage by just going to
book/chapter/verse directly. But if you're not, and you just want to find
all verses, where "Nathan", "Solomon" and "mother" is mentioned, you need
something which lets you do that - and fast.
What you want is something which does:
select verse_text from bible where verse_text contains 'Nathan & Solomon &
mother';
or even:
select verse_text from bible where verse_text contains 'Nath* & Solo* &
moth*';
This would be similar to "find file on hard drive by content" - which, if
not indexed, takes forever.
Hope this makes the issue a little more clear.
Best regards,
Chris
- Original Message -
From: "Josh Berkus" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, July 23, 2001 10:32 AM
Subject: [SQL] Full-text Indexing and Primary Keys
> Folks,
>
> 1. Can anyone explain to me what "full-text indexing" is, and why we do
> or don't need it for Postgres? The marketing types keep asking me about
> it ("buzzword o' the day") and I don't have an answer for them.
>
> 2. I propose that future versions of PostgreSQL require a primary key at
> table creation. Frankly, I'm a little mystified as to why this was not
> done already, but it's not too late to correct ...
>
> -Josh
>
>
>
> __AGLIO DATABASE SOLUTIONS___
>Josh Berkus
> Complete information technology [EMAIL PROTECTED]
>and data management solutions (415) 565-7293
> for law firms, small businessesfax 621-2533
> and non-profit organizations. San Francisco
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] position(text,text) function
The correct way would be:
select position('el' in 'Hello');
Dorin
At 02:27 PM 7/23/01 +, Karl Orbell wrote:
I'm having trouble using the position
function, it's a simple built-in function.
It returns the index of a substring within a main string.
But, I just can't get it to work, it always gives this silly parser
error.
I've tried it in a variety of ways with variables instead of constants
casting
to text and using it in other contexts, (ie. in functions, not just in a
select). What am I doing wrong? Is this not the correct
syntax for a two
argument function?
test=# select position('hello','el');
ERROR: parser: parse error at or near ","
Karl Orbell.
---(end of
broadcast)---
TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]
Re: [SQL] RE: position(text,text) function
Jeff, Karl,
Actually, he's confusing 3 functions. See postgresql.org --> user
lounge --> interactive docs --> functions and operators --> string
functions.
-Josh
> You are confusing the syntax of two similar functions:
> position ('substring' in 'string')
> strpos ('string', 'substring').
> I have a feeling that "position" actually calls "strpos", but I am
> guessing
> on that.
>
> > -Original Message-
> > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> > Sent: Monday, July 23, 2001 9:27 AM
> > To: [EMAIL PROTECTED]
> > Subject: position(text,text) function
> >
> > I'm having trouble using the position function, it's a simple
> built-in
> > function.
> >
> > It returns the index of a substring within a main string.
> >
> > But, I just can't get it to work, it always gives this silly parser
> error.
> > I've tried it in a variety of ways with variables instead of
> constants
> > casting
> > to text and using it in other contexts, (ie. in functions, not just
> in a
> > select). What am I doing wrong? Is this not the correct syntax
> for a two
> >
> > argument function?
> >
> > test=# select position('hello','el');
> > ERROR: parser: parse error at or near ","
> >
> >
> > Karl Orbell.
> >
> >
> > ---(end of
> broadcast)---
> > TIP 1: subscribe and unsubscribe commands go to
> [EMAIL PROTECTED]
>
> ---(end of
> broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(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] unique index on more than one field using functions
On 22 Jul 2001, Domingo Alvarez Duarte wrote: > I'm trying create a unique index using more than one field and > applying a function in one field to achieve case insensitive > uniqueness but postgresql doesn't accept. > > create table a( > > id int primary key, > id2 int not null, > name varchar(50), > unique(id2, lower(name)) > ); > > Anyone have an idea ? IIRC, Functional indexes are constrained to a single function with one or more column references (no constants, etc), so you can't precisely do the above directly. You might be able to make a function which takes id2 and name and combines them in some way returning a single varchar and make the unique index on that result. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] position(text,text) function
position ( 'el' in 'hello') ;
>From: [EMAIL PROTECTED] (Karl Orbell)
>To: [EMAIL PROTECTED]
>Subject: [SQL] position(text,text) function
>Date: Mon, 23 Jul 2001 14:27:15 + (UTC)
>
>I'm having trouble using the position function, it's a simple built-in
>function.
>
>It returns the index of a substring within a main string.
>
>But, I just can't get it to work, it always gives this silly parser error.
>I've tried it in a variety of ways with variables instead of constants
>casting
>to text and using it in other contexts, (ie. in functions, not just in a
>select). What am I doing wrong? Is this not the correct syntax for a two
>argument function?
>
>test=# select position('hello','el');
>ERROR: parser: parse error at or near ","
>
>
>Karl Orbell.
>
>
>---(end of broadcast)---
>TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Determining if two subnets intersect
Is there some efficient PostgreSQL expression which is true if and only if two subnets (given as values of type cidr) have non-empty intersection (even if the intersection is not a CIDR network)? -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://cert.uni-stuttgart.de/ RUS-CERT +49-711-685-5973/fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: Get the tables names?
On Mon, 23 Jul 2001, dado feigenblatt wrote: > > system tables all ~ '^pg', which is probably a better check than > > user=postgresql. > > You never know when someone will name their tables starting with "pg". > Well, you never know when someone will create their tables as user postgres > either. You probably want to check for '^pg_' since you shouldn't be able to create user table names starting with pg_. As the administrator on my home test system: sszabo=> create table "pg_a"(A int); ERROR: Illegal class name 'pg_a' The 'pg_' name prefix is reserved for system catalogs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
