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 * … Read more
You can run the dropdb command from the command line: dropdb ‘database name’ Note that you have to be a superuser or the database owner to be able to drop it. You can also check the pg_stat_activity view to see what type of activity is currently taking place against your database, including all idle processes. … Read more
This would be shorter, faster and safer: CREATE OR REPLACE FUNCTION get_parent_ltree(parent_id bigint, tbl_name regclass , OUT parent_ltree ltree) LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format(‘SELECT l_tree FROM %s WHERE id = $1′, tbl_name) INTO parent_ltree USING parent_id; END $func$; Why? Most importantly, use the USING clause of EXECUTE for parameter values. Don’t convert them … Read more
The main purpose of Docker Compose is to spin up a set of Docker containers, which will then function as independent entities. By default, all containers will have a virtual network connection to all others, though you can change that if you wish; you will get that feature, since you have not specified a custom … Read more
One old trick is using parser for this purpose: postgres=# select e’Telefon\u00ED kontakty’; ?column? ——————- Telefoní kontakty (1 row) CREATE OR REPLACE FUNCTION public.unescape(text) RETURNS text LANGUAGE plpgsql AS $function$ DECLARE result text; BEGIN EXECUTE format(‘SELECT e”%s”’, $1) INTO result; RETURN result; END; $function$ It works, but it is SQL injection vulnerable – so you … Read more
As a workaround to avoid the ? operator, you could create a new operator doing exactly the same. This is the code of the original operator: CREATE OPERATOR ?( PROCEDURE = jsonb_exists, LEFTARG = jsonb, RIGHTARG = text, RESTRICT = contsel, JOIN = contjoinsel); SELECT ‘{“a”:1, “b”:2}’::jsonb ? ‘b’; — true Use a different name, … Read more
As @Pavel explained, it is not simply possible to traverse a record, like you could traverse an array. But there are several ways around it – depending on your exact requirements. Ultimately, since you want to return all values in the same column, you need to cast them to the same type – text is … Read more
Have you tried setting the datestyle setting of the server? SET datestyle=”ISO,DMY”; You are using the psql meta-command \copy, which means the input file is local to the client. But it’s still the server who has to coerce the input to matching data-types. More generally, unlike the psql meta-command \copy which invokes COPY on the … Read more
You can use direct call to a function jsonb_exists_any(). So in your case it would be jsonb_exists_any(u.authorities::jsonb, array[‘ROLE_1’, ‘ROLE_2’])
It is not allowed to install extensions multiple times per database. Quoting the manual on CREATE EXTENSION: Remember that the extension itself is not considered to be within any schema: extensions have unqualified names that must be unique database-wide. But objects belonging to the extension can be within schemas. If you don’t want to include … Read more