Tag: postgresql

Getting Foreign Keys as separate columns

i’m not really much of an expert when it comes to databases and i was wondering if it’s possible to do something which might be a little weird I’m having a simple table called options which has 3 …

Postgres 10 sql How can I do distinct without all columns

postgresql sql

I have this following query that gets the closest cities/town to a certain coordinate that is passed in . It will return a max of 5 records and I only want to do a distinct on city and state . In the …

Include parameters in posgresql assert message

assert postgresql sql

Is there a way to include parameters in PostgreSQL ASSERT messages. Example: do $$ declare c integer; begin c := (select count(*) from pg_database); assert c = 7, ‘not 7!’; assert c …

Postgres Type of Parameter does not match

postgresql postgresql-10 sql

I am having an odd problem with Postgres (10.5). I have a function, generate_unique_name which takes in three text values. It works fine; however, calling this function seems to be an issue. When I …

Generically identify the rowid causing a constraint conflict in SQLite

check-constraints constraints postgresql sql sqlite

I need to identify the row that caused a constraint check to fail after an INSERT OR IGNORE statement. I need this for a tool that generates SQL statements, so I can’t tie this to a specific use case …

Returning multiple result sets from postgres: refcursors, functions or new features?

postgresql sql

Premise: An entity has many children and grandchildren (gists, versions and files respectively, in this instance). When it (gist) is requested, we want its children (versions) and grandchildren (files for versions) as well. Here I am really pushing to do as much in SQL as possible, instead of composing severa…