Insert if not exists, else return id in postgresql

by

Yes there is returning

INSERT INTO tag ("key", "value")
SELECT 'key1', 'value1'
WHERE NOT EXISTS (
    SELECT id, "key", "value"
    FROM node_tag
    WHERE key = 'key1' AND value="value1"
    )
returning id, "key", "value"

To return the row if it already exists

with s as (
    select id, "key", "value"
    from tag
    where key = 'key1' and value="value1"
), i as (
    insert into tag ("key", "value")
    select 'key1', 'value1'
    where not exists (select 1 from s)
    returning id, "key", "value"
)
select id, "key", "value"
from i
union all
select id, "key", "value"
from s

If the row does not exist it will return the inserted one else the existing one.

BTW, if the pair “key”https://stackoverflow.com/”value” makes it unique then it is the primary key, and there is no need for an id column. Unless one or both of the “key”https://stackoverflow.com/”value” pair can be null.

More Related Contents:

  1. What is PostgreSQL equivalent to autoincrement?
  2. How do I specify a password to ‘psql’ non-interactively?
  3. INSERT with dynamic table name in trigger function
  4. psql: FATAL: Ident authentication failed for user “postgres”
  5. What is the format for the PostgreSQL connection string / URL?
  6. Go and IN clause in Postgres
  7. Where does PostgreSQL store the database?
  8. Is there a way to disable function overloading in Postgres
  9. Grant all on a specific schema in the db to a group role in PostgreSQL
  10. Can I make a plpgsql function return an integer without using a variable?
  11. Migrate database from Postgres to MySQL [closed]
  12. How can I start PostgreSQL on Windows?
  13. PostgreSQL – max number of parameters in “IN” clause?
  14. SQLAlchemy, Psycopg2 and Postgresql COPY
  15. How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
  16. Why can I create a table with PRIMARY KEY on a nullable column?
  17. Test for null in function with varying parameters
  18. PostgreSQL: Why psql can’t connect to server?
  19. Postgres error on insert – ERROR: invalid byte sequence for encoding “UTF8”: 0x00
  20. Can I use return value of INSERT…RETURNING in another INSERT?
  21. Changing a postgres containers server port in Docker Compose
  22. How do you find the disk size of a Postgres / PostgreSQL table and its indexes
  23. no pg_hba.conf entry for host
  24. PostgreSQL – disabling constraints
  25. PostgreSQL authentication method 10 not supported
  26. How to delete an enum type value in postgres?
  27. Why unsigned integer is not available in PostgreSQL?
  28. now() default values are all showing same timestamp
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment