Linked Questions

0 votes
0 answers
92 views

Best practice for select or insert into [duplicate]

I was looking for quite some time on whats the best way of selecting or inserting some data. I have two tables: ReportData: report_data_id(pk), source_id(fk), value1; Source: source_id(pk), ...
RockNinja's user avatar
  • 683
4 votes
1 answer
11k views

Create index on very large table with many shared values

I'm looking to create an index on a large table (~50 million rows) on a field with lots of non-unique values. Table schema looks like: Column | Type | Modifiers | Storage | Stats ...
GrantD71's user avatar
  • 141
5 votes
2 answers
4k views

How do I implement insert-if-not-found for transactions at serializable isolation level?

I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following. We have a table called artist with 2 columns, (name, id) where name is the ...
ocharles's user avatar
  • 208
2 votes
2 answers
5k views

Inserting into multiple tables with a foreign compound key

I have these two table that I would like to insert alot of data into. CREATE TABLE IF NOT EXISTS N ( id INT GENERATED ALWAYS AS IDENTITY, name varchar(50) ); CREATE TABLE ...
SomethingsGottaGive's user avatar
0 votes
1 answer
5k views

foreign key violation within trigger on insert of 'new' id in other table as foreign key

Within a trigger I am inserting the id of the table the trigger is assigned to as a foreign key in another table, but the 'new' id doesn't exist yet, so I get a foreign key constraint violation. Here ...
mountainclimber11's user avatar
0 votes
1 answer
9k views

ON CONFLICT DO UPDATE command cannot affect row a second time when trying to pass additional columns from a CTE in postgresql

Based on the SO: https://dba.stackexchange.com/a/46477 answer provided, I have been extending the Function with UPSERT in Postgres 9.5 or later for my own purposes to insert data into multiple ...
Aaron Murray's user avatar
2 votes
1 answer
5k views

How to check other table for value during insert

In PostgreSQL is it possible to check another table's data to see if there is data that fits a certain condition during an INSERT statement? I have attempted to use WHERE NOT EXISTS as I want to not ...
Andre Yonadam's user avatar
0 votes
1 answer
3k views

Inserting multiple values into multiple tables with generated foreign keys

Let's say I have the following schema: CREATE TABLE foo ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, content text NOT NULL ) CREATE TABLE bar ( id integer PRIMARY KEY GENERATED ALWAYS ...
cfreksen's user avatar
2 votes
0 answers
2k views

postgresql Insert into table B based on foreign key in table A, if exists in A else

the scenario is as follows: I have an invoice and supplier table. The invoice references supplier on supplier_id. I want to insert into invoice. If supplier_company does not exist in the supplier ...
butteredtoast's user avatar
0 votes
2 answers
2k views

mysql - insert multiple values sharing same foreign key

I have the following tables: create table countries ( country_id tinyint unsigned, iso_alpha3_code varchar(4) ); create table provinces ( province_id bigint unsigned, province_name ...
user avatar
7 votes
1 answer
514 views

Deduplicate SELECT statements in relational division

I have a query that does a lot of duplicate work: SELECT visitor_id, '1'::text AS filter FROM events WHERE id IN (SELECT event_id FROM params WHERE key = 'utm_campaign' AND value ...
Sam's user avatar
  • 637
0 votes
1 answer
446 views

Insert a group of consistent item with foreign key but colliding with existing items

Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database. For example, given a ...
hokkos's user avatar
  • 101
1 vote
1 answer
369 views

UPDATE n:m relation in view as array (operations)

Environment I came up with a view and trigger based solution to modify rows in multiple underlying tables in a Postgres 9.5.3 database. This works reasonable using Qt's model view programming. The ...
maxik's user avatar
  • 255
1 vote
2 answers
218 views

Insert New Foreign Key Row for new Strings

Lets say I have a table new_data like this: ndx | someval | somemachine serial | integer | integer ---------------------------- And let's say I have a reference table new_data_someval_ref: ...
anthropomo's user avatar
0 votes
1 answer
450 views

Searching against a large list of people at the same time in Postgres

I have a list of people that varies between 2-5k entries that has this general format: firstname1 lastname1, firstname2 lastname2, firstname3 lastname3 ... I have a user_table which contains about 250-...
thesofaking's user avatar

15 30 50 per page