8 votes

Fortnightly Programming Q&A Thread

Automatically posted
Tags: programming, recurring.biweekly

General Programming Q&A thread! Ask any questions about programming, answer the questions of other users, or post suggestions for future threads.

Don't forget to format your code using the triple backticks or tildes: 

Here is my schema:

```sql
CREATE TABLE article_to_warehouse (
  article_id   INTEGER
, warehouse_id INTEGER
)
;
```

How do I add a `UNIQUE` constraint?

4 comments

  1. PapaNachos
    Link
    I use Python a lot for math and analytics, but I have 2 major gaps in my skill set 1)I don't really know much about creating my own custom visualizations and graphics. Like, I'm perfectly happy...

    I use Python a lot for math and analytics, but I have 2 major gaps in my skill set
    1)I don't really know much about creating my own custom visualizations and graphics. Like, I'm perfectly happy using matplotlib or bokeh or whatever else to use prebaked visualizations. But I wanted to make a completely custom animation or graphic to demonstrate a concept I'm basically in the dark. Does anyone know of a good primer on that subject?
    2)The same goes for UI. I tend to leave my parameters as raw variables, or something similar to an .ini file. I would love to move toward something more user friendly, but don't really know where to start.

    I primarily use Jupyter Notebook in Anaconda and would love any tips anyone has to offer

    2 votes
  2. [3]
    Bauke
    Link
    Is there a way in SQL (specifically PostgreSQL, if it matters) to insert a bunch of rows but skip certain ones if a specific column's value from that row already exists in the database?...

    Is there a way in SQL (specifically PostgreSQL, if it matters) to insert a bunch of rows but skip certain ones if a specific column's value from that row already exists in the database? Essentially, I have a bunch of feeds (with title and url values) I want to import, but I want to skip certain ones when the URL already exists.

    I can think of some inefficient ways to do this in the code itself (basically just a loop and see if each URL exists one by one) but I'm curious if there's a simpler/more efficient way through SQL, since I imagine this isn't too rare of a scenario.

    1 vote
    1. [2]
      pvik
      Link Parent
      You could add an unique constraint on the url column and use the ON CONFLICT[1] modifier when INSERTing (You can also perform an UPSERT operation using the ON CONFLICT modifier, i.e. update the...

      You could add an unique constraint on the url column and use the ON CONFLICT[1] modifier when INSERTing
      (You can also perform an UPSERT operation using the ON CONFLICT modifier, i.e. update the row if a row exists already)

      For example:

      Create a dummy table

      postgres=# create table data (id serial primary key, url text unique, title text);
CREATE TABLE

      Insert some initial dummy data

      postgres=# insert into data (url, title) values ('a', 'A'), ('b', 'B');
INSERT 0 2
postgres=# select * from data;
 id | url | title
----+-----+-------
  1 | a   | A
  2 | b   | B
(2 rows)

      Trying to reinsert rows with same URL will fail and error out on the first error.

      postgres=# insert into data (url, title) values ('a', 'A'), ('b', 'B');
ERROR:  duplicate key value violates unique constraint "data_url_key"
DETAIL:  Key (url)=(a) already exists.

      Using ON CONFLICT:

      postgres=# insert into data (url, title) values ('a', 'A'), ('b', 'B') on conflict do nothing;
INSERT 0 0
postgres=# insert into data (url, title) values ('a', 'A'), ('c', 'C') on conflict do nothing;
INSERT 0 1
postgres=# select * from data;
 id | url | title
----+-----+-------
  1 | a   | A
  2 | b   | B
  7 | c   | C
(3 rows)
      2 votes
      1. Bauke
        Link Parent
        That's exactly what I'm looking for, thanks!

        That's exactly what I'm looking for, thanks!

        2 votes