[SQL] very frustrating feature-bug

[silly sad]

acc=>

CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT)
RETURNS usr AS $$
  INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4)
  RETURNING usr.*;
$$ LANGUAGE sql SECURITY DEFINER;

acc=>

ERROR:  return type mismatch in function declared to return usr
DETAIL:  Function's final statement must be a SELECT.
CONTEXT:  SQL function "add_user"

SURPRISE :-) SURPRISE :-)

--
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] very frustrating feature-bug

[silly sad]

On 02/17/10 13:51, Jasen Betts wrote:

On 2010-02-17, silly sad  wrote:


acc=>

CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT)
RETURNS usr AS $$
INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4)
RETURNING usr.*;
$$ LANGUAGE sql SECURITY DEFINER;

acc=>

ERROR:  return type mismatch in function declared to return usr
DETAIL:  Function's final statement must be a SELECT.
CONTEXT:  SQL function "add_user"

SURPRISE :-) SURPRISE :-)


SQL functions are inlined when invoked, and so must be valid subselects.

rewrite it in plpgsql.


thanx for advice.

may i ask? when this feature will be fixed?
(now i am using 8.3.9)


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] what exactly is a query structure?

[silly sad]

hello.

Postgresql 8.3.9

CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT);

CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$
BEGIN
  RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob;
  RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

SELECT * from get_noobs();

And we have the following error

ERROR:  structure of query does not match function result type

--
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] what exactly is a query structure?

[silly sad]

On 02/26/10 10:19, A. Kretschmer wrote:

In response to silly sad :

hello.

Postgresql 8.3.9

CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT);

CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$
BEGIN
   RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob;
   RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

SELECT * from get_noobs();

And we have the following error

ERROR:  structure of query does not match function result type


Wild guess: your table noob has an other structure as expected, in
particular login and/or shop_pass are not TEXT.


they are texts.

if we substitute constant '*' with a text field or even a subselect, the 
error disappear.




--
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] what exactly is a query structure?

[silly sad]

On 02/26/10 09:50, silly sad wrote:

hello.

Postgresql 8.3.9

CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT);

CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$
BEGIN
RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob;
RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

SELECT * from get_noobs();

And we have the following error

ERROR: structure of query does not match function result type




my own wild guess:
string constant '*' is of type "unknown"


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] client_timezone to server_timezone and reverse

[silly sad]

HELO

i am looking for strange things:
a timezone conversion AS transparent AS a charset encoding conversion are.

for __example__ (not intending to override the current behavior)

i want to see now()::timestampTZ always the same
(the server side time with the timezone predefined firmly)

and to see now()::timeztamp calculated according to the client_timezone 
setting whatever the user set it to.


In addition to the current behavior it whould be NICE to
cast timestamTZ to timestamp taking in account a shift between server 
and client time, if client specified his TZ.



--
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] Private functions

[silly sad]

On 03/14/10 06:21, Jasen Betts wrote:

On 2010-03-13, Gianvito Pio  wrote:

Hi all,
is there a way to write a function that can only be called by another
function but not directly using SELECT function_name ( )?


not really.

but there may be another way to get the effect you want.



read the section SECURITY DEFINER
and GRANT and REVOKE and CREATE USER

--
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] Odd query behavior

[silly sad]

On 03/12/10 18:41, Dan McFadyen wrote:

Hello,

I've come across an odd situation. I've had access to a database where a
the following happens:

" SELECT * FROM table WHERE name LIKE 'abc%' " returns 2 rows...

but...

" SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name
LIKE 'abc%') " returns 0 rows...


I am sorry if it was already spoken.

SELECT name FROM table WHERE name LIKE 'abc%'

EXPLAIN SELECT name FROM table WHERE name LIKE 'abc%'

EXPLAIN SELECT * FROM table WHERE name IN
(SELECT name FROM table WHERE name LIKE 'abc%')



--
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] list of all months

[silly sad]

It looks like a procedural problem.
I would solve it in plpgsql.

--
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] I, nead to capture the IP number from the PC how is running the script ...

[silly sad]

On 03/15/10 22:18, John Dizaro wrote:

I, nead to capture the IP number from the PC how is running the script


first of all you must tell us how your client is connected to your database?
there are a lot of different variants, and the most common of them is 
not PG related at all while the connection is made via http.


--
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] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

[silly sad]

On 03/17/10 17:52, Ignacio Balcarce wrote:

CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID

  @NEWID VARCHAR(20) OUTPUT

AS

