Re: [SQL] Simple Problem ?
Hengky Lie a écrit : > This is the same as what i think but the table has primary key and has > no relation to other table. This is the table definition : > [...] > > These query result non updateable records : > > SELECT kode, namabarang from tblproduk; > > SELECT * from tblproduk; > > What is the problem ? > Oops, I was wrong on this. You can't edit results in the query tool. You need to use the data viewer (menu Tools/View Data/View All Rows ; there's also a button in the toolbar). -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- 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] Simple Problem ?
I have to use sql command because i want to retrieve data according to several criteria. The SQL command just a sample to show that the data i retrieve not updateable. The real query like : SELECT * FROM TBLPRODUK WHERE SUBKAT='abc'; So i do not need to view data from data viewer, but using query with an updateable result. Any suggestion ? Thanks in advance Guillaume Lelarge wrote: Hengky Lie a écrit : This is the same as what i think but the table has primary key and has no relation to other table. This is the table definition : [...] These query result non updateable records : SELECT kode, namabarang from tblproduk; SELECT * from tblproduk; What is the problem ? Oops, I was wrong on this. You can't edit results in the query tool. You need to use the data viewer (menu Tools/View Data/View All Rows ; there's also a button in the toolbar). -- 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] Simple Problem ?
Hengky Lie a écrit : > I have to use sql command because i want to retrieve data according to > several criteria. > The SQL command just a sample to show that the data i retrieve not > updateable. The real query like : > > SELECT * FROM TBLPRODUK WHERE SUBKAT='abc'; > > So i do not need to view data from data viewer, but using query with an > updateable result. > > Any suggestion ? > Yes, use the data viewer : menu Tools/View Data/View Fitered Rows. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- 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] a simple transform
Karl Grossner wrote: I've thrashed at this transform for quite a while and come up empty. The crosstab() functions, and the documented examples, all do something more complex than I need. I can do this after the fact trivially in python with the 'zip()' function, but I need it real-time from a view or function. Of course I could be overlooking the obvious (it's happened before!) I have this: attr1 attr2 attr3 -- -- -- cat10.340.120.22 cat20.110.670.31 and need it transposed thus: cat1cat2 - - attr1 0.340.11 attr2 0.120.67 attr3 0.220.31 I see no response; so I thought I'd take a stab at it. I don't have 8.3 yet so I cannot test my idea. I read the docs, and I think what you need to do is create SQL that will produce cat attr val - cat1 attr1 0.34 cat1 attr2 0.12 cat1 attr3 0.22 cat2 attr1 0.11 cat2 attr2 0.67 cat2 attr3 0.31 That pass the SQL into crosstab(). -- 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
On Mon, Sep 15, 2008 at 3:45 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > for > instance, when you book a flight with an airline, you'll get a locator > code like A89JK3 that is unique to any other locator code in the > system. Sure, you could make a natural key of first name, last name, > address, phone number, flight number, departure / arrival and day and > time, but there's no way that's going to perform as well as a single > char(6). Scott, My understanding is that as soon as any code becomes visible outside of the database or application it can no longer be called a surrogate key. From my reading of some of the Celko books, he strongly ascribes to codes as primary keys. His suggestion is to use internationally recognized codes (if they exist) for identify items. If none exist then nationally recognized codes, then industry recognized codes, and then finally if non of these exists then he recommends developing a company specific unique code (for these he recommends codes that have a built in check-sum (I think check-sum is the correct word) for data entry validation). Any thoughts? My DB experience has been limited to small < 2 GB OLTP databases, so I understand that for very large databases there may be cases where natural keys are a clear looser for performace and storage reasons. In my case, I've tried to evaluate the trade-offs between using natural vs surrogate keys for every relation. Most of the time I use natural keys, however there are some occations when surrogate keys (for me) are only way to go. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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
On Tue, Sep 16, 2008 at 07:59:20AM -0700, Richard Broersma wrote: > key. From my reading of some of the Celko books, he strongly ascribes > to codes as primary keys. His suggestion is to use internationally > recognized codes (if they exist) for identify items. The problem with that strategy is that any identifier has to be NOT NULL and UNIQUE. So if the international identifier assigner makes a mistake (fails to assign or assigns duplicates), you're hosed. In addition, you need to make sure that the purpose for which you are using the identifier is also the purpose for which the identifier is assigned. ISO, for instance, is willing to re-use country codes (even though the specification never suggested they were). So if you expect to use the ISO 2-letter codes over time, you may get a nasty surprise. (For an example, in 2003 "CS" became historically ambiguous.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] a simple transform
On Tue, Sep 16, 2008 at 6:37 AM, Frank Bax <[EMAIL PROTECTED]> wrote: > > That pass the SQL into crosstab(). It might be interesting to look at this blog also: http://okbob.blogspot.com/#7449458148004287481 -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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
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
On Tue, Sep 16, 2008 at 6:10 PM, Seb <[EMAIL PROTECTED]> wrote: > 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. Definitely on a per table basis. for instance, if you create a lookup table to use as a target for an FK, there's usually little need for an artificial key. > 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) > ); create table t2 ( d1 varchar(200), d2 int8, d3 varchar(1000), foreign key t2_fk references t1(c1,c2) ); or something like that. > > > 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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Insert a space between each character
Hi, can anybody help me, to insert a space between each character in postgresql. for example, ABC output A B C -Dominic
Re: [SQL] Insert a space between each character
am Wed, dem 17.09.2008, um 11:49:27 +0530 mailte Nicholas I folgendes: > Hi, > > can anybody help me, to insert a space between each character in postgresql. > > for example, > > ABC > > output > A B C write a function in plpgsql, you can use string-function like substr to split the string. Write a loop over the string, pick up every char, and concatenate a space. Return the new string. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] Insert a space between each character
Hi,
Use a regular expression, e.g.:
select trim(regexp_replace('foobarbaz', '(.)', E'\\1 ', 'g'));
See http://www.postgresql.org/docs/8.3/static/functions-matching.html
for more.
+mt
Nicholas I wrote:
> Hi,
>
> can anybody help me, to insert a space between each character in
> postgresql.
>
> for example,
>
> ABC
>
> output
> A B C
>
> -Dominic
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] May I have an assistance on CREATE TABLE Command
Hello Mambers of PGSQL-SQL, I have two tables namely: REGION (region_id, region_name) DISTRICT (dist_id, dist_name, region_id (FK)) I would like to have the CREATE TABLE Command which will create these tables in such a way that when REGION table is UPDATED automatical the FOREGN KEY in DISTRICT table is also updated. I will appriciate for your assistance ! Regards James Kitambara
