[SQL] The empty list?
Hi. Is there someone who can elaborate on why the "empty list" is'nt implemented in Postgresql? This works: # select 1 in (1,2,3); ?column? -- t (1 row) And this works: jesper=# select * from test; id (0 rows) jesper=# select 1 in (select id from test); ?column? -- f (1 row) Whereas this gives a syntax error: # select 1 in (); ERROR: syntax error at or near ")" at character 14 LINE 1: select 1 in (); ^ If everyone where writing their SQL by hand .. this would probably not be a problem, but nowadays lots of people use ORM's for accessing the database.. thus it would be nice if the SQL implementation was a bit more generic like a "language". Jesper -- Jesper Krogh, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] The empty list?
Aaron Bono wrote: > On 11/2/06, Tom Lane <[EMAIL PROTECTED]> wrote: >> >> Jesper Krogh <[EMAIL PROTECTED]> writes: >> > Whereas this gives a syntax error: >> >> > # select 1 in (); >> > ERROR: syntax error at or near ")" at character 14 >> > LINE 1: select 1 in (); >> > ^ >> >> I think the short answer why the spec disallows this (which it does) >> is that it considers the right-hand side to be a table, and with >> absolutely nothing there, there is no way to impute a rowtype to the >> table. >> >> > So if this syntax violates the specs then the ORM is the what needs to be > change. The ORM should not attempt to do this. The SQL-spec is not widely available.. (as far as I know), so I cannot tell if it violates the spec. But Sqlite3 actually supports the empty list syntax (). We agree that another place to fix this would be inside the ORM, but it seems odd that this is considered as a list (1,2,3) whereas this is an empty row () and not the empty list () cause the right-hand-side of on in operation will allways be a list (if I havent forgotten something). (Trying to stuff a row into the right-hand-side of an in operation) jesper=# insert into test(id,description) values(1,'teststring'); INSERT 0 1 jesper=# select 1 in (select * from test);ERROR: subquery has too many columns And as the original example showed, if the empty list is returned from a select statement, then it works flawless. > I have to use DB2 sometimes and it also does not allow empty (). I know that it is quite unimplemented, mysql doesn't do this either > What ORM are you using that is trying to do this? Perl DBIx::Class.. but it is as much about not having to encode all SQL-strange-cases into a large piece of application logic (the ORM) it seems quite natural to encode the "in list" in the application as a list(or array), but missing SQL for the empty list basically means that we cant handle the empty set encoded in the empty array without explicitly introducing code for this size of array. From a programatically viewpoint, this seems quite odd. Jesper -- Jesper Krogh, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] The empty list?
Tom Lane wrote: > Jesper Krogh <[EMAIL PROTECTED]> writes: >> ... the right-hand-side of on >> in operation will allways be a list (if I havent forgotten something). > > IN (SELECT ...) for one thing. Isn't that "just" a list of rows(complex value) instead of an list of types(simple values)? >> ... but missing SQL for the empty list basically means that >> we cant handle the empty set encoded in the empty array without >> explicitly introducing code for this size of array. From a >> programatically viewpoint, this seems quite odd. > > FWIW, as of 8.2 the best option will probably be to use > "col = ANY (array)", which does support zero-length arrays > if you use either an out-of-line parameter or an array literal. That looks nice.. is ANY in the SQL-spec? Jesper -- Jesper Krogh, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] = or LIKE ?
Hi. Can anyone explain me this behavior? testdb=# select E'\\' = E'\\'; ?column? -- t (1 row) testdb=# select E'\\' like E'\\'; ?column? -- f (1 row) Shouldnt the like operator do the same as the = if there occours no wildcards and stuff in the string? -- Jesper -- 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] = or LIKE ?
Tom Lane wrote: Jesper Krogh writes: Shouldnt the like operator do the same as the = if there occours no wildcards and stuff in the string? If there are also no escape characters, then yeah. FWIW, 8.4 will complain about this case: regression=# select E'\\' like E'\\'; ERROR: LIKE pattern must not end with escape character So I cannot rely on the like operator to behave correct if I'd like to compare strings with backslashes (e.g. filepaths from MS Windows filesystems)? I actually get the same if it doesnt end with the slashes: testdb=# select E'\\t' like E'\\t'; ?column? -- f (1 row) testdb=# select E'\\t' = E'\\t'; ?column? -- t (1 row) -- Jesper -- 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] = or LIKE ?
Jesper Krogh wrote: Tom Lane wrote: Jesper Krogh writes: Shouldnt the like operator do the same as the = if there occours no wildcards and stuff in the string? If there are also no escape characters, then yeah. FWIW, 8.4 will complain about this case: regression=# select E'\\' like E'\\'; ERROR: LIKE pattern must not end with escape character So I cannot rely on the like operator to behave correct if I'd like to compare strings with backslashes (e.g. filepaths from MS Windows filesystems)? I actually get the same if it doesnt end with the slashes: testdb=# select E'\\t' like E'\\t'; ?column? -- f (1 row) testdb=# select E'\\t' = E'\\t'; ?column? -- t (1 row) Ok. The pattern has to be "double escaped".. testdb=# select E'\\t' like E't'; ?column? -- t (1 row) (for the archives a ref to the documentations i didnt get by the read through) http://www.postgresql.org/docs/8.2/static/functions-matching.html#FUNCTIONS-LIKE -- Jesper -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