SET @NEWID = (

SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')

+ CAST(REPLICATE(0,8-LEN (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS

INTEGER),0) + 1)) AS VARCHAR)

+ CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1 AS

VARCHAR)

FROM THUBAN_SEQ

WHERE SUBSTRING(SEQ_ID,1,8)=

REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')

)

INSERT INTO THUBAN_SEQ VALUES (@NEWID)

SELECT @NEWID AS ITEM_ID;

GO



At a first glance
it looks like an

INSERT INTO thuban_seq(seq_id)
 VALUES (your_strange_string_processing( now() ))
  RETURNING seq_id;

But i couldn't interpret your extremely strange string processing with 
dates.

Please, FIRST OF ALL, get rid of this unnecessary brainfuck,
use postgres date-time arithmetic and clarify the idea of this routine.


--
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] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

[silly sad]

On 03/17/10 17:52, Ignacio Balcarce wrote:

-- IF EXISTS A ROW IN THE TABLE STARTING WITH THE CURRENT_DATE


Sorry, your field is not an atom => your database does not met a FIRST 
normal form.


it needs normalization urgently.

--
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] SQL Developer accessing PostgreSQL

[silly sad]

On 03/29/10 20:33, Snyder, James wrote:

Hello,

Is there a way to configure Oracle’s SQL Developer to access a
PostgreSQL database?



IMHO, no.
And (even if it possible) it is completely useless,
since the Postgresql has the "psql" program far better than any oracle 
tool and than all oracle tools together.


--
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] Table Design for Hierarchical Data

[silly sad]

single table.
nested tree + ordinal parent reference.

nests are calculated in a trigger on insert.

--
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] Table Design for Hierarchical Data

[silly sad]

P.S.
almost foget, do not try any oracle-like "tree-jouns" or "special types" 
or such a crap.


your problem as plain as to store a pair of integers
(or numerics (i prefer))

--
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] Table Design for Hierarchical Data

[silly sad]

On 04/07/10 11:00, Achilleas Mantzios wrote:


  Column  |   Type| Modifiers
-+---+---
  id  | integer   | not null default 
nextval(('public.paintgentypes_id_seq'::text)::regclass)
  name| text  | not null
  parents | integer[] |



The parents of any node to the root, i.e. the path of any node to the root are 
depicted as
parents[0] : immediate parent
parents[1] : immediate parent of the above parent
.
parents[n] : root of the tree


what this schema gives?

(1) the parent branch in one select.
what else?
nothing.

compare it to a nested-tree

   id  | integer   | NOT NULL
   name| text  | not null
   parent  | integer   |
   l   | numeric
   r   | numeric

(1) parent branch in one select
(2) child subtree in one select
(it makes a sence!)



--
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] Cascading sum in tree with CTE?

[silly sad]

to select a whole subtree of a particular node of a tree.
u have to modify the tree representation in one of the two ways
(according to you fine-tuned needs)

(1) store in the EACH node the PATH from root to this node
(2) store (l,r) segment representing the INCLUSIONS of nodes into other 
nodes subtree (exactly as segments include each other)


(2) i forgot the "official" name of this type of tree representation.

--
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] How to max() make null as biggest value?

[silly sad]

On 04/14/10 08:33, Feixiong Li wrote:

Hi , guys ,

I am newbie for sql, I have a problem when using max() function, I need
get null when there are null in the value list, or return the largest
value as usual, who can do this?

i.e. max([1,2,3,4,5]) => 5
max([1,2,3,4,5,null]) => null


if u want a function, not an aggregate
then u have the
greatest(...)
except it does not return null on null input
(i was really surprised with this completely perverted behavior
(very unusual for postgres), but it is a fact)

if u want to cheat u may just
coalesce() each input argument
then nullif() a result of the greatest() function
(if only u have enough space in a reference range to room the one 
special value instead of null)





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SPAM]-D] [SQL] How to find broken UTF-8 characters ?

[silly sad]

On 04/26/10 04:12, Andreas wrote:


Excel files

> pgAdmin
> Access.

looks like a complete offtopic


How can I find those broken UTF-8 characters?
How can I get rid of them?


iconv -c

BUT
u should not have those characters at all
if one is occured it most probably an error

AND
u should get rid of this error itself --
not of its consequences.



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SPAM]-D] Re: [SPAM]-D] [SQL] How to find broken UTF-8 characters ?

[silly sad]

How can I get rid of them?

iconv -c

AFAIK iconv would translate on file system level but I would think that
messed up a allready messed up Excel workmap even further.
I'd be glad to handle csv, too.


pg_dump | iconv -c | psql

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] LIMIT problem

