[SQL] column type for pdf file
Hello, To save pdf files into postgresql8.3, what is the best column type? bytea, blob, etc? Thank you, Emi -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] foreign keys and lots of tables
I have the following tables (individual seat allocation removed to make it simpler) create table coaches ( -- carriages c_id serial primary key, c_namevarchar(20) not null ); create table trains ( -- one for each train t_id serial primary key ); create table train_coaches ( -- which carriages are on what trains t_id int4 not null references trains(t_id), c_id int4 not null references coaches(c_id) ); I now want to create bookings and allocate seats, but the seat must exist on the coach_seats table *AND* only for a carriage included in the train, i.e. an entry in train_coaches. create table bookings ( b_id serial primary key, t_id int4 not null references trains(t_id) ); create table booking_seats ( b_id int4 not null references bookings(b_id), c_id int4, -- carriage ID c_seatvarchar(10) -- seat label ); The following ensures the seat exists on the coach. (not shown) alter table booking_seats add constraint seat_exists foreign key (c_id, c_seat) references coach_seats (c_id,c_seat); How would I ensure that the coach exists on the train. I would need to convert the b_id to a t_id using the bookings table and I don't know how. To complicate things, when the initial booking is made, bot c_id and c_seat are NULL. Will this make any difference? Gary -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] client-side lo_import() provided by libpq ?
Hello,
Postgresql8.3, tried:
create table test(id, image oid);
insert into test values(1, lo_import('apple.jpg'));
ERROR: must be superuser to use server-side lo_import()
HINT: Anyone can use the client-side lo_import() provided by libpq.
About client-side lo_import(), is there an online doc about install
lo_import?
Thank you,
Emi
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
Unless you've good reasons to do so it's best to store the file on the file system and the file name/path in the database ... On 05/18/2011 22:20, Emi Lu wrote: Hello, To save pdf files into postgresql8.3, what is the best column type? bytea, blob, etc? Thank you, Emi <> -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
On Wed, May 18, 2011 at 10:46:23PM +0200, Julien Cigar wrote: > Unless you've good reasons to do so it's best to store the file on > the file system Why ? If you suggest reasons are needed for storing the PDF in the database I'd like to know the reasons for *not* doing so. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
On 05/18/2011 23:00, Karsten Hilbert wrote: On Wed, May 18, 2011 at 10:46:23PM +0200, Julien Cigar wrote: Unless you've good reasons to do so it's best to store the file on the file system Why ? If you suggest reasons are needed for storing the PDF in the database I'd like to know the reasons for *not* doing so. It increases the load, consume connections, but the biggest drawback is probably the memory consumption .. IMHO storing binary data in a database is almost always a bad idea .. it could be OK to store things like avatars, small icons, etc, but certainly not to store files of several MB ... file systems are done for that ! Karsten <> -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
On Wed, May 18, 2011 at 11:21:43PM +0200, Julien Cigar wrote: > >>Unless you've good reasons to do so it's best to store the file on > >>the file system > >Why ? > > > >If you suggest reasons are needed for storing the PDF in the > >database I'd like to know the reasons for *not* doing so. > > It increases the load, consume connections, but the biggest drawback > is probably the memory consumption .. Thanks. At least now the OP knows some of the reasoning for not doing so :-) Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
On 05/18/2011 23:27, Karsten Hilbert wrote: On Wed, May 18, 2011 at 11:21:43PM +0200, Julien Cigar wrote: Unless you've good reasons to do so it's best to store the file on the file system Why ? If you suggest reasons are needed for storing the PDF in the database I'd like to know the reasons for *not* doing so. It increases the load, consume connections, but the biggest drawback is probably the memory consumption .. Thanks. At least now the OP knows some of the reasoning for not doing so :-) Yep sorry, I answered a bit too fast :) Karsten <> -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Sorting Issue
Is there anywhere that gives you all the available collations and their definitions? I found with the C collation it now sorts the spaces correct but it is also case sensitive which messes with some of our other sorts. -Original Message- From: Tom Lane [mailto:[email protected]] Sent: Tuesday, May 10, 2011 9:47 AM To: Ozer, Pam Cc: Samuel Gendler; [email protected]; [email protected] Subject: Re: [SQL] Sorting Issue "Ozer, Pam" writes: > Isn't this the English standard for collation? Or is this a non-c > locale as mentioned below? Is there anyway around this? >LC_COLLATE = 'en_US.utf8' en_US is probably using somebody's idea of "dictionary order", which I believe includes ignoring spaces in the first pass. You might be happier using "C" collation. Unfortunately that requires re-initdb'ing your database (as of existing PG releases). regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
On Wed, May 18, 2011 at 2:20 PM, Emi Lu wrote: > Hello, > > To save pdf files into postgresql8.3, what is the best column type? > > bytea, blob, etc? > > Thank you, > Emi > Everyone else has pointed out reasons for not doing this, and I agree with them that in the large majority of cases just storing a reference to a file stored outside the database is preferable. However, to answer the question you asked, my rule of thumb is that if you need to store binary data in the database is to use a bytea column, unless you need the random access capabilities that the large object interface provides. A bytea column is typically easier to use, and has proper transactional behavior, enforcement of referential integrity, etc. -Eric
Re: [SQL] foreign keys and lots of tables
On Wed, 18 May 2011 20:10:19 +0100, Gary Stainburn wrote about [SQL] foreign keys and lots of tables: >I have the following tables (individual seat allocation removed to >make it simpler) Omitting details makes the problem more difficult to comprehend. [snip] >create table booking_seats ( > b_id int4 not null references bookings(b_id), > c_id int4, -- carriage ID > c_seat varchar(10) -- seat label >); > >The following ensures the seat exists on the coach. (not shown) > >alter table booking_seats add constraint seat_exists >foreign key (c_id, c_seat) references coach_seats (c_id,c_seat); > >How would I ensure that the coach exists on the train. I would need to >convert the b_id to a t_id using the bookings table and I don't know >how. I think you will need to write a trigger procedure with something like the following query inside it: IF NOT EXISTS(SELECT * FROM train_coaches AS tc INNER JOIN bookings AS b ON b.t_id = tc.t_id WHERE b.b_id = NEW.b_id AND tc.c_id = NEW.c_id)) THEN -- Something is wrong. You cannot use a subquery in a CHECK constraint, so I think a trigger will be the go. >To complicate things, when the initial booking is made, bot c_id and >c_seat are NULL. Will this make any difference? If c_id is NULL you cannot check anything against it, so your data integrity has just gone for a Burton. -- Regards, Dave [RLU #314465] *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* [email protected] (David W Noon) *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* signature.asc Description: PGP signature
Re: [SQL] client-side lo_import() provided by libpq ?
On 05/19/2011 04:51 AM, Emi Lu wrote: About client-side lo_import(), is there an online doc about install lo_import? It's in the manual. http://www.postgresql.org/docs/current/static/lo-examplesect.html -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
On 05/19/2011 05:21 AM, Julien Cigar wrote: On 05/18/2011 23:00, Karsten Hilbert wrote: On Wed, May 18, 2011 at 10:46:23PM +0200, Julien Cigar wrote: Unless you've good reasons to do so it's best to store the file on the file system Why ? If you suggest reasons are needed for storing the PDF in the database I'd like to know the reasons for *not* doing so. It increases the load, consume connections, but the biggest drawback is probably the memory consumption .. IMHO storing binary data in a database is almost always a bad idea .. it could be OK to store things like avatars, small icons, etc, but certainly not to store files of several MB ... file systems are done for that ! _however_, you lose transactional properties when doing this. You can land up with a file that's been added/updated where the associated transaction in the DB failed or rolled back. Very careful application programming and the use of 2 phase commit can provide reliable behaviour, but it's not trivial. This is one area where I envy Microsoft. As they control the file system and the database, they've been able to come up with a really cool system where the file system integrates into database transactions, so you kind of get the best of both worlds. Very cool. If reiser4 hadn't gone the way of the dodo such a thing might've become possible on Linux, but I'm not aware of any other Linux file systems that safely support transactions. -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
Right! The external binary file storage has another advantage (that may be considered as disadvantage as well) - it is usually easier to develop and test mechanism as you're able to browse uploaded file result using os / application or even "exchange" file contents for test purposes, while in case of iternal storage you need some extra code to do it for you and you newer know, if it works well. Another problem is caching / feeding files in case of heavy load condition - caching of huge objects is problematic while you never know the dimension of the problem, non-caching approach may cause bottlenecks on frequent database reads. I was considering both scenarios and finally've choosen external storage for our invoice generation system. Regards, Piotr -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
