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 so, how can you find your records? The idea is to have a surrogate key to save you from real world troubles, and then constraints to implement the database design. I usually use this convention: - primary surrogate keys named pk and defined as primary keys - database design keys named _key and defined with a unique constraint. Luca -- 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] 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
argument provides the definition of all reachable tables (by mean of
search_path) that are not belonging to the information schema or toast
space, that is:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f'
THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
This kind of queries are hard-coded into the psql program, and
therefore cannot be altered on the fly as far as I know.
One trick could be to define a custom query as a psql variable, let's say:
\set my_d '* from pg_class left join ';
and then do something like
select :my_d;
It's shorter, but it is not the same as a builtin command.
Luca
--
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] 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 crossing the two defining tables. Unless you take your PK down there is > no way that can happen, and even if it does, a cartesian product defining > how many languages a user speaks does not look like needing more than > killing doubles. So what would be the rationale for investing process into > this? You are probably right: you are like to never refactor this kind of design, and this situation using a surrogate key is useless. But what happens if your language is no more uniquely identified by lpp_language_id? Suppose you need to track also the language version and therefore a language is identified by the couple (id, version). In this case you have to refactor two tables: the language one and the person-language join table. Having a surrogate key on both sides allows you to smoothly add such constraint without having to refactor the latter table and ensuring all previous joins still work. Ok, not a really smart example, but the only one that comes into my mind at the moment. Luca -- 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] 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 UNION SELECT number + 1 FROM months WHERE number < 12 ) SELECT m.number, s.id, s.name, count( h.state_id ) FROM state s JOIN history h ON s.id = h.state_id JOIN months m ON m.number = date_part( 'month', h.ts ) Luca -- 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] 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. However I made a few simple tests with etags and it seems to work with plpgsql. However I would expect it to fail for some advanced features. Luca -- 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] 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 test. I've done the same test using the etags (from ctags) and it works too, so now the important thing is to make vim using the tags file and see what is missing. Luca -- 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] 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 xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent') || chr(10) || 'hello world' Luca -- 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] 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, only the insert statement should be executed There is nothing that allows a trigger to distinguish such kind of invocation. However there is a different kind of approach that comes into my mind and that I would try: within the trigger test for a specific temporary table and a value into it. In the case the trigger is invoked "directly" (1) the table will not be created, while in the case of a trigger chain the other trigger is responsible for populating the temp table (and the current trigger to truncate it). Of course a drawback of this approach is that you need to create and populate the temp table on each trigger of an higher level in the call chain. Luca -- 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] 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 trigger also for an insert, which of course does not have an old value. You should either set the trigger to run only on update statements or enforce your check to see if the trigger has been invoked for something different than insert statements. Luca -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] delete on cascade
Hi all, I guess this is an already asked question, but I didn't found an answer, so apologize me. Imagine I've got two tables: skill(id,description) // primary key => id family(id,description) // primary key => id and I want to associate skills to families: ass_sf(id_skill,id_family) // appropriate foreign keys Tables are already created and the database is running. Now I'd like to implement a delete cascade, thus when I delete a skill also its association with the family must be deleted. I gues I should have declared the skill table as follows: CREATE TABLE skill ( id varchar(20) on delete cascade, description varchar(50), primary key(id) ); right? The problem is: how can I alter the table to add the column constraint now, without redeclaring the table? Thanks very much for helping me. Luca ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] delete on cascade
On Monday 23 October 2006 11:00 Achilleas Mantzios's cat, walking on the keyboard, wrote: > not right!!! > ON DELETE CASCADE is specified in FOREIGN KEY contsraints. > So that would be in ass_sf table. > If you find ALTER TABLE ass_sf command hard to run, then drob your > ass_sf table and define it like > Sorry, but this would define that when I delete a row on ass_sf also the skin and the family will be deleted, right? I would like to get the opposite behaviour: when I delete a skill also its association on ass_sf will be deleted. Is this possible? Thanks, Luca ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] hiding column values for specific rows
Hi, I don't know if this's possible but I'd like to hide column values for specific rows within a query. Imagine I've got a table with columns username and password: users(username,password). Now I'd like the user registered in the table to see her password, to see who is registered but not to see the other people passwords. For example, if the table contains: username | password --+- luca| myPaswd roberto | otherPaswd gianna| thirdPaswd I'd like to do a query like: "SELECT * FROM users where username=luca" obtaining something like: username | password --+- luca| myPaswd roberto | x gianna| X Is it possible to specify in the above query that all password columns that do not belong to selected row (username=luca) must be hidden? Anyone has an idea about how to implement this on the database side? Thanks, Luca ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] sql problem with join
Hi all, I've got a problem tryng to define a view with a few joins, I'll appreciate if someone could drive me in the definition of such query. I've got a table roleSkill that contains a row for each skill belonging to a defined role and with the desired competence level for such skill in such role: roleSkill = (id_role, id_skill, desired_level) PRIMARY KEY(id_role,id_skill) Then I've got a table peopleSkill with a row for each evaluated skill for a person (a skill in this case could not belong to a defined role): peopleSkill = (id_person,id_skill, evaluated_level) PRIMARY KEY(id_person,id_skill) Finally I've got an association between a person and a role: peopleRole = (id_person,id_role) PRIMARY KEY(id_person,id_role) Now I'd like to build a view that shows a row for each skill a person has (i.e. it has been evaluated) and should have (i.e. it is listed in the role). Initially I tried with something like: select p.*,r.* from roleSkill r JOIN peopleRole p on p.id_role=r.id_role /* this gives me all the roles a person has and all her skills */ LEFT JOIN peopleSkill s on s.id_skill = r.id_skill /* this should keep all the role skills and show the one evaluated */ So the first join should give me all the role played from a person, with its skills, the second join should take the evaluated skills and should keep the not evaluated (i.e., present only in roleSkill) rows. But this is not working, I see a lot of rows with the same role for the same person and I cannot find the error. Any clues? Thanks, Luca ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] sql problem with join
On Wednesday 15 November 2006 16:20 your cat, walking on the keyboard, wrote: > May be you could show the results you are getting and then make up some > results that you would really like to get. I am not entirely clear what > you are trying to achieve. Lastly I found the solution, for the moment, that should be the following: select * from peopleRole pr left join roleSkill rs on rs.id_role = pr.id_role left join peopleSkill ps on ps.id_skill = rs.id_skill I think it should go, don't you think? Thanks, Luca ---(end of broadcast)--- TIP 6: explain analyze is your friend