[silly sad]

suppose i request

SELECT foo(t.x) FROM t LIMIT 1;

Whither it DEFINED how many times foo() will be executed?

May anyone rely on it?
Or we have to avoid this non SQLish trick?

--
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] LIMIT problem

[silly sad]

On 04/30/10 16:57, Nilesh Govindarajan wrote:

On 04/30/2010 06:20 PM, silly sad wrote:

suppose i request

SELECT foo(t.x) FROM t LIMIT 1;

Whither it DEFINED how many times foo() will be executed?

May anyone rely on it?
Or we have to avoid this non SQLish trick?



It will execute foo only once, and give only one row out of the n rows
it returns. If I'm wrong please correct me.


I did not asked how many times foo() would be executed.

--
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] LIMIT problem

[silly sad]

On 05/01/10 03:58, Jasen Betts wrote:

On 2010-04-30, silly sad  wrote:

suppose i request

SELECT foo(t.x) FROM t LIMIT 1;

Whither it DEFINED how many times foo() will be executed?


foo will be executed repeatedly until it returns a result or all the
rows in t are exhausted.


May anyone rely on it?


not sure


Or we have to avoid this non SQLish trick?



This will execute it once (or not at all where t has no rows)

  SELECT foo(x) FROM (SELECT x FROM t LIMIT 1) as bar;



this subselect "isolation" looks like a safe way.
Unlike LIMITed select looks unpredictable to me.

Thanx for assistance :)

--
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] [GENERAL] Schema design / joins

[silly sad]

__Orgs__
  id
  name

__Seasons__
  id
  org_id  fk(orgs.id)
  name

__Teams__
  id
  season_id  fk(seasons.id)
  name

__TeamFees__
  id
  team_id  fk(teams.id)
  *org_id<--- (?put extra fk here to avoid many joins?)


NO.

instead of it
use triggers before insert/update

CREATE FUNCTION foo() RETURNS TRIGGER AS $$
BEGIN
  SELECT org_id INTO new.org_id FROM __seasons__ WHERE id=new.season_id;
END;
$$ LANGUAGE plpgsql;

et cetera.

AND now other way lead you to the future.

--
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] [Trigger] Help needed with NEW.* and TG_TABLE_NAME

[silly sad]

On 05/11/10 18:26, Torsten Zühlsdorff wrote:



Tom Lane schrieb:

=?ISO-8859-15?Q?Torsten_Z=FChlsdorff?=  writes:

NEW.revision := addContentRevision (OLD.content_id, OLD.revision);



/* not working line, just a stub:
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW;
*/



RETURN NULL;


This seems like the hard way. Why don't you just RETURN NEW and let the
normal insertion happen?


The trigger catches an UPDATE, not an INSERT. I need the old and the new
row, because this should emulate revision-control of the content.


just set whatever value u want to the fields of the row NEW.

--
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] best paging strategies for large datasets?

[silly sad]

On 05/12/10 09:41, Louis-David Mitterrand wrote:

Hi,

I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and
am in the process of developping a pager to let users leaf through it
(30K rows).

Ideally I'd like to know when requesting any 'page' of data where I am
within the dataset: how many pages are available each way, etc.

Of course that can be done by doing a count(*) query before requesting a
limit/offset subset. But the main query is already quite slow, so I'd
like to minimize them.


nowadays i tend to bet on AJAX.
in other words i propose to move some calculations to a client side at all.

and this particular situation might looks similar to the following:

First u count(*) the rows and select a requested page
returning to a client the count result bundled "with a page of rows"

(1) client renders the acquired rows
(2)__memorize__ what part of the data he just got
(3) and stores the count result to calculate "the pager div"

all the subsequent clicks on "the pager div" should not immediately 
generate requests and decides if the request is needed.







--
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] best paging strategies for large datasets?

[silly sad]

First u count(*) the rows and select a requested page
returning to a client the count result bundled "with a page of rows"

(1) client renders the acquired rows
(2)__memorize__ what part of the data he just got
(3) and stores the count result to calculate "the pager div"

all the subsequent clicks on "the pager div" should not immediately
generate requests and decides if the request is needed.


Yes, rendering the results throught ajax is a good idea, but one has to
be careful not to expose one's LIMIT and OFFSET to the client, but only
the "page" number. Or else the client could query the whole data set. A
lot of "professional" web site have that hole.



this is not a hole, it is only a matter of aesthetic

--
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] Postgresql database

[silly sad]

On 05/14/10 12:42, Trinath Somanchi wrote:

Hi All,

I have Tree structured database to maintain.

