[GENERAL] database design best pratice help
Hi all, I have a question about database design best pratice. In my db I have about one hundred tables like this: code description To avoid to have a so great number of similar tables in the db I wonder if it is a good idea to unify all these tables in one big table like this: id code table_ name description The advantages are: 1. only one table in the db instead of 100 2. only one controller to manage the table Could this be a way to enhance db performance? Is there any negative point that I don't see? Thanks for any comments. j -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dump order by
I'm sorry my first example was incomplete I need to migrate data from postgresql to oracle thus I have to use dump --column-inserts instead of copy to have an output like this but order by pk: INSERT INTO test (id, note, id_father) VALUES (6, 'Homer Simpson ', 5); INSERT INTO test (id, note, id_father) VALUES (5, 'Abraham Simpson', NULL); INSERT INTO test (id, note, id_father) VALUES (10, 'Maggie Simpson ', 6); INSERT INTO test (id, note, id_father) VALUES (1, 'Ned Flanders ', NULL); INSERT INTO test (id, note, id_father) VALUES (2, 'Maude Flanders ', NULL); INSERT INTO test (id, note, id_father) VALUES (9, 'Bart Simpson ', 6); INSERT INTO test (id, note, id_father) VALUES (3, 'Rod Flanders ', 1); INSERT INTO test (id, note, id_father) VALUES (4, 'Todd Flanders ', 1); INSERT INTO test (id, note, id_father) VALUES (7, 'Marge Simpson ', NULL); INSERT INTO test (id, note, id_father) VALUES (8, 'Lisa Simpson ', 6); j On 12/22/2012 10:35 PM, Robert Treat wrote: You can COPY based on a select statement, so if you copy to stdout with a select with an order by clause, it should sort the data the way you want. Robert Treat conjecture: xzilla.net consulting: omniti.com On Sat, Dec 22, 2012 at 12:29 PM, jo wrote: Hi all, I would like to know if it is possible to dump a table ordered by its primary key. Take a look at the this test table... \d test Table "public.test" Column| Type | Modifiers ---+-+--- id| integer | not null name | text| id_father | integer | Indexes: "test_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "test_id_father_fkey" FOREIGN KEY (id_father) REFERENCES test(id) select * from test; id | nome | id_father +-+--- 6 | Homer Simpson | 5 5 | Abraham Simpson | 10 | Maggie Simpson | 6 1 | Ned Flanders| 2 | Maude Flanders | 9 | Bart Simpson| 6 3 | Rod Flanders| 1 4 | Todd Flanders | 1 7 | Marge Simpson | 8 | Lisa Simpson| 6 (10 rows) I would like to dump the table with this order: COPY test (id, nome, id_father) FROM stdin; 1 Ned Flanders\N 2 Maude Flanders \N 3 Rod Flanders1 4 Todd Flanders 1 5 Abraham Simpson \N 6 Homer Simpson 5 7 Marge Simpson \N 8 Lisa Simpson6 9 Bart Simpson6 10 Maggie Simpson 6 \. instead it is dumped like this: COPY test (id, note, id_father) FROM stdin; 6 Homer Simpson 5 5 Abraham Simpson \N 10 Maggie Simpson 6 1 Ned Flanders\N 2 Maude Flanders \N 9 Bart Simpson6 3 Rod Flanders1 4 Todd Flanders 1 7 Marge Simpson \N 8 Lisa Simpson6 \. and I can't upload the table because the foreing keys. j -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: "don't know how to print type 715"
Carilda A. Thomas wrote: > > Since I have now seen two other people complain about this, I will join > the fray. > > I am running Solaris 2.6, Postgresql v6.3.2. > I have also gotten the same notice on FreeBSD 2.1. but not the threaded one -- this was for a client a while back) running > v6.3.1 > > Well, the "bug" only happens if one forces the complete build to use > dynamic libraries (.so instead of .a). > > And, no, I do NOT accept the Microsoft-type solution: "don't use > dynamic libraries." > > It doesn't seem to have any effect on operation other than being > annoying > Other info: The freeBSD was built with gcc 2.7.; the Solaris > 2.6 was built with gcc 2.8.1. All the auxiliary programs (readline, > history, etc.) are the latest versions (I'm too lazy to look right now) > and linked in as dynamic, also. > > A bogus value is being passed somewhere, and the function it winds up in > just doesn't know what to do with it. I didn't have the time to chase > down the path it was using to get there because I am not that familiar > with the internals. > > This message appears to show up in anything related to a trigger -- > whether it's a restriction in the schema definition or a manually > created trigger. > > cat The problem should be connected with DEBUG, I solved my problem getting off the -d parameter from postmaster command line. Another way to have this message should be compiling the parser subdirectory with -DPARSEDEBUG. (See the reply of Thomas G. Lockhart). Jose'
Re: [GENERAL] float8 to text converter
Hi Memphisto,
If you are using v6.4 you can use CAST as:
prova=> select cast('1.7976931348623e+308'::float8 as text);
?column?
1.7976931348623e+308
(1 row)
or:
prova=> select cast(f as text) from a;
text
--
4714-11--2147483624 BC <---
4714-11--2147483624 BC <---
(2 rows)
Ooops! that seems like a bug.
prova=> select f from a;
f
1.7976931348623e+308
1.7976931348623e+308
(2 rows)
Jose'
Memphisto wrote:
>
> Hi,
>
> I'd like to know if there's an operator that converts from float8 to
> text. It should be because PGSQL can print a float8 number.
>
Re: [GENERAL] Transaction aborted?
Mike Meyer wrote: > > I'm seeing these messages in the process of adding entries to a > database. > > NOTICE: (transaction aborted): queries ignored until END > > I'm not positive what they mean. Is there someone who is who could > tell me, and tell me how to catch then and deal with them? > That's mean that you had an error during transaction. The only command that backend accepts at this point is the END. Jose'
Re: [GENERAL] select using date
Try: select current_date, CURRENT_DATE - INTERVAL '1 DAY'; ?column?|?column? --+-- 1999-01-08|1999-01-07 00:00:00+01 (1 row) PostgreSQL has a syntax sligth different than SQL92. You have to enclose '1 DAY' instead of '1' DAY. -Jose'- Kevin Heflin wrote: > > Trying to use select statement using CURRENT_DATE > Which works fine like so: > > select * from headlines where dateof = CURRENT_DATE order by dateof desc > > But I'm also wanting to do something similar to: > > select * from headlines where dateof = CURRENT_DATE - INTERVAL '1' DAY > order by dateof desc > > Basically just trying to subtract 1 day from the CURRENT_DATE > > When I try the above I get > > ERROR: parser: parse error at or near "day" > > Any suggestions would be appreciated. > > Kevin > > > Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 > VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 > [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net >
Re: [GENERAL] Representation of big integer numbers.
Memphisto wrote:
>
> Hi,
>
> Is there a way to display big integer numbers splitted by periods in
> PostgreSQL queries?
>
> Example: 123.456.789 instead of 123456789 .
>
> Thanks in advance
You can create a function to format numbers.
See attached example.
-Jose'-
-- la funzione dec(float) ritorna la parte decimale come un intero
-- la limitazione e' che ha solo 3 cifre arrotondate 000-999
-- purtroppo la differenza tra un float e dtrunc(float) non funziona a
-- dovere, quindi ho usato la funzione date_parte('millisecond',float)
-- che tratta la parte decimale di un float come milisecondi.
drop function dec(float);
create function dec(float) returns text as
'
declare
txt text;
begin
--get decimal part...
txt:= dround(datetime_part(''millisecond'',$1));
if textlen(txt) = 2 then
txt:= ''0'' || txt;
end if;
if textlen(txt) = 1 then
txt:= ''00'' || txt;
end if;
return txt;
end;
' language 'plpgsql';
-- funzioni per la formattazione di interi e float
drop function format(float4,text);
create function format(float4,text) returns text as
'
begin
return format(float8($1),$2);
end;
' language 'plpgsql';
drop function format(float8,text);
create function format(float8,text) returns text as
'declare
fbak text;
vbak int8;
out text;
fout text;
lh text;
res1 int8;
res float8;
i int2;
df int2;
sval text;
begin
vbak:= dtrunc($1);
fbak:= $2;
res:=$1;
if $1 < 0 then
res := -(res);
else
res := res;
end if;
df:= textlen(fbak);
i:= textpos(fbak,'','');
if i > 0 then
fbak:= substr(fbak,1,i - 1);
end if;
lh:=dec(res);
fout:= format(vbak,fbak);
if i = 0 then
return fout;
end if;
out:= fout || ('','');
out:= out || (substr(lh || '''',1,df - i));
return out;
end;
' language 'plpgsql';
drop function format(int8,text);
create function format(int8,text) returns text as
'declare
fbak text;
vbak int8;
out text;
sign int2;
num char(1);
car char(1);
car1 char(1);
car0 char(1);
lf int2;
bf int2;
lv int2;
sval text;
begin
vbak := $1;
fbak := $2;
if vbak < 0 then
sign := 1;
vbak := -(vbak);
else
sign := 0;
end if;
lf := textlen(fbak);
bf := lf;
sval := vbak;
lv := textlen(sval);
if lv > lf then
raise exception ''the value % is greater than %'',$1,$2;
end if;
while (lv>0 or lf>0) loop
car:= substr(fbak,lf,1);
car0:= substr(fbak,lf+1,1);
car1:= substr(fbak,lf - 1,1);
if lv > 0 then
if lf=0 then
raise exception ''The value % is greater than
%'',$1,$2;
end if;
if car = ''#'' or car = ''&'' then
num := substr(sval,lv,1);
out := substr(fbak,1,lf - 1) || num;
if bf > lf then
fbak := out || substr(fbak,lf+1);
else
fbak := out;
end if;
lf := lf - 1;
lv := lv - 1;
else
lf := lf - 1;
end if;
else
if sign = 1 then
sign := 2;
fbak := substr(fbak,1,lf - 1) || (''-'' ||
substr(fbak,lf + 1));
else
if sign = 2 or car <> ''&'' then
if car0 = ''-'' or car0 = '' '' then
fbak := substr(fbak,1,lf - 1) || (''
'' || substr(fbak,lf + 1));
else
if car1 = ''#'' and (car <> ''#'' and
car <> ''&'') then
fbak := substr(fbak,1,lf - 1)
|| ('' '' || substr(fbak,lf + 1));
else
if car1 = ''#'' then
fbak :=
substr(fbak,1,lf - 1) || ('' '' || substr(fbak,lf + 1));
end if;
en
Re: [GENERAL] Newbie interface question
Dan Warren ha scritto: > You'll have to forgive me if this is a trivial question but I'm *very* > new to this. I'm developing an online time clock program and was > wondering if there was a character based front end, similar to Oracle's > SQL*Forms, that could be used for a postgres 6.4.2 database. > > Thanks > > Dan Warren > Design Dimensions > [EMAIL PROTECTED] I don't know SQL*Forms but, there is a 4gl (a la Informix) for PostgreSQL very interesting available at PostgreSQL spanish mailing list (documentation available only in spanish and no sources), you can download it from: ftp://tlali.iztacala.unam.mx/pub/postgreSQL/hy4gl-inst.0.6.tgz -Jose'-
Re: [GENERAL] A mistake generates strange result
Stéphane Dupille ha scritto:
Hi !
"Ricardo J.C.Coelho" <[EMAIL PROTECTED]> writes:
> Just for PgSQL's development group think about
> I made a mistake typing a query that generates a strange result
> (Very strange).
> The query: select text('12345678'::float8);
> It returns a date in datetime format !!
I didn't found any function
of name "text" that converts
float8 to text. So I think Postgres made an implicit cast of the data
to datatime. So: String->Float8->DateTime->Text. Stranger : I didn't
found any function to cinvert float to text !
> If you use: select ('12345678'::float8)::text;
everything runs well.
Here, you made an explicit
cast, without the use of any
function. So your data is casted well.
Hope this helps !
This seems like a bug, because there's no a text(float8) built-in
function.
hygea=> select text('12345678'::float8);
text
--
2000-05-22 23:21:18+02
but if you create the function like:
create function text(float8) returns text as
'
begin
return $1;
end;
' language 'plpgsql';
CREATE
select text('12345678.2'::float8);
text
--
12345678.2
(1 row)
- Jose' -
And behold, I tell you these things that ye may learn wisdom; that
ye may
learn that when ye are in the service of your fellow beings ye
are only
in the service of your God.
- Mosiah 2:17 -
Re: [GENERAL] timestamps
[EMAIL PROTECTED] ha scritto:
> I'm trying to create a column that defaults to the current time and date. I
> tried the SQLServer like syntax below but potgresql choked:
>
> CREATE TABLE clicks (
> avo_userid varchar (10) NOT NULL ,
> link_id int NOT NULL ,
> the_time timestamp NOT NULL CONSTRAINT df_now DEFAULT (timestamp('now'))
> );
> NOTICE: there is more than one function named "timestamp"
> NOTICE: that satisfies the given argument types. you will have to
> NOTICE: retype your query using explicit typecasts.
> ERROR: function timestamp(unknown) does not exist
>
> Is "timestamp" not both a type and a function? How can I look it up?
>
>
You can create table like:
CREATE TABLE clicks (
useridvarchar(10) not null,
linksintnot null,
df_timetimestamp constraint df_now DEFAULT current_timestamp
);
but remember in such case you can insert a value to df_time column different
than current timestamp.
If you want to avoid this you have to create a trigger (see attached example).
--
>
> And one more question: How does one construct a foreign key relationship in
> postgres?
>
> Thanks for any help.
Foreign key is not yet implemented but you may use triggers (See attached
example).
NB: You need v6.4.? to use examples.
--
- Jose' -
"No other success in life can compensate for failure in the home" (David O.
McKay)
DROP TABLE version_test;
CREATE TABLE version_test (
nometext,
usernamechar(10), -- user name
version timestamp -- last update
);
drop function f_version();
create function f_version() returns opaque as '
begin
new.version:= current_timestamp;
new.username:= current_user;
return new;
end;
' language 'plpgsql';
CREATE TRIGGER t_version
BEFORE INSERT OR UPDATE ON version_test
FOR EACH ROW
EXECUTE PROCEDURE f_version();
INSERT INTO version_test VALUES ('jose','marco','1998-12-31 12:30:00');
INSERT INTO version_test VALUES ('miriam');
update version_test set username='jose';
SELECT * FROM version_test;
DROP TABLE header;
CREATE TABLE header (
distretto CHAR(4) NOT NULL,
annoDECIMAL(4) NOT NULL,
numero INTEGER NOT NULL,
dataDATE NOT NULL,
azienda CHAR(11) NOT NULL,
CONSTRAINT k_header PRIMARY KEY (distretto,anno,numero)
);
DROP TABLE detail;
CREATE TABLE detail (
distretto CHAR(4) NOT NULL,
annoDECIMAL(4) NOT NULL,
numero INTEGER NOT NULL,
cod_prestazione CHAR(05) NOT NULL,
quantitaFLOAT(4) NOT NULL,
importo FLOAT(8),
CONSTRAINT k_detail PRIMARY KEY (distretto,anno,numero,cod_prestazione),
CONSTRAINT k_extern FOREIGN KEY(distretto,anno,numero) references HEADER
);
drop function f_not_add_detail();
create function f_not_add_detail() returns opaque as '
declare
/* to avoid insert detail if header doesn''t exist */
tot int;
begin
select numero into tot from header
where anno = new.anno and numero = new.numero;
if not found then
raise notice ''Impossible add new detail!'';
return NULL;
else
return new;
end if;
end;
' language 'plpgsql';
create trigger t_not_add_detail before insert
on detail for each row execute procedure f_not_add_detail();
--EXAMPLE:
select * from header;
select * from detail;
INSERT INTO detail VALUES ('E14','1999',2,'IMPOSSIBLE',123,345.5); --impossible
INSERT INTO header VALUES ('E14','1999',2,current_date,1235);
INSERT INTO detail VALUES ('E14','1999',2,'AB',523,35.5);
INSERT INTO header VALUES ('E14','1999',1,current_date,1235);
INSERT INTO detail VALUES ('E14','1999',1,'A1',423,45.5);
INSERT INTO detail VALUES ('E14','1999',1,'AC',123,345.5);
select * from header;
select * from detail;
drop function f_upd_key_detail();
create function f_upd_key_detail() returns opaque as '
declare
/* change in cascade the key of every detail if header key is changed */
tot int;
begin
update detail
set anno = new.anno, numero = new.numero
where anno = old.anno and numero = old.numero;
return NULL;
end;
' language 'plpgsql';
drop trigger t_upd_key_detail on header;
create trigger t_upd_key_detail after update
on header for each row execute procedure f_upd_key_detail();
--EXAMPLE:
select * from header;
select * from detail;
update header set anno='1997', numero=33 where numero = 1 and anno='1999';
select * from header;
select * from detail;
drop function f_del_cascade();
create function f_del_cascade() returns opaque as '
declare
/* cancel in cascade all details after header is deleted */
begin
delete from detail
where anno = old.anno a
Re: [GENERAL] Comments on tables, functions, etc.
Michael Davis ha scritto: How can I add a comment to a table, function, etc. that will should up in a /dd comment in psql? pg_description has two fields... joe=> \d pg_description Table = pg_description +--+--+---+ | Field | Type | Length| +--+--+---+ | objoid | oid | 4 | | description | text | var | +--+--+---+ To add your own description you have to know the oid of your table,function, etc and then assign this valuo to pg_description.objoid and your description to pg_description.description. Take a look: joe=> select relname,oid from pg_class where relname='btmm'; relname| oid ---+-- btmm |190177 (1 row) joe=> \dd btmm description -- no description (1 row) joe=> insert into pg_description values(190177,'a minha tabela'); INSERT 226304 1 joe=> \dd btmm description -- a minha tabela (1 row) -- - Jose' - "No other success in life can compensate for failure in the home" (David O. McKay)
[GENERAL] Re: NOTICE: _outNode: don't know how to print type 715
Hi all, PostgreSQL gives me a strange message when I try to create a table with a NOT NULL column. drop table prova; DROP create table prova( campo1 INTEGER ); CREATE drop table prova; DROP create table prova( campo1 INTEGER NOT NULL ); NOTICE: _outNode: don't know how to print type 715 CREATE \d prova Table= prova +---+--+---+ | Field | Type| Length| +---+--+---+ | campo1| int4 not null| 4 | +---+--+---+ Does someone knows what this message means ? Thank you, Jose'
Re: [GENERAL] ODBC-client->Linux-server: datatype boolean not recognized?
oh! this was for old releases, now I'm using the following: create function MsBool(bool,int4) returns bool as ' declare bool_int int4; begin if $1 is NULL then return NULL; end if; if $1 is TRUE then if $2 <> 0 then return TRUE; end if; else if $2 = 0 then return TRUE; end if; end if; return FALSE; end; ' language 'plpgsql'; create operator = ( leftarg=bool, rightarg=int4, procedure=MsBool, commutator='=', negator='!=', restrict=eqsel, join=eqjoinsel ); Moray McConnachie ha scritto: >create function MsAccessBool(bool,int4) returns bool > as '' language 'internal'; There is surely something missing here, between the empty single quotes? When I execute that, I get "There is no internal function msaccessbool"
Re: [GENERAL] update view
You must create a rule to make a view updatable as in the following
example:
drop table emp;
create table emp (
empno
int,
ename char(20),
job
char(12),
hiredate date,
sal
money,
comm
int,
deptno int,
level
int,
mgr
int
);
insert into emp values (7499,'ALLEN', 'SALESMAN', '20-FEB-81', '$1600',
300, 20,
insert into emp values (7698,'BLAKE', 'MANAGER', '01-MAY-81',
'$2850',NULL, 30,
insert into emp values (7900,'JONES', 'CLERK',
'03-DEC-81', '$0950',NULL, 30,
insert into emp values (7901,'KING', 'SALESMAN', '03-DEC-81', '$1950',NULL,
30,
drop view vista;
create view vista
as select empno, ename, job
from emp
where job='SALESMAN';
create rule "_UPDvista" as on update to vista
WHERE new.job='SALESMAN'
do instead
update emp set empno=new.empno, ename=new.ename,job=new.job
where empno=OLD.empno;
select * from vista;
empno|ename
|job
-++
7499|ALLEN
|SALESMAN
7901|KING
|SALESMAN
(2 rows)
update vista set empno=1 WHERE ename='ALLEN';
select * from vista;
empno|ename
|job
-++
7901|KING
|SALESMAN
1|ALLEN
|SALESMAN
(2 rows)
José
Brian Haney ha scritto:
I'm trying to update a table through a view and have
read up on what
constitutes an 'updatable' view. I created a simple test case
and cannot
get it to update the table through the view. In the transcript
below,
notice that when I update the view, I get 'UPDATE 0' with no error
message
or other complaints. What am I missing? Does PostgreSQL
not support
updating through views?
-- Brian Haney
[EMAIL PROTECTED]
BEGIN SAMPLE
$ psql -f /tmp/viewtest test2
create table peanuts (
name text,
age int4,
height int4,
weight int4);
CREATE
insert into peanuts values ('Charlie Brown', 50, 24, 75);
INSERT 21228 1
insert into peanuts values ('Snoopy', 21, 18, 25);
INSERT 21229 1
insert into peanuts values ('Lucy van Pelt', 50, 27, 65);
INSERT 21230 1
insert into peanuts values ('Linus van Pelt', 50, 24, 75);
INSERT 21231 1
select * from peanuts;
name |age|height|weight
--+---+--+--
Charlie Brown | 50| 24| 75
Snoopy | 21|
18| 25
Lucy van Pelt | 50| 27| 65
Linus van Pelt| 50| 24| 75
(4 rows)
create view dogs as select * from peanuts where name = 'Snoopy';
CREATE
select * from dogs;
name |age|height|weight
--+---+--+--
Snoopy| 21| 18| 25
(1 row)
update dogs set age = 145;
UPDATE 0
select * from dogs;
name |age|height|weight
--+---+--+--
Snoopy| 21| 18| 25
(1 row)
EOF
$
Re: [GENERAL] Date & Time
Ed Loehr ha scritto: > Just curious: anyone have any comment on any practical differences between now() >and CURRENT_TIMESTAMP, which seems to work > the same? > I think it is the same function, both of them return the current date and time. now() should be the internal postgreSQL function. and CURRENT_TIMESTAMP is the exact SQL-92 syntax Jose'
Re: [GENERAL] How to stop implicit rollback on certain errors?
Peter Eisentraut wrote: > On 1999-12-08, Lincoln Yeoh mentioned: > > > begin; > > insert into stuff; > > do some nondatabase things based on last inserted id; > > update a date in stuff; > > commit; > > > > It seems that if the date is out of the database range, everything is > > thrown out. Is it possible to catch the database error and use a null date > > instead, without throwing everything away? > > Yes, use no transaction at all. :) > > Seriously, why do you use a transaction, when you don't want any errors > caught? Transactions are defined as everything succeeds or nothing goes. > If you want update to succeed anyhow, put it in it's own transaction > (i.e., commit before it). I have the same problem using transactions. I want to use transactions anyway and I want to caught only some errors and thrown out some others depends on which kind of error, and at end decide to give an explicit COMMIT or ROLLBACK. I tried other databases and they have a behavior different from PostgreSQL. SOLID for example attends for an explicit COMMIT or ROLLBACK and doesn't have a default rollback, the same with Oracle8i, take a look at this Oracle example: $sqlplus scott/tiger SQL*Plus: Release 8.0.5.0.0 - Production on Thu Dec 9 15:00:47 1999 (c) Copyright 1998 Oracle Corporation. All rights reserved Connected to: Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production SQL> create table a (a int); table created. SQL> insert into a values(1); 1 row created. SQL> insert into a value(2); ERROR at line 1: ORA-00928: missing SELECT keyword SQL> commit work; Commit complete SQL> select * from a; A 1 SQL> And now the same example in PostgreSQL: $ psql prova Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: prova prova=> begin; BEGIN prova=> create table a(a int); CREATE prova=> insert into a values(1); INSERT 1902953 1 prova=> insert into a value(2); ERROR: parser: parse error at or near "value" prova=> commit work; END prova=> select * from a; ERROR: a: Table does not exist. prova=> Who are right. Oracle or PostgreSQL ? Jose'
Re: [GENERAL] Where is IFNULL?
select * from a;
a|b
-+-
1|primo
2|
(2 rows)
SELECT CASE WHEN b IS NOT NULL
THEN 'pref.'||b||'.suf'
ELSE 'pref.'||'NULL'||'.suf'
END FROM a;
case
--
pref.primo.suf
pref.NULL.suf
(2 rows)
Jose'
Sascha Ziemann wrote:
"Gene Selkov Jr." <[EMAIL PROTECTED]>:
| > the user manual describes the function IFNULL on page 38, but when
I
| > try to use it I get the following error:
| >
| > users=> select ifnull (NULL, 'nix');
| > ERROR: No such function 'ifnull' with the specified
attributes
|
| The manual seems to be obsolete. The related functions are named
| nullvalue and nonnullvalue, but neither takes more than one argument.
IFNULL isn't syntactic suguar. It is like C's "1 ? 1 : 0".
| > Or does anybody know a simple way to concatenate strings, which
can be
| > NULL?
|
| Don't know the answer to this, but it appears to be wrong not to
| ignore NULLs in concatenation.
That is the way Postgresql works:
users=> select 'tach' || NULL;
ERROR: parser: parse error at or near ";"
users=> create function cat (text,text) returns text as 'begin return
$1 || $2; end;' language 'plpgsql';
CREATE
users=> select cat ('tach', NULL);
ERROR: typeidTypeRelid: Invalid type - oid = 0
| Why not trust this job to your client-side
| code?
Because I do not write that code and because my computer isn't
religious enough to trust anybody ;-)
Sascha
Re: [GENERAL] How to stop implicit rollback on certain errors?
"Ross J. Reedstrom" wrote: > > Hmm, sounds like a vote for nested transactions. The JDBC driver developer > (Peter Mount) was musing that nested transaction would make large object > support easier for him, as well. > > As to the other example of Oracle not forcing a rollback, I have a feeling > that this may be specific to syntax errors in an interactive session. > Implementing this sort of behavior has been discussed recently on the > hackers list, in the context of making it easier to work interactively > inside a transaction. > > I would be surprised if Oracle allows non-syntax errors inside a > transaction to be ignored, or ignores anything in a non-interactive > session. How about testing an example like links, where you provide data > in a format the backend can't handle, (an out of range int or date or > something) and see how Oracle handles that. here the test: SQL*Plus: Release 8.0.5.0.0 - Production on Mon Dec 13 21:37:48 1999 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production SQL> create table test(i numeric(12,3), d date); Table created. SQL> insert into TEST VALUES(9.999,'28-FEB-1999'); 1 row created. SQL> insert into TEST VALUES(1.999,'29-FEB-1999') * ERROR at line 1: ORA-01830: date format picture ends before converting entire input string SQL> insert into TEST VALUES(.999,'28-FEB-1999') * ERROR at line 1: ORA-01438: value larger than specified precision allows for this column SQL> commit; Commit complete. select * from test; I D -- - 10 28-FEB-99 SQL> insert into TEST VALUES(1.999,'10-JAN-1999'); 1 row created. SQL> select * from test; I D -- - 10 28-FEB-99 1.999 10-JAN-99 SQL> rollback; Rollback complete. SQL> select * from test; I D -- - 10 28-FEB-99 Jose' > > Who's right? Well, as Peter Eisentraut said, what Postgres implements is > the _definition_ of a transaction: all together, or nothing at all. This > isn't just an arbitrary rule: the validity of the relational calculus > depends on transactional semantics. > > Ross > > -- > Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005 > > On Thu, Dec 09, 1999 at 09:23:35AM +0800, Lincoln Yeoh wrote: > > At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote: > > >Seriously, why do you use a transaction, when you don't want any errors > > >caught? Transactions are defined as everything succeeds or nothing goes. > > >If you want update to succeed anyhow, put it in it's own transaction > > >(i.e., commit before it). > > > > I want errors caught, most errors abort everything but some errors I want > > to try a different update instead, if that doesn't work then only rollback > > everything. > > > > >> I guess that's expected, and I should insert big years using another less > > >> ambiguous format. What is the recommended format? > > > > > >The safest way would be to set a date format with SET DATESTYLE TO and use > > >that, possibly assisted by library formatting routines. > > > > OK. > > > > Link. > > > > > > > > > >
Re: [GENERAL] How to stop implicit rollback on certain errors?
"Ross J. Reedstrom" wrote: > > Hmm, sounds like a vote for nested transactions. The JDBC driver developer > (Peter Mount) was musing that nested transaction would make large object > support easier for him, as well. > > As to the other example of Oracle not forcing a rollback, I have a feeling > that this may be specific to syntax errors in an interactive session. > Implementing this sort of behavior has been discussed recently on the > hackers list, in the context of making it easier to work interactively > inside a transaction. > > I would be surprised if Oracle allows non-syntax errors inside a > transaction to be ignored, or ignores anything in a non-interactive > session. How about testing an example like links, where you provide data > in a format the backend can't handle, (an out of range int or date or > something) and see how Oracle handles that. > Here my test on Oracle: $ sqlplus scott/tiger SQL*Plus: Release 8.0.5.0.0 - Production on Mon Dec 13 23:22:31 1999 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production SQL> create table test(id int primary key,i numeric(12,3), d date); Table created. SQL> insert into TEST VALUES(1,9.999,'28-FEB-1999'); 1 row created. SQL> insert into TEST VALUES(1,1.999,'29-FEB-1999') ERROR at line 1: ORA-01830: date format picture ends before converting entire input string SQL> insert into TEST VALUES(1,.999,'28-FEB-1999') ERROR at line 1: ORA-01438: value larger than specified precision allows for this column SQL> commit; Commit complete. SQL> select * from test; ID I D -- -- - 1 10 28-FEB-99 SQL> insert into TEST VALUES(1,1.999,'10-JAN-1999') * ERROR at line 1: ORA-1: unique constraint (SCOTT.SYS_C001590) violated SQL> insert into TEST VALUES(2,1.119,'10-MAR-1999'); 1 row created. SQL> select * from test; ID I D -- -- - 1 10 28-FEB-99 2 1.119 10-MAR-99 SQL> rollback; Rollback complete. SQL> select * from test; ID I D -- -- - 1 10 28-FEB-99 SQL> exit Disconnected from Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production Jose' > Who's right? Well, as Peter Eisentraut said, what Postgres implements is > the _definition_ of a transaction: all together, or nothing at all. This > isn't just an arbitrary rule: the validity of the relational calculus > depends on transactional semantics. > > Ross > > -- > Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005 > > On Thu, Dec 09, 1999 at 09:23:35AM +0800, Lincoln Yeoh wrote: > > At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote: > > >Seriously, why do you use a transaction, when you don't want any errors > > >caught? Transactions are defined as everything succeeds or nothing goes. > > >If you want update to succeed anyhow, put it in it's own transaction > > >(i.e., commit before it). > > > > I want errors caught, most errors abort everything but some errors I want > > to try a different update instead, if that doesn't work then only rollback > > everything. > > > > >> I guess that's expected, and I should insert big years using another less > > >> ambiguous format. What is the recommended format? > > > > > >The safest way would be to set a date format with SET DATESTYLE TO and use > > >that, possibly assisted by library formatting routines. > > > > OK. > > > > Link. > > > > > > > > > >
[GENERAL] Re: [HACKERS] \copy problem
Sorry Peter, I don't say you any thing, I'm using psql on win95. 1. I see psql for Linux requires \N only for data fields with null values other fields (char,int,etc) doesn't need \N. Why ? 2. psql for M$Windows95 has a different behavior. For example I can't insert date fields even using \N I tried to load a file by changing every NULL value of date fields with \N and it works on Linux psql, but Win95 psql shows me the following message: pqReadData() -- read() failed: errno=0 No error PQendcopy: resetting connection Copy failed. Any ideas ? Peter Eisentraut wrote: > > On 1999-12-16, Jose Soares mentioned: > > > I have a problem using \copy to load data into tables. > > > > I have to load data into a table that contains data type fields with > > NULL values. > > I tried using \N but it doesn't work. > > What can I do to insert a null into a data field? > > Works for me. I also just messed with that part in the devel sources the > other day and I don't see a reason why it wouldn't. Perhaps you could run > the COPY command instead (and make sure the file is accessible to the > server process) or simply run a COPY FROM STDIN; and enter a few things by > hand and see what you get. > > > the \copy error messages.. > > > > What about to have the row number and the error type instead of that... > > hygea=> \copy movimentazioni from 4; > > pqReadData() -- read() failed: errno=32 > > Broken pipe > > PQendcopy: resetting connection > > Copy failed. > > When the backend sends garbage it cannot possibly send the error > message. The error was that the read from the connection failed. Of course > one could argue why that is ... Hmm. > > -- > Peter Eisentraut Sernanders väg 10:115 > [EMAIL PROTECTED] 75262 Uppsala > http://yi.org/peter-e/Sweden > >
Re: [GENERAL] Import table from MS Access?
I have some troubles to access PostgreSQL tables linked to M$-Access2000. Does anyone have any ideas, how to do that? Thanks. [EMAIL PROTECTED] wrote: > We've had good luck with something we found at: > > http://www.sevainc.com/ > > David Boerwinkle > > -Original Message- > From: Mike Mascari <[EMAIL PROTECTED]> > To: Chris Carbaugh <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> > Date: Sunday, December 26, 1999 6:08 PM > Subject: Re: [GENERAL] Import table from MS Access? > > >Chris Carbaugh wrote: > > > >> What is the best way to import a table from Microsoft Access 2000? > >> > >> I was able to export to a text file from access, but this was only the > >> data. Can I export/import the table definition as well? > >> > >> I have been using pgaccess to administer my DB. It seems I can't tell > >> it to import a comma delimited file? Is there any way around this? > >> > >> Any help is greatly appreciated. > >> > >> Chris > >> > > > >One way is to use the PostgreSQL ODBC driver from Insight (search > >yahoo.com for: postgres Insight ODBC), and use the File->Export function > >in Access to export the tables to PostgreSQL. There are a few problems > >with this method, though, if I recall correctly: > > > >1. Table and field names will be case-sensitive, so if you have a table > >in Access called Employees with a field HireDate, then in PostgreSQL, > >you must refer to this as "Employees"."HireDate", not employees.hiredate, > >although you could programmatically rename the tables by performing an > >update on pg_class and pg_attribute. > > > >2. Column constraints are not exported. If I recall (its been some time), > >column constraints are not exported from Access when the tables are > >created. And, unfortunately, there's no easy way to add them in > >PostgreSQL using an ALTER TABLE statement. > > > >Nevertheless, it might be easier to perform the export in Access using > >ODBC, pg_dump the database to a text file, perform whatever cleanup is > >necessary, and then reimport. > > > >Also, I rember that there's a PostgreSQL upsizing tool somewhere that > >does all this stuff for you. But for the life of me I can't remember > >where... > > > >Hope that helps, > > > >Mike Mascari > > > > > > > > > > > >
Re: [GENERAL] How to get number of the week from datetime?
"Hojdar Karel Ing." wrote:
> Hi,
>
> how I can get some agregates (avg, min, max) for whole week (in specified
> year)?
> For example from table with two columns : time datetime and value float8.
> And I want to get average of value based on whole weeks. If I try to use
> date_part('week',time) - Postgresql doesn't recognize word 'week'.
>
> Thanks Karel
>
>
select date_part('dow',current_date);
If you are looking for a function to calculate the no. week of the year...
try the attached function.
José
-- ISO-8601 assigns a number to each week of the year.
-- A week that lies partly in one year and partly in another is
-- assigned a number in the year in which most of its days lie.
-- This means that:
-- Week 1 of any year is the week that contains 4 January.
-- or equivalently
-- Week 1 of any year is the week that contains the first Thrusday
-- in January .
-- If the week is 0 that means that first week is less than 4 days.
-- returns the week number of the year (1 - 53)...
drop function week(date);
create function week(date) returns int2 as
'declare
p int2;
i int2;
weekint4;
yeartext;
start date;
difftimespan;
maisint2;
txt text;
begin
year:= date_part(''year'',$1);
if textlen(year) = 1 then
year:= ''000'' || year;
end if;
if textlen(year) = 2 then
year:= ''00'' || year;
end if;
start:= year || ''-01-01'';
week:= date_part(''dow'',start);
if week > 3 or week = 0 then
mais:= 0;
else
mais:= 1;
end if;
week:= date_part(''dow'',start);
diff:= date_part(''epoch'',$1) - date_part(''epoch'',start);
txt:= diff;
p:= textpos(txt,'' '');
if p = 0 then
i:= 0;
else
i:= substr(txt,1,p - 1);
end if;
return (i + week) / 7 + mais;
end;
' language 'plpgsql';
select week('1997-01-01'), week('1997-12-31');
select week('1998-01-01'), week('1998-12-31');
select week('1999-01-01'), week('1999-12-31');
select week(current_date);
Re: [GENERAL] max(oid)
Peter Eisentraut wrote:
> On 2000-01-21, Bruce Momjian mentioned:
>
> > > Is there a way to use the max aggregate on an oid field? When I try on
> > > 6.5.3, I get the following error message:
> > >
> > > test=> select max(uid) from user_base;
> > > ERROR: Unable to select an aggregate function max(oid)
> > >
> > > If there's any work-around, please let me know.
> > > Marc
> > >
> >
> > Added to TODO:
> >
> > * allow aggregates on oid
>
> We already had a TODO item for this and came to the conclusion that
> * Make type equivalency apply to aggregates
> will solve this.
>
> For right now the user could do the following:
>
> INSERT INTO pg_aggregate VALUES ('max', , 'int4larger', '-',
> '-', 26, 26, 0, 26, NULL, NULL);
>
We need also aggregates for data type TIME
MAX(time)
MIN(time)
José
Re: [GENERAL] Can || be used in ORDER BY?
create table tablename ( field1 text, field2 text);
CREATE
insert into tablename values('bottom','yes');
INSERT 2282464 1
insert into tablename values('top','no');
INSERT 2282465 1
select field1,field2 from tablename order by (field1||'-top');
field1|field2
--+--
bottom|yes
top |no
(2 rows)
José
Jeremy Malcolm wrote:
-BEGIN PGP SIGNED MESSAGE-
I would like to do this:
select
field1,field2
from tablename
order by (field1||'-top')
ie. order the records by the contents of field1 with the text "-top"
concatenated to it. It doesn't work, I get a parse error.
Can anyone offer advice?
Thanks.
- --
JEREMY MALCOLM [EMAIL PROTECTED] http://malcolm.wattle.id.au
SIG of the day: [ ] Contact [ ] Web [ ] PGP [ ] Taglines
#1 [x] #2
"I'm a lawyer." "Honest?" "No, the usual kind." | Linux, the choice
of
a GNU generation. | Are you the brain specialist? | "Could anyone pass
the sodium chloride, please?" - Adric (5W) | The Nanites have lawyers?
-BEGIN PGP SIGNATURE-
Version: PGPfreeware 6.0.2i
iQB1AwUBOI3a6L/mBljD2JABAQG3RQL8DxUkukKGm7jEa9rSgyFzXMcd5KJejRxU
abscA8SuVq7ENXdFncx+5OsAk0VQfzBkUkRlobD9LEFXm6aTsK2zqmlhdVsJFKyh
f/YOixdaGiNzE+9xfIpEz+iizzKBwPRy
=bED8
-END PGP SIGNATURE-
Re: [GENERAL] what is "view?"
A view is a table with a rule SELECT For excample if you have a table named my_table and you create a rule like: CREATE RULE "_RETmy_table" AS ON SELECT TO "my_table" DO INSTEAD SELECT * FROM your_table; In this way you your table my_table became a view. José Marc Tardif wrote: > When listing my tables and indices in psql, I see a "view?" in type. What > is this type? Where can I read about it in the manual? > > The table listed as "view?" use to be listed as "table" but suddently > changed when I added rules. Do rules make a table a view? > > Marc > >
Re: [GENERAL] Bug with indexing int4?
Yury Don wrote: Hello All, I ma sorry, I have sent previous uncomplited e-mail accidentally. I have created the table CREATE TABLE "tt" ( "cc" int4); COPY "tt" FROM stdin; -2112563299 -2111287024 -2110307960 . 2146589610 2146589611 2146589612 \. About 30 000 records totally Then I am doing the following: mdb=> select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) mdb=> create index i_tt_cc on tt (cc); CREATE mdb=> select cc from tt where cc = -2112563299; cc (0 rows) mdb=> drop index i_tt_cc; DROP mdb=> select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) So, whith index postgresql works incorectly in this situation. But when there are less records in the table (about 12 000), everything works well. I tested this on 3 different computers on Debian Linux 2.1 with postgresql 6.5.2 and 6.5.3. Best regards, Yury ICQ 11831432 mailto:[EMAIL PROTECTED] I tried it on my Debian and it works... hygea=> select version(); version -- PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 (1 row) uname -a Linux Debian 2.0.37 #1 Thu Sep 2 17:24:38 CEST 1999 i586 unknown == CREATE TABLE "tt" ( "cc" int4); CREATE COPY "tt" FROM stdin; select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) create index i_tt_cc on tt (cc); CREATE select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) drop index i_tt_cc; DROP select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) José
Re: [GENERAL] Need help creating a function
create function money(float8) returns money as '
declare
f2 float8;
m money;
i2 int2;
i1 int4;
txt text;
begin
if $1 isnull then
return NULL;
end if;
--integer part...
i1:= dtrunc($1);
-- decimal part...
i2:= dround(datetime_part(''millisecond'',$1));
-- cut 3th digit...
txt:= dround(i2/10.0);
if textlen(txt) = 1 then
txt:= ''0'' || txt;
end if;
m:= i1 || (''.'' || txt);
return m;
end; ' language 'plpgsql';
Hitesh Patel wrote:
> Does anyone have a function laying around that convert a 'money' type to
> a float8 and return it?
>
>
--
Jose' Soares
Bologna, Italy [EMAIL PROTECTED]
[GENERAL] TRANSACTIONS
Hi all, The transactions should be the way to distinguish a relational database from others no-relational databases, (MySQL is the right example). We are very proud of PostgreSQL transactions but seems that it doesn't work in the right way. It shoud be important to be sure that PostgreSQL is compliant with SQL92. I need absolutely to use transactions but until now I could not use it, in my case it is completely unusable. I tried transactions in other databases and I compared it with PostgreSQL and no one of which I tried has the same PostgreSQL behavior. I tried the following script: --- PostgreSQL: --- begin transaction; create table tmp(a int); insert into tmp values (1); insert into tmp values (10); ERROR: pg_atoi: error reading "10": Numerical result out of range commit; select * from tmp; ERROR: tmp: Table does not exist. --- Interbase, Oracle,Informix,Solid,Ms-Access,DB2: --- connect hygea.gdb; create table temp(a int); insert into temp values (1); insert into temp values (10); commit; select * from temp; arithmetic exception, numeric overflow, or string truncation A === 1 I would like to know what the Standard says and who is in the rigth path PostgreSQL or the others, considering the two examples reported below. Comments? -- Jose' Soares Bologna, Italy [EMAIL PROTECTED]
[GENERAL] Re: [HACKERS] TRANSACTIONS
Dmitry Samersoff wrote: > On 22-Feb-2000 Jose Soares wrote: > > begin transaction; > > create table tmp(a int); > > insert into tmp values (1); > > insert into tmp values (10); > > ERROR: pg_atoi: error reading "10": > > Numerical result out of range > > commit; > > select * from tmp; > > ERROR: tmp: Table does not exist. > > --- > > Interbase, Oracle,Informix,Solid,Ms-Access,DB2: >^ > AFAIK, MS Access have no transactions inside it, > Informix (at least old versions I worked with) always > perform create,drop, alter object outside transaction > but IMHO it's not right behavior. I don't know and I don't care about old software, I'm talking about Ms_Access97 and Informix 8. -- Jose' Soares Bologna, Italy [EMAIL PROTECTED]
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Sorry for my english, Tom, but the point is another, I'm talking about transactions not about error messages. This is only a stupid example how to abort a transaction, PostgreSQL aborts automatically transactions if an error occurs, even an warning or a syntax error. I can believe that all other databases are wrong and only we (PostgreSQL) are right, but please try to understand me. This is not easy to believe anyway. I'm looking for another database with a behavior like PostgreSQL but I can't find it, and I tried a lot of them until now. Do you know some database with transactions like PostgreSQL? Tom Lane wrote: > Jose Soares <[EMAIL PROTECTED]> writes: > > --- > > Interbase, Oracle,Informix,Solid,Ms-Access,DB2: > > --- > > connect hygea.gdb; > > create table temp(a int); > > insert into temp values (1); > > insert into temp values (10); > > commit; > > select * from temp; > > > arithmetic exception, numeric overflow, or string truncation > > > A > > === > > 1 > > > I would like to know what the Standard says and who is in the rigth path > > PostgreSQL or the others, considering the two examples reported below. > > I think those other guys are unquestionably failing to conform to SQL92. > 6.10 general rule 3.a says > > a) If SD is exact numeric or approximate numeric, then > > Case: > > i) If there is a representation of SV in the data type TD > that does not lose any leading significant digits after > rounding or truncating if necessary, then TV is that rep- > resentation. The choice of whether to round or truncate is > implementation-defined. > > ii) Otherwise, an exception condition is raised: data exception- > numeric value out of range. > > and 3.3.4.1 says > > The phrase "an exception condition is raised:", followed by the > name of a condition, is used in General Rules and elsewhere to > indicate that the execution of a statement is unsuccessful, ap- > plication of General Rules, other than those of Subclause 12.3, > "", and Subclause 20.1, "", may > be terminated, diagnostic information is to be made available, > and execution of the statement is to have no effect on SQL-data or > schemas. The effect on s and SQL descriptor > areas of an SQL-statement that terminates with an exception condi- > tion, unless explicitly defined by this International Standard, is > implementation-dependent. > > I see no way that allowing the transaction to commit after an overflow > can be called consistent with the spec. > > regards, tom lane > > -- Jose' Soares Bologna, Italy [EMAIL PROTECTED]
[GENERAL] Re: [HACKERS] TRANSACTIONS
Don Baccus wrote: > At 11:32 AM 2/22/00 -0500, Tom Lane wrote: > > >I see no way that allowing the transaction to commit after an overflow > >can be called consistent with the spec. > > You are absolutely right. The whole point is that either a) everything > commits or b) nothing commits. > > Having some kinds of exceptions allow a partial commit while other > exceptions rollback the transaction seems like a very error-prone > programming environment to me. > It is hard to believe all world is wrong and only we are right. Isn't it ? ;) > > - Don Baccus, Portland OR <[EMAIL PROTECTED]> > Nature photos, on-line guides, Pacific Northwest > Rare Bird Alert Service and other goodies at > http://donb.photo.net. > > -- Jose' Soares Bologna, Italy [EMAIL PROTECTED]
Re: [GENERAL] Date problem
Try using standard sql:
hygea=# select current_date + interval '3 days';
?column?
2000-03-16 00:00:00+01
edNET System Admin wrote:
Hi, I'm trying to do something with dates which is
proving to be a bit
tricky.
I'm trying to get the current "date" and add 3 days to this.
I've tried:
$date = "(now::date) + ('3 days'::interval)";
DBD::Pg::st execute failed: ERROR: parser: parse error at or near "3"
.. and :
$date = "(now::date) + (\\'3 days\\'::timespan)";
DBD::Pg::st execute failed: ERROR: Bad date external representation
'(now::date) + ('3 days'::timespan)'
Needless to say I'm using this date creation string as a parameter of
a
DBD::Pg CGI query, hence further complications with apostrophes.
I've found nothing concrete in the documentation about how to do this,
and
the closest thing to an answer came from this newsgroup. No luck so
far
tho'
does anyone know how to do this and possibly and source of good
documentation on this type of thing.
Regards
Scott McDaid
edNET
t: +44 131 625 5557 (direct dial)
t: +44 131 466 7003 (office)
--
Jose' Soares
Bologna, Italy
[EMAIL PROTECTED]
Re: [GENERAL] Max Length for VARCHAR?
It seems to be 8104: prova=> create table a (a varchar(9)); ERROR: length for type 'varchar' cannot exceed 8104 Chris Gokey wrote: > Hi everyone, > > What is the maximum length of VARCHAR? If there something equivalent > to Oracle's LONG in postgresql? I need a datatype that can hold very > large text? > > Chris > > -- > Christopher D. Gokey, Raytheon ITSS, NASA/GCMD > 18 Martin Road, Shelburne Falls, MA 01370 > Phone: Voice (413) 625-8129 / FAX 208-248-9055 > [EMAIL PROTECTED] / http://gcmd.nasa.gov -- Jose' Soares Bologna, Italy [EMAIL PROTECTED]
Re: [GENERAL] alter table
It works for me in v7beta: hygea1=# create table tmp ( idhygea1(# id int, hygea1(# first text); CREATE hygea1=# insert into tmp values(1,'one'); INSERT 120138 1 hygea1=# alter table tmp add column last text; ALTER hygea1=# update tmp set last=''; UPDATE 1 hygea1=# insert into tmp values(2,'two','two'); INSERT 120140 1 hygea1=# select * from tmp where last='two'; id | first | last +---+-- 2 | two | two (1 row) Wim Ceulemans wrote: > hikmat farhat wrote: > > > > hi, > > i having problems adding a column to table, for example > > the postgres response is preceded with ">" > > > > create table tmp ( > > id int, > > first text); > > >CREATE > > > > insert into tmp values(1,'one'); > > >INSERT 356298 1 > > > > alter table tmp add column last text; > > >ADD > > > > update tmp set last=''; i though that this might do it, but no > > >UPDATE 1 > > > > insert into tmp values(2,'two','two'); > > >INSERT 356300 1 > > > > select * from tmp where last='two' > > > > >ERROR: RestrictionClauseSensitivity: bad value -1.998613 > > > > i tried to search the mailing lists but i couldn't find anything > > > > any ideas? > > It's working here with version 6.5.3. Which version are you using? > > Wim -- Jose' Soares Bologna, Italy [EMAIL PROTECTED]
Re: [GENERAL] How to retrieve table definition in SQL
It seems to work in version 6.5.2 but not in version 6.5.3 what's happened with -E parameter ? from man psql: ... PSQL(UNIX) PostgreSQL PSQL(UNIX) -E Echo the actual query generated by \d and other backslash commands -f filename Use the file filename as the source of queries instead of reading queries interactively. ... anyway try this one: (replace MY_TABLE with your table name): SELECT a.attnum, a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'MY_TABLE' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY attnum ; attnum|attname |typname|attlen|atttypmod|attnotnull|atthasdef --+--+---+--+-+--+- 1|istat |bpchar | -1| 10|t |f 2|nome |bpchar | -1| 54|t |f 3|provincia |bpchar | -1| 6|f |f 4|codice_fiscale|bpchar | -1| 8|f |f 5|cap |bpchar | -1| 9|f |f 6|regione |bpchar | -1| 7|f |f 7|distretto |bpchar | -1| 8|f |f (7 rows) Jeff wrote: That doesn't seem to work for me the -E switch doesn't exist, but the -e (echo) does, which I assume you are referring to. The problem is that postgress doesn't echo anything for \commands only real SQL commands. If it works on yours could you copy the echoed query and forward it to me. Jeff Seese > From: Jose Soares <[EMAIL PROTECTED]> > Organization: Sfera Carta > Newsgroups: muc.lists.postgres.questions > Date: 20 Mar 2000 17:04:13 +0100 > Subject: Re: [GENERAL] How to retrieve table definition in SQL > > If you start pgsql with -E parameter > when you type \d pgsql shows you the query it executes to display > the table definition. > > > Stan Jacobs wrote: > >> Hi everyone, >> >> This probably isn't a Postgres-specific question, but I'm hoping that >> someone knows the answer to this off the top of their heads... :-) >> >> I'd like to retrieve the table definition via SQL. I'm using ColdFusion >> to access a PostgreSQL 6.5.3 database, and I'd like to retrieve the table >> info, field names/types/sizes, so that my Coldfusion page/script can >> dynamically build the html forms to edit the tables. >> >> Any ideas how to get to this in SQL? I have another C++ class which >> builds nice table headers with this info, but ColdFusion doesn't seem to >> do that with the returned data. >> >> Thanks! >> >> - Stan - > > -- > Jose' Soares > Bologna, Italy [EMAIL PROTECTED] > > > -- Jose' Soares Bologna, Italy [EMAIL PROTECTED]
[GENERAL] COALESCE() or NVL()
Hi all, I'm looking for a function like COALESCE() or the Oracle NVL(), to returns a ZERO value instead of a NULL value. To have the result: NULL+1 = 1 instead of NULL+1 = NULL Have PostgreSQL something like this ? I tried to write it on C but I can't realize the beavior of NULLs, I can't get that my program returns a zero instead of a null. I'm not a C programmer, could somebody help me ? SELECT * FROM emp; name |salary|age|dept ---+--+---+- Sam| 1200| 16|toy Claire | 5000| 32|shoe Bill | 4200| 36|shoe Ginger | 4800| 30|candy NULL VALUES| | | (5 rows) SELECT name,NVL(salary)+100 AS dream FROM emp; name |dream ---+- Sam| 1300 Claire | 5100 Bill | 4300 Ginger | 4900 NULL VALUES| <--- I expected 100 here. (5 rows) Thanks, Jose' | | | | Progetto HYGEA www.sferacarta.com Sfera Carta Software [EMAIL PROTECTED] Via Bazzanese, 69 | |Fax. ++39 51 6131537 Casalecchio R.(BO) Italy | |Tel. ++39 51 591054
Re: [GENERAL] extracting date information?
On Mon, 18 May 1998, Kevin Heflin wrote:
> Any hints or clues on this one?
> Using Postgresql6.3 and PHP2
>
> I have a simple database with a list of events, one of the fields is of
> the data type "date".
>
> When I call this information from the database, it prints out the date
> in the form of "1998-05-05"
>
> What I would really like to do, when this date is retrieved, Is to
> format it something like "Thursday May 5, 1998" Anything more
> descriptive than "1998-05-05" would be great.
>
> I've tried using the "date(format,date)" call from PHP by saving the
> date in a variable, then:
> echo date("YMdl","$thedate");
>
> But when it is displayed it always prints out this date:
> "1969Dec31Wednesday"
>
> Any suggestions or comments would be appreciated.
>
If you use "datetime" type instead of "date" and set datestyle = 'Postgres'
your data will be like: Sat Dec 12 00:00:00 1998 CET
instead of: 1998-12-12
Jose'
Re: [GENERAL] extracting date information?
On Tue, 19 May 1998, Kevin Heflin wrote: > On Tue, 19 May 1998, Jose' Soares Da Silva wrote: > > > If you use "datetime" type instead of "date" and set datestyle = 'Postgres' > > your data will be like: Sat Dec 12 00:00:00 1998 CET > > instead of: 1998-12-12 > > I actually stumbled across this after my initial post. > > Now that my dates are being retreived in this format: > Sat Dec 12 00:00:00 1998 CET > > Is there a way to format this to leave off the "time" portion and maybe > the "timezone" info ? try this: prova=> select c from a; mydate Sat Dec 12 00:00:00 1998 CET (1 row) prova=> select substring(cast(c as text) from 1 for 10)||substring(cast(c as text) from 20 for 5) from a; ?column? --- Sat Dec 12 1998 (1 row) You may also create a function like this: prova=> create function mydate(datetime) returns text prova-> as 'select substring(cast($1 as text) from 1 for 10)||substring(cast($1 as text) from 20 for 5) from a;' language 'sql'; CREATE prova=> select mydate(c) from a; mydate --- Sat Dec 12 1998 (1 row) Jose'
[GENERAL] error messages not only English
Hi all, I see that PostgreSQL mainly gives error messages in English, I see also that in some cases there's the possibility to configure it to give messages in other languages like global.c that may be configured to give messages in German. MySQL gives the possibility to configure it using an external file containing the messages by specifying it using the parameter LANGUAGE= where is one of the following: czech english french germany italian norwegian norwegian-ny polish portuguese spanish swedish It will be great if we could have also this feature on PostreSQL. I'm available to help on translation to Portuguese, Spanish and Italian. Jose'
Re: [GENERAL] Privileges
On Sun, 24 May 1998, The Hermit Hacker wrote: > That command did not return an error, rather it said CHANGE (without > the exclamation marks, of course). However, afterward, I was still able > to insert into the table. What gives? It works for me, I have v6.3 > > Also, another question: > > How do you check to see exactly who has permissions to any particular > table? psql \z command Jose'
Re: [GENERAL] GRANT/REVOKE problems
On Mon, 25 May 1998, Marin D wrote:
Do you want revoke all previleges from postgres?
Remember that user postgres is the super user.
I think you don't do that.
Jose'
>
> Hi!
>
> I cant make grant/revoke work...
>
> An example
>
> test=> \z
> ...
> | test | |
> ...
> test=> REVOKE ALL ON test FROM postgres;
> CHANGE
> test=> \z
>
> Database= test
> +--++
> | Relation| Grant/Revoke Permissions |
> +--++
> ...
> | test | {"=r"} |
> ...
> test=> \q
>
> [3:32pm]#su postgres
> Password:
> bash$ psql test
> Welcome to the POSTGRESQL interactive sql monitor:
> Please read the file COPYRIGHT for copyright terms of POSTGRESQL
>
>type \? for help on slash commands
>type \q to quit
>type \g or terminate with semicolon to execute query
> You are currently connected to the database: test
>
> test=> insert into test values ('wow!');
> INSERT 276704 1
> test=>
>
>
> Any hints?
>
> BTW the version is 6.2.1p6
>
> Thanx for the attention!
>
> Marin
>
>
> -= Why do we need gates in a world without fences? =-
>
[GENERAL] PgAccess running on Win95 ?
On Wed, 3 Jun 1998, Constantin Teodorescu wrote: > Jose' Soares Da Silva wrote: > > > > pgtcl.c:20: tcl.h: No such file or directory > > I really do not understand why it wouldn't compile the 6.3.2! > Be aware ! 6.3.2 has a little error in ./configure determining the right > type of the system. You are right, Constantin, Now it works, Thanks for your pacience. I thought that solving this problem I would be able to connect to PostgreSQL from Win95 using PgAccess but I can't to do that yet. Is it maybe a problem with PostgreSQL 'username' ? Using Linux the 'username' is the logname, but using Win95 what's the 'username' ? Is there somebody using successfully PgAccess on Win95 ? Thanks, Jose'
[GENERAL] TRIGGERS
Hi, all! I am trying to create a trigger to update a field on a "son" table when a linked field (foreign key) is modified on a table "father". example: table son: table father: - id /-< id description /name son_id <--/ address ... ... - I see there's a check_foreign_key() function doing the following: CASCADE - to delete corresponding foreign key, RESTRICT - to abort transaction if foreign keys exist, SETNULL - to set foreign key referencing primary/unique key being deleted to null) I need to implement a MODIFY clause to set 'son.son_id' equal to 'father.id' when 'father.id' is updated. I'm not a C-programmer, then I created a SQL function, but seems that TRIGGER doesn't recognize SQL functions. Am I right ? Thanks, Jose'
Re: [GENERAL] Missing SQL Syntax & Problem with Create Table
On Mon, 8 Jun 1998, Fredrick Meunier wrote:
> Hi,
> I have a database design tool under windows, and it has a
> feature where you can store varoius metadata about your database
> in an ODBC database. I would like to use PostgreSQL as the
> repository. The product goes and creates it's own schema in a
> datasource, but has problems with the following DDL:
>
> CREATE VIEW ALL_TEXT (TEXTIDTF,TEXTNSEG)
> AS SELECT B.BLBJ,B.NSEG FROM BLBJ B WHERE B.BTYP = 1
> ERROR from backend during send_query: 'ERROR: parser: parse error at or
> near "("'
PostgreSQL doesn't support the above syntax. Try this instead:
CREATE VIEW ALL_TEXT
AS SELECT B.BLBJ AS TEXTIDTF, B.NSEG AS TEXTNSEG
FROM BLBJ B WHERE B.BTYP = 1
>
>
> What are the chances of getting view creation syntax like the above
> accepted?
>
> The other problem is:
> CREATE TABLE MPDREFR
> ( REFR int4 NOT NULL,
> SRCE int4 NOT NULL,
> TRGT int4 NOT NULL,
> LABL varchar(254) ,
> URUL int2 ,
> DRUL int2 ,
> MAND int2 ,
> CPRT int2 ,
> TOBJ int2 ,
> COBJ varchar(80) ,
> SOID int4 ,
> FKCN varchar(64) ,
> CMIN varchar(10) ,
> CMAX varchar(10) ,
> NGEN int2 )'
> ERROR from backend during send_query: 'ERROR: create: system attribute
> named "cmin"'
cmin and cmax are reserved words, try to rename to C_MIN C_MAX for example.
>
> Can the system attribute limitation be removed, or can the system
> attributes be renamed to not conflict with legal SQL92 column names?
>
> Thanks for any help you can offer,
> Fred
> --
> To have no errors
> Would be life without meaning
> No struggle, no joy-- Brian M. Porter
Ciao, Jose'
Re: [GENERAL] Foreign Keys
On Mon, 8 Jun 1998, ENTER YOU NAME HERE wrote: > Hi all > I am using PostgreSQL as part of my MSc project. Because it is an > object-relational database it is a superset of a pure relational and so > tried to add foreign keys but are not supported. How can I overcome this > problem? man create_triggers see also .../contrib/spi/refint* Jose'
Re: [GENERAL] Setting the table separator in psql command mode
On Tue, 9 Jun 1998, Mehrdad Ghassempoory wrote: > I am trying to set up the table separator (Default "|") to TAB > caharcter. > > I have tried : > > \f\t > \f \t > \f '\t' > > Without any luck. > How is it done? Try psql -F"" this mean ^I Jose'
Re: [GENERAL] Problem with apostrophe
On Wed, 17 Jun 1998 [EMAIL PROTECTED] wrote: > Is there any way of having an apostrophe in a data field? > It is a problem because the apostrophe is the same character as the single > quote, so the database gets a parser error when a name such as "O'Brian" > is entered. > > There must be a work-around, but I can not find any solution. > > thank you for your help You may use doubble apostrophe as in O''Brian to match O'Brian this is SQL92. Jose' | | | | Progetto HYGEA www.sferacarta.com Sfera Carta Software [EMAIL PROTECTED] Via Bazzanese, 69 | |Fax. ++39 51 6131537 Casalecchio R.(BO) Italy | |Tel. ++39 51 591054 -
