function returns multiple columns as a single column instead of multiple columns

you need to call the function like this: select * from foo(6); which will return something like this: project_id | project_name | project_type | project_description | project_status ———–|————–|————–|———————|—————- 6 | test project | inbound | inbound test | processing it’s a quirk of postgres that it can be called both ways and give you a … Read more

How to convert a postgres database to sqlite

I found this blog entry which guides you to do these steps: Create a dump of the PostgreSQL database. ssh -C pg_dump –data-only –inserts YOUR_DB_NAME > dump.sql Remove/modify the dump. Remove the lines starting with SET Remove the lines starting with SELECT pg_catalog.setval Replace true for ‘t’ Replace false for ‘f’ Add BEGIN; as … Read more

Postgres Npgsql Connection Pooling

Npgsql connection pooling is implemented inside your application process – it has nothing to do with PostgreSQL, which is completely unaware of it. The mechanism is very simple. When you close a pooled connection, instead of physically closing the connection to PostgreSQL the physical connection is kept around idle in memory (in a “pool”). The … Read more

Composite PRIMARY KEY enforces NOT NULL constraints on involved columns

If you need to allow NULL values, use a UNIQUE constraint (or index) instead of a PRIMARY KEY (and add a surrogate PK column – I suggest a serial or IDENTITY column in Postgres 10 or later). Auto increment table column A UNIQUE constraint allows columns to be NULL: CREATE TABLE distributor ( distributor_id GENERATED … Read more

Postgres query optimization (forcing an index scan)

For testing purposes you can force the use of the index by “disabling” sequential scans – best in your current session only: SET enable_seqscan = OFF; Do not use this on a productive server. Details in the manual here. I quoted “disabling”, because you cannot actually disable sequential table scans. But any other available option … Read more

In PostgreSQL, how to insert data with COPY command?

COPY tbl FROM STDIN; is not supported by pgAdmin. You get a plain syntax error because Postgres gets the data as SQL code. Four possible solutions: 1. Use a multi-row INSERT instead: INSERT INTO beer(name, tags, alcohol, brewery, id, brewery_id, image) VALUES (‘Bons Voeux’, ‘blonde’, 9.5, ‘Brasserie Dupont’, 250, 130, ‘generic.png’) , (‘Boerke Blond’, ‘blonde’, … Read more

SQL Sub queries in check constraint

It is not supported to look beyond the current row in a CHECK constraint. says: A check constraint specified as a column constraint should reference that column’s value only, while an expression appearing in a table constraint can reference multiple columns. Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns … Read more

Insert if not exists, else return id in postgresql

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’ … Read more