Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <ul> <li>Your first <strong>big mistake</strong>:</li> </ul> <blockquote> <p>We are using both SQLite3 (locally/testing) and PostgreSQL (deployment).</p> </blockquote> <p>This is begging for trouble. You will keep running into minor incompatibilities. Or not even notice them until much later, when damage is done. <strong>Don't do it.</strong> Use PostgreSQL locally, too. It's freely available for most every OS. For someone involved in a "databases course project" this is a surprising folly.</p> <ul> <li><p>In PostgreSQL use a <a href="https://stackoverflow.com/questions/9875223/auto-increment-sql-function/9875517#9875517"><code>serial</code> column</a> instead of SQLite <code>AUTOINCREMENT</code>.<br> Use <a href="http://www.postgresql.org/docs/current/interactive/datatype-datetime.html" rel="nofollow noreferrer"><code>timestamp</code> (or <code>timestamptz</code>)</a> instead of <code>datetime</code>.</p></li> <li><p><a href="https://stackoverflow.com/questions/9831377/postgresql-trigger-exception/10001394#10001394">Don't use mixed case identifiers</a>.</p></li> <li><p>Don't use non-descriptive column names like <code>id</code>. Ever. That's an anti-pattern introduced by half-wit middleware and ORMs. When you join a couple of tables you end up with multiple columns of the name <code>id</code>. That's actively hurtful.</p></li> <li><p>There are many naming styles, but most agree it's better to have singular terms as table names. It's shorter and at least as intuitive / logical. <code>label</code>, not <code>labels</code>. </p></li> <li><p>As <a href="https://stackoverflow.com/questions/15582023/foreign-key-contraints-in-many-to-many-relationships#comment22091625_15582023">@Priidu mentioned in the comments</a>, your foreign key constraints are backwards. This is not up for debate, they are <strong>simply wrong</strong>.</p></li> </ul> <p>Everything put together, it could look like this:</p> <p></p></p> <pre><code>CREATE TABLE IF NOT EXISTS post ( post_id serial PRIMARY KEY ,author_id integer ,title text ,content text ,image_url text ,date timestamp ); CREATE TABLE IF NOT EXISTS label ( label_id serial PRIMARY KEY ,name text UNIQUE ); CREATE TABLE IF NOT EXISTS label_post( post_id integer REFERENCES post(post_id) ON UPDATE CASCADE ON DELETE CASCADE ,label_id integer REFERENCES label(label_id) ON UPDATE CASCADE ON DELETE CASCADE ,PRIMARY KEY (post_id, label_id) ); </code></pre> <h3>Trigger</h3> <ul> <li>To delete unused labels, implement a <strong>trigger</strong>. I supply another version since I am not happy with <a href="https://stackoverflow.com/a/15582391/939860">the one provided by @Priidu</a>:</li> </ul> <p></p></p> <pre><code>CREATE OR REPLACE FUNCTION f_trg_kill_orphaned_label() RETURNS TRIGGER AS $func$ BEGIN DELETE FROM label WHERE label_id = OLD.label_id AND NOT EXISTS ( SELECT 1 FROM label_post WHERE label_id = OLD.label_id ); END $func$ LANGUAGE plpgsql; </code></pre> <ul> <li><p>trigger function must be created <em>before</em> the trigger.</p></li> <li><p>A simple <code>DELETE</code> command can do the job. No second query needed - in particular no <code>count(*)</code>. <code>EXISTS</code> is cheaper.</p></li> <li><p>No single-quotes around <code>plpgsql</code>. It's an identifier, not a value!</p></li> </ul> <p></p></p> <pre><code>CREATE TRIGGER label_post_delaft_kill_orphaned_label AFTER DELETE ON label_post FOR EACH ROW EXECUTE PROCEDURE f_trg_kill_orphaned_label(); </code></pre> <p>There is no <code>CREATE OR REPLACE TRIGGER</code> in PostgreSQL, yet. <a href="http://www.postgresql.org/docs/current/interactive/sql-createtrigger.html" rel="nofollow noreferrer">Just <code>CREATE TRIGGER</code></a>.</p>
    singulars
    1. This table or related slice is empty.
    1. POForeign key contraints in many-to-many relationships
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USErwin Brandstetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POForeign key contraints in many-to-many relationships
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou should prefer Erwin's implementation of the trigger, as he does everything in one atomic statement which is always good (not only for being faster).
      singulars
      1. PO
      1. USPriidu Neemre
    2. CO@Priidu .. also minimizing the chance for a race condition. :)
      singulars
      1. PO
      1. USErwin Brandstetter
    3. COWow, that's a great lot of good info! I really appreciate your insight, I didn't get to receive practical advices like that from class.
      singulars
      1. PO
      1. USAntoineG
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload