Re: [SQL] Tip: a function for creating a remote view using dblink
> Hello, >I'm posting a function here in the hope others may find it useful > and/or correct my mistakes/make improvements :) > > This creates a view of a remote table, using dblink: ... > Is there any existing site (a wiki for example) for posting PostgreSQL > specific tips? The PG cookbook ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Trace for postgreSQL
On Tue, Feb 10, 2004 at 12:04:42PM +, beyaNet Consultancy wrote: > Hi, > can anyone tell me whether there is a trace facility (application) > available for postgreSQL version 7.4.1 which will enable me to see all > incoming requests being made to the database (ala SQL Server)? Sure. Alter your configuration to echo queries, and then watch your log file. Alternatively, you can enable the command string statistics function, and then you get the queries in near to real time in pg_stat_activity. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Unique Constraint with foreign Key
Greg Patnude wrote:
Pleas also note that the referenced column in the foreign table either needs
to be the PRIMARY KEY or have a unique constraint on it or maybe it just
requires an index on it -- I'm not sure but I discovered that if the column
in the foreign table (containing the REFERENCED key...) is NOT the primary
key column -- the REFERENCES a(x) will faill unless a.x is specified as
'UNIQUE' -- as in the following example:
This is according to the SQL specification, which doesn't like doubts.
Imagine rows (1, 99), (2, 99) in table a and row (3, 99) in table b.
Which of the a-rows is now referenced and am I allowed to delete the
other? There are good arguments either way, but if you require a UNIQUE
on a.x, then this question will never come up.
Jan
create table a (
y integer not null primary key default nextval('nexta_seq'),
x varchar not null UNIQUE
);
create table b (
z integer not null PRIMARY KEY default nextval('nextbz_seq'),
x varchar NOT NULL REFERENCES a(x),
);
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Ok, what am I doing wrong here?
I want to insert values from one table into another, and add some "default" values (that are not defaults on the table different reasons - that is, this is maintenance function and in normal operation there would be "real" values there - and null is valid) So, I want to do, for example, the following: insert into table (id, time, type) values (select id, now(), '1' from secondtable); Postgres's command line pukes on this, complaining that "select" is invalid inside the values part of the definition. SQL's language specification says otherwise, as does "\h insert" from the command line. The query stand-alone returns a table with values that are valid for the table I wish to insert into. Where's my brain-fade on this? -- -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist http://www.denninger.netTired of spam at your company? LOOK HERE! http://childrens-justice.orgWorking for family and children's rights http://diversunion.org LOG IN AND GET YOUR TANK STICKERS TODAY! http://scubaforum.org Come talk about DIVING! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] bytea or blobs?
beyaNet Consultancy wrote: Hi, what I am trying to do is to be able to store images in my database. What I wanted to know is this: 1. Would it be better to have the image field type as a bytea or a blob? I have heard it mentioned that bytea would be better as doing data dumps would also insure that the image was saved as well! 2. Would it be better to make reference to mp3 files (i.e. storing the address of the image /images/*.jpg) or is it feasible to store the mp3 in the database as bytea or blobs as well? many thanks in adavance If you want the same access and data protection (including transactional semantics and network access) as for your other data, it has to be inside the database. Now unless you're going for video streams, I think most databases (even MySQL as of 4.0) can handle multi-megabyte columns just fine, and as long as they contain just some 7bit ascii you'll be absolutely portable. Storing the data in Postgres in regular tables will give you the least amount of backup etc. problems, as they just don't exist in that case. To achieve this, I'd recommend to let the application convert the binary data to and from base64, which is a well defined and not too bloated standard. It is reasonably fast too. That will let you easily embed any binary data into a text or varchar column. You don't even need to quote it any more when inserting it into the query string. To get the ultimate out of Postgres' storage capabilities then, I would create a data table with a bytea column, hidden behind a view and rewrite rules that use encode(data, 'base64') and decode(data, 'base64') when rewriting the queries. The bytea column of that table will be configured without toast compression if the intended data usually is compressed, like jpeg or mp3. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Ok, what am I doing wrong here?
[EMAIL PROTECTED] On Tue, 17 Feb 2004, Karl Denninger wrote: > I want to insert values from one table into another, and add some "default" > values (that are not defaults on the table different reasons - that is, this > is maintenance function and in normal operation there would be "real" values > there - and null is valid) > > So, I want to do, for example, the following: > > insert into table (id, time, type) values (select id, now(), '1' from secondtable); > > Postgres's command line pukes on this, complaining that "select" is invalid > inside the values part of the definition. > > SQL's language specification says otherwise, as does "\h insert" from the > command line. I think what you want is insert into table (id, time, type) select id, now(), '1' from secondtable; The choices allowed right now are default values, something that is basically a row constructor with values [*] or a query. [*] - The full spec allows a list of row constructors but we don't currently. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Ok, what am I doing wrong here?
Dnia 2004-02-17 17:02, Użytkownik Karl Denninger napisał:
I want to insert values from one table into another, and add some "default"
values (that are not defaults on the table different reasons - that is, this
is maintenance function and in normal operation there would be "real" values
there - and null is valid)
So, I want to do, for example, the following:
insert into table (id, time, type) values (select id, now(), '1' from secondtable);
Documentation says:
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) |
SELECT query }
insert into table (id, time, type)
select id, now(), '1' from secondtable;
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Ok, what am I doing wrong here?
Thanks... -- -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist http://www.denninger.netTired of spam at your company? LOOK HERE! http://childrens-justice.orgWorking for family and children's rights http://diversunion.org LOG IN AND GET YOUR TANK STICKERS TODAY! http://scubaforum.org Come talk about DIVING! On Tue, Feb 17, 2004 at 08:29:06AM -0800, Stephan Szabo wrote: > [EMAIL PROTECTED] > > On Tue, 17 Feb 2004, Karl Denninger wrote: > > > I want to insert values from one table into another, and add some "default" > > values (that are not defaults on the table different reasons - that is, this > > is maintenance function and in normal operation there would be "real" values > > there - and null is valid) > > > > So, I want to do, for example, the following: > > > > insert into table (id, time, type) values (select id, now(), '1' from secondtable); > > > > Postgres's command line pukes on this, complaining that "select" is invalid > > inside the values part of the definition. > > > > SQL's language specification says otherwise, as does "\h insert" from the > > command line. > > I think what you want is > insert into table (id, time, type) select id, now(), '1' from secondtable; > > The choices allowed right now are default values, something that is > basically a row constructor with values [*] or a query. > > [*] - The full spec allows a list of row constructors but we don't > currently. > > > > %SPAMBLOCK-SYS: Matched [EMAIL PROTECTED], message ok ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] bytea or blobs?
On this subject, isn't it actually better to just store image names in the database and pull the image itself from a directory? That's what I do on my site because I didn't want to bloat up my database unnecessarily. Are there additional benefits to storing the image information in the database that I'm missing? Thanks, Jeremy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jan Wieck Sent: Tuesday, February 17, 2004 10:08 AM To: beyaNet Consultancy Cc: [EMAIL PROTECTED] Subject: Re: [SQL] bytea or blobs? beyaNet Consultancy wrote: > Hi, > what I am trying to do is to be able to store images in my database. > What I wanted to know is this: > > 1. Would it be better to have the image field type as a bytea or a > blob? I have heard it mentioned that bytea would be better as doing > data dumps would also insure that the image was saved as well! > > 2. Would it be better to make reference to mp3 files (i.e. storing the > address of the image /images/*.jpg) or is it feasible to store the mp3 > in the database as bytea or blobs as well? > > many thanks in adavance If you want the same access and data protection (including transactional semantics and network access) as for your other data, it has to be inside the database. Now unless you're going for video streams, I think most databases (even MySQL as of 4.0) can handle multi-megabyte columns just fine, and as long as they contain just some 7bit ascii you'll be absolutely portable. Storing the data in Postgres in regular tables will give you the least amount of backup etc. problems, as they just don't exist in that case. To achieve this, I'd recommend to let the application convert the binary data to and from base64, which is a well defined and not too bloated standard. It is reasonably fast too. That will let you easily embed any binary data into a text or varchar column. You don't even need to quote it any more when inserting it into the query string. To get the ultimate out of Postgres' storage capabilities then, I would create a data table with a bytea column, hidden behind a view and rewrite rules that use encode(data, 'base64') and decode(data, 'base64') when rewriting the queries. The bytea column of that table will be configured without toast compression if the intended data usually is compressed, like jpeg or mp3. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Tip: a function for creating a remote view using dblink
Mark, > I'm posting a function here in the hope others may find it useful > and/or correct my mistakes/make improvements :) Thanks! Way cool! > Is there any existing site (a wiki for example) for posting PostgreSQL > specific tips? > (Wasn't sure if pgsql-sql is the right place for this kind of thing) We're working on something, but nothing's up yet. In the meantime, use the Techdocs Wiki to post it so that we don't lose track: http://techdocs.postgresql.org/guides -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] CHAR(n) always trims trailing spaces in 7.4
Hello, I just discovered the following change to CHAR(n) (taken from varlena.com, general bits, issue 62). This will cause me serious heart-ache, back-ache and bug-ache if we upgrade to 7.4. Is there any compatibility flag available to use pre-7.4 behaviour for CHAR(n)? Regards, John == In 7.4, one of the changes was that the char(n) type now truncates trailing spaces. This causes a problem for those of us using ::char(n) to create fixed length fields. Creating fixed length field batch files are usually required to interface with legacy systems. In the example below, psql is called from a shell with tuples only (t) and unaligned (A). The result of the selection creates a fixed width file. One of the techniques used to create fixed length fields in 7.3 and earlier was to cast the value to char(n). This along with the various to_char() functions used to be able to create fixed length records. For example: % psql -At > batch.out << END select accountid::char(30), to_char( transaction_amount, 'FM000.00'), (lastname || ',' || firstname )::char(40), bankid::char(15), to_char( now(), 'MMDD'); END In 7.4 this no longer works. The fields created with the cast are no longer fixed length. Instead of using the cast to make fixed length fields, use rpad() and lpad() to do the same thing. rpad(string text, length integer [, fill text]) lpad(string text, length integer [, fill text]) The previous selection should now be written as follows. % psql -At > batch.out << END select rpad(accountid,30), to_char( transaction_amount, 'FM000.00'), rpad( (lastname || ',' || firstname ), 40), rpad(bankid, 15), to_char( now(), 'MMDD'); END ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
FW: [SQL] Function
Is there nothing like SET NOCOUNT ON; in plpgsql Is there any option like SET ANSI_NULLS ON in plpgsql. Do we append # before the temp table names in plpgsql, like we do for MSSQL ? In case no how do we create a temp table in a function in plsql? -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Monday, February 16, 2004 11:10 PM To: Sumita Biswas (sbiswas) Cc: [EMAIL PROTECTED] Subject: RE: [SQL] Function On Mon, 16 Feb 2004, Sumita Biswas (sbiswas) wrote: > Thanks for the answer. > I have one more issue. How do I test a function that I wrote? I was > able to create a function called Proc_ConferenceSummary(). In SQL > Server I used to run it through query analyzer by writing the > following command: exec Proc_ConferenceSummary > '12/1/2003','1/23/2004',1,1,0,5001 For functions that return setof or complex types, you can do it as select * from Proc_ConferenceSummary(...) as alias; For simple functions you can just use: select functionname(...); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] nextval problem
New feature for posgreSQL 7.3 & later -- sequences are automatically dropped
when the owning table is dropped -- you need to recreate the sequence
The easiest way is in your CREATE TABLE tblName (
id serial PRIMARY KEY,
blah...,
blah...,
) WITH OIDS;
--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762
"Ivo Anon" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I'm new to postgresql and I'm having problems with the 'nextval' function
> (or whatever it is called in postgresql).
>
> I created a table (called ADDRESS) using 'pgadmin' which has a serial
field
> (called addressid). When I tried the sql statement:
>
> select nextval('public."ADDRESS_addressid_seq"')
>
> everything seemed to be working fine.
>
> I then used the create table statement (displayed in pgadmin when the
table
> 'ADDRESS' is selected) to create the same table (of course after first
> dropping 'ADDRESS'). When I try the same select-statement I suddenly got
an
> error message saying:
>
> ERROR: Relation "public"."ADDRESS_addressid_seq" does not exist
>
> Can anybody help me with this problem?
>
>
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Function
Thanks for the answer. I have one more issue. How do I test a function that I wrote? I was able to create a function called Proc_ConferenceSummary(). In SQL Server I used to run it through query analyzer by writing the following command: exec Proc_ConferenceSummary '12/1/2003','1/23/2004',1,1,0,5001 But I don't know how to do it in Postgresql. Any help is appreciated. Thanks, Sumita -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Monday, February 16, 2004 1:07 AM To: Sumita Biswas (sbiswas) Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Function On Thu, 12 Feb 2004, Sumita Biswas (sbiswas) wrote: > Hi All, > > This function of mine gives a Parse Error: > > CREATE or replace FUNCTION > Proc_ConferenceSummary(date,date,int,int,int) > RETURNS SETOF tbl_error_master AS > ' > declare > li_CallManagerId int; > begin > select * FROM tbl_error_master; > end; > ' > LANGUAGE SQL; I think you want plpgsql as the language. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] SQL query seach + rearranging results
hey guys! I need your
help in writing a php name-day searcher script. here's what ive done:
i have the hostname, login and pw defined in a file, this one calls it:
then i made the
query, where (sorry for not translating the variable names, would be easier for
you to understand )
the "nevek" table contains the names, the honapok table has the month names
and the "nevnapok.nap" tells which day of the month has the name (nevek.nev).
hope you understand me, i'm a bit confused
mysql_select_db($database_rubin_nevnapok,
$rubin_nevnapok); $honap_szama = Date("m"); $nap_szama = Date("d");
$query_mainevnap_lekerdezes = "SELECT nevek.nev, honapok.honap_kod,
nevnapok.nap FROM nevek INNER JOIN (honapok INNER JOIN nevnapok ON
honapok.honap_kod = nevnapok.honap_kod) ON nevek.nev_kod = nevnapok.nev_kod
WHERE honapok.honap_kod = '$honap_szama' AND nevnapok.nap = '$nap_szama'";
$mainevnap_lekerdezes = mysql_query($query_mainevnap_lekerdezes,
$rubin_nevnapok) or die("MySQL hiba! [EMAIL PROTECTED] " . mysql_error());
$row_mainevnap_lekerdezes = mysql_fetch_assoc($mainevnap_lekerdezes);
$totalRows_mainevnap_lekerdezes = mysql_num_rows($mainevnap_lekerdezes);
?> so here comes the important code, which i need help in...:
if ($totalRows_nevnapok_lekerdezes <= 0) {
$hibauzenet = "Ilyen név nincs az adatbázisban: "; print $hibauzenet;
print $p_name;} else {echo $row_nevnapok_lekerdezes['nev']; do {
echo " napja:"; echo ""; echo
$row_nevnapok_lekerdezes['honap']; echo " "; echo
$row_nevnapok_lekerdezes['nap']; echo "."; } while
($row_nevnapok_lekerdezes = mysql_fetch_assoc($nevnapok_lekerdezes)); } ?>
here if the totalrows is 0 then the name ($p_name entered in a html form
btw) was not found in the database and prints an error message (which is btw
$hibauzenet). from now comes the problem: one day (one date) CAN have more
names, and one name CAN be on more days... now what happens is that
if theres a name that has more days, the name is written once and the
corresponding dates ('honap' is the month, 'nap' is the day) are printed (if
there are more dates of the specific name, if there is only one, that one is
printed). BUT the mysql query as you can see, querys using WHERE nevek.nev
LIKE "%$p_name%"... this has a reason: there should be matches if the user
enters only a part of the name, and therefore if they enter only one letter
(say "a") there will be a lot of names, the ones containing that letter... and
in this case, only the first match of the name is printed,and all the other
dates... So how can i make the script write the "nev" if there are different
names, but i want it to write the name only once if the specific name has two or
more dates according to the database? i know should've expressed my
problem simplier but i couldn't for
that, i apologise! but i hope you have my point and can help me!
unfortunately i'm not a php expert, i tried some things but they didn't
work for me... i tried to do the printing with a for, where the $i keeps
changing while it checks if the consecutive result rows
(mysql_result($nevnapok_lekerdezes,$i) and $eredmeny2 =
mysql_result($nevnapok_lekerdezes,$i+1)) are the same, and if they are, writes
the name only once, but i might have screwed it up because it did not do what i
meant it to do
there's another problem: my language has characters that the regular
code tables do not contain, in html they are shown by: "ő" "ű". some names
contain some of these, and they are not found by the above script, although i
have added them to the mysql database... for example if there's a name like
"Dezsõ" (hope you will see this correctly, the last letter is the 337), it's not
found if i enter exactly this into the search field, but is found if i do a
query with "Dezso" written in the field (now it's the same letter, but has
no commas on its top), it is found, and the printed name is not Dezso but Dezsõ,
so the database keeps the special letter, just can't find it! what do you
suggest? regards, lowdog [EMAIL PROTECTED]
ps: i attach here the create tables, just to make the
above more understandable:
CREATE TABLE honapok ( honap_kod tinyint(2) NOT NULL default
'0', honap text NOT NULL, PRIMARY KEY (honap_kod))
TYPE=MyISAM COMMENT='honapok';
CREATE TABLE nevek ( nev_kod smallint(6) NOT NULL default
'0', nev text NOT NULL, PRIMARY KEY (nev_kod))
TYPE=MyISAM COMMENT='nevek listaja';CREATE TABLE nevnapok (
entry_kod smallint(6) NOT NULL default '0', nev_kod smallint(6) NOT
NULL default '0', honap_kod tinyint(2) NOT NULL default '0',
nap tinyint(2) NOT NULL default '0', PRIMARY KEY
(entry_kod)) TYPE=MyISAM COMMENT='a
nevnapok...';
Re: [SQL] Unique Constraint with foreign Key
You've got it wrong when you reference the data column (a.x) -- your
foreign key should reference the primary key in the referenced table
(a.y)...
Besides, in your table A -- 1, 99 2, 99 violates your unique constraint
on column 'X' -- it would never happen...
What I suggested is like this:
create table a (
y integer not null primary key default nextval('nexta_seq'),
x varchar not null UNIQUE
);
create table b (
z integer not null PRIMARY KEY default nextval('nextbz_seq'),
x integer NOT NULL REFERENCES a(y),
);
Table A would have
1, 99
2, 99
..
99,99
and table B would have
1, 1
1, 2
..
1, 99
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID. 83835
(208) 762-0762
Send replies to: [EMAIL PROTECTED]
Website: http://www.left-center.com
-Original Message-
From: Jan Wieck [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 17, 2004 6:42 AM
To: Greg Patnude
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Unique Constraint with foreign Key
Greg Patnude wrote:
> Pleas also note that the referenced column in the foreign table either
needs
> to be the PRIMARY KEY or have a unique constraint on it or maybe it
just
> requires an index on it -- I'm not sure but I discovered that if the
column
> in the foreign table (containing the REFERENCED key...) is NOT the
primary
> key column -- the REFERENCES a(x) will faill unless a.x is specified
as
> 'UNIQUE' -- as in the following example:
This is according to the SQL specification, which doesn't like doubts.
Imagine rows (1, 99), (2, 99) in table a and row (3, 99) in table b.
Which of the a-rows is now referenced and am I allowed to delete the
other? There are good arguments either way, but if you require a UNIQUE
on a.x, then this question will never come up.
Jan
>
> create table a (
> y integer not null primary key default nextval('nexta_seq'),
> x varchar not null UNIQUE
>
> );
>
> create table b (
>
> z integer not null PRIMARY KEY default nextval('nextbz_seq'),
> x varchar NOT NULL REFERENCES a(x),
>
> );
>
>
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Return relation table data in a single value CSV
I should probably be punished for even asking this question, but a simplified version of what I want is this... I have 2 tables: floorplans floorplan_id | description -- 2240 | test floorplan and a table elevations floorplan_id | elevation 2240 | A 2240 | B 2240 | C I want to perform a query that returns this result set: baseplan_id | elevations 2240| A,B,C The real query is, of course, *much* more complex then that, as there are many more fields in floorplans, and floorplans is joined to other tables. Currently I return my floorplan information, then perform a second query to get the elevation records, and loop over the second query to compile the comma separated list of elevations. I have tried subselects concatenated with basically || ',' || where each subselect does an OFFSET X LIMIT 1, and the ',' is wrapped with a case statement to hide the comma if there are no further elevations. It gets very messy very fast as and further I end up hard coding the max number of elevations. Any ideas? Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] bytea or blobs?
Jeremy Smith wrote: On this subject, isn't it actually better to just store image names in the database and pull the image itself from a directory? That's what I do on my site because I didn't want to bloat up my database unnecessarily. Are there additional benefits to storing the image information in the database that I'm missing? Sure, you don't backup/restore the images together (and in a consistent snapshot) with the rest of the data and you cannot access the images through the same, authenticated, database connection in a transactional way. If you for example not only store the path, but the dimensions of the image and let's say an imagemap for a clickable image on a web page as well and now replace the image. The new image data in the directory and the change to the meta information in the database will not change for other transactions at the same time of a transaction boundary. And the changes to the image file will not roll back if something goes wrong before you can commit the transaction. That can lead to funny effects on said website. Jan Thanks, Jeremy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jan Wieck Sent: Tuesday, February 17, 2004 10:08 AM To: beyaNet Consultancy Cc: [EMAIL PROTECTED] Subject: Re: [SQL] bytea or blobs? beyaNet Consultancy wrote: Hi, what I am trying to do is to be able to store images in my database. What I wanted to know is this: 1. Would it be better to have the image field type as a bytea or a blob? I have heard it mentioned that bytea would be better as doing data dumps would also insure that the image was saved as well! 2. Would it be better to make reference to mp3 files (i.e. storing the address of the image /images/*.jpg) or is it feasible to store the mp3 in the database as bytea or blobs as well? many thanks in adavance If you want the same access and data protection (including transactional semantics and network access) as for your other data, it has to be inside the database. Now unless you're going for video streams, I think most databases (even MySQL as of 4.0) can handle multi-megabyte columns just fine, and as long as they contain just some 7bit ascii you'll be absolutely portable. Storing the data in Postgres in regular tables will give you the least amount of backup etc. problems, as they just don't exist in that case. To achieve this, I'd recommend to let the application convert the binary data to and from base64, which is a well defined and not too bloated standard. It is reasonably fast too. That will let you easily embed any binary data into a text or varchar column. You don't even need to quote it any more when inserting it into the query string. To get the ultimate out of Postgres' storage capabilities then, I would create a data table with a bytea column, hidden behind a view and rewrite rules that use encode(data, 'base64') and decode(data, 'base64') when rewriting the queries. The bytea column of that table will be configured without toast compression if the intended data usually is compressed, like jpeg or mp3. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL query seach + rearranging results
On Monday 16 February 2004 16:07, lowdog wrote:
> hey guys!
>
> I need your help in writing a php name-day searcher script.
Actually, you probably want a mysql list - this is a PostgreSQL list.
However, from what I can understand of your problem I think the easiest
solution for you might be to order the results by name (nevek table?) and
then loop through building a results list while the name remains the same.
Something like:
$current_name = '';
while (/* fetch next row */) {
if ($row['name']==$current_name) {
/* add next "day" to output list */
}
else {
/* display output list if anything there */
$current_name = $row['name'];
$output_list = array();
$output_list[] = $row['day'];
}
}
/* on exiting loop, check if there is a final row to display */
Hope that's of some use.
PS - indenting your SQL cleanly on multiple lines will save you time in the
future - trust me on this.
PPS - localization (the accented characters you are having problems with) is a
difficult issue, especially on the web where client/page/database encoding
can all interact.
PPPS - if you port your application to PostgreSQL, you've found the right list
for SQL questions. We also have a PHP list and a novice list if you're just
starting.
Good luck
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: FW: [SQL] Function
On Tuesday 17 February 2004 14:41, Sumita Biswas (sbiswas) wrote: > Is there nothing like SET NOCOUNT ON; in plpgsql What is this supposed to do for you? > Is there any option like SET ANSI_NULLS ON in plpgsql. What is this supposed to do for you too? > Do we append # before the temp table names in plpgsql, like we do for > MSSQL ? In case no how do we create a temp table in a function in plsql? The thing to remember with plpgsql is that it's compiled (unlike say plTcl). This means that it translates table-names etc. into oids. So what you have to be careful of is referring to a temporary table that is created, destroyed an re-created (because it's oid will change). The solution is to use the EXECUTE command to build a dynamic query. Check the archives for plenty of other people doing this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Return relation table data in a single value CSV
On Tuesday 17 February 2004 20:05, [EMAIL PROTECTED] wrote: > I should probably be punished for even asking this question, but a > simplified version of what I want is this... > > I have 2 tables: > floorplans > floorplan_id | description > -- > 2240 | test floorplan > > and a table elevations > floorplan_id | elevation > > 2240 | A > 2240 | B > 2240 | C > > I want to perform a query that returns this result set: > baseplan_id | elevations > 2240| A,B,C You've got two options here: 1. Write a set-returning function in plpgsql (or whatever) to do your looping and build the CSV value. Perhaps look in the contrib/ folder too - might be something in the tablefunc section. 2. Write a custom aggregate function (like sum()) to do the concatenation. This is easy to do, but the order your ABC get processed in is undefined. You can find info on both in the archives, probably with examples. Also - check techdocs. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
"news.postgresql.org" <[EMAIL PROTECTED]> writes: > I just discovered the following change to CHAR(n) (taken from varlena.com, > general bits, issue 62). The description you quote doesn't appear to have much of anything to do with the actual behavior of 7.4. 7.4 will trim trailing spaces when converting char(n) to varchar or text, but the example query does not do that. It just coerces query output columns to char(n), and that works the same as it did before. For instance regression=# select 'zit'::char(77); bpchar --- zit (1 row) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
This is an example of the problem. It used to expand the middle thing to 15. elein=# select 'x' || ' '::char(15) || 'x'; ?column? -- xx (1 row) On Tue, Feb 17, 2004 at 06:10:56PM -0500, Tom Lane wrote: > "news.postgresql.org" <[EMAIL PROTECTED]> writes: > > I just discovered the following change to CHAR(n) (taken from varlena.com, > > general bits, issue 62). > > The description you quote doesn't appear to have much of anything to do > with the actual behavior of 7.4. > > 7.4 will trim trailing spaces when converting char(n) to varchar or > text, but the example query does not do that. It just coerces query > output columns to char(n), and that works the same as it did before. > For instance > > regression=# select 'zit'::char(77); > bpchar > --- > zit > (1 row) > > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Return relation table data in a single value CSV
Richard Huxton <[EMAIL PROTECTED]> writes: > 2. Write a custom aggregate function (like sum()) to do the concatenation. > This is easy to do, but the order your ABC get processed in is undefined. Actually, as of 7.4 it is possible to control the order of inputs to a custom aggregate. You do something like this: SELECT foo, myagg(bar) FROM (SELECT foo, bar FROM table ORDER BY foo, baz) AS ss GROUP BY foo The inner sub-select must order by the columns that the outer will group on; it can then order by additional columns that determine the sort order within each group. Here, myagg() will see its input ordered by increasing values of baz. Before 7.4 this method didn't work because the planner was too stupid to avoid re-sorting the subquery output. You could only make it work in cases where you weren't doing grouping ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
elein <[EMAIL PROTECTED]> writes: > This is an example of the problem. It used to expand > the middle thing to 15. > elein=# select 'x' || ' '::char(15) || 'x'; > ?column? > -- > xx > (1 row) Still does, but then the spaces go away again when the value goes into the concatenation, because concatenation is a text operator. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
So the problem is there. But blaming it on char was wrong. It should be blamed on the varchar change. Hey, I thought the truncation was for varchar and not text? It was for both? It would be semantically tricky to change the operator. The precendence is to convert to text. Now with the implicit update of the char(n) to text for the operator "corrupts" the char() value. elein On Tue, Feb 17, 2004 at 06:40:49PM -0500, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > This is an example of the problem. It used to expand > > the middle thing to 15. > > > elein=# select 'x' || ' '::char(15) || 'x'; > > ?column? > > -- > > xx > > (1 row) > > Still does, but then the spaces go away again when the value goes into > the concatenation, because concatenation is a text operator. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
On Tue, 17 Feb 2004, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > This is an example of the problem. It used to expand > > the middle thing to 15. > > > elein=# select 'x' || ' '::char(15) || 'x'; > > ?column? > > -- > > xx > > (1 row) > > Still does, but then the spaces go away again when the value goes into > the concatenation, because concatenation is a text operator. But then this: select 'x'||' '||'x' should produce xx, but it produces x x. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
"scott.marlowe" <[EMAIL PROTECTED]> writes: > But then this: > select 'x'||' '||'x' > should produce xx, but it produces x x. No, because the imputed type of those literals is text. You'd have to cast the middle guy to char(n) explicitly to make its trailing spaces go away when it's reconverted to text. The real issue here is that trailing spaces in char(n) are semantically insignificant according to the SQL spec. The spec is pretty vague about which operations should actually honor that insignificance --- it's clear that comparisons should, less clear about other things. I think the 7.4 behavior is more consistent than what we had before, but I'm willing to be persuaded to change it again if someone can give an alternate definition that's more workable than this one. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
> select 'x'||' '||'x' > > should produce xx, but it produces x x. > INCORRECT This select 'x'||' '::char ||'x' Should produce xx This select 'x'||' '||'x' is restateable as select 'x'|| ' '::text ||'x' And the || operand for text is not dropping the extra spaces hence correctly x x Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of scott.marlowe > Sent: Tuesday, February 17, 2004 7:07 PM > To: Tom Lane > Cc: elein; news.postgresql.org; [EMAIL PROTECTED] > Subject: Re: [SQL] CHAR(n) always trims trailing spaces in 7.4 > > > On Tue, 17 Feb 2004, Tom Lane wrote: > > > elein <[EMAIL PROTECTED]> writes: > > > This is an example of the problem. It used to expand > > > the middle thing to 15. > > > > > elein=# select 'x' || ' '::char(15) || 'x'; > > > ?column? > > > -- > > > xx > > > (1 row) > > > > Still does, but then the spaces go away again when the > value goes into > > the concatenation, because concatenation is a text operator. > > But then this: > > select 'x'||' '||'x' > > should produce xx, but it produces x x. > > > ---(end of > broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
elein <[EMAIL PROTECTED]> writes: > Apparently the ::char is cast to varchar and then text? No, directly to text, because the || operator is defined as taking text inputs. But there's no practical difference between text and varchar on this point. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Indexes and statistics
Hi all, This is a further post from last week. I've got a table of phone call detail records. buns=# select count(*) from cdr; count - 2800653 (1 row) One of the customers is quite large (8.3% of the records): buns=# select count(*) from cdr where cust_id = 99201110; count 231889 (1 row) I have indexes on cust_id (integer) and bill_id (integer). If I try to do a query on that customer it doesn't use the index: buns=# explain analyse select count(*) from cdr where cust_id = 99201110 and bill_id is null; QUERY PLAN - Aggregate (cost=87082.81..87082.81 rows=1 width=0) (actual time=82279.63..82279.63 rows=1 loops=1) -> Seq Scan on cdr (cost=0.00..87037.71 rows=18041 width=0) (actual time=82279.61..82279.61 rows=0 loops=1) Filter: ((cust_id = 99201110) AND (bill_id IS NULL)) Total runtime: 82280.19 msec (4 rows) I tried this: alter table cdr alter column cust_id set statistics 1000; alter table cdr alter column bill_id set statistics 1000; analyze verbose; The I ran the query again but I still got the same result. Then I tried disabling sequential scans in postgresql.conf, restarted the postmaster and did the query again: buns=# explain analyse select count(*) from cdr where cust_id = 99201110 and bill_id is null; QUERY PLAN -- Aggregate (cost=913498.60..913498.60 rows=1 width=0) (actual time=48387.91..48387.91 rows=1 loops=1) -> Index Scan using cdr_ix3 on cdr (cost=0.00..913453.49 rows=18041 width=0) (actual time=48387.89..48387.89 rows=0 loops=1) Index Cond: (cust_id = 99201110) Filter: (bill_id IS NULL) Total runtime: 48388.47 msec (5 rows) The computed cost of using the index was a factor of 10 higher which I presume is why the query planner wasn't using the index, but it ran in half the time. So I guess I need to know how to alter the statistics collection so that the index will get used. I gather that index columns that occur in more than "a few" percent of the table cause the query planner to not use the index. Does that mean I won't be able to get the query planner to ever use the cust_id index for that customer or can I tune some parameters to alter that? Any suggestions appreciated. Thanks, David David Witham Telephony Platforms Architect Unidial, Australia ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Indexes and statistics
"David Witham" <[EMAIL PROTECTED]> writes: > One of the customers is quite large (8.3% of the records): Hmm. Unless your rows are quite wide, a random sampling of 8.3% of the table would be expected to visit every page of the table, probably several times. So the planner's cost estimates do not seem out of line to me; an indexscan *should* be slow. The first question to ask is why the deviation from reality. Are the rows for that customer ID likely to be physically concentrated into a limited number of physical pages? Do you have so much RAM that the whole table got swapped in, eliminating the extra I/O that the planner is expecting? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] bytea or blobs?
On Tuesday 17 February 2004 18:08, you wrote: > I'd recommend to let the application convert the binary > data to and from base64, Don't, please don't ! Since you have the good bytea rule to convert so called "binary" data into so called "text". You have no need another encoding at all. Generally, the problem is to represent zero (0x00) when input/output. Any other byte might be stored, dumped, input, output without any problem. Then why to avoid 8bit chars ? Bytea notation rule completely resolve the problem of zeroes. (and also apostrophes :-) naturally) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Indexes and statistics
Tom,
I'm running PostgreSQL 7.3.2 on Red Hat Linux 7.3 with 512Mb RAM.
The table definition is:
Table "public.cdr"
Column | Type | Modifiers
---+---+---
carrier_id| integer | not null
file_id | integer | not null
service_num | character varying(10) | not null
day | date | not null
time | integer | not null
destination | character varying(20) | not null
duration | integer | not null
charge_wholesale | numeric(8,2) | not null
charge_band_id| integer |
charge_retail | numeric(8,2) | not null
rate_plan_id | integer | not null
item_code | integer | not null
cust_id | integer | not null
bill_id | integer |
prefix| character varying(12) |
charge_wholesale_calc | numeric(8,2) |
Indexes: cdr_ix1 btree ("day"),
cdr_ix2 btree (service_num),
cdr_ix3 btree (cust_id),
cdr_ix4 btree (bill_id),
cdr_ix5 btree (carrier_id),
cdr_ix6 btree (file_id)
Does this make it a "wide" table?
The data arrives ordered by service_num, day, time. This customer has one primary
service_num that most of the calls are made from. Therefore each day a clump of CDRs
will be loaded for that customer, interspersed with CDRs from all the other customers.
Therefore the distribution of records for a service_num is clumpy but evenly
distributed throughout the table. For a customer with a single primary number, this
result applies to the customer as a whole. For a customer with many service_num's the
result is a little more doubtful depending on whether their service_num's arrive
sequentially or not. This would not necessarily be the case.
I hope this makes sense. Does it help any?
Thanks,
David
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 18 February 2004 16:10
To: David Witham
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Indexes and statistics
"David Witham" <[EMAIL PROTECTED]> writes:
> One of the customers is quite large (8.3% of the records):
Hmm. Unless your rows are quite wide, a random sampling of 8.3% of the
table would be expected to visit every page of the table, probably
several times. So the planner's cost estimates do not seem out of line
to me; an indexscan *should* be slow. The first question to ask is why
the deviation from reality. Are the rows for that customer ID likely to
be physically concentrated into a limited number of physical pages?
Do you have so much RAM that the whole table got swapped in, eliminating
the extra I/O that the planner is expecting?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] bytea or blobs?
Generally this is the task of various drivers.
The postgresql jdbc for instance makes this task
easy with the ResultSet.getBytes() method.
The servlet code to display the contents of any mimetype
bytea column looks like:
PreparedStatement st =
con.prepareStatement("select mimetype,image from images where id=?");
st.setInt(1,id);
ResultSet rs = st.executeQuery();
if (!rs.next()) throw new Exception("Error Getting this image. Contact IT DEPT.");
String mimetype = rs.getString(1);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
byte[] img = rs.getBytes(2);
baos.write(img);
res.setContentType(mimetype);
baos.writeTo(out);
out.flush();
out.close();
Besides backups, a reason for sticking with bytea fields
is the ability to even replicate an image column across
databases in the usual manner that holds for the rest
of the datatypes, but then again these decisions are
dominated by the special conditions of each case.
Maybe if bandwidth is a restriction the base64 solution
saves some bandwith, since base64 file is ~ 1.3 times larger
than the original, whereas the escaped octal representation
will be ~ 4 times larger.
O kyrios Dana Hudes egrapse stis Feb 18, 2004 :
> The documentation indicates that bytea will -store- binary data
> The problem is getting the data into the column through SQL.
> The Postgresql SQL is character data only (side note: what about unicode?
> is this USASCII only?). You cannot just wrap your binary stream in a pair
> of quotes and off you go. You must encode before saving and decode after
> retrieving. The encoded form is stored in the column.
>
> What I have not figured out is how to pass my data to encode.
> This is a builtin function but it doesn't take a filename so how can
> you use it!
> At least with base64 I have ample libraries and can convert my data
> before sending to sql or after receiving from sql. It becomes my
> application's issue. Mind, this bloats the data considerably.
> escape is less bloat but I have to recreate the encode/decode in my app,
> so far as I see.
>
>
> On Wed, 18
> Feb 2004, sad wrote:
>
> > On Tuesday 17 February 2004 18:08, you wrote:
> >
> > > I'd recommend to let the application convert the binary
> > > data to and from base64,
> >
> > Don't, please don't !
> >
> > Since you have the good bytea rule to convert so called "binary" data into so
> > called "text". You have no need another encoding at all.
> >
> > Generally, the problem is to represent zero (0x00) when input/output. Any
> > other byte might be stored, dumped, input, output without any problem. Then
> > why to avoid 8bit chars ?
> >
> > Bytea notation rule completely resolve the problem of zeroes.
> > (and also apostrophes :-) naturally)
> >
> >
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> > joining column's datatypes do not match
> >
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
--
-Achilleus
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Disabling constraints
Dear friends, I am working opn Postgres 7.3.4 on RH Linux 7.2. I wanted to disable constraints. Alter table 'table name' disable constraint 'constraint name'; doesn't work. I got some information from google, which says about indirect way of disabling and enabling a constraint, as follows. update pg_class set reltriggers=0 where relname = 'crm.activities';update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname='crm.activities'; Also doesnt work. Is there a way to do it? Thanks Kumar
