Re: [SQL] Many to many link tables with history?

On 2013-10-10 Thu 15:25 PM |, Dylan Sanders wrote: > > unregister-pattern ? -- Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsq

Re: [SQL] Many to many link tables with history?

unregister-pattern soe...@gmail.com On Thu, Oct 10, 2013 at 3:23 PM, Craig R. Skinner wrote: > On 2013-10-08 Tue 18:04 PM |, Craig R. Skinner wrote: > > Hi folks, I'm new here. I hope this is the correct list > > > > While creating a database of customer subscribed services with > > DNS do

Re: [SQL] Many to many link tables with history?

On 2013-10-08 Tue 18:04 PM |, Craig R. Skinner wrote: > Hi folks, I'm new here. I hope this is the correct list > > While creating a database of customer subscribed services with > DNS domains, I want to: > *) enable customers to subscribe to multiple services > *) each service can have multip

Re: [SQL] generate a range within a view

I've managed to do it using a function, shown below, but is there a better way? create type site_user_department_limits as (s_id char, de_id int4, date date, day_id_week int4, day_limit); create or replace function site_user_department_limits(date,date) returns setof site_user_department_l

Re: [SQL]

Ok, got it. Thank you every one for your response.   Thanks and Regards Kaleeswaran Velu On Tuesday, October 8, 2013 10:00 PM, Andreas Kretschmer wrote: Kaleeswaran Velu wrote: > Hi Team, > I am using PostgreSQL 9.2.3 in Windows platform. I have created two databases > in it. Now I want t

Re: [SQL] Question about index/constraint definition in a table

