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
I found this blog entry which guides you to do these steps: Create a dump of the PostgreSQL database. ssh -C username@hostname.com 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
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
Converting an int to a serial more or less only means adding a sequence default to the value, so to make it a serial; Pick a starting value for the serial, greater than any existing value in the table SELECT MAX(id)+1 FROM mytable Create a sequence for the serial (tablename_columnname_seq is a good name) CREATE … Read more
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
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
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
It is not supported to look beyond the current row in a CHECK constraint. http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html 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
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
select c.table_schema, c.table_name, c.column_name, pgd.description from pg_catalog.pg_statio_all_tables as st inner join pg_catalog.pg_description pgd on ( pgd.objoid = st.relid ) inner join information_schema.columns c on ( pgd.objsubid = c.ordinal_position and c.table_schema = st.schemaname and c.table_name = st.relname );