Its as follows.

Vehicles


u r talking about a single entity vehicles
it is very common to represent a single entity with a single table

and IF u also have to have an entity "class of vehicles"
u r free to declare the second table named "class"

and IF u also have to represent a reference of manfacturers, another one 
table u have to create then.


that's trivial case

--
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] Postgresql database

[silly sad]

On 05/14/10 13:28, Trinath Somanchi wrote:

yes... But attribute of this table asl has an instance .. that two year
of releases are possible
also... I'm speaking of a very trivial case.


it doesnt multiplies the multitude of table.
it makes no problems with the "joins of ALL the tables"

--
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] plperlu user function.

[silly sad]

On 05/19/10 01:29, David Harel wrote:


I am trying to write a user function on the server to retrive image
files. Currently I wrote the following:



my $tmpfile = shift;
open my $IFHAND, '<', $tmpfile



Any recommendation how to do it right?


first of all, stop opening files at all,
return to a client a pathname,
then give to Caesar what is Caesar's, and to God what is God's.


--
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] How to get CURRENT_DATE in a pl/pgSQL function

[silly sad]

On 05/18/10 23:27, Kenneth Marshall wrote:


It works using 'now' and I assume that since curtime is


now() is NOT the CURRENT timestamp in fact,
it is about the timestamp of the current transaction has been started.

it is the really USEFUL value, still u have to remember this meaning.

--
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] oracle to postgres migration question

[silly sad]

On 06/16/10 02:45, Bruce Momjian wrote:

Scott Marlowe wrote:

Note that psql automagically right justifies numerics and dynamically
sizes all columns so you don't have to do as much of this stuff.
Oracle always made me feel like I was operating the machine behind the
curtain in the Wizard of Oz, lots of handles and switches and knobs I
had to mess with to get useful output.


Yeah, I have heard that description many times in other forms.


count me in :-)

i even suspect this exactly is a Secret of the oracle Power.
"higher performance through lower level of control"


--
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] How to Insert and retrieve multilingual (Hindi "an Indian language") into PostgreSQL

[silly sad]

On 06/22/10 14:48, venkat wrote:


   I want to insert and retrieve multilingual (Hindi) into database.is
 PostgreSQL supports that ?if it is ... please guide
me how to enable multilingual in the table.


in addition to the previous advice
("just use UTF-8 for entire database cluster")
i notice
"multilingual" is not a data layer property at all -- it's application.

--
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] ORDER BY is case insensitive

[silly sad]

On 06/23/10 17:45, Jasen Betts wrote:

On 2010-06-22, Bryan White  wrote:

I was suprised to find out that ORDER BY is case insensitive.  Is
there a way to do a case sensitive ORDER BY clause?


use bytea instead of a text type.

  try this:

  select * from t order by replace(f,e'\\', e'')::bytea

you may want to index on  replace(f,e'\\', e'')::bytea


certainly wrong way.
keyword is "multibyte chars".

as he asked about case sensitivity, i presume he still want to preserve 
alphabet order -- u propose to cancel alphabet order at all.


--
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] round(Numeric)

[silly sad]

On 06/26/10 17:09,  wrote:

On 2010-06-25, Lee Hachadoorian  wrote:

Is it documented anywhere that floating-point numbers round
"scientifically", that is 0.5 rounds to the nearest even number?


That's swiss rounding.  And no, as I understand it documented that
most arithmetic) is platform specific.

Postgres is written in C and the relevant portions of the
C standards douments (and discussions thereof) give a good
picture of the functioning of postgres arithmetic.


IT IS REALLY SAD !
the round(Numeric) does not behave as Math science prescribes :(

Shame on PostgresQL !


--
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] How to select text field as interger

[silly sad]

| SELECT Name FROM Test WHERE Name::INT = 1;


But note that this is going to throw an error if there are any table
rows where the name field *doesn't* contain a valid integer.


and it recalls the problem of error suppressing once again



--
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] How do I remove selected words from text field?

[silly sad]

On 07/02/10 03:25, Frank Bax wrote:

Osvaldo Kussama wrote:

2010/7/1 Frank Bax :

Create some tables; then add some data:

create table t1 (i int, v varchar);
insert into t1 values(1,'A B C D');
insert into t1 values(2,'B D E F');
insert into t1 values(3,'G H I J');
insert into t1 values(4,'E');


first of all rebuild the table.
explode these strings by space char
and put into another table for convenient use

In other words
construct proper data scheme PRIOR TO USE it.

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

[silly sad]

On 07/05/10 09:57,  wrote:

Hi,