David Johnston wrote > > JORGE MALDONADO wrote >> I have a table as follows: >> >> Table Artist Colaborations >> >> * car_id (integer field, primary key) >> * car_song (integer field, foreign key, for

Re: [SQL] Question about index/constraint definition in a table

JORGE MALDONADO wrote > I have a table as follows: > > Table Artist Colaborations > > * car_id (integer field, primary key) > * car_song (integer field, foreign key, foreign table is a catalog of > son

Re: [SQL]

Kaleeswaran Velu wrote: > Hi Team, > I am using PostgreSQL 9.2.3 in Windows platform. I have created two databases > in it. Now I want to refer the tables across the databases. Meaning would like > to create Database link. Can anyone guide me on how to create a DB link? You can use db_link, see

Re: [SQL]

1 - why two databases? Couldn't you have just created two separate SCHEMAS? 2 - if you insist on two separate databases: http://www.postgresql.org/docs/9.2/static/dblink.html From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Kaleeswaran Velu Sent: Tues

Re: [SQL] Unique index VS unique constraint

On Sat, Oct 5, 2013 at 3:24 PM, JORGE MALDONADO wrote: > So, let´s say that I have the following simple example table: > > 1. cus_id > 2. cus_name > 3. Other fields . . . > > Where "cus_id" is the primary key. And let´s also say that I want "cus_name" > to be unique. I have the option to create a

Re: [SQL] Unique index VS unique constraint

So, let´s say that I have the following simple example table: 1. cus_id 2. cus_name 3. Other fields . . . Where "cus_id" is the primary key. And let´s also say that I want "cus_name" to be unique. I have the option to create a unique constraint or a unique index. What would be the best decision a

Re: [SQL] Unique index VS unique constraint

Steve Grey-2 wrote > Unique indexes can be partial, i.e. defined with a where clause (that must > be included in a query so that PostgreSQL knows to use that index) whereas > unique constraints cannot. This implies there can be data in the table but not in the index and thus said index is not part

Re: [SQL] Unique index VS unique constraint

Unique indexes can be partial, i.e. defined with a where clause (that must be included in a query so that PostgreSQL knows to use that index) whereas unique constraints cannot. JORGE MALDONADO wrote > I have search for information about the difference between "unique index" > and "unique constrain

Re: [SQL] Advice on defining indexes

JORGE MALDONADO wrote > If a table has a foreign key on 2 fields, should I also create an index > composed of such fields? Yes. If you want to truly/actually model a foreign key the system will require you to create a unique constraint/index on the "primary/one" side of the relationship. CREATE

Re: [SQL] Unique index VS unique constraint

JORGE MALDONADO wrote > I have search for information about the difference between "unique index" > and "unique constraint" in PostgreSQL without getting to a specific > answer, > so I kindly ask for an explanation that helps me clarify such concept. A constraint says what valid data looks like.

Re: [SQL] Advice on defining indexes

I really appreciate your fast and very complete answer. If a table has a foreign key on 2 fields, should I also create an index composed of such fields? For example: --- Table Sources --- 1. src_id 2. src_date 3. Other fields . . . Here, the "primary key"

Re: [SQL] Advice on defining indexes

JORGE MALDONADO wrote > I have a table with fields that I guess would be a good idea to set as > indexes because users may query it to get results ordered by different > criteria. For example: > > -- > Artists Table > -- > 1. art_id > 2. art_name > 3. art_bday > 4.

Re: [SQL] Unique index VS unique constraint

On 10/04/2013 10:41 AM, luca...@gmail.com wrote: Il 04/10/2013 18:48, JORGE MALDONADO ha scritto: I have search for information about the difference between "unique index" and "unique constraint" in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me

Re: [SQL] Unique index VS unique constraint

On 10/04/2013 09:48 AM, JORGE MALDONADO wrote: I have search for information about the difference between "unique index" and "unique constraint" in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. The way I think of it is, t

Re: [SQL] Unique index VS unique constraint

Il 04/10/2013 18:48, JORGE MALDONADO ha scritto: I have search for information about the difference between "unique index" and "unique constraint" in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. 2 main differences. Fi

Re: [SQL] Help needed with Window function

> This is an approach I also considered, but hoped for a solution without the > expense (albeit small) of having to create a function. How about this query? CREATE TABLE transactions ( item_code text, _date date, qty double precision ) ; INSERT INTO transaction

Re: [SQL] Help needed with Window function

David Johnston wrote > Basic idea: use ORDER BY in the window to auto-define a range-preceding > frame. Create an array of all dates (tags in the example) that match with > positive amounts. Negative amounts get their matching tag added to the > array as NULL. The provided function looks into th

Re: [SQL] Help needed with Window function

gmb wrote > item_code | _date| qty | max > - > ABC | 2013-04-05 | 10.00| 2013-04-05 > ABC | 2013-04-06 | 10.00| 2013-04-06 > ABC | 2013-04-06 | -2.00| 2013-04-06

Re: [SQL] DB link from postgres to Oracle; how to query Dbname.tablename?

In PostgreSQL, you always connect to a 'database', then query tables. So if you are connecting to the 'wrong' database, you will get the error you mentioned. You can troubleshoot this in many ways - one way would be to enable logging on PostgreSQL side and check the log and see which database you a

Re: [SQL] openclinica

On 09/30/2013 04:20 PM, hugh holston wrote: I cant understand why I am not able to access and open my openclinica webpage, . So what do I do. My guess is you will get a answer sooner here: https://community.openclinica.com/forums/users-email-forum -- Adrian Klaver adrian.kla...@gmail.com

Re: [SQL] Can I simplify this somehow?

On Fri, Sep 27, 2013 at 12:32 PM, Larry Rosenman wrote: > On 2013-09-27 14:24, Adam Jelinek wrote: >> TIME MON TUE WED ... >> 09:00 13 4 >> 10:00 50 8 >> > That would be cool, but just a list is good too. You need to take a look at this module: http://

Re: [SQL] postgres subfunction return error

Hurray, that works! Many thanks David CREATE OR REPLACE FUNCTION custom.pr_test_parentfunction ( v_action varchar, out swv_refcur refcursor, out swv_refcur2 refcursor, out swv_refcur3 refcursor ) RETURNS record AS $body$ DECLARE SWV_Action VARCHAR(50) DEFAULT Coalesce(v_Act

Re: [SQL] Can I simplify this somehow?

On 2013-09-27 14:24, Adam Jelinek wrote: Are you looking for something like this for the result for the last 45 days or something else? TIME  MON   TUE  WED ... 09:00     1        3       4 10:00     5        0       8 That would be cool, but just a list is good too. -- Larry Rosenman

Re: [SQL] Can I simplify this somehow?

Are you looking for something like this for the result for the last 45 days or something else? TIME MON TUE WED ... 09:00 13 4 10:00 50 8 On Fri, Sep 27, 2013 at 10:22 AM, Larry Rosenman wrote: > I tried(!) to write this as a with (CTE), but failed. > >

Re: [SQL] postgres subfunction return error

jonathansfl wrote > SELECT * INTO v_outvar1, v_outvar2, v_outvar3 FROM > custom.pr_test_subfunction(SWV_Action); > OPEN swv_refcur for SELECT v_outvar1; > OPEN swv_refcur2 for SELECT v_outvar2; > OPEN swv_refcur3 for SELECT v_outvar3; > RETURN; I've never used cursors in this

Re: [SQL] postgres subfunction return error

I'm trying to pass a REFCURSOR variable from a subfunction to its parent calling function, who will then pass it to the user (for parsing). thanks to David J I fixed it somewhat, but the user now receives the TEXT of (etc.) instead of the actual data in that REFCURSOR variable. I think the proble

Re: [SQL] postgres subfunction return error

Thank you! What kind of variable would I declare? Is this any form of right? No change to subfunction. In PARENT Function: DECLARE v_outvar1 REFCURSOR; v_outvar2 REFCURSOR; v_outvar3 REFCURSOR; ?And use: SELECT * INTO v_outvar1, v_outvar2, v_outvar3 FROM dev.pr_test_subfunction(SWV_Action);

Re: [SQL] postgres subfunction return error

jonathansfl wrote > greetings. I'm trying to write a function that acts like a switchboard, > calling other functions depending on incoming parameters. > I'm getting error: query has no destination for result data > > > SELECT * FROM dev.pr_test_subfunction(SWV_Action); In pl/pgsql if you

Re: [SQL] the value of OLD on an initial row insert

On Fri, Sep 20, 2013 at 6:43 PM, James Sharrett wrote: > ERROR: record "old" is not assigned yet > SQL state: 55000 > Detail: The tuple structure of a not-yet-assigned record is indeterminate. > > Is this what's happening? If so, how can I avoid the issue. If I get it right you are running the

Re: [SQL] unique key problem on update

Gary Stainburn wrote on 20.09.2013 18:07: I want to add a new page after page 2 so I try to increase the sequence number of each row from page 3 onwards to make space in the sequence for the new record. However, I get duplicate key errors when I try. Can anyone suggest how I get round this. Also

Re: [SQL] unique key problem on update

Gary Stainburn wrote on 20.09.2013 18:30: You need to define the primary key as deferrable: create table skills_pages ( sp_idserial not null, sp_sequence integer not null, sp_title character varying(80), sp_narative text, primary key (sp_id) deferrable ); Cheers. I

Re: [SQL] unique key problem on update

On Friday 20 September 2013 17:26:58 Thomas Kellerer wrote: > You need to define the primary key as deferrable: > > create table skills_pages > ( > sp_idserial not null, > sp_sequence integer not null, > sp_title character varying(80), > sp_narative text, > primary key (sp_i

Re: [SQL] detect initiator of update/delete action

On Fri, Sep 20, 2013 at 9:52 AM, ssylla wrote: > There are two cases: > 1. if the deletion was executed through a user action, both the update and > insert statement of the 'table1_del' function should be executed > 2. if the deletion was initiated through a function fired from another > table, on

Re: [SQL] xmlElement and \n

On Thu, Sep 19, 2013 at 9:46 AM, Enrico Oliva wrote: > I would like to have > > > Yes > ... I'm not sure there is an indenting facility, since indentation strongly depends on what the user think about. However you can concatenate a new line at each xmlelement call: SELECT xmleleme

Re: [SQL] removing duplicates and using sort

Yes, that's correct, modifying the original ORDER BY gives: ORDER BY lastname, firstname, refid, appldate DESC; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions Using WITH works great: WITH distinct_query AS ( SELECT DISTINCT ON (refid) id, refid, lastname, firs

Re: [SQL] removing duplicates and using sort

Note that you could always do something like: WITH original_query AS ( SELECT DISTINCT ... ) SELECT * FROM original_query ORDER BY lastname, firstname; OR SELECT * FROM ( SELECT DISTINCT ) sub_query ORDER BY lastname, firstname I am thinking you cannot alter the existing ORDER BY other

Re: [SQL] removing duplicates and using sort

Change the order by to order by lastname, firstname, refid, appldate From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Nathan Mailg Sent: Saturday, September 14, 2013 10:36 PM To: pgsql-sql@postgresql.org Subject: [SQL] removing duplicates and using sort

Re: [SQL] apt.postgresql.org vs. Pitti PPA - install error

> So either you try to compile your own binary packages > from the source packages or you download the postgresql-common .deb > and try to forcly install it (man pkg, dpkg --force-help, dpkg > --force-breaks). Jan, thank you, but both alternatives are - for me - not secure. So I hoped, that Pi

Re: [SQL] apt.postgresql.org vs. Pitti PPA - install error

Am 11.09.2013 11:51, schrieb rawi: > Same conditions: > > Ubuntu 13.04 64bit (raring) > > /etc/apt/sources.list.d/apt_postgresql_org.list contains: > > deb http://apt.postgresql.org/pub/repos/apt/ [1] precise-pgdg main > > lsb_release -c > Codename: raring > > Same fault: > > The follow

Re: [SQL] apt.postgresql.org vs. Pitti PPA - install error

Sorry for bumping the thread... ... but I'd like to install now finally postgresql 9.3 - and it doesn't work. Same conditions: Ubuntu 13.04 64bit (raring) /etc/apt/sources.list.d/apt_postgresql_org.list contains: deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main lsb_release -c C

Re: [SQL] Understanding Encoding

Hello, Thank you all. Amit, Changing the encoding of the terminal emulator worked. Sebastiean, the tip was helpful. -- Beena Emerson

Re: [SQL] Understanding Encoding

Hi, Tip: To identify what encoding you enter in the psql command interpreter: 1) Open a file with vim 2) Type in you SQL or copy/paste 3) Save the file and quit vim 4) $ file Should give you the encoding of that text file. For ex: sf@orca:~$ echo $LC_ALL en_US.UTF-8 sf@orca:~$ cat /tmp/xx

Re: [SQL] [NOVICE] Understanding Encoding

On Fri, Sep 6, 2013 at 12:29 PM, Tom Lane wrote: > Beena Emerson writes: > > It still gives same result: > > > $ LANG=ko_KR LC_ALL=ko_KR > > $ psql -d korean > > > korean=# SHOW client_encoding; > > client_encoding > > - > > EUC_KR > > (1 row) > > > korean=# INSERT INTO tbl VAL

Re: [SQL] [NOVICE] Understanding Encoding

On Fri, Sep 6, 2013 at 3:47 PM, Beena Emerson wrote: > >> >> I wonder if you have tried changing your "locale" to ko_KR; something >> like: >> >> LANG=ko_KR LC_ALL=ko_KR \ >> psql -d korean >> > > Hi, > > It still gives same result: > > $ LANG=ko_KR LC_ALL=ko_KR > $ psql -d korean > > korean=# SHO

Re: [SQL] Understanding Encoding

> Hello All, > > I am not able to understand how the encoding is handled. I would be happy > if someone can tell what is happening in the following scenario: > > 1. I have created a database with EUC_KR encoding and created a table and > inserted some korean value into it. > > =# CREATE DATABASE

Re: [SQL] [NOVICE] Understanding Encoding

Beena Emerson writes: > It still gives same result: > $ LANG=ko_KR LC_ALL=ko_KR > $ psql -d korean > korean=# SHOW client_encoding; > client_encoding > - > EUC_KR > (1 row) > korean=# INSERT INTO tbl VALUES ('그레스'); > ERROR: invalid byte sequence for encoding "EUC_KR":

Re: [SQL] [NOVICE] Understanding Encoding

> > I wonder if you have tried changing your "locale" to ko_KR; something like: > > LANG=ko_KR LC_ALL=ko_KR \ > psql -d korean > > Hi, It still gives same result: $ LANG=ko_KR LC_ALL=ko_KR $ psql -d korean korean=# SHOW client_encoding; client_encoding - EUC_KR (1 row) korean=

Re: [SQL] [NOVICE] Understanding Encoding

On Fri, Sep 6, 2013 at 2:56 PM, Beena Emerson wrote: > Hello All, > > I am not able to understand how the encoding is handled. I would be happy if > someone can tell what is happening in the following scenario: > > 1. I have created a database with EUC_KR encoding and created a table and > inserte

Re: [SQL] Understanding Encoding

You can refer : http://www.postgresql.org/docs/9.2/static/multibyte.html On Fri, Sep 6, 2013 at 11:26 AM, Beena Emerson wrote: > Hello All, > > I am not able to understand how the encoding is handled. I would be happy > if someone can tell what is happening in the following scenario: > > 1. I ha

Re: [SQL] Trigger for updating view with join

Heh, it's me spamming you again :) now -- asking for comments. > > create trigger fsl_update instead of insert or update on fsl ... > > > > but till now did not succeed in it. Quick googlink did not help either. > > Argh. My google-fu is definitely low in the night ;) > > for the record: it'

Re: [SQL] Trigger for updating view with join

On Wed, 4 Sep 2013, Dmitry Morozovsky wrote: > Dear colleagues, > > I'm running Pg 9.1 and have schema like the following: [snip] > I understand I should use smth like > > create trigger fsl_update instead of insert or update on fsl ... > > but till now did not succeed in it. Quick googlink

Re: [SQL] Find Out a Way to Recover Data From Android Phone

Perhaps this is just what you are looking for, right? Recover Data from Android Phone -- View this message in context: http://p

Re: [SQL] CTAGS for PL/pgSQL ?

On 13-08-29 9:00 AM, Luca Ferrari wrote: On Thu, Aug 29, 2013 at 3:33 PM, Bruce Momjian wrote: Uh, I think Vim can use etags, no? Isn't etags Exuberant Ctags? The Exuberant Ctags's FAQ mentions Vim: I was referring to the etags shipped with emacs, that even if does not support (explicitly)

Re: [SQL] CTAGS for PL/pgSQL ?

On Thu, Aug 29, 2013 at 3:33 PM, Bruce Momjian wrote: > Uh, I think Vim can use etags, no? Isn't etags Exuberant Ctags? The > Exuberant Ctags's FAQ mentions Vim: > I was referring to the etags shipped with emacs, that even if does not support (explicitly) sql seems to work for a very simple te

Re: [SQL] Using regexp_matches in the WHERE clause

spulatkan wrote > so following is enough to get the rows that matches regular expression > This is bad form even if it works. If the only point of the expression is to filter rows it should appear in the WHERE clause. The fact that regexp_matches(...) behaves in this way at all is, IMO, a flaw

Re: [SQL] CTAGS for PL/pgSQL ?

On Thu, Aug 29, 2013 at 08:18:03AM -0500, Charles Sheridan wrote: > > >>Does anyone know if there are any CTAGS extensions or variants that support > >>PL/pgSQL ? > >> > >>I use exuberant-ctags which does not support it, and a web search does not > >>return anything promising. > >As far as I know,

Re: [SQL] CTAGS for PL/pgSQL ?

Does anyone know if there are any CTAGS extensions or variants that support PL/pgSQL ? I use exuberant-ctags which does not support it, and a web search does not return anything promising. As far as I know, the quick answer is NO. However I made a few simple tests with etags and it seems to wo

Re: [SQL] Using regexp_matches in the WHERE clause

I noticed that regexp_matches already returns the rows which matches the regular expression now when I make a full table select query but if I make a search with regexp_matches, it only returns rows that matches regular expression on pgadmin the column type is shown as text[] thus I also do no

Re: [SQL] CTAGS for PL/pgSQL ?

On Wed, Aug 28, 2013 at 5:08 PM, Charles Sheridan wrote: > Does anyone know if there are any CTAGS extensions or variants that support > PL/pgSQL ? > > I use exuberant-ctags which does not support it, and a web search does not > return anything promising. As far as I know, the quick answer is NO.

Re: [SQL] Is there a way to build a query based on data in a table?

Hello, >From what I know, in pure SQL, it is not possible to perform what you want. But it is feasible in PL/pgSQL (and this script language is supported by default by postgresql...). Regards, Brice 2013/8/28 Herouth Maoz : > Hello. > > I want to run an automatic archiving SQL script, that look

Re: [SQL] function array_to_string(text[]) does not exist

Hello you should to enter separator postgres=# select array_to_string(ARRAY[1,2,3,4], '|'); array_to_string ─ 1|2|3|4 (1 row) Regards Pavel Stehule 2013/8/25 Victor Sterpu > Hello > > When I run : > SELECT array_to_string(array_agg(CONCAT(CAST (ltrv1.val_min AS CHAR), '-

Re: [SQL] function array_to_string(text[]) does not exist

Yes,array_to_string(text[]) does not exist. This from the doc may help: array_to_string(anyarray,text [, text]) text concatenates array elements using supplied delimiter and optional null stringarray_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') 1,2,3,*,5 Jov blog: http:amutu.com/blog

Re: [SQL] Presenting data in 5 Rows & 5 Cols for 25 specific values

On 08/03/2013 07:26 AM, F Bax wrote: I have a table containing tasks completed in a game I'm playing. The game includes an extra BINGO Challenge where each cell of standard BINGO card contains a particular task to be completed. The goal is score a BINGO (row, column, diagonal) by completing f

Re: [SQL] Presenting data in 5 Rows & 5 Cols for 25 specific values

On 08/03/2013 07:26 AM, F Bax wrote: I have a table containing tasks completed in a game I'm playing. The game includes an extra BINGO Challenge where each cell of standard BINGO card contains a particular task to be completed. The goal is score a BINGO (row, column, diagonal) by completing f

Re: [SQL] Unique index and unique constraint

2013/7/27 Alvaro Herrera > > PostgreSQL implements unique constraints by way of unique indexes (and > it's likely that all RDBMSs do likewise). Also, the syntax to declare > unique indexes allows for more features than the unique constraints > syntax. For example, you can have a unique index tha

Re: [SQL] Unique index and unique constraint

On Fri, Jul 26, 2013 at 3:19 PM, Alvaro Herrera wrote: > JORGE MALDONADO escribió: >> I guess I am understanding that it is possible to set a unique index or a >> unique constraint in a table, but I cannot fully understand the difference, >> even though I have Google some articles about it. I will

Re: [SQL] Criteria to define indexes

JORGE MALDONADO wrote > And so on. What I see is that it is not a good decision to set a key for > every possibility because it will have an impact on performance due to > index maintenance. What would be a good way to define indexes in a case > like this? For your specific case, and also more gen

Re: [SQL] Unique index and unique constraint

JORGE MALDONADO escribió: > I guess I am understanding that it is possible to set a unique index or a > unique constraint in a table, but I cannot fully understand the difference, > even though I have Google some articles about it. I will very much > appreciate any guidance. The SQL standard does

Re: [SQL] Unique index and unique constraint

I try to explain my point of view, also in my not so good English: A primary key is defined by dr. Codd in relational model. The key is used to identify a record. In good practice, you must always define a primary key. Always. The unique constraint will simply say: this value (or combination) shou

Re: [SQL] value from max row in group by

Sorry, but I never thought. I'm developing this on my server I'm developing it for someone else who wants it in a WordPress / MySQL environment (I don't know MySQL). Would this or something similar work in mysql? (Sorry for going O.T.) On Thursday 25 July 2013 19:53:06 Marc Mamin wrote: > >___

Re: [SQL] value from max row in group by

ution with distinct on: select distinct on (stts_id, stts_offset) stts_id, stts_offset+stts_duration from table order by stts_id, stts_offset desc Marc Mamin From: pgsql-sql-ow...@postgresql.org on behalf of Gary Stainburn Sent: Thursday, July 25, 2013 10:

Re: [SQL] value from max row in group by

: pgsql-sql@postgresql.org Subject: Re: [SQL] value from max row in group by As usual, once I've asked the question, I find the answer myself. However, it *feels* like there should be a more efficient way. Can anyone comment or suggest a better method? timetable=> select stts_id, stt

Re: [SQL] value from max row in group by

On Thu, Jul 25, 2013 at 10:45 AM, Gary Stainburn < gary.stainb...@ringways.co.uk> wrote: > Hi folks, > > I need help please. > > I have a table of trip section details which includes a trip ID, start > time as > an offset, and a duration for that section. > > I need to extract the full trip durati

Re: [SQL] value from max row in group by

As usual, once I've asked the question, I find the answer myself. However, it *feels* like there should be a more efficient way. Can anyone comment or suggest a better method? timetable=> select stts_id, stts_offset+stts_duration as total_duration timetable-> from standard_trip_sections timeta

Re: [SQL] monthly statistics

On Mon, Jul 8, 2013 at 2:18 PM, Andreas wrote: > How could I combine those 2 queries so that the date in query 1 would be > replaced dynamically with the result of the series? > Surely I'm missing something, but maybe this is something to work on: WITH RECURSIVE months(number) AS ( SELECT 1 UN

Re: [SQL] Advice on key design

Hi, yeah, I am okay with design prudence, just used to be so paranoid about performance that just any possible "one more thing to do" gets me nervous :) Language versions do exist, say Dutch has different orthography depending on what convention is used, so you may well need to suddenly add a furt

Re: [SQL] Advice on key design

On Wed, Jul 24, 2013 at 11:47 AM, Bèrto ëd Sèra wrote: > Hi, > > It looks heavy, performance-wise. If this is not OLTP intensive you can > probably survive, but I'd still really be interested to know ow you can end > up having non unique records on a Cartesian product, where the PK is defined > by

Re: [SQL] Listing table definitions by only one command

On Wed, Jul 17, 2013 at 5:29 PM, Carla Goncalves wrote: > Hi > I would like to list the definition of all user tables by only one command. > Is there a way to *not* show pg_catalog tables when using "\d ." in > PostgreSQL 9.1.9? > What do you mean by "user tables"? The execution of \d without any

Re: [SQL] Advice on key design

Hi, It looks heavy, performance-wise. If this is not OLTP intensive you can probably survive, but I'd still really be interested to know ow you can end up having non unique records on a Cartesian product, where the PK is defined by crossing the two defining tables. Unless you take your PK down the

Re: [SQL] Advice on key design

On Wed, Jul 24, 2013 at 10:38 AM, Bèrto ëd Sèra wrote: > What would be the rationale behind the serial number? > The serial key, also named "surrogate key" is there for management purposes. Imagine one day you find out your database design is wrong and what was unique the day before is no more s

Re: [SQL] Advice on key design

Hi, > I also need the field "lpp_id" as a unique key which is a field that contains a consecutive number of type serial. Are you sure you REALLY need this? It looks like a lot of trouble for nothing, as you now have one thing warranting the unicity of the record (your serial) plus a unique key wa

Re: [SQL] Advice on key design

Agreed with Anton, as PK, lpp_id is easier to be managed than (lpp_person_id + lpp_language_id) One more suggestion, foreign key constraints could be setup on lpp_person_id and lpp_language_id to link with target tables. Thanks, Stan 2013/7/24 Anton Gavazuk > The reason is simple - as you nee

Re: [SQL] Advice on key design

The reason is simple - as you need the artificial PK lpp_id, then everything else becomes an constraint Thanks, Anton On Jul 24, 2013, at 0:28, JORGE MALDONADO wrote: >> In your case it would be lpp_id as PK, and >> lpp_person_id,lpp_language_id as unique constraint >> >> Thanks, >> Anton Is

Re: [SQL] Advice on key design

>> In your case it would be lpp_id as PK, and >> lpp_person_id,lpp_language_id as unique constraint >> >> Thanks, >> Anton Is there a reason to do it the way you suggest? Regards, Jorge Maldonado On Tue, Jul 23, 2013 at 5:02 PM, Anton Gavazuk wrote: > Hi Jorge, > > In your case it would be lpp

Re: [SQL] Advice on key design

Hi Jorge, In your case it would be lpp_id as PK, and lpp_person_id,lpp_language_id as unique constraint Thanks, Anton On Jul 23, 2013, at 23:45, JORGE MALDONADO wrote: > I have 2 tables, a parent (tbl_persons) and a child > (tbl_languages_per_person) as follows (a language table is also invol

Re: [SQL] table constraint on two columns

On 07/22/2013 04:05 PM, ldrlj1 wrote: > Postgres 9.2.4. > > I have two columns, approved and comments. Approved is a boolean with no > default value and comments is a character varying (255) and nullable. > > I am trying to create a constraint that will not allow a row to be entered > if approved i

Re: [SQL] table constraint on two columns

On 07/22/2013 07:16 AM, Vik Fearing wrote: On 07/22/2013 04:05 PM, ldrlj1 wrote: Postgres 9.2.4. I have two columns, approved and comments. Approved is a boolean with no default value and comments is a character varying (255) and nullable. I am trying to create a constraint that will not allow

Re: [SQL] table constraint on two columns

DOH! Complete brain fart. Thank you for re-educating me :)! Worked like a charm. -- View this message in context: http://postgresql.1045698.n5.nabble.com/table-constraint-on-two-columns-tp5764645p5764649.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgs

Re: [SQL] Listing table definitions by only one command

Hi Carla, Il 17/07/2013 17:29, Carla Goncalves ha scritto: Hi I would like to list the definition of all user tables by only one command. Is there a way to *not* show pg_catalog tables when using "\d ." in PostgreSQL 9.1.9? The simpler way similar to a "\d ." I know is a query like this (su

Re: [SQL] Listing table definitions by only one command

On Wed, Jul 17, 2013 at 9:29 AM, Carla Goncalves wrote: > Hi > I would like to list the definition of all user tables by only one > command. Is there a way to *not* show pg_catalog tables when using "\d ." > in PostgreSQL 9.1.9? > > Thanks. > I didn't see a way to do that with \ commands, but fou

Re: [SQL] update column based on postgis query on anther table

Sent from my iPhone İ On 16 Tem 2013, îat 08:24, Tom Lane wrote: > Stefan Sylla writes: >> Now I want to use a trigger function to automatically update the column >> 'id_test1_poly' in tabel 'test1_point': > >> /**/ >> create or replace function test1_point_get_id_test1_poly() returns >> tr

Re: [SQL] SOLVED: update column based on postgis query on anther table

Hi Igor, thank you so much, the trigger function that you provided is exactly what I was looking for. I already read/heard about the SELECT INTO statement but I never actually understood what it is needed for. Here I go ;-) Stefan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) T

Re: [SQL] update column based on postgis query on anther table

> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of ssylla > Sent: Tuesday, July 16, 2013 3:58 AM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] update column based on postgis query on anther table &

Re: [SQL] update column based on postgis query on anther table

Hi Tom, I tried changing the trigger to be BEFORE instead of AFTER: create trigger test1_point_get_id_test1_poly before insert or update on test1_point for each row execute procedure test1_point_get_id_test1_poly(); But the problem persits, the column id_test1_poly remains empty. Stefan --

Re: [SQL] update column based on postgis query on anther table

Stefan Sylla writes: > Now I want to use a trigger function to automatically update the column > 'id_test1_poly' in tabel 'test1_point': > /**/ > create or replace function test1_point_get_id_test1_poly() returns > trigger as $$ > begin > new.id_test1_poly=test1_point_get_id_test1

  1   2   3   4   5   6   7   8   9   10   >