Re: [SQL] Table design question

On Thu, 01 Jun 2006, Chris Browne wrote: > Celko is decidedly *NOT* promoting the notion that you should use a > 100 byte long "natural key." > > Jamie's comments of "Orthodox versus Reform" seem reasonably > appropriate in outlining something of the difference between the > positions. Just to

Re: [SQL] Table design question

On Thu, 01 Jun 2006, David Clarke wrote: > So I'm designing a table and I'm looking for an appropriate key. The > natural key is a string from a few characters up to a maximum of > perhaps 100. Joe gets quite fierce about avoiding the use of a serial > id column as a key. The string is unique in t

Re: [SQL] [ADMIN] plpgsql question

I have data in one table called articles and I would like to make a function in which takes certain data from it and display the results. Example: CREATE TABLE articles ( article_id serial, title varchar(200), posted timestamp, article_subject varchar(200), article_body text, allow_comments boole

[SQL] plpgsql question

Can I do something like this: CREATE TABLE sample (id SERIAL, node INTEGER, parent INTEGER); INSERT INTO sample(node,parent) VALUES(1,0); INSERT INTO sample(node,parent) VALUES(2,0); INSERT INTO sample(node,parent) VALUES(3,1); INSERT INTO sample(node,parent) VALUES(4,3) CREATE OR REPLACE FUNCTIO

[SQL] Data insert

Sorry for the attachment, but copying and pasting this data does not work. I don't have any idea how to insert the type of data into PostgreSQL. Basically, it's encrypted data in which I would like that keep raw format. Thanks for any help, J sample_data.pdf Description: Adobe PDF document --

Re: [SQL] PostgreSQL and Delphi 6

So you installed psqlodbc 8 on the client machine with Delphi installed, correct? What problems did you have with cursors? Any other suggestions? Thanks a lot for the help! J Din Adrian wrote: > we are using postgresql8 +psqlodbc8+ delphi7 ... the only problem is > the server side cursor = doe

[SQL] PostgreSQL and Delphi 6

I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J

Re: [SQL] Aggregate Functions Template

Hi, Sqlers - I just installed 8.0.3, and am getting up to speed with the new features. First on my list is Tablespaces. In the docs, I see this: Note: There is usually not much point in making more than one tablespace per logical file system, since you cannot control the

Re: [SQL] ERROR: unterminated quoted string... help

Scott Marlowe wrote: Use a bytea field and use pg_escape_bytea() to prepare the data for insertion. Thanks Scott, I will try it now. J ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-noma

[SQL] ERROR: unterminated quoted string... help

Hi I'm trying to insert encrypted data into the database and I'm noticing error dealing with quotes. Below is the error print out... suggestions and/or at least point me in the direction to find a solution, Thanks, J INSERT INTO sample.users (user_name, first_name) VALUES ('jokers', '=ïµiF!¶6

[SQL] Which type of functions are best?

Hi, I'm just starting out and am looking to speed up queries using either SQL functions or PLPGSQL functions. I have googled around and have not found a great answer saying that this is the way to go. I would like to use PREPARE/EXECUTE... but of course they only last for each connection, I w

[SQL] varray? table of varchar?

Hi, I posted asking about varray a few days ago. Also I've run into an Oracle type that is defined as "table of varchar2(4000) index by binary_integer". Has anyone encountered either of these before? I'm not exactly sure what they do as I'm not an Oracle expert. So it is hard for me to see wha

[SQL] oracle varray functionality?

I've run across a custom type in an oracle database that I am porting to PostGreSQL: create or replace type number_varray as varray(1000) of number; Is the int4array example the same as this? create type int4array(input=int4array_in,output=int4array_out, internallength=variable,element=int4)

[SQL] Problems with tsearch2: ERROR: datumGetSize: Invalid typLen 0

Hi all - I'm playing with tsearch2. It built and installed normally (this is PG 7.3.2 on an Alpha running Debian Stable, with the December 18th tsearch2), and portions of it work, but, for instance ts_tsvector doesn't: jal=# select to_tsvector('default', 'Our first string used today first str

Re: [SQL] update more than 1 table (mysql to postgres)

On Fri, 30 Jan 2004, treeml wrote: > I am migrating from MySQL to Postagres. I have problem with postgres > updating 2 tables with one statement. > > In MySQL I can update 2 tables (parent, child) with a statement like this > > UPDATE parent LEFT JOIN child ON parent.pid = chi

Re: [SQL] switching default integer datatype to int8 and "IN (...)"

Thanks Andy - this "solves" the problem(*1) on the first level, where I know how to quote the params, so they must be evaluated / casted. But when I get into the subselects, I don't know how to make postgres to cast / evaluate the results of the subselect, so it again does

[SQL] switching default integer datatype to int8 and "IN (...)" clause

Hi, Short: postrgesql-7.2.3 doesn't figure to convert int4 to int8 implicitly / by context (as in a query). How do I help it ? Long: I have index on a table, a select like this takes a split second: # select * from file where id = fileIDseq.last_value-1; id | name -+

[SQL] Using Transaction Blocks w/ SELECT

Goinging throught the libpq docs, I noticed that in all of the examples involving select statements, transaction blocks are used. I see why this is necessary for write operations, but I don't see the need in read operations that don't commit. Am I missing something? Any help appreciated.