How can I store Byte strings into a postgresql database.
Is there any special command to store it. How will be the sql query.


there is only '\0' byte incapable to input-output.
so u have to have it escaped at all costs _AND NOTHING MORE_.

"escaped" doesn't mean "prefixed with backslash"
("backslash method" cause a zero-byte to pass SQL parser an to be 
actually stored, BUT
the output will be corrupted, because of this zero-byte will be actually 
output)


You may use the BYTEA type
(similar to the TEXT but with different input-output) which effectively 
escapes zero-byte and a lot of other completely harmless bytes as well 
(probably to reach a better overhead)


Or you may introduce a pair of your own escape rules.

Unfortunately there are no way to influence The Pg Developers to get rid 
of the nasty god damned CSTRING off the input/output operations.



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

[silly sad]

On 07/05/10 10:30, Trinath Somanchi wrote:

Hi,

I'm new in using BLOB. How will the insert for storing very large byte
strings into a column  of data type Blob.


i didn't advice you to use BLOB.

you may store a string as long as 2GB at any TEXT or BYTEA field.

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

[silly sad]

On 07/05/10 10:43, Pavel Stehule wrote:


The good size for text or bytea is less than 100M and real max isn't
2G but it is 1G. LO isn't these limits because it isn't accessable on
SQL level.


any regular file on my filesystem isn't accessible on SQL level.
i am happy with them and never tried to store at a database.

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

[silly sad]

On 07/05/10 11:03, Pavel Stehule wrote:

2010/7/5 silly sad:

On 07/05/10 10:43, Pavel Stehule wrote:


The good size for text or bytea is less than 100M and real max isn't
2G but it is 1G. LO isn't these limits because it isn't accessable on
SQL level.


any regular file on my filesystem isn't accessible on SQL level.
i am happy with them and never tried to store at a database.


this is second extreme - you can use everything if you know what you
do - and mainly it depends on applications and requests that you have
to solve.


the trouble is the initiator of the thread didn't determine what is his 
problem either storing of a zero-byte containing string or storing of 
huge strings.

I answered him about BYTEA and he replied about BLOB.
I only tried to say i didn't say a word about BLOB.

P.S.
Practically for storing pictures i prefer regular files.

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

[silly sad]

On 07/05/10 11:18, Pavel Stehule wrote:


P.S.
Practically for storing pictures i prefer regular files.



how I say - it depends on application - sometime can be useful have to
access to all data only from db connect - for million small pictures
the bytea can be best.


i really love postgres TEXT type, but i hate CSTRING input-output 
bottleneck.


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

[silly sad]

On 07/06/10 21:52, Justin Graf wrote:
> I wrote an article covering this on the wiki
> 
> http://wiki.postgresql.org/wiki/BinaryFilesInDB

there are some "red flags" in communication
(particularly reading papers)
one of them is "binary data" which ITSELF IS NONSENCE.

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

[silly sad]

On 07/07/10 18:35, Justin Graf wrote:

On 7/7/2010 12:00 AM, silly sad wrote:

On 07/06/10 21:52, Justin Graf wrote:


I wrote an article covering this on the wiki

http://wiki.postgresql.org/wiki/BinaryFilesInDB


there are some "red flags" in communication
(particularly reading papers)
one of them is "binary data" which ITSELF IS NONSENCE.



WHAT???

You do understand that if you don't like it you can spend time fixing it.


fix what? human minds of so-called programmers?

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

[silly sad]

On 07/08/10 11:38, Dave Page wrote:

On Thu, Jul 8, 2010 at 8:31 AM, silly sad  wrote:

On 07/07/10 18:35, Justin Graf wrote:


On 7/7/2010 12:00 AM, silly sad wrote:


On 07/06/10 21:52, Justin Graf wrote:


I wrote an article covering this on the wiki

http://wiki.postgresql.org/wiki/BinaryFilesInDB


there are some "red flags" in communication
(particularly reading papers)
one of them is "binary data" which ITSELF IS NONSENCE.



WHAT???

You do understand that if you don't like it you can spend time fixing it.


fix what? human minds of so-called programmers?


That is not appropriate behaviour for the PostgreSQL mailing lists.
Please treat others with respect, even if you disagree with them.


i do not disagree.
i only meant a fixing of human communication problem is out of my hand.


--
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] subtract two dates to get the number of days

[silly sad]

On 07/13/10 18:58, Campbell, Lance wrote:

I want to subtract to dates to know the number of days different.


it would be far more interesting operation if u want to divide an 
INTERVAL by INTERVAL to get a ratio :-)


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql