[SQL] Date/Time types

[Seb]

Hello,

I'm french, so excuse me if my english is not correct.

I'd like to create a table with a Timestamp row (named date for example) and
width a resolution as smaller as possible (1 microsecond if possible).

What is exactly the correct query for that question ?

The query I've written is :
CREATE TABLE "table" (
"id" int4 NOT NULL,
"date" timestamp [13] NOT NULL )


If this correct, I've a second question : how can I insert a value ??? I've
tried many ways but any works ! I'm lost
I've tried to cast, to use CURRENT_TIMESTAMP with a precision, but nothing
works...


Thanks for your help

Seb



---(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

[SQL] updateable/insertable view having left joined tables

[Seb]

Hi,

I'm taking a first foray into writing rules, and am struggling with one
for a view that has a left joined table:

---<cut here---start--->---
CREATE TABLE shoes (
sh_id serial PRIMARY KEY,
sh_name text,
sh_avail integer
);

CREATE TABLE shoelaces (
sl_id serial PRIMARY KEY,
sh_id integer REFERENCES shoes,
sl_name text
);

INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh1', 2);
INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh2', 0);
INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh3', 4);
INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh4', 3);

INSERT INTO shoelaces (sh_id, sl_name) VALUES (1, 'sl1');
INSERT INTO shoelaces (sh_id, sl_name) VALUES (3, 'sl2');

SELECT * FROM shoes;
 sh_id | sh_name | sh_avail 
---+-+--
 1 | sh1 |2
 2 | sh2 |0
 3 | sh3 |4
 4 | sh4 |3

SELECT * FROM shoelaces;
 sl_id | sh_id | sl_name 
---+---+-
 1 | 1 | sl1
 2 | 3 | sl2

-- We create a view that could be used to easily insert data into
-- shoelaces table:

CREATE VIEW shoe AS
SELECT sh.sh_id, sh_name, sh_avail, sl_name
FROM shoes sh LEFT JOIN shoelaces sl USING (sh_id);

SELECT * FROM shoe;
 sh_id | sh_name | sh_avail | sl_name 
---+-+--+-
 1 | sh1 |2 | sl1
 2 | sh2 |0 | 
 3 | sh3 |4 | sl2
 4 | sh4 |3 | 
---<cut here---end->---

Say I want to update this view like:

UPDATE shoe SET sl_name = 'sl3' WHERE sh_id = 2;

The right (well, to me) thing to do would be to insert a row in
shoelaces like this:

INSERT INTO shoelaces (sh_id, sl_name) VALUES (2, 'sl3');

Of course, if the update involves a row that is already available from
shoelaces, it would be an update on at least one of the tables, rather
than an insert on shoelaces.  I'm not sure how a rule to do this would
look like, so any pointers would be appreciated.  Thanks.


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] updateable/insertable view having left joined tables

[Seb]

The following seems to work, but I don't feel confident this is really
the correct code:

CREATE RULE shoe_upd AS
ON UPDATE TO shoe
DO INSTEAD (
UPDATE shoes
SET sh_name = NEW.sh_name, sh_avail = NEW.sh_avail
WHERE shoes.sh_id = NEW.sh_id;
INSERT INTO shoelaces (sh_id, sl_name)
SELECT New.sh_id, New.sl_name WHERE New.sl_name IS NOT NULL;);

I could find out doing a number of tests, but it would be good to
understand what is going on.

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Fwd: conditional rule not applied

[Seb]

Hi,

Apologies for posting this from postgresql.general, but this failed to
get any follow-ups in that NG.  Hopefully someone here can shed some
light on this.



--- Begin Message ---
Topics:
   conditional rule not applied
   Re: conditional rule not applied
   Re: conditional rule not applied
   Re: conditional rule not applied
   Re: conditional rule not applied
--- End Message ---
--- Begin Message ---
Hi,

I'm trying to create a rule to be applied on update to a view that
consists of two joined tables.  Table 'shoes' below is left-joined with
table 'shoelaces' in the view 'footwear'.  I'd like to create a simple
update rule on the view, only if the value of a common column
corresponds to an inexistent record in 'shoelaces', so the result is an
INSERT into 'shoelaces' with the new record:

---<cut here---start--->---
CREATE TABLE shoes (
sh_id serial PRIMARY KEY,
sh_name text,
sh_avail integer
);

CREATE TABLE shoelaces (
sl_id serial PRIMARY KEY,
sh_id integer REFERENCES shoes,
sl_name text
);

INSERT INTO shoes (sh_name, sh_avail)
VALUES ('sh1', 2), ('sh2', 0), ('sh3', 4), ('sh4', 3);

INSERT INTO shoelaces (sh_id, sl_name)
VALUES (1, 'sl1'), (3, 'sl2');

SELECT * FROM shoes;

 sh_id | sh_name | sh_avail 
---+-+--
 1 | sh1 |2
 2 | sh2 |0
 3 | sh3 |4
 4 | sh4 |3

SELECT * FROM shoelaces;

 sl_id | sh_id | sl_name 
---+---+-
 1 | 1 | sl1
 2 | 3 | sl2
(2 rows)

CREATE VIEW footwear AS
SELECT sh.sh_id, sh_name, sh_avail, sl_name
FROM shoes sh LEFT JOIN shoelaces sl USING (sh_id);

SELECT * FROM footwear;

 sh_id | sh_name | sh_avail | sl_name 
---+-+--+-
 1 | sh1 |2 | sl1
 2 | sh2 |0 | 
 3 | sh3 |4 | sl2
 4 | sh4 |3 | 
(4 rows)

CREATE RULE footwear_nothing_upd AS
ON UPDATE TO footwear DO INSTEAD NOTHING;
CREATE RULE footwear_newshoelaces_upd AS
ON UPDATE TO footwear
WHERE NEW.sl_name <> OLD.sl_name AND OLD.sl_name IS NULL
DO
INSERT INTO shoelaces (sh_id, sl_name)
VALUES(NEW.sh_id, NEW.sl_name);

-- Testing: result should be a new record in 'shoelaces'
UPDATE footwear SET sl_name = 'sl3' WHERE sh_name = 'sh2';

-- but that doesn't happen:
SELECT * FROM shoelaces;

 sl_id | sh_id | sl_name 
---+---+-
     1 | 1 | sl1
 2 | 3 | sl2
(2 rows)
---<cut here---end->---

Any tips would be much appreciated.

-- 
Seb


-- 
Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--- End Message ---
--- Begin Message ---
On Wed, 30 Dec 2009 19:39:15 -0600,
Seb  wrote:

> CREATE RULE footwear_nothing_upd AS
> ON UPDATE TO footwear DO INSTEAD NOTHING;
> CREATE RULE footwear_newshoelaces_upd AS
> ON UPDATE TO footwear
> WHERE NEW.sl_name <> OLD.sl_name AND OLD.sl_name IS NULL
> DO
> INSERT INTO shoelaces (sh_id, sl_name)
> VALUES(NEW.sh_id, NEW.sl_name);

I think my error is in the test expression, which doesn't deal properly
with the null value, so correcting:

CREATE RULE footwear_nothing_upd AS
ON UPDATE TO footwear DO INSTEAD NOTHING;
CREATE RULE footwear_newshoelaces_upd AS
ON UPDATE TO footwear
WHERE NEW.sl_name IS DISTINCT FROM OLD.sl_name AND OLD.sl_name IS NULL
DO
INSERT INTO shoelaces (sh_id, sl_name)
VALUES(NEW.sh_id, NEW.sl_name);

However, could a more direct and robust test for an inexistent record in
'shoelaces' be made?


-- 
Seb


-- 
Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--- End Message ---
--- Begin Message ---
On Wed, 30 Dec 2009 20:04:51 -0600,
Seb  wrote:

> On Wed, 30 Dec 2009 19:39:15 -0600,
> Seb  wrote:

> CREATE RULE footwear_nothing_upd AS
>> ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE
>> footwear_newshoelaces_upd AS ON UPDATE TO footwear WHERE NEW.sl_name
>> <> OLD.sl_name AND OLD.sl_name IS NULL DO INSERT INTO shoelaces
>> (sh_id, sl_name) VALUES(NEW.sh_id, NEW.sl_name);

> I think my error is in the test expression, which doesn't deal
> properly with the null value, so correcting:

> CREATE RULE footwear_nothing_upd AS
> ON UPDATE TO footwear DO INSTEAD NOTHING;
> CREATE RULE footwear_newshoelaces_upd AS
> ON UPDATE TO footwear
> WHERE NEW.sl_name IS DISTINCT FROM OLD.sl_name AND OLD.sl_name IS NULL
> DO
> INSERT 

Re: [SQL] Fwd: conditional rule not applied

[Seb]

On Wed, 6 Jan 2010 13:01:02 -0800,
Richard Broersma  wrote:

> On Wed, Jan 6, 2010 at 12:40 PM, Seb  wrote:
>> I'm trying to create a rule to be applied on update to a view that
>> consists of two joined tables.  Table 'shoes' below is left-joined
>> with table 'shoelaces' in the view 'footwear'.  I'd like to create a
>> simple update rule on the view, only if the value of a common column
>> corresponds to an inexistent record in 'shoelaces', so the result is
>> an INSERT into 'shoelaces' with the new record:

> A couple of year's ago, I was seriously looking into update-able
> views.  But from my experience, I'm sorry to say you not going to find
> a robust solution to this problem.  There are at least three problems
> with joined table update-able views: 1) You can only issue
> insert-update-delete statements that will only affect one row.  2) You
> cannot serialize the update of a view's virtual row like you can with
> a table's row.  This allow leave the possibility of concurrent update
> anomalies.  3) Application frameworks that use optimistic locking or
> use the updated row count for validation will complain (and
> automatically roll-back your work) when you attempt to perform an
> update.

> The official use for update-able views is for limiting the results
> from a *single* base table.

> Having said all of this, it is possible to do what your describing.
> I've seen Keith Larson make update-able views from a composite of
> selected UNION and FULL OUT JOIN queries.  But his solution was
> extremely hackish.

Thank you, Richard.  So IIUC, this may not be problematic in my
particular case of a single user database, where I have some control
over concurrent operations, i.e. the possibility of those anomalies is
minimal (or at least is under my control to a large extent).  WRT item
(1), in the example I showed (with the last rule), the following update
appears to work correctly:

UPDATE footwear SET sl_name='sl3' WHERE sh_name='sh2' OR sh_name='sh4';

where 2 tuples are inserted into shoelaces, as expected.  Maybe you're
referring to views with other types of joined tables?  Do you think the
NOT EXISTS statement in my last rule makes sense in the context of what
I described?  I'm not sure I'm following the docs on the rule system
properly on how the NEW and OLD relations should be used, especially the
apparent contradiction in the "condition" parameter.

At any rate, I'm thankful for the warning about the limitations of
updteable views.


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Fwd: conditional rule not applied

[Seb]

On Thu, 07 Jan 2010 16:31:29 +0100,
Leo Mannhart  wrote:

[...]

> I can give a 'first cut' solution.  But I strongly discourage from
> doing this in a real world application as chances are big, that you'll
> forget something to implement correctly (nullable fields come to mind
> immediately as an example).  Your example is also simplified as it
> makes no sense for instance, that sh_name is nullable...

> here we go:

[...]

Thank you very much for your time on this.  I can see that this is
indeed a difficult thing to implement.  I'll study your code carefully
to understand the issues a little better.


Cheers,

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] rename primary key

[Seb]

Hi,

Is it possible to rename a primary key?  I learnt that to rename foreign
keys one has to drop it and recreate with the new name, or add a new new
and drop the old one.  What is the approach for primary keys?  Thanks.


Cheers,

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] rename primary key

[Seb]

On Fri, 15 Jan 2010 12:34:15 +0900,
Ian Barwick  wrote:

[...]

> Is this what you mean?

[...]

Exactly!  Thanks.

On a related note: how come pgadmin3 shows "Indexes (0)" for such a
table, even though an index does exist for the primary key?  Are these
indexes created in a separate table that is looked up by the foo table?


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] indexes

[Seb]

Hi,

I have some views and queries that take a bit too long to return, so
perhaps some judicious indexes might help, but I don't know much about
how to use them.  The PostgreSQL manual has a good section on indexes,
but I can't find guidance on (unless I missed something):

o How to decide what columns need an index?
o Should all foreign keys have an index?
o Naming conventions?
o Does PostgreSQL use available indexes that can be useful in any query,
  without the user having to do anything in particular?

I'd appreciate any pointers to documents with guidance on these
questions.  Thanks.


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] rename primary key

[Seb]

On Fri, 15 Jan 2010 07:35:17 +0100,
Guillaume Lelarge  wrote:

[...]

> Primary keys are constraints. They are enforced with an index, but
> actually they are constraints. So we put them on the constraints
> nodes.

> There is the same behaviour for unique constraints.

Thanks.  Would there be any problem with listing the index used to
enforce the primary key constraint in the Indexes node to let us know of
its existence?  In fact, psql does report it with the meta-command \d.
It was somewhat confusing to see Indexes(0), and then having to rename
an index to rename a primary key constraint.


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] indexes

[Seb]

On Mon, 18 Jan 2010 08:59:56 +1100,
Chris  wrote:

>> o Should all foreign keys have an index?

> Not necessarily, you might just want the db to enforce the restriction
> but not actually use the data in it. For example, keep a userid (and
> timestamp) column of the last person to update a row. You may need it
> to say "aha - this was last changed on this date and by person X", but
> you'll never generally use it.

> If you never have a where clause with that column, no need to index
> it. If you're using it in a join all the time, then yes it would be
> better to index it.

Thanks for all your pointers!

Do views use the indexes in the underlying tables, whenever say a SELECT
operation is called on the view?  If so, indexes on views don't make any
sense right?


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] slightly OT - Using psql from Emacs with sql.el

[Seb]

Hi,

When working with psql via sql.el, multiple prompts accumulate in a
single line when sending multi-line input to the SQLi buffer.  For
example, sending the following:

SELECT a,
b,
c,
FROM some_table;

with 'C-c C-r' results in these lines in the SQLi buffer:

database_name=# database_name-# database_name-# database_name-# 

before showing the output of the query.

This doesn't happen when working with psql directly from a shell.

Has someone dealt with this problem or can suggest some ideas to avoid
this?

Thanks,

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] slightly OT - Using psql from Emacs with sql.el

[Seb]

On Thu, 05 May 2011 16:47:09 -0600,
Rob Sargent  wrote:

[...]

> Doesn't appear to.  I use sql-mode alot/daily.  The multiple prompts
> never bothers me, though the output not starting at the left kind of
> does.

I've adapted someone's suggestion at the Emacs Wiki for that:

(defun sl/sql-add-newline-before-output (output)
  "Add newline to beginning of OUTPUT for `comint-preoutput-filter-functions'"
  (concat "\n" output))

(add-hook 'sql-interactive-mode-hook
  (lambda ()
(add-hook 'comint-preoutput-filter-functions
  'sl/sql-add-newline-before-output)))

... but this breaks navigation (e.g. 'C-c C-p')


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] slightly OT - Using psql from Emacs with sql.el

[Seb]

On Thu, 05 May 2011 16:47:09 -0600,
Rob Sargent  wrote:

[...]

> Doesn't appear to.  I use sql-mode alot/daily.  The multiple prompts
> never bothers me, though the output not starting at the left kind of
> does.

Then you might like this:

http://www.emacswiki.org/emacs/SqlMode#toc3

bottom section

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] self join

[Seb]

Hi,

This probably reflects my confusion with how self joins work.

Suppose we have this table:

=# SELECT * FROM tmp;
 a | b 
---+---
 1 | 2
 2 | 3
 4 | 5
(3 rows)

If I want to get a table with records where none of the values in column
b are found in column a, I thought this should do it:

=# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a;
 a | b | a | b 
---+---+---+---
 1 | 2 | 1 | 2
 1 | 2 | 2 | 3
 1 | 2 | 4 | 5
 2 | 3 | 2 | 3
 2 | 3 | 4 | 5
 4 | 5 | 1 | 2
 4 | 5 | 2 | 3
 4 | 5 | 4 | 5
(8 rows)

I need to get:

 a | b | a | b 
---+---+---+---
 1 | 2 | 1 | 2
 4 | 5 | 4 | 5

Or just:

 a | b  
---+---
 1 | 2 
 4 | 5 


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] self join

[Seb]

On Sun, 15 May 2011 07:39:06 +0900,
Ian Lawrence Barwick  wrote:

[...]

> Your query doesn't have an explicit join and is producing a cartesian
> result.

> I don't think a self- join will work here; a subquery should produce
> the result you're after:

> SELECT * FROM tmp t1 WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE
> t2.b=t1.a);

This produces exactly the result I'm after.  I'll need to understand the
EXISTS statement there in more detail.

Thanks!

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] enum data type vs table

[Seb]

Hi,

Are there any guidelines for deciding whether to 1) create an enum data
type or 2) create a table with the set of values and then have foreign
keys referencing this table?  Some fields in a database take a small
number of values, and I'm not sure which of these routes to take.  The
enum data type seems like a clean way to handle this without creating a
constellation of tables for all these values, but if one wants to add a
new label to the enum or make changes to it at some point, then the
tables using it have to be recreated, so it's quite rigid.  Have I got
this right?  Thanks.


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] enum data type vs table

[Seb]

On Wed, 25 May 2011 17:23:26 -0500,
Peter Koczan  wrote:

> On Tue, May 17, 2011 at 11:23 PM, Seb  wrote:
>> Are there any guidelines for deciding whether to 1) create an enum
>> data type or 2) create a table with the set of values and then have
>> foreign keys referencing this table?  Some fields in a database take
>> a small number of values, and I'm not sure which of these routes to
>> take.  The enum data type seems like a clean way to handle this
>> without creating a constellation of tables for all these values, but
>> if one wants to add a new label to the enum or make changes to it at
>> some point, then the tables using it have to be recreated, so it's
>> quite rigid.  Have I got this right?  Thanks.

> I think your choice depends on a few things:

> 1 - How do you want to interact with the tables? What I mean is, are
> you planning on querying, inserting, or updating data to those tables
> via text or will you need to join to your reference table? If you
> don't want to join, you'll either need to use enum types, use views
> (which can be a pain if you want to update a view), or
> duplicate/reference the text directly (which is slow and a bad idea
> for several reasons).

> 2 - How much can you tolerate downtime or a busy database? Changing
> types is a single transaction and requires an exclusive lock. On small
> tables this is negligible, but on big tables it can require downtime.

> 3 - How often do you really expect changes to the enum type? If adding
> a new value to an enum type is truly a rare event, it's . If it's
> frequent or regular, you should probably have a table.

> I've used both of these approaches and I've found enum types to be
> well worth any trouble to drop/recreate types. The changes I've made
> have been rare, and I've been able to schedule downtime pretty easily,
> so it made the most sense for me.

> Also, Postgres 9.1 allows adding values to enum types, so you could
> always use that when it is finally released.

These are great guidelines, thanks.


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] \copy multiline

[Seb]

Hi,

I use \copy to output tables into CSV files:

\copy (SELECT ...) TO 'a.csv' CSV

but for long and complex SELECT statements, it is cumbersome and
confusing to write everything in a single line, and multiline statements
don't seem to be accepted.  Is there an alternative, or am I missing an
continuation-character/option/variable that would allow multiline
statements in this case?

Cheers,

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] surrogate vs natural primary keys

[Seb]

Hi,

I've been reading several articles on this hotly debated issue and still
can't find proper criteria to select one or the other approach for the
database I'm currently designing.  I'd appreciate any pointers.  Thanks.


Cheers,

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] surrogate vs natural primary keys

[Seb]

On Mon, 15 Sep 2008 16:45:08 -0600,
"Scott Marlowe" <[EMAIL PROTECTED]> wrote:

[...]

> I think this question is a lot like "how large should I set
> shared_buffers?"  There's lots of different answers based on how you
> are using your data.

Yes, this is precisely what I'm after: *criteria* to help me decide
which approach to take for different scenarios.  Such guidance is what
seems to be lacking from most of the discussions I've seen on the
subject.  It's hard to distill this information when most of the
discussion is centered on advocating one or the other approach.


Thanks,

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] surrogate vs natural primary keys

[Seb]

Hi,

After some more reading and considering your feedback, I'm still
somewhat confused about this issue.

1. Should the choice of surrogate/natural primary keys be done across an
entire database, or does it make more sense to do it on a per-table
basis?  I reckon one could do it on a per-table basis, but its direct
relationships would influence the choice.

2. If we do find a suitable natural primary key for a table, but it
turns out to be a composite one, how can such a key be referred to in
another table?  Say we have:

CREATE TABLE t1 (
c1 varchar(200),
c2 int8,
c3 varchar(500),
PRIMARY KEY (c1, c2)
);


and I want to create a table t2 which needs to refer to the composite
primary key of t1.  Should one create 2 columns in t2 that REFERENCE c1
and c2?  If so, this seems very cumbersome and I'm tempted to create a
surrogate key in t1 just to be able to refer to it more efficiently.  Is
this something we should be considering when choosing natural
vs. surrogate keys?  Thanks again.


-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] surrogate vs natural primary keys

[Seb]

On Tue, 16 Sep 2008 20:34:51 -0600,
"Scott Marlowe" <[EMAIL PROTECTED]> wrote:

[...]

> create table t2 (
> d1 varchar(200),
> d2 int8,
> d3 varchar(1000),
> foreign key t2_fk references t1(c1,c2) );

Thanks Scott, I guess you meant:

CREATE TABLE t2 (
d1 varchar(200),
d2 int8,
d3 varchar(1000),
PRIMARY KEY (d1, d2)
FOREIGN KEY (d1, d2) REFERENCES t1(c1, c2) );

But this makes it difficult to work with t2 because it has 2 fields that
are the same as in t1.  Isn't it better to just use a surrogate key and
use a single field in t2, thereby avoiding repeating multiple pieces of
information?


Thanks,

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] complex column definition in query

[Seb]

Hi,

Say we have a table:

SELECT * FROM weather;
 city  | temp_lo | temp_hi | prcp  
---+-+-+---
 San Francisco |  46 |  50 | 0.25 
 San Francisco |  43 |  57 |0 
 Hayward   |  37 |  54 |  
 Hayward   |  30 |  58 |  
 Somewhere |  25 |  60 |  
 Somewhere |  28 |  50 |  
(6 rows)

I'm struggling to build a query with a column temp, where the first row
is the lowest temp_lo followed by all the temp_hi for each city.  So
this would be the output:

 city  | temp  
---+--
 San Francisco |   43
 San Francisco |   50 
 San Francisco |   57 
 Hayward   |   30
 Hayward   |   54 
 Hayward   |   58 
 Somewhere |   25
 Somewhere |   60 
 Somewhere |   50 
(6 rows)

Any ideas appreciated!  Thanks.


Cheers,

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] complex column definition in query

[Seb]

On Wed, 3 Jun 2009 07:04:32 +0200,
"A. Kretschmer"  wrote:

> In response to Seb :
>> Hi,

>> Say we have a table:

>> SELECT * FROM weather; city | temp_lo | temp_hi | prcp
>> ---+-+-+--- San Francisco | 46 | 50 |
>> 0.25 San Francisco | 43 | 57 | 0 Hayward | 37 | 54 | Hayward | 30 |
>> 58 | Somewhere | 25 | 60 | Somewhere | 28 | 50 | (6 rows)

>> I'm struggling to build a query with a column temp, where the first
>> row is the lowest temp_lo followed by all the temp_hi for each city.
>> So this would be the output:

>> city | temp ---+-- San Francisco | 43 San Francisco |
>> 50 San Francisco | 57 Hayward | 30 Hayward | 54 Hayward | 58
>> Somewhere | 25 Somewhere | 60 Somewhere | 50 (6 rows)

>> Any ideas appreciated!  Thanks.

> test=# select * from weather ; city | temp_lo | temp_hi
> ---+-+- San Francisco | 46 | 50 San
> Francisco | 43 | 57 Hayward | 37 | 54 Hayward | 30 | 58 (4 rows)

> test=*# select city, min(temp_lo) as temp from weather group by city
> union all select city, temp_hi from weather order by 1,2; city | temp
> ---+-- Hayward | 30 Hayward | 54 Hayward | 58 San
> Francisco | 43 San Francisco | 50 San Francisco | 57 (6 rows)

Thanks to all that responded on and off list.  Is it necessary to ensure
that the "FROM" part of the two queries are exactly the same (the real
case scenario involves 3 tables)?

Cheers,

-- 
Seb


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] RE : [SQL] Convert text from UTF8 to ASCII

[seb JACK]

Hi
 
Perhaps you should try to set your db client_encoding to UTF8.
 
How do you know you have 'patiënt' instead of 'patiënt'?
I mean i also deals with UTF8 databases and all i store is correctly stored. 
But i can't check it with command line psql as my shell is configured with 
iso-8859-1 charset.
So with psql i will also see 'patiënt' even if 'patiënt' is correctly stored!
Perhaps your problem is as simple as that!?
 
Sebastien.
 



De: pgsql-sql-ow...@postgresql.org de la part de Paul Dam
Date: mer. 11/03/2009 16:51
À: Leif B. Kristensen; pgsql-sql@postgresql.org
Objet : Re: [SQL] Convert text from UTF8 to ASCII



UNICODE.

Met vriendelijke groet,

Paul Dam
Informatieanalist




Amyyon
Bijsterhuizen 11.58
6546 AS Nijmegen
050 - 311 5686
www.amyyon.nl

-Oorspronkelijk bericht-
Van: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] 
Namens Leif B. Kristensen
Verzonden: woensdag 11 maart 2009 16:46
Aan: pgsql-sql@postgresql.org
CC: Paul Dam
Onderwerp: Re: [SQL] Convert text from UTF8 to ASCII

On Wednesday 11. March 2009, Paul Dam wrote:
>Hoi,
>
>
>
>I store content of an .txt file in a text column in the database.
>
>server_encoding is UTF8.
>
>
>
>If the .txt file is in ASCII this is correctly stored in the database.
>
>If the .txt file is in UTF8 this is NOT correctly stored in the
> database.
>
>
>
>Examples:
>
>In Dutch the term for patient is 'patiënt'.
>
>It is stored as 'patiënt'.

That looks a lot like UTF-8 to me. What is your client-encoding?

--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql