That’s our database up and running, and it will sit there until we hit ctrl + c in the terminal to kill the session. You can then spin this up with docker-compose up: ( 'david lee', 29, 35000) Code language: JavaScript ( javascript ) Insert into employees (name, age, salary) values Here’s the corresponding init.sql file: create table employees ( This allows you to run any SQL scripts in that file when the container is started up, which is very useful for initialising the database schema and loading our demo data. init.sql” to the container directory “ /docker-entrypoint-initdb.d/init.sql“. The volumes section maps the local file “. The environment variables should hopefully be self explanatory, but they are defined by the official postgres docker image, and I am using the explicit config above to override them to my own values. This allows you to access the database from your PC. The service exposes port 5494 on the host machine (your local machine), which is mapped to the default Postgres port 5432 in the container. The service runs a Postgres database version 15.2 using the official Postgres Docker image. This docker compose file defines a single service named “db”. These contents go into a docker-compose.yaml file. "./init.sql:/docker-entrypoint-initdb.d/init.sql" Code language: JavaScript ( javascript ) POSTGRES_PASSWORD: my_top_secret_password For this I will use docker-compose: version: '3.9' services: I’m going to use Docker to quickly spin up a throwaway Postgres DB. I needed an example and to have a play about. The final else clause is the default value if none of the other conditions are hit. This is a bit like a switch statement in JavaScript, allowing you to specify multiple conditions and the result you want for each. Here’s some basic syntax that broadly explains the concept: select case when condition1 then result1Įnd from table_name Code language: SQL (Structured Query Language) ( sql ) statement allows you to create conditional expressions within a select statement. This should be enough to understand what it does, and to give you a place to dive in further if needed. However, it doesn’t matter as I have created a repeatable example that demonstrates select case. And I figured I would document my findings for my future reference.įor obvious reasons, I cannot share the true query. However on my current project I was faced with a query where I had to understand it, so I went about setting up a little local demo file to figure out how to use case in a select query in Postgres. How can I compare the description of tableA or tableB against the master_table in a CASE statement? When attempting the above, I am getting an error column schema_z.master_table.I’ve come across the select case syntax in Postgres before, but never had to figure out what it does. Usually table schema_z.table1 has the record for specific filters, but others the value has to be extracted from one of the other tables (schema_a.tableA, schema_b.tableB) that are a match for the master_Table in the description. The other tables are using either the code or description of the catalog table. Where schema_z.master_table is the table saving the codes and description catalog. WHEN schema_a.scription = schema_z.master_scription or schema_b.scription = schema_z.master_scription THEN schema_z.master_de::character varying Is there a way to not hard code the description and code in the case statement, and be able to do something like this? CASE WHEN 'Admin'::text THEN 'ADM'::character varyingĮLSE COALESCE(schema_a.scription, schema_b.scription) WHEN 'Social Worker'::text THEN 'SW'::character varying WHEN schema_z.table1.type_code IS NULL THENĬASE COALESCE(schema_a.scription, schema_b.scription) Currently I have to do something like this to get a correct result: CASE I'd like to use field values instead of hard coded values. Wondering if CASE WHEN in postgres can go beyond these kind of examples: SELECT title,
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |