I am trying to grant a new user permissions on the current database (whose name I do not know). Basically I want to archive something like this: Any hints how this could be achieved? Answer You cannot combine GRANTs with queries directly, but you may write a small block to achieve that. Since GRANT allows onl…
Tag: postgresql
PostgreSQL conditional ENUM
Let’s say I have a table inventory with columns item_id, category and size. item_id is auto incremented integer (primary key) category is ENUM size is ENUM I want size to be a conditional ENUM. …
Update SQL rows from another table and a static value
I’m attempting to create rows in a temporary table using values from another table as well as static values. The best I could come up with is this snipped of code, using multiple queries. I’m curious …
Find unmatched rows between two tables
Given this setup: CREATE TABLE table1 (column1 text, column2 text); CREATE TABLE table2 (column1 text, column2 text); INSERT INTO table1 VALUES (‘A’, ‘A’) , (‘B’, ‘N’) , (‘C’, ‘C’) , (‘B’, ‘A’)…
Get a list of database tables that contain a specific column field?
How to select all tables that contain a specific column?
How to update each row that shares the same id differently in postgresql?
Hope this isn’t too hard to explain. I need to update the first table with addresses from the second So basically I have mytable1 that has the following columns: id (pkey) | super_id | address …
Best data type for PostgreSQL datatime type
Actually I have: CREATE TABLE public.user( id BIGSERIAL NOT NULL, nick VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, born DATE, joined DATE, tags text[] ); I want to choose best …
Update key of object inside array of objects in jsonb column
I have a jsonb column called data. It is deeply nested and there’s a key which’s value is an array of objects: As you see, there are different forms of “b” key. My goal is to update all rows with “b:” and “b_” keys and set them to “b”. Answer This an…
How to insert data with custom enum type from a csv into an existing PostgreSQL table
I want to insert data from a csv file into an existing table on a PostgreSQL database – let’s call the table automobile. One of my field is a custom enum – let’s call it brand. When I try to import records from a csv file with DataGrip built-in feature I got an error message: Yet in th…
How to aggregate multiple points by two columns and create a LineString / MultiLineString out of them
I have a table called locations that has these rows: id uuid NOT NULL, “deviceId” text COLLATE pg_catalog.”default”, “userId” uuid, “userName” text COLLATE pg_catalog.”default”, “creationDateTime” …