[SQL] select tbl1.attr1, number of tuples where tbl2.attr1=tbl1.attr1

views.person_id but this gave very strange results which are definitely not what i wanted. How can I do that? TIA, Markus Bertheau Cenes Data GmbH Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com ---(end of broadcast

[SQL] strange query execution times

hly vacuum analyzed. The first query takes 0.038 sec, the second 0.879 secs. Why is the negotiation of all values except the one we are looking for faster than to look for equality of the one we are looking for? Markus Bertheau & Horst Schwarz Cenes Data GmbH ---(en

Re: [SQL] strange query execution times

On Tue, 2001-10-02 at 17:49, Tom Lane wrote: > Markus Bertheau <[EMAIL PROTECTED]> writes: > > r_kunden_anbieter describes the relationship between customers and > > suppliers. there are five status, 0 to 4 in attribute beziehung. both > > queries return the sa

[SQL] performance difference in count(1) vs. count(*)?

Hello, is there a difference performance-wise between select count(1) and select count(*)? -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] multi-table unique index

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 "unreg

Re: [SQL] multi-table unique index

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: su

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 > > >

[SQL] Inheritance and standards

Hi, in how far are the Table Inheritance features of PostgreSQL SQL92 or SQL99? What other databases support table inheritance? Do they use the same syntax? Thanks -- Markus Bertheau Cenes Data GmbH ---(end of broadcast)--- TIP 2: you can get

[SQL] ERROR: ExecEvalExpr: unknown expression type 108

on select bis as von from daten) as d1 where d1.von < d2.von) as number, von from (select von from daten union select bis as von from daten) d2) as table2 using (number); ERROR: ExecEvalExpr: unknown expression type 108 Is that a bug? Thanks -- Markus Bertheau Cenes Data GmbH Berlin, Ger

Re: [SQL] Merge Record in database(sql statement)

>science Indiaexcellent select distinct * from table; -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EM

[SQL] avoid select expens_expr(col) like unneccessary calculations

Hi, when you have select expensive_expression(column), * from table offset 20 limit 40 can you somehow save the cost for the first 20 calculations of expensive_expression? -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP

[SQL] slow query

ost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) Total runtime: 680.23 msec (rows: 76) At the moment we have ~300 rows in ressourcen, there are going to be 1000-1 rows in it in production. The turnus union also normally has around 100 rows. With 300 rows in ressourcen and

Re: [SQL] slow query

I'm trying to explain the bigger function a bit although it's only called in 2% of the cases. В Втр, 22.07.2003, в 19:07, Markus Bertheau пишет: > CREATE FUNCTION iGetNumOfBookedRes(integer, timestamp, timestamp) RETURNS numeric AS > ' > SELECT > CASE WHEN (MAX(

Re: [SQL] slow query

I've forgotten to put data for the belegungen table in the test data set, I've corrected that now, an updated data set is available at the same URL: http://www.bab24.de/media/testdata.sql -- Markus Bertheau Cenes Data GmbH ---(end of

Re: [SQL] restoring database

lename'|grep -4 database > > to find the starting points of individual databases etc... and then used > split to break it into the exact right size pieces to do this. That sounds like something pg_dump / pg_dumpall should be able to do itself, don't you think? -- Markus Berthea

Re: [SQL] Multiple outer join on same table

n alias a table to join it to itself. I'm not sure whether that answers your question. SELECT t1.field FROM table AS t1 JOIN table as t2 on (cond) -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Invalid Unicode Character Sequence found

ror: > > ERROR: Invalid UNICODE character sequence found (0xc000) > > This is fixed in 7.3.6. I remember to have stumbled over this bug, too. I poked around in the code a bit but found nothing that hinted to cause of the bug. So I wonder what the cause of this bug was? -- Markus Berthe

Re: [SQL] problem porting MySQL SQL to Postgres

Ð ÐÑÐ, 15.04.2004, Ð 13:15, Paul Thomas ÐÐÑÐÑ: > On 15/04/2004 11:25 Dan Field wrote: > Your "= NULL" tests are also not valid SQL (should be IS NULL). I think = NULL _is_ valid SQL, it just doesn't do what you think. -- Markus Bertheau <[EMAIL PROTECTED]> ---

[SQL] question about which column(s) are the right foreign key

t with just the object id is probably both faster and smaller. I'd be thankful for enlightenment :) -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] question about which column(s) are the right foreign key

hich will then make the > object-version id redundant, since the table will then have two keys. > > That's "the Right Oneâ" Thanks. That really did provide the neccessary insight. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] question about which column(s) are the right foreign key

foreign key to the object versions table? Thanks. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Empty array IS NULL?

uot; at character 14 oocms=# SELECT '{}'::INT[]; int4 -- {} (1 ÑÑ) oocms=# SELECT ARRAY(SELECT 1 UNION SELECT 2); ?column? -- {1,2} (1 ÑÑ) oocms=# SELECT ARRAY(SELECT 1 WHERE FALSE); ?column? ------ (1 ÑÑ) -- Markus Bertheau <[EMAIL PROTECTED]&g

Re: [SQL] Empty array IS NULL?

Ð ÐÐÐ, 28.06.2004, Ð 18:26, Joe Conway ÐÐÑÐÑ: > Markus Bertheau wrote: > > is the empty array representable in PostgreSQL, and is it > > distinguishable from NULL? > > Yes, and yes. > > Since NULL array elements are not currently supported, attempting to > construc

[SQL] Why no exprs in format string to RAISE

Hi, is there a non-implementation reason as to why there are no expressions allowed in the arguments to the format string to RAISE, or is that just not implemented yet? Thanks. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)-

[SQL] Custom type where not all elements are comparable

days) are not comparable. Thanks. [1] http://www.w3.org/TR/xmlschema-2/#duration -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] surrogate key or not?

questions? Thanks. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Stored procedures and "pseudo" fields..

ve > to use your function twice: > > SELECT *, acl_check( objects.obid, ) AS mode FROM objects > WHERE acl_check( objects.obid, ) > 0; and if you properly marked the function STABLE and I am not mistaken, then PostgreSQL is smart enough to execute the function only once per row. -- Markus

Re: [SQL] surrogate key or not?

el, and 5) applies. > 5) Making a primary key: if there is no real key at all. When there's no key at all, there can't be a surrogate key, as I understand it. In such cases a generated unique number comes in handy, and it's a real primary key and no surrogate key. ÐÐÐÑÐÐÐ

Re: [SQL] Please help me.. problem in to_char

o use to_char function > but it still work because still need to put ::date such as > SELECT to_char('2005-03-27'::date,'DD/MM/'); > > How can i put ::date beside t1.created to get the output? Just do it: to_char(t1.created::date, 'DD/MM/') --

[SQL] immutable function calling stable function

Hi, shouldn't it be illegal for an immutable function to call a stable one? -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail

[SQL] Is a backend id or something available for use as a foreign key?

WHERE ub.backend_id = CURRENT_BACKEND_ID; Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] s

Re: [SQL] SELECT from a list

SELECT * FROM table WHERE phrase LIKE '0%' OR phrase LIKE '1%' OR phrase > LIKE '2%' OR phrase LIKE '3%' OR phrase LIKE '4%' OR phrase LIKE '5%' OR > phrase LIKE '6%' OR phrase LIKE '7%' OR phrase LIKE '8%'

Re: [SQL] SELECT from a list

Ð ÐÑÐ, 25.07.2004, Ð 16:40, Jean-Luc Lachance ÐÐÑÐÑ: > Better yet: > > SELECT * FROM table WHERE phrase ~ '^[0-9]'; Not so sure if that's better - the regex engines aren't the fastest. -- Markus Bertheau <[EMAIL PROTECTED]> -

Re: [SQL] regex engine

Ð ÐÑÐ, 25.07.2004, Ð 19:34, Tom Lane ÐÐÑÐÑ: > Markus Bertheau <[EMAIL PROTECTED]> writes: > > Ð ÐÑÐ, 25.07.2004, Ð 16:40, Jean-Luc Lachance ÐÐÑÐÑ: > >> Better yet: > >> > >> SELECT * FROM table WHERE phrase ~ '^[0-9]'; > > > Not so

Re: [SQL] regex engine

Ð ÐÑÐ, 25.07.2004, Ð 21:41, Tom Lane ÐÐÑÐÑ: > Markus Bertheau <[EMAIL PROTECTED]> writes: > > pg's regex engine does have one > > shortcoming though: it doesn't know UTF-8. > > Sure it does. We borrowed it from Tcl, remember? > > The "characte

Re: [SQL] surrogate key or not?

Ð ÐÑÐ, 23.07.2004, Ð 09:57, Kenneth Gonsalves ÐÐÑÐÑ: > also, how did you get that neatly formatted output of the schema? This is postgresql_autodoc: http://www.rbt.ca/autodoc/ -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--

Re: [SQL] Inserting into point[] type.

, (1.0, 4.3)}' or ARRAY[(3.3, 4.4), (1.0, 4.3)]. Maybe even something like '{''(3.3, 4.4)'', ''(1.0, 4.3)''}' -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] any chance SQL ASSERTIONS will be implemented?

Hi, in the course of designing a database schema and ensuring integrity everywhere I have stumbled over a problem that Josh told me ASSERTIONS would solve. Is there any chance ASSERTIONS will be implemented in PostgreSQL? Thanks -- Markus Bertheau <[EMAIL PROTEC

[SQL] multi column foreign key for implicitly unique columns

renced table "p" Is this on purpose? I think the foreign key should be allowed. Creating an extra unique key only has a negative impact on performance, right? Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)---

Re: [SQL] multi column foreign key for implicitly unique columns

_name, class_name) REFERENCES objects(name, class_name), FOREIGN KEY(class_name, field_name) REFERENCES class_fields(class_name, field_name) ); ERROR: there is no unique constraint matching given keys for referenced table "objects" I need the fk on the columns.

[SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

Hi, why is the following query not allowed: SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = 'foo' FOR UPDATE OF classes) AS foo It's clear which rows should be locked here, I think. Thanks -- Markus Bertheau <[EMAIL PROTECTED]> -

Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

elds WHERE class = arg_class_name FOR UPDATE OF class_fields; which didn't work. Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-no

Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

See my other answer. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] multi column foreign key for implicitly unique columns

table b (f1 int, f2 int, > foreign key (f1,f2) references a(f1,f2)); > > How would you decide which constraint to make the FK depend on? Either way, the semantics are the same, right? -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)

Re: [SQL] multi column foreign key for implicitly unique columns

Ð ÐÑÑ, 17.08.2004, Ð 17:06, Stephan Szabo ÐÐÑÐÑ: > On Tue, 17 Aug 2004, Markus Bertheau wrote: > > > Ð ÐÑÑ, 17.08.2004, Ð 16:46, Tom Lane ÐÐÑÐÑ: > > > > > I think one reason for this is that otherwise it's not clear which > > > unique constrain

Re: [SQL] multi column foreign key for implicitly unique columns

ied about the performance hit because (name, class_name) will always be unique, yet they will be checked for uniqueness. Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] multi column foreign key for implicitly unique columns

bset of these columns. So no additional uniqueness enforcing needed. -- Markus Bertheau <[EMAIL PROTECTED]> ---(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 column foreign key for implicitly unique columns

Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(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

[SQL] array_in: '{}}'::text[]

Is there a reason the array_in parser accepts additional closing braces at the end? oocms=# SELECT '{}}'::text[]; text -- {} (1 ÑÑ) Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have

Re: [SQL] array_in: '{}}'::text[]

Ð ÐÐÐ, 23.08.2004, Ð 13:45, Markus Bertheau ÐÐÑÐÑ: > Is there a reason the array_in parser accepts additional closing braces > at the end? In fact it seems to accept everything after the closing brace matching the first opening brace. Thanks -- Markus Bertheau <[EMAIL

Re: [SQL] disable constrained (contd.)

ow in another table that has not been imported yet, but will be > later during the import) Add the constraint when you're done importing all data. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] PL/pgSQL, RETURN NEXT, ORDER

Hi, When I call a PL/pgSQL function that looks roughly like the following: ... FOR x IN SELECT ... ORDER BY ... LOOP RETURN NEXT x; END LOOP; RETURN; END; Is the order of the rows guaranteed to be preserved? Thanks. -- Markus Bertheau <[EMAIL PROTEC

Re: [SQL] PL/pgSQL, RETURN NEXT, ORDER

Ð ÐÑÑ, 12.10.2004, Ð 15:24, Markus Bertheau ÐÐÑÐÑ: > Is the order of the rows guaranteed to be preserved? Neil clarified on IRC that - there is no interface guarantee - in the current implementation the order is preserved - a future implementation is likely to change that Thanks. -- Mar

[SQL] Difference between IN and JOIN

e two. Are there differences in performance or anything else that matters? The execution plans seem to match except for the use of an "Hash IN Join" in place of a "Hash Join". Estimated costs match. Thanks for your advice -- Markus Bertheau <[EMAIL PROTECTED]> ---

Re: [SQL] Difference between IN and JOIN

Ð ÐÐÐ, 04.10.2004, Ð 16:17, Tom Lane ÐÐÑÐÑ: > Markus Bertheau <[EMAIL PROTECTED]> writes: > > I lately wondered if there is a difference between a JOIN and a IN in > > queries similar to the following: > > > SELECT f1 FROM t1 JOIN t2 ON (t.f2 = t2.f2) WHERE t2.f

[SQL] time of constraint checking

TE or INITIALLY DEFERRED is the default. Thanks. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] time of constraint checking

Ð ÐÑÐ, 20.10.2004, Ð 17:58, Markus Bertheau ÐÐÑÐÑ: > I also noticed, that the docs don't state whether INITIALLY IMMEDIATE or > INITIALLY DEFERRED is the default. I just overlooked that, sorry, it is stated. -- Markus Bertheau <[EMAIL PROTECTED]> ---

Re: [SQL] Functions return a select in a table, which data type I

QL'; CREATE TYPE foo_type AS (cod_aluno TEXT, nome TEXT, cpf TEXT); CREATE FUNCTION bar(int4) RETURNS SETOF foo_type LANGUAGE 'SQL' AS ' DECLARE var_rec foo_type; BEGIN FOR var_rec IN SELECT cod_aluno, nome, cpf FROM table WHERE ...

Re: [SQL] Functions return a select in a table, which data type I

Ð ÐÑÐ, 22.10.2004, Ð 15:38, Markus Bertheau ÐÐÑÐÑ: > CREATE TYPE foo_type AS (cod_aluno TEXT, nome TEXT, cpf TEXT); > CREATE FUNCTION bar(int4) > RETURNS SETOF foo_type > LANGUAGE 'SQL' That should be LANGUAGE 'plpgsql' > AS ' >

[SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

Hi, why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of ARRAY[] resp. '{}'? Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part

Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

sn't work for empty arrays. Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part

Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

Dnia 24-05-2005, wto o godzinie 00:06 -0400, Tom Lane napisał(a): > Joe Conway <[EMAIL PROTECTED]> writes: > > Markus Bertheau wrote: > >> why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of > >> ARRAY[] resp. '{}'? > > > W

[SQL] getting details about integrity constraint violation

d like to avoid parsing the text error message because it can be different depending on the LC_MESSAGES the server / libpq runs with. Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part

Re: [SQL] SQL equivalent to nested loop

27;Themen') as b, object_get_list_of_reference_property(b.object_id, 'Objekte'); ERROR: ?? ? ?? FROM ?? ? ? ?? ?? ? ?? ??? ?? ?? ??? I can't get an english error message atm :/, but that doesn't work. Markus -- Mark

Re: [SQL] getting details about integrity constraint violation

Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part

[SQL] optimizer, view, union

hy I don't just use the second query. Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part

[SQL] empty view, replace view, column type change?

Hi, it seems to me that the following should work but it fails: CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE; CREATE TABLE link (ov TIMESTAMP); CREATE OR REPLACE VIEW co AS SELECT ov FROM link; Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Descr

Re: [SQL] empty view, replace view, column type change?

Dnia 23-06-2005, czw o godzinie 22:03 +0200, Markus Bertheau napisał(a): > Hi, > > it seems to me that the following should work but it fails: > > CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE; > CREATE TABLE link (ov TIMESTAMP); > CREATE OR REPLACE

Re: [SQL] empty view, replace view, column type change?

Dnia 23-06-2005, czw o godzinie 16:19 -0400, Rod Taylor napisał(a): > The data types are different, as one has the timestamp to (6) decimal > places after seconds. That's strange. I explicitly specified ::TIMESTAMP on both the view and the table. Is that not unambiguous? Markus

Re: [SQL] SELECT * FROM foo OFFSET -1 LIMIT 1

being processed as 0 offset. Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part

Re: [SQL] How to call table returning function with other table

ut I > seem unsuccessful in getting the ID's into it. You have to use something like SELECT (xxx(id)).* FROM othertable WHERE otherwhere = 't', I believe. Markus Bertheau ---(end of broadcast)--- TIP 9: In versions below 8.0, th

Re: [SQL] PostgreSQL Handling of Special Characters

uld be the possible reason of this? Perhaps the ODBC driver thinks SQL_ASCII means ASCII and therefore discards all bytes > 127. On PostgreSQL SQL_ASCII really means SQL_ANYTHING, so to speak. Try to use for the database the encoding you really use. Markus Bertheau

Re: [SQL] How to optimize this query?

Send an EXPLAIN ANALYZE of the query along with the description of the involved tables. Also hardware information (RAM, disks, CPU), what other applications are running on that box and the parameter values in postgresql.conf that you changed from the defaults would be interesting. Markus 2006/3/2

Re: [SQL] How to optimize this query?

That's an explain. We need explain analyze. 2006/3/23, Maciej Piekielniak <[EMAIL PROTECTED]>: > Hello Markus, > > Wednesday, March 22, 2006, 6:58:44 PM, you wrote: > > MB> Send an EXPLAIN ANALYZE of the query along with the description of the > MB> involved tables. Also hardware information (RAM,

Re: [SQL] How to optimize this query?

, jednostka_miary.jednostka, towar.ilosc_paczkowa, towar.key1 ORDER BY id_dostawcy; I basically pulled the subselect from the field list into the from list. 2006/3/23, Markus Bertheau <[EMAIL PROTECTED]>: > That's an explain. We need explain analyze. > > 2006/3/23

[SQL] alter column type from boolean to char with default doesn't work

ed to work? Note that I don't need a solution for this special case, but rather want to know, how it is supposed to work - am I doing it wrong or is it not possible to do it like that (and is that because the SQL standard or because of how postgresql implements it?) Thanks Markus Bertheau

Re: [SQL] Cast in PG 8.3

2008/2/6, Jaime Casanova <[EMAIL PROTECTED]>: > did you read the release notes? Obviously he did: > I Know that changing the SQL command to : > SELECT * FROM TEMP WHERE CAST(id AS TEXT) ilike ('%122%'); > work´s but for now isn't possible... :( > > > I Tries create a cast but the function text

Re: [SQL] comment on COLUMN, broken or misunderstanding?

2008/2/18, Bryce Nesbitt <[EMAIL PROTECTED]>: > I'm expecting COLUMN comments to work much like table comments, but I'm > getting nothing back. Is this a reportable bug, or a misunderstanding? Try \d+ sched Markus ---(end of broadcast)--- TIP 5: d

Re: [SQL] Function returns error (view)

2008/2/27, Bart Degryse <[EMAIL PROTECTED]>: > > I would also suggest you replace the > ...t.cod_user IN (subselect) > by a join construction. I think it's more performant. In recent versions PostgreSQL is quite smart when planning IN, so that shouldn't be a concern.

Re: [SQL] Function returns error (view)

ituation where an explicit join is planned significantly different than an equivalent IN to be a bug in PostgreSQL: Markus -- Markus Bertheau Blog: http://www.bluetwanger.de/blog/ ---(end of broadcast)--- TIP 7: You can help support the

[SQL] UPDATE .. FROM

| bigint | not null | usage_counter | bigint | not null default 0 | Indexes: "tag_list_tag_data_pkey" PRIMARY KEY, btree (id) "tag_list_tag_data_list_id_key" UNIQUE, btree (list_id, tag_id) This is 8.3.0. Thanks -- Markus Bertheau Blog: http://www.bluetwanger.de/blog/

[SQL] Impossible with pl/pgsql?

4 | egg | john | 2003-05-05 5 | ham | dave | 2004-03-01 I can't figure this out for the life of me. I also have the impression that that's impossible to do without changing find() or decorate(). Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---

Re: [SQL] getting details about integrity constraint violation

are, but they only say something along the lines of "unique constraint violated", they don't say which one. Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(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

[SQL] 'true'::TEXT::BOOLEAN

Hi, What's the type I need to convert text to before I can convert it to boolean? Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your

Re: [SQL] 'true'::TEXT::BOOLEAN

В Птн, 03/06/2005 в 14:20 +0300, Achilleus Mantzios пишет: > O Markus Bertheau β^Ψ­ έγραψε στις Jun 3, 2005 : > > > Hi, > > > > What's the type I need to convert text to before I can convert it to > > boolean? > > just 't' will suffice. Well

Re: [SQL] 'true'::TEXT::BOOLEAN

ent postgres' boolean parsing. > Hmm, why dont you leave it as 'true' or 'false' without any castings. Because then pg doesn't find the function because it looks for one with a text argument. Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]>

Re: [SQL] 'true'::TEXT::BOOLEAN

n 3, 2005, at 8:52 PM, Markus Bertheau ☭ wrote: > > > And I can't call it with a TEXT variable, because casting from TEXT to > > BOOLEAN isn't possible. > > > I'd be surprised if there weren't a some way to coerce the cast from > text to boolean,

Re: [SQL] 'true'::TEXT::BOOLEAN

not work). This can be accomplished > using the CASE expression: CASE WHEN boolval THEN 'value if true' ELSE > 'value if false' END." Ah, that works. Thanks very much. Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(end of bro

[SQL] CASE WHEN foo IS NULL THEN DEFAULT ELSE foo END

Hi, is it planned to support the following insert syntax? INSERT INTO table VALUES (CASE WHEN arg_whatever IS NULL THEN DEFAULT ELSE arg_whatever END); I have the DEFAULT inside the CASE expression in mind. Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(

Re: [SQL] 'true'::TEXT::BOOLEAN doesn't work

В Птн, 03/06/2005 в 15:07 +0200, Markus Bertheau ☭ пишет: > В Птн, 03/06/2005 в 15:46 +0300, Achilleus Mantzios пишет: > > > Also according to the docs: > > http://www.postgresql.org/docs/current/static/datatype-boolean.html > > > > "Tip: Values of the bool

Re: [SQL] 'true'::TEXT::BOOLEAN

. I certainly consider the way you proposed in the other mail a workaround. What are the counter arguments? Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the un

Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

В Втр, 24/05/2005 в 00:06 -0400, Tom Lane пишет: > Joe Conway <[EMAIL PROTECTED]> writes: > > Markus Bertheau wrote: > >> why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of > >> ARRAY[] resp. '{}'? > > > Why would you expect an

[SQL] SQL equivalent to nested loop

Hi, I basically need the SQL equivalent of the following pseudo code: BEGIN FOR v IN SELECT * FROM f(4, 'foo') LOOP FOR w IN SELECT * FROM f(v.id, 'bar') LOOP RETURN NEXT W END LOOP; END LOOP; RETURN; Is that possible in SQL? Markus -- Markus Bertheau

[SQL] SQL repr of bytea val

one know a way to do that? Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] SELECT * FROM foo OFFSET -1 LIMIT 1

Hi, is there a reason that SELECT * FROM t1 OFFSET -1 LIMIT 1 does not return 0 rows? Accordingly SELECT * FROM t1 OFFSET -1 LIMIT 2 should return 1 row, imo. Markus -- Markus Bertheau ☭ <[EMAIL PROTECTED]> ---(end of broadcast)---