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? Depends on the precise details of your situation. Check the mailing-list archives (this list and general would be a good start) for plenty of discussion on these. -- Richard Huxton Archonet Ltd ---(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] ORDER BY TIMESTAMP_column ASC, NULL first
Hi Rod,
Try this
ace=> create table test(name text, age int );
CREATE
ace=> insert into test values ('Denis',26);
INSERT 1823531 1
ace=> insert into test values (null,26);
INSERT 1823532 1
ace=> select * from test order by name;
name | age
---+-
Denis | 26
| 26
(2 rows)
ace=> select * from test order by coalesce(name,'');
name | age
---+-
| 26
Denis | 26
HTH
Thanx
Denis
- Original Message -
From: "Rod Taylor" <[EMAIL PROTECTED]>
To: "Fredrik Wendt" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, February 16, 2004 12:32 AM
Subject: Re: [SQL] ORDER BY TIMESTAMP_column ASC, NULL first
> On Thu, 2004-02-12 at 05:06, Fredrik Wendt wrote:
> > Hi!
> >
> > I read posts telling me that NULL values are considered greater than
> > non-null values. Fine. Is there a way to explicitly reverse this?
>
> ORDER BY column IS NOT NULL, column ASC;
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Reference with condition on other table column?
Hi to all, I have the following tables: CREATE TABLE t1(id serial PRIMARY KEY,active boolean NOT NULL DEFAULT 'y',num int4 NOT NULL,);CREATE UNIQUE INDEX t1_uniqueidx ON t1(num) WHERE active; CREATE TABLE t2(id serial PRIMARY KEY,active boolean NOT NULL DEFAULT 'y',num int4 NOT NULL,);CREATE UNIQUE INDEX t2_uniqueidx ON t2(num) WHERE active; CREATE TABLE relations(id serial PRIMARY KEY,id_t1 int4 NOT NULL REFERENCES t1(num),id_t2 int4 NOT NULL REFERENCES t2(num)); On tables T1 and T2 the "num" columns have unique values for all lines that have active='y'(true).How can I write a constraint on Table T1 and Table T2 that if the "num" from T1 and "num" from T2 are referenced from table "relation" than I cannot update the "active" field to "false". My target is that I don't want to have any reference from "relation" table to T1 and T2 where in the T1 and T2 the active field is "n"(false) or with other words:if a line from T1/T2 is referenced from table "relations" than I don't want to be able to put active='y'.I hope I was so clear as possible. Thnkx in advance for helping. Andy.
Re: [SQL] Date format problems
Tom Lane mentioned : => Check the manual about runtime => configuration settings and postmaster switches. I use this : export PGDATESTYLE= (=ISO,European) P.S. Is there something wrong with the date on the postgres mailing list machine? All my mail from the list arrives with a date of : 16 Feb 2004 and hides the bottom of my e-mail folder. Cheers Stef ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Date format problems
Mark Roberts wrote: Hi im using the function below to insert data into my db; im using now() to get the timestamptz, however when inserted in the db the format seems to vary, the majority of the time its in the required European style but does spontaniously change to various other type can anyone throw any light on this problem. Not sure what you mean here. Are you saying if you insert 5 timestamps in a row, the third comes out wrong, or that all from one client are wrong, or what? Further info: DATESTYLE is currently set to European. db table type is 'timestamptz' OK - first inspections seem OK. Are you sure no clients have the wrong datestyle set? ### CREATE FUNCTION newmess(int4, text, varchar) RETURNS varchar AS ' DECLARE userid ALIAS for $1; message ALIAS for $2; touser ALIAS for $3; enttime DATETIME; touserid INTEGER; rdset BIT; from VARCHAR; BEGIN rdset = 0; touserid=(select id from users where lastname=touser); enttime=(select now()); Easier to say: enttime := now(); Or, just to use now() in the query below and get rid of the variable altogether. from=(select lastname from users where id = userid); INSERT INTO CallLog.message(message, fromuser, touser, txtime, rd, fromusern) values(message. userid, touserid, enttime, rdset, from); END; ' LANGUAGE 'plpgsql'; Well, assuming this works at all (and I'm assuming you retyped it since there are some obvious syntax errors), it shouldn't be possible to insert the wrong timestamp. Whether European/American or Klingon format, now() is the current time at the server. Can you show a sample select where the timestamp is incorrect, but the ones either side are fine? I assume there is some serial message_id column that should act as a clue. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Reference with condition on other table column?
On Thu, 3 Jun 2004, Andrei Bintintan wrote: > Hi to all, > > I have the following tables: > CREATE TABLE t1( > id serial PRIMARY KEY, > active boolean NOT NULL DEFAULT 'y', > num int4 NOT NULL, > ); > CREATE UNIQUE INDEX t1_uniqueidx ON t1(num) WHERE active; > > CREATE TABLE t2( > id serial PRIMARY KEY, > active boolean NOT NULL DEFAULT 'y', > num int4 NOT NULL, > ); > CREATE UNIQUE INDEX t2_uniqueidx ON t2(num) WHERE active; > > > CREATE TABLE relations( > id serial PRIMARY KEY, > id_t1 int4 NOT NULL REFERENCES t1(num), > id_t2 int4 NOT NULL REFERENCES t2(num) > ); > > On tables T1 and T2 the "num" columns have unique values for all lines that > have active='y'(true). > > How can I write a constraint on Table T1 and Table T2 that if the "num" from > T1 and "num" from T2 are referenced from table "relation" than I cannot > update the "active" field to "false". My target is that I don't want to have > any reference from "relation" table to T1 and T2 where in the T1 and T2 the > active field is "n"(false) > > or with other words: > > if a line from T1/T2 is referenced from table "relations" than I don't want > to be able to put active='y'. Hmm, I can see is having two other tables that you reference that have rows containing num added/removed by triggers when T1 or T2 are changed, so that inserting an active='y' row inserts a row into the appropriate one, update a ='n' -> 'y' inserts a row, update 'y'->'n' removes a row and deleting a ='y' row removes a row. That might get messy though. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ORDER BY TIMESTAMP_column ASC, NULL first
On Thu, Feb 12, 2004 at 11:06:26 +0100, Fredrik Wendt <[EMAIL PROTECTED]> wrote: > > The query to use would be, > > SELECT ip.id FROM IPv4Address ip ORDER BY assignedAt ASC LIMIT 1; > > with the exception that this returns rows with NULL at the end, instead > of at the beginning which is what I'd like. > > How do achieve this with one query? I'm using Postgres 7.4. SELECT ip.id FROM IPv4Address ip ORDER BY assignedAt IS NULL, assignedAt ASC LIMIT 1; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Selecting "sample" data from large tables.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I have a table with a decent number of rows (let's say for example a
billion rows). I am trying to construct a graph that displays the
distribution of that data. However, I don't want to read in the
complete data set (as reading a billion rows would take a while). Can
anyone thing of a way to do this is postgresql? I've been looking
online and most of the stuff I've found has been for other databases.
As far as I can tell ANSI SQL doesn't provide for this scenario.
I could potentially write a function to do this, however I'd prefer
not to. But if that's what I'm going to be stuck doing I'd like to
know earlier then later. Here's the description of the table:
create table score
{
pageId Integer NOT NULL,
ruleId, Integer NOT NULL
score Double precision NULL,
rowAddedDate BigInt NULL,
primary key (pageId, ruleId)
};
I also have an index on row added date, which is just the number of
millis since the epoc (Jan 1, 1970 or so [java style timestamps]).
I'd be willing to accept that the row added date values are random
enough to represent random.
Thanks in advance,
-- Joe T.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)
iD8DBQFAv2Bqs/P36Z9SDAARAkmLAJ9dDB0sqACgFrxH8NukFUsizXz5zgCgt9IT
/wh3ryz4WQzc5qQY2cAZtVE=
=5dg+
-END PGP SIGNATURE-
---(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] Reference with condition on other table column?
On Thu, Jun 03, 2004 at 12:16:43 +0300, Andrei Bintintan <[EMAIL PROTECTED]> wrote: > > How can I write a constraint on Table T1 and Table T2 that if the "num" from > T1 and "num" from T2 are referenced from table "relation" than I cannot > update the "active" field to "false". My target is that I don't want to have > any reference from "relation" table to T1 and T2 where in the T1 and T2 the > active field is "n"(false) I think something like the following will work: CREATE TABLE t1( id serial PRIMARY KEY, active boolean NOT NULL DEFAULT TRUE, num int4 NOT NULL, unique (id, active) ); CREATE UNIQUE INDEX t1_uniqueidx ON t1(num) WHERE active; CREATE TABLE t2( id serial PRIMARY KEY, active boolean NOT NULL DEFAULT TRUE, num int4 NOT NULL, unique (id, active) ); CREATE UNIQUE INDEX t2_uniqueidx ON t2(num) WHERE active; CREATE TABLE relations( id serial PRIMARY KEY, id_t1 int4 NOT NULL, active_t1 boolean NOT NULL DEFAULT TRUE constraint t1_true check(active_t1), id_t2 int4 NOT NULL, active_t2 boolean NOT NULL DEFAULT TRUE constraint t2_true check(active_t2), foreign key (id_t1, active_t1) references t1 (id, active), foreign key (id_t2, active_t2) references t2 (id, active) ); ---(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] Ok, what am I doing wrong here?
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. > > 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? INSERT INTO table (id, time, type) SELECT id, now(), '1' FROM secondtable; Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Selecting "sample" data from large tables.
Joseph Turner <[EMAIL PROTECTED]> writes: > I have a table with a decent number of rows (let's say for example a > billion rows). I am trying to construct a graph that displays the > distribution of that data. However, I don't want to read in the > complete data set (as reading a billion rows would take a while). Can > anyone thing of a way to do this is postgresql? There is a fairly decent random-sampling engine inside ANALYZE, but no way for the user to get at it :-(. Can you make any use of ANALYZE's results, viz the pg_stats view? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] need help with os x objects into a db
Does anyone here have experience with OS X and storing NSData objects in a postgresql db? I have archived data that I want to store in a bytea column. I just can not get it to work. I hope there is a kind soul that can help Ted __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
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 > > But I don't know how to do it in Postgresql. select functioname(arg1,arg2,arg3); ---(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
Apparently the ::char is cast to varchar and then text? That explains x || ' ' || x On Tue, Feb 17, 2004 at 05:07:24PM -0700, scott.marlowe wrote: > 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 8: explain analyze is your friend
Re: [SQL] Selecting "sample" data from large tables.
> Joseph Turner <[EMAIL PROTECTED]> writes: > > I have a table with a decent number of rows (let's say for example a > > billion rows). I am trying to construct a graph that displays the > > distribution of that data. However, I don't want to read in the > > complete data set (as reading a billion rows would take a while). Can > > anyone thing of a way to do this is postgresql? One way would be to have an indexed column with random values in it. Then you could use an index scan to pull out samples. However this has a few downsides. a) index scans are a lot less efficient than sequential scans. Effectively reducing the sample size you can get for a given amount of time even further. a 10% sample using this technique is probably almost as slow as reading the entire table, for example. If you only need .1% though this might be a good approach. b) the data in the random column would have to be static meaning multiple samples wouldn't be entirely independent. Depending on what you're doing with the samples this might be a problem or not. Oh, and if you're content with always using the same sample but want to analyze it multiple different ways, you might want to use a partial index. You could have partial indexes matching the order by and where clauses of your analyses but with a where clause of its own selecting on the random data. Then make sure that where clause is in every query. But still, the sample has to be small enough that even using an index scan you're winning over reading the entire data. -- greg ---(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
