postgresql

PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values

by

When you are upserting a row (PostgreSQL >= 9.5), and you want the possible INSERT to be exactly the same as the possible UPDATE, you can write it like this: INSERT INTO tablename (id, username, password, level, email) VALUES (1, ‘John’, ‘qwerty’, 5, ‘[email protected]’) ON CONFLICT (id) DO UPDATE SET id=EXCLUDED.id, username=EXCLUDED.username, password=EXCLUDED.password, level=EXCLUDED.level,email=EXCLUDED.email Is … Read more

Why does PostgreSQL perform sequential scan on indexed column?

by

Very simple example – one table, one index, one query: CREATE TABLE book ( id bigserial NOT NULL, “year” integer, — other columns… ); CREATE INDEX book_year_idx ON book (year) EXPLAIN SELECT * FROM book b WHERE b.year > 2009 gives me: Seq Scan on book b (cost=0.00..25663.80 rows=105425 width=622) Filter: (year > 2009) Why … Read more

Are PostgreSQL column names case-sensitive?

by

I have a db table say, persons in Postgres handed down by another team that has a column name say, “first_Name”. Now am trying to use PG commander to query this table on this column-name. select * from persons where first_Name=”xyz”; And it just returns ERROR: column “first_Name” does not exist Not sure if I … Read more

How to configure postgresql for the first time?

by

I have just installed postgresql and I specified password x during installation. When I try to do createdb and specify any password I get the message: createdb: could not connect to database postgres: FATAL: password authentication failed for user Same for createuser. How should I start? Can I add myself as a user to the … Read more

Connect to a heroku database with pgadmin

by

I would like to manage my Heroku database with pgadmin client. By now, I’ve been doing this with psql. When I use data from heroku pg:credentials to connect de DB using pgadmin, I obtain: An error has occurred: Error connecting to the server: FATAL: permission denied for database “postgres” DETAIL: User does not have CONNECT … Read more

How to perform update operations on columns of type JSONB in Postgres 9.4

by

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns. Documentation for JSONB types and functions: http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html As an examples, I have this basic table structure: CREATE TABLE test(id serial, data jsonb); Inserting is easy, as in: INSERT INTO test(data) … Read more