SELECT or INSERT a row in one command

by

Have you tried to union it?

Edit – this requires Postgres 9.1:

create table mytable (id serial primary key, other_key varchar not null unique);

WITH new_row AS (
INSERT INTO mytable (other_key)
SELECT 'SOMETHING'
WHERE NOT EXISTS (SELECT * FROM mytable WHERE other_key = 'SOMETHING')
RETURNING *
)
SELECT * FROM new_row
UNION
SELECT * FROM mytable WHERE other_key = 'SOMETHING';

results in:

 id | other_key 
----+-----------
  1 | SOMETHING
(1 row)

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. psql: FATAL: Ident authentication failed for user “postgres”
  3. Use multiple conflict_target in ON CONFLICT clause
  4. PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function
  5. How to find table creation time?
  6. What is the format for the PostgreSQL connection string / URL?
  7. Go and IN clause in Postgres
  8. How to add “on delete cascade” constraints?
  9. Is there a way to disable function overloading in Postgres
  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. Oracle SQL Developer and PostgreSQL
  17. PostgreSQL array of elements that each are a foreign key
  18. How to reset sequence in postgres and fill id column with new data?
  19. Differentiate inserted and updated rows in UPSERT using system columns
  20. Postgres ERROR: could not open file for reading: Permission denied
  21. Custom ORDER BY Explanation
  22. Configure JPA to let PostgreSQL generate the primary key value
  23. ERROR: unterminated quoted string at or near
  24. How to see the CREATE VIEW code for a view in PostgreSQL?
  25. How can I ensure that a materialized view is always up to date?
  26. PostgreSQL: Query has no destination for result data
  27. Importing .csv with timestamp column (dd.mm.yyyy hh.mm.ss) using psql \copy
  28. Convert escaped Unicode character back to actual character in PostgreSQL
  29. Syntax error at end of input in PostgreSQL
  30. Error when pushing data to Heroku: time zone displacement out of range

Leave a Comment