Styjun

Blood-based alcohol for vampires?

How can solar sailed ships be protected from space debris?

What is the difference between case and adpositions?

what is the meaning of "stock" dilution on the Massive Dev Chart Website?

What instances can be solved today by modern solvers (pure LP)?

Will greasing clutch parts make it softer

"Best practices" for formulating MIPs

Can the word "coexist" be used for more than two things/people/subjects/... etc?

Use real text instead of lipsum in moderncv quote alignment

Birthday girl's casino game

Do I pay more tax when 2 extra payments are in this paypacket

Are the plates of a battery really charged?

Why is the saxophone not common in classical repertoire?

C++20 with u8, char8_t and std::string?

Should I hide my travel history to the UK when I apply for an Australian visa?

What could a Medieval society do with excess animal blood?

What is -(-2,3,4)?

Wrong Output in self defined Quaternionic Multiplication

How come having a Deathly Hallow is not a big deal?

When should we use dependency injection (C#)

How can I get a file's size with C++17?

Do I need to be legally qualified to install a Hive smart thermostat?

Can I deep fry food in butter instead of vegetable oil?

Has there ever been a cold war other than between the U.S. and the U.S.S.R.?

psycopg2.NotSupportedError: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules

Solutions for INSERT OR UPDATE on SQL ServerInsert, on duplicate update in PostgreSQL?Manual inserts on a postgres table with a primary key sequenceInsert into a MySQL table or update if existsPostgresql, update if row with some unique value exists, else insertcreating insert, update rules in postgresql tablesHow to UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?How can the INSERT … ON CONFLICT (id) DO UPDATE… syntax be used with a sequence ID?PostgreSQL 9.5 Insert/Update while partitioning tablemake django not insert certain fields

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

1

When i'm creating a update RULE beside of CONFLICT clause then that error is throwing.

Here my conflict code

 insert_query = "INSERT INTO my_company (id, name, login, logout) VALUES %s
 ON CONFLICT (id) DO NOTHING"

my update RULE

CREATE RULE log_shoelace AS ON UPDATE TO my_company
 WHERE NEW.login <> OLD.login or NEW.logout <> OLD.logout
 DO INSERT INTO my_company VALUES (
 new.id, new.name, new.login, new.logout, new.interval_time, current_date);

my_company table field contains id, name, login. logout, interval_time, today.

if any data is updated then insert those data into same table.
But here i can't use CONFLICT and RULE in the same time. So in this case what can i do?

Thanks.

Table creation and sequence creation for testing:

CREATE SEQUENCE IF NOT EXISTS my_company_id_seq;

CREATE TABLE public.my_company
( id integer NOT NULL DEFAULT nextval('my_company_id_seq'::regclass)
, name character varying(50)
, login time without time zone
, logout time without time zone
, interval_time time without time zone
, today date DEFAULT CURRENT_DATE
, CONSTRAINT my_company_pkey PRIMARY KEY (id) 
);

database postgresql rules insert-update

share|improve this question

edited Mar 25 at 20:51

J Spratt

51444 silver badges1212 bronze badges

asked Mar 25 at 17:55

ProsenjitProsenjit

5122 silver badges1010 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Do you want to insert on conflict or update on conflict? It seems like you're trying to do an INSERT ... ON CONFLICT DO UPDATE. It looks like you don't need a rule at all to me. What are the data types for login and logout? If they're timestamp, it's unlikely they'd ever be equal.
  
  – J Spratt
  Mar 25 at 18:22
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  login and logout both are TIME data type
  
  – Prosenjit
  Mar 25 at 18:23
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  my conflict is for insert and rule is for update
  
  – Prosenjit
  Mar 25 at 18:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  but without creating a RULE how can i insert data into today fields when something will be updated?
  
  – Prosenjit
  Mar 25 at 18:26
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Take a look at this SQLFiddle and let me know if you still think you need a RULE: sqlfiddle.com/#!17/073e9/16. Unless I'm missing something, you get the same results using INSERT ... ON CONFLICT UPDATE. Except there are times where your RULE will fail on a duplicate key violation.
  
  – J Spratt
  Mar 25 at 20:51
  
  

  
  
  
  

 | 
show 5 more comments

0

1

When i'm creating a update RULE beside of CONFLICT clause then that error is throwing.

Here my conflict code

 insert_query = "INSERT INTO my_company (id, name, login, logout) VALUES %s
 ON CONFLICT (id) DO NOTHING"

my update RULE

CREATE RULE log_shoelace AS ON UPDATE TO my_company
 WHERE NEW.login <> OLD.login or NEW.logout <> OLD.logout
 DO INSERT INTO my_company VALUES (
 new.id, new.name, new.login, new.logout, new.interval_time, current_date);

my_company table field contains id, name, login. logout, interval_time, today.

if any data is updated then insert those data into same table.
But here i can't use CONFLICT and RULE in the same time. So in this case what can i do?

Thanks.

Table creation and sequence creation for testing:

CREATE SEQUENCE IF NOT EXISTS my_company_id_seq;

CREATE TABLE public.my_company
( id integer NOT NULL DEFAULT nextval('my_company_id_seq'::regclass)
, name character varying(50)
, login time without time zone
, logout time without time zone
, interval_time time without time zone
, today date DEFAULT CURRENT_DATE
, CONSTRAINT my_company_pkey PRIMARY KEY (id) 
);

database postgresql rules insert-update

share|improve this question

edited Mar 25 at 20:51

J Spratt

51444 silver badges1212 bronze badges

asked Mar 25 at 17:55

ProsenjitProsenjit

5122 silver badges1010 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Do you want to insert on conflict or update on conflict? It seems like you're trying to do an INSERT ... ON CONFLICT DO UPDATE. It looks like you don't need a rule at all to me. What are the data types for login and logout? If they're timestamp, it's unlikely they'd ever be equal.
  
  – J Spratt
  Mar 25 at 18:22
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  login and logout both are TIME data type
  
  – Prosenjit
  Mar 25 at 18:23
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  my conflict is for insert and rule is for update
  
  – Prosenjit
  Mar 25 at 18:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  but without creating a RULE how can i insert data into today fields when something will be updated?
  
  – Prosenjit
  Mar 25 at 18:26
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Take a look at this SQLFiddle and let me know if you still think you need a RULE: sqlfiddle.com/#!17/073e9/16. Unless I'm missing something, you get the same results using INSERT ... ON CONFLICT UPDATE. Except there are times where your RULE will fail on a duplicate key violation.
  
  – J Spratt
  Mar 25 at 20:51
  
  

  
  
  
  

 | 
show 5 more comments

When i'm creating a update RULE beside of CONFLICT clause then that error is throwing.

Here my conflict code

 insert_query = "INSERT INTO my_company (id, name, login, logout) VALUES %s
 ON CONFLICT (id) DO NOTHING"

my update RULE

CREATE RULE log_shoelace AS ON UPDATE TO my_company
 WHERE NEW.login <> OLD.login or NEW.logout <> OLD.logout
 DO INSERT INTO my_company VALUES (
 new.id, new.name, new.login, new.logout, new.interval_time, current_date);

my_company table field contains id, name, login. logout, interval_time, today.

if any data is updated then insert those data into same table.
But here i can't use CONFLICT and RULE in the same time. So in this case what can i do?

Thanks.

Table creation and sequence creation for testing:

CREATE SEQUENCE IF NOT EXISTS my_company_id_seq;

CREATE TABLE public.my_company
( id integer NOT NULL DEFAULT nextval('my_company_id_seq'::regclass)
, name character varying(50)
, login time without time zone
, logout time without time zone
, interval_time time without time zone
, today date DEFAULT CURRENT_DATE
, CONSTRAINT my_company_pkey PRIMARY KEY (id) 
);

database postgresql rules insert-update

share|improve this question

edited Mar 25 at 20:51

J Spratt

51444 silver badges1212 bronze badges

asked Mar 25 at 17:55

ProsenjitProsenjit

5122 silver badges1010 bronze badges

 insert_query = "INSERT INTO my_company (id, name, login, logout) VALUES %s
 ON CONFLICT (id) DO NOTHING"

CREATE RULE log_shoelace AS ON UPDATE TO my_company
 WHERE NEW.login <> OLD.login or NEW.logout <> OLD.logout
 DO INSERT INTO my_company VALUES (
 new.id, new.name, new.login, new.logout, new.interval_time, current_date);

CREATE SEQUENCE IF NOT EXISTS my_company_id_seq;

CREATE TABLE public.my_company
( id integer NOT NULL DEFAULT nextval('my_company_id_seq'::regclass)
, name character varying(50)
, login time without time zone
, logout time without time zone
, interval_time time without time zone
, today date DEFAULT CURRENT_DATE
, CONSTRAINT my_company_pkey PRIMARY KEY (id) 
);

share|improve this question

edited Mar 25 at 20:51

J Spratt

51444 silver badges1212 bronze badges

asked Mar 25 at 17:55

ProsenjitProsenjit

5122 silver badges1010 bronze badges

share|improve this question

edited Mar 25 at 20:51

J Spratt

51444 silver badges1212 bronze badges

asked Mar 25 at 17:55

ProsenjitProsenjit

5122 silver badges1010 bronze badges

edited Mar 25 at 20:51

J Spratt

51444 silver badges1212 bronze badges

edited Mar 25 at 20:51

J Spratt

51444 silver badges1212 bronze badges

asked Mar 25 at 17:55

ProsenjitProsenjit

5122 silver badges1010 bronze badges

asked Mar 25 at 17:55

ProsenjitProsenjit

5122 silver badges1010 bronze badges