Re: [SQL] Unique index and unique constraint

JORGE MALDONADO escribió: I guess I am understanding that it is possible to set a unique index or a unique constraint in a table, but I cannot fully understand the difference, even though I have Google some articles about it. I will very much appreciate any guidance. The SQL standard does not

Re: [SQL] Unquoted column names fold to lower case

Dev Kumkar escribió: But what I am asking here is if an alias name is provided be it upper case, lower case, or a mix then shouldn't it be preserved as as it is given. All this talk is when alias names are unquoted, when quoted then its standard behavior as seen in other databases. Aliases

Re: [SQL] regexp_replace behavior

Marcin Krawczyk escribió: Hi list, I'm trying to use regexp_replace to get rid of all occurrences of certain sub strings from my string. What I'm doing is: SELECT regexp_replace('F0301 305-149-101-0 F0302 {x1} 12W47 0635H {tt}{POL23423423}', E'\{.+\}', '', 'g') so get rid of whatever

Re: Fwd: [SQL] i want small information regarding postgres

Excerpts from Samuel Gendler's message of lun ene 02 08:44:53 -0300 2012: would someone with the appropriate authority please unsubscribe this person's email address from this list so we don't all get a bounce message after every email we send to the list? Thanks. Just did it. In the

Re: [SQL] 9.0rc1 - query of view produces unexpected results

Excerpts from Nathan Grange's message of sáb sep 11 21:31:04 -0400 2010: Hello list, I don't know if it's me, or maybe even the way I designed these dependencies, but I'm getting unexpected results when i query a specific view. Included are a buildDemo.sql file that will create the

Re: [SQL] How do you do the opposite of regexp_split_to_table?

Excerpts from John Gage's message of mar ago 03 16:21:58 -0400 2010: Grouping by summing numerical fields seems to be straightforward, but grouping by concatenating text fields escapes my search of the documentation. You can create a new custom aggregate function that does this. It's

Re: [SQL] WAL-files restore and nextval('PK')

to the logged values, which are necessarily higher than the values they last delivered before the crash. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make

Re: [SQL] Is there any function to test for numeric ips?

with an exception block; return false inside the exception, true otherwise. It looks something like begin perform $1::inet; return true; exception when invalid_something then return false end; I don't remember the exact syntax and the exception name but that should get you started. -- Alvaro

Re: [SQL] get sequence name from table name

Uwe Maiwald wrote: how to get the name of the sequence that is responsible for setting the autoincrement value of a tables primary key column? You can use the pg_get_serial_sequence() function. You need the name of the column in addition to the table name though. -- Alvaro Herrera

Re: [SQL] [GENERAL] Data audit trail techniques in postgresql

Nathaniel Smith wrote: What do others use to accomplish this? Do most pg users just write triggers by hand? Or is there some nice auditing module that Google just isn't revealing to me? I think tablelog (to be found in pgfoundry too) is the most commonly used audit module. -- Alvaro Herrera

Re: [SQL] [GENERAL] Data audit trail techniques in postgresql

Rob Sargent escribió: tablelog doesn't appear any more lively than the OPs audittrail2. Perhaps, but I have heard of people using it successfully recently, whereas Nathaniel reported that audittrail2 seems to have obvious bugs. -- Alvaro Herrerahttp

Re: [SQL] mail alert

- the email can be sent on whatever schedule fits the listener program - the listener client can run elsewhere, not only in the database server - any further external processing can take place at that time, without bothering the database server - other stuff I don't recall ATM -- Alvaro Herrera

Re: [SQL] mail alert

fairly frequently, it is probably better to stay with polling. (This is what Skype's replication system does, and Hannu Krossing says what, are you going to optimize for the time when the server is idle?) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL

Re: [SQL] Bit by commands ignored until end of transaction block again

, as we found out, is that the function must always have control at the same level of transaction nestedness in SPI; you can't just let the user define and release savepoints arbitrarily.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command

Re: [SQL] function returning a cursor and a scalar

the function explicitly RETURN a refcursor? Why wouldn't you just use two OUT params? BTW how do you plan on returning the number of matches? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: [SQL] proposal for a CookBook in postgresql.org

the contents? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [DOCS] [SQL] proposal for a CookBook in postgresql.org

Bruce Momjian escribió: Alvaro Herrera wrote: I don't know about Roberto Mello's site. Did we get a copyright transfer or a license saying we could use the contents? Nope, but I assumed it was BSD-licensed. I see this Josh Berkus copyright: --Copyright Josh Berkus, j

Re: [SQL] proposal for a CookBook in postgresql.org

Pavel Stehule escribió: others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks There's a lot of good stuff in there ... would you care about copying/moving it to wiki.postgresql.org/wiki/Snippets ? -- Alvaro Herrerahttp://www.CommandPrompt.com

Re: [SQL] proposal for a CookBook in postgresql.org

Pavel Stehule escribió: 2009/5/18 Alvaro Herrera alvhe...@commandprompt.com: Pavel Stehule escribió: others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks There's a lot of good stuff in there ... would you care about copying/moving it to wiki.postgresql.org/wiki

Re: [SQL] Distinct oddity

Maximilian Tyrtania wrote: am 11.05.2009 16:38 Uhr schrieb Alvaro Herrera unter alvhe...@commandprompt.com: Note that the de_DE locale uses Latin9 encoding, which is incompatible with UTF8. I'd try checking if the problem is reproducible in de_DE.utf8 (you need to create a new database

Re: [SQL] Distinct oddity

Maximilian Tyrtania wrote: am 10.05.2009 4:58 Uhr schrieb Alvaro Herrera unter alvhe...@commandprompt.com: I'd try checking if the problem is reproducible in de_DE.utf8 (you need to create a new database for testing, obviously). Wait a minute. I need to re- initdb with de_DE.UTF-8, don't

Re: [SQL] Distinct oddity

not, then the incompatible locale definition is causing the problem. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

Re: [SQL] SQL to Check whether AN HOUR PERIOD is between start and end timestamps

:59', '2008-12-07 08:59:59') What I'm not so sure about is how optimizable this construct is. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes

Re: [SQL] Re: [SQL] Relator io da composiçao de FKs e PKs

Oliveiros Cristina wrote: Escreve a tua dúvida em inglês, por favor, isto é uma lista de mailing em ingles. Isn't there a portuguese mailing list? Should one be created? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt

Re: [SQL] Object create date

of a careless programmer who forgets to update the tags. Myself being the prime suspect. :) You don't update the tags. They are updated automatically by CVS (or Subversion, whatever you use) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command

Re: [SQL] How can this be legal syntax

is a declared variable name). Nasty. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

Re: [SQL] [HACKERS] Reg: Nested query

as contrib/ltree. In the upcoming 8.4 version you will be able to write queries with the WITH RECURSIVE construct to handle this directly in SQL. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: [SQL] JOIN results of refcursor functions

a (2 filas) I guess you should be able to do the same with cursor operations. I haven't seen how you use refcursor in a plpgsql function. -- Alvaro Herrera

Re: [SQL] JOIN results of refcursor functions

for setof. Then use OUT variables. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?

privilege from plain users, and have your delete trigger be a security definer function. There would be another security definer function to delete non-deduced rows which users can call directly. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication

Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?

, maybe you can turn a regular DELETE into a function call by using an INSTEAD rule, but I'm not sure. That way they would just do a plain DELETE and the sec-def function would be called instead. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication

Re: [SQL] Function Syntax involving pipes and ' marks?

to read) v_where varchar(256) := $a$ where m.jb_date $a$||p_date + integer '1'|| $a$ and m.jb_date =$b$ $a$ ||p_date|| $a$ $b$ $a$; -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc

Re: [SQL] DELETE with JOIN

that Oracle implementation details apply to Postgres, because they do not, most of the time. They certainly don't in this case. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql

Re: [SQL] Single Quote in tsquery

it calls simplified regular expressions or some such, which includes character classes (delimited by brackets). So it would seem that what you show is actually supported and correct. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command

Re: [SQL] Query prepared plan

Emi Lu wrote: Similar to \dt to show all tables, within one session, may I know the command to list all prepared query plan please? select * from pg_prepared_statements; -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt

Re: [SQL] Size or efficiency differences varchar(128) vs. varchar(32)

Emi Lu wrote: May I know does varchar(128) and varchar(32) will cause any size or efficiency differences? None at all. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql

Re: [SQL] pg_advisory_lock(bigint) vs. LOCK TABLE

Mark Roberts wrote: On Thu, 2008-07-17 at 12:16 -0400, Alvaro Herrera wrote: Volkan YAZICI wrote: Hi, What's the difference between below two queue implementations? They are two different lock spaces. pg_advisory_lock does not conflict with regular system locks, whereas LOCK

Re: [SQL] pg_advisory_lock(bigint) vs. LOCK TABLE

Volkan YAZICI wrote: Hi, What's the difference between below two queue implementations? They are two different lock spaces. pg_advisory_lock does not conflict with regular system locks, whereas LOCK TABLE does. -- Alvaro Herrerahttp://www.CommandPrompt.com

Re: [SQL] Rollback in Postgres

it would come in handy wouldn't have enabled it. (FWIW this feature used to exist in the Berkeley code, under the cool name time travel, and was removed a long time ago.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom

Re: [SQL] Rollback in Postgres

. It just saves the undo that is created anyway for any DML anyway. That undo is already on disk. Which means it doesn't work for us, because we don't have UNDO (we only have REDO). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting

Re: [SQL] exception handling and CONTINUE

this: for a in select * from xxx loop begin insert into yyy values (...) exception when unique_violation then null; -- noop, just for clarity end; end loop; -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development

Re: [SQL] Best practice for long-lived journal tables: bigint or recycling IDs?

of the int type in the normal course of things. I see two options to prepare for that: 3. Deal with wraparound by ensuring that the applications behave sanely -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development

Re: [SQL] Re: Best practice for long-lived journal tables: bigint or recycling IDs?

Mark Stosberg wrote: On Tue, 8 Jul 2008 17:20:13 -0400 Alvaro Herrera [EMAIL PROTECTED] wrote: 3. Deal with wraparound by ensuring that the applications behave sanely Wrap-around? Exceeding the max size of int looks more like a brick wall than wrap-around to me: insert into t

Re: [SQL] Auto-formatting timestamps?

the tables with timestamps (and creating appropriate rules for updating views) would be a huge administrative PITA. If you're really set about that, you can add a new DateStyle setting. It's a bit of C hacking. (Or you can hire someone to do it for you.) -- Alvaro Herrera

Re: [SQL] psql: no schema info

| Modificadores -+-+- col1| integer | default nextval('old_s.seq1'::regclass) Another option would be to set it to the given schema, so that any name not on that schema is qualified. -- Alvaro Herrera

Re: [SQL] pl/PgSQL, variable names in NEW

. Is there anything it can do that can't be done with PL/Perl native calls? Question for plperl hackers: Should we remove the mention of DBD::PgSPI from the PL/Perl manual? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development

Re: [DOCS] [HACKERS] [SQL] pl/PgSQL, variable names in NEW

. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] pl/PgSQL, variable names in NEW

Martin Edlman wrote: I don't want to rewrite whole trigger to plPerl as I would have to use DBD-PgSPI. Huh? Certainly not -- there are functions in PL/Perl for this. See spi_exec_query in http://www.postgresql.org/docs/8.3/static/plperl-database.html -- Alvaro Herrera

Re: [SQL] Pattern Matchig

Tk421 escribió: The result is only record number 3. How can i search the entire FOOBAR word? The result wanted must be all, excepting 5. Something like this: select code from table where text ~ '[[::]]foobar[[::]]' -- Alvaro Herrerahttp

Re: [SQL] duplicate key violates unique constraint

want to use the SQL window you could use SAVEPOINT and ROLLBACK TO. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3

Re: [SQL] SQL standards in Mysql

Ken Johanson wrote: Here's one Mysql developer's response to adding (fixing) the integer/bigint/tinyint types to their CAST function: http://bugs.mysql.com/bug.php?id=34562 So they are anal too, but in the opposite direction? -- Alvaro Herrerahttp

Re: [SQL] Usage of UUID with 8.3 (Windows)

be to make that work. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map

Re: [SQL] transaction and triggers

-- the transaction only does a NOTIFY, which is certain to be delivered only when the transaction commits. So if it aborts, no spurious write occurs. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: [SQL] trigger for TRUNCATE?

Replicator does replicate TRUNCATE commands. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives

Re: [SQL] trigger for TRUNCATE?

Tom Lane escribió: Alvaro Herrera [EMAIL PROTECTED] writes: Gerardo Herzig escribi�: Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level thing than i think. TRUNCATE currently does not fire triggers, but that doesn't mean it's impossible to do it. I think it would

Re: [SQL] Foreign Key for multi PK or design question

Q2, 5 and 6 - the user.fk would store values 2,5,6 - but I have passed most of logic to the answer table. That would have made no sense. Does this look correct? or most efficient? Yeah it seems sane, however you have forgotten to add NOT NULL to the FK fields. -- Alvaro Herrera

Re: [SQL] SQL INSERT/TRIGGER Help

field)? It's not a regex. I assume you are confused because it says regclass. This is shorthand for registered class (where class is a synonymous for relation, in this case a sequence). -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC La felicidad no es mañana. La

Re: [SQL] statement-level trigger sample out there?

a single update adding the number in memory. This would work only if the FOR EACH STATEMENT trigger was promised to be executed after all the FOR EACH ROW triggers were called. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J No reniegues de lo que alguna vez creíste

Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE

about their storage system, but I'm not completely sure that it can be really blamed. This is on 8.1.10. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 Major Fambrough: You wish to see the frontier? John Dunbar: Yes sir, before it's gone

Re: [SQL] Select in From clause

into a table name. One way to construct queries is to build plpgsql functions and use EXECUTE. However, the approach you are using looks like bad practice (read: bad database design). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc

Re: [HACKERS] [SQL] Why does the sequence skip a number with generate_series?

, as Stephan says above, it works fine. Anywhere else they have strange behavior and they are supported only because of backwards compatibility. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: [SQL] Extracting hostname from URI column

. A very good resource on regular expressions is Mastering Regular Expressions by Jeffrey Friedl, now in its third edition: http://www.oreilly.com/catalog/regex3/ I read the first ed. years ago and it was very illuminating. -- Alvaro Herrera Developer, http

Re: [SQL] postgresql HEAD build failure

and let the build directory be constructed again by configure. That gets things in sync. Now that I look closer, however, this was always with source dirs, not include dirs. I think the problem here is that you're not passing -d to cvs update. -- Alvaro Herrerahttp

Re: [SQL] postgresql HEAD build failure

John Summerfield wrote: Alvaro Herrera wrote: Hmm, try deleting the build tree and start again. Sometimes, what happens to me is that somebody adds a new directory, and my build tree does not contain it. It's easy to fix: when I had a slower computer what I did was create the offending

Re: [SQL] Incomprehensible dogged sort in Merge Join

, yes. Try 8.2 at the very least, but in some cases you're going to get current CVS HEAD does it better, so if you want to suggest improvements to the planner you should be really looking into that. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC No reniegues de lo

Re: [SQL] SELECT syntax synopsis: column_definition?

RECORD needs it, when you don't use OUT params. Before OUT params existed, it was the only way to use those functions. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 The eagle never lost so much time, as when he submitted to learn of the crow. (William Blake

Re: [SQL] Starting autovacuum in postgresql-8.1.9

to enable stats_row_level too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet

Re: [SQL] Vacation days

of plan. :) Instead of the whole week, I just wanted to take next monday and tuesday off. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast

Re: [SQL] FATAL: cache lookup failed for function 1003

--auth) works for psql (PostgreSQL) 7.4.6 so I'm guessing I'm close. What might be the problem? Try doing a make distclean and recompile. -- Alvaro Herrerahttp://www.advogato.org/person/alvherre Hay dos momentos en la vida de un hombre en los que no debería especular

Re: [SQL] FATAL: cache lookup failed for function 1003

John Summerfield wrote: Alvaro Herrera wrote: John Summerfield wrote: Is this the right list for this? I'm off the 'net atm and can't easily check. I'm running on self-built RHEL 4. I built from cvs this morning, and get the above error. Running grep over the source doesn't show

Re: [SQL] auto vacuuming

into the core. So the way to find if it's installed is yes. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your

Re: [SQL] auto vacuuming

Sumeet escribió: Then do i need to still provide the auto vacumming options in the postgres.conf file or these options are automatically taken care of. You have to enable the autovacuum setting in postgresql.conf. It is not enabled by default. -- Alvaro Herrera

Re: [SQL] Subqueryes

date = (current_date + (Select daysToAdd from base.Table1 where myFKey_id = Table1Id) ) where Expire_Date = now()::Date; So what is the error message? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc

Re: [SQL] unsubscribe

no effect. The pgsql-es-ayuda list had it set to no (I just changed it), and it said This value was determined by the DEFAULT settings. So maybe the other lists are missing this setting. I remember Bruce complaining about this not long ago (and threatening with Mailman!?) -- Alvaro Herrera

Re: [SQL] INSERT INTO

Shavonne Marietta Wijesinghe wrote: Thanks alot What happens if you try to insert a string with $$ on it? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end

Re: [SQL] For loop

Ezequias R. da Rocha wrote: They are not working well. Allways the same error. (if while) ERROR: syntax error at or near WHILE SQL state: 42601 Character: 1 You may have a problem in the code just _before_ these lines. -- Alvaro Herrerahttp

Re: [SQL] For loop

as is, but you should get the hang of it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free

Re: [SQL] import CSV file

? Is the server on the same machine that's running psql? If not, then this fails because it tries to open the file server-side. The suggested workaround is to use psql's \copy. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom

Re: [SQL] A form of inheritance with PostgreSQL

Greg Toombs wrote: What disaster do you foresee? Is that version unstable? Yes. There are known, unfixed bugs, and architectural problems that cannot be fixed. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc

Re: [SQL] Uppercase and Lowercase

Shavonne Marietta Wijesinghe wrote: Hey thanks Bart. it worked ;) but sadly what it does is changes VIA SENATO in to Via senato but what i need is Via Senato Anyoneee?? initcap() does what you want. -- Alvaro Herrerahttp://www.CommandPrompt.com

Re: [SQL] There is a different cast than ::MyOtherType() ?

Ezequias Rodrigues da Rocha escribió: Hi list, My Delphi app does not suport this kind of cast: Select id, desc::Varchar(50) from myTable Try select id, cast(desc as varchar(50)) from yourTable -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL

Re: [SQL] Retrieving 'Credit' when 'C'

Ezequias Rodrigues da Rocha escribió: Hello, Does anyone know how to make a Select that even having a Char(1) with the letter C the statement makes the rows appearing 'CREDIT' ? case when column = 'C' then 'CREDIT' end -- Alvaro Herrerahttp

Re: [SQL] Odd PL/PgSQL Error -- relation X does not exist when using index expression

text); CREATE INDEX foo_idx ON foo (lower(t)), 0); Hmm, are we short of a CommandCounterIncrement in the middle of both commands? Does the same error show up if you do SPI_exec(CREATE TABLE foo (t text);, 0); SPI_exec(CREATE INDEX foo_idx ON foo (lower(t)), 0); ? -- Alvaro Herrera

Re: [SQL] COPY FROM - force a value

they will be blocked of the table during that transaction. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL

Re: [SQL] huge disparities in =/IN/BETWEEN performance

buried because you've lost performance doing useless work. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9

Re: [SQL] Compilation Error AIX

-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -I../../../src/include -c -o dynloader.o dynloader.c Where does the src/backend/port/dynloader.c symlink point to? What AIX version is this? GCC version? -- Alvaro Herrerahttp://www.CommandPrompt.com

Re: [SQL] Evaluate a variable

Luís Sousa wrote: Hi, Is there anyway to evaluate a variable in plpgsql, like eval on PHP? Suppose the example: my_var:=''some value!''; a:=''my_var''; b:=eval!! a; I already tried b:=EXECUTE a; without luck! Maybe stashing a SELECT in front? -- Alvaro Herrera

Re: [SQL] Droping indexes

(for example, the eventackuser could probably be put on a separate table and on this one store just an integer ID). This will make the table and the index on that column a lot smaller. 3. add more disks to your installation 4. research a more effective VACUUM policy -- Alvaro Herrera

Re: [SQL] Finding gaps in scheduled events

Marcin Stępnicki wrote: Now I need to create a query to find hours at which each of the type can start. So, if it's event A (which take 15 minutes) it can start at: 8:00 (to 8:15) 8:15 (to 8:30) ( 8:30 to 8:45 is already taken ) 8:45 (to 9:00) 9:00 (to 9:15) 9:15 (to 9:30) ( 9:30 to

Re: [SQL] Autovaccum

if you don't delete anything. The easiest way to do it is let autovacuum do it for you. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain

Re: [SQL] Select (1-3)

Ezequias Rodrigues da Rocha wrote: Hi list, It is possible to make a selection like Select (list 1 to 1000); Sure, use the generate_series() function. select * from generate_series(1, 1000); -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL

Re: [SQL] psql -F TAB

. On those I know, you'd type ^V tab. (Maybe it would work to use '\t' as well, not sure if psql interprets that.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end

Re: [SQL] record datatype comparisons

also fails with the same error (one would think these are the same shape): select (select (1,2)) is distinct from (select (1,2)) ; This one works: alvherre=# select row(1,2) is distinct from row(1,2) ; ?column? -- f (1 fila) Is that what you're after? -- Alvaro

Re: [GENERAL] [SQL] Can we convert from Postgres to Oracle !!???

? Extensibility? The feeling that you won't at any moment be greeted by men in dark suits wanting to audit your facilities? Using Open Source is cool? Maybe you are thinking of the opposite process!!??? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL

Re: [SQL] Postgres regexp matching failure?

Mario Splivalo wrote: On Tue, 2006-09-05 at 10:21 -0400, Alvaro Herrera wrote: Mario Splivalo wrote: On Tue, 2006-09-05 at 08:42 -0500, Aaron Bono wrote: On 9/5/06, Mario Splivalo [EMAIL PROTECTED] wrote: pulitzer2=# select 'stop works' ~ '^\s

Re: [SQL] Create Assertion -- Question from a newbie

look forward to implementing (some form of) it some day. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze

Re: [SQL] to get DD-MM-YYYY format of data

on the DateStyle parameter, noticing that it controls two things at once, input and output of dates. You may also use to_char() or extract() to format the date manually without messing with the configuration. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company

Re: [SQL] viewing the description of tables from python DB-API

. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Rows with exclusive lock

Martin Marques escribió: On Sat, 22 Jul 2006, Alvaro Herrera wrote: Martin Marques escribió: Is it posible to get an exclusive (read/write) lock on certain rows? I don't want to block the whole table, only certain rows, but I want it to be a read/write lock. That's what SELECT FOR UPDATE

Re: [SQL] Rows with exclusive lock

? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

  1   2